DW Chat Traffic Analyzer: pull.py (Zendesk chats->realestate.dw_chats, metadata+traffic+lead fields, no transcripts) + dashboard/leads viewer (newest-first, NEW+LEAD badges, per-row Zendesk link)

f52cdb635bb8b8f21431f22802c76a54e8ae900d · 2026-08-11 07:42:00 -0700 · steve
Files touched

A .cursor
A .gitignore
A package-lock.json
A package.json
A public/index.html
A pull.py
A server.js
Diff

commit f52cdb635bb8b8f21431f22802c76a54e8ae900d
Author: steve <steve@designerwallcoverings.com>
Date:   Tue Aug 11 07:42:00 2026 -0700

    DW Chat Traffic Analyzer: pull.py (Zendesk chats->realestate.dw_chats, metadata+traffic+lead fields, no transcripts) + dashboard/leads viewer (newest-first, NEW+LEAD badges, per-row Zendesk link)
---
 .cursor           |   1 +
 .gitignore        |   5 ++
 package-lock.json | 161 ++++++++++++++++++++++++++++++++++++++++++++++++++
 package.json      |   1 +
 public/index.html | 127 +++++++++++++++++++++++++++++++++++++++
 pull.py           | 173 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 server.js         |  77 ++++++++++++++++++++++++
 7 files changed, 545 insertions(+)

diff --git a/.cursor b/.cursor
new file mode 100644
index 0000000..b625b8d
--- /dev/null
+++ b/.cursor
@@ -0,0 +1 @@
+https://www.zopim.com/api/v2/chats?cursor=eyJjb3VudCI6IDM4ODcwLCAiaWQiOiAiMjMwNC4xNzM3OTU3OS5UYU5sUjZpc3NLT3J3IiwgInByZXZfcmVmX3N0YXJ0IjogIjIzMDQuMTczNzk1NzkuVGIzSVpQdjV1bUN0SSJ9
\ No newline at end of file
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1a3d517
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,5 @@
+node_modules/
+.env*
+*.log
+*.tsv
+.DS_Store
diff --git a/package-lock.json b/package-lock.json
new file mode 100644
index 0000000..4fbe07d
--- /dev/null
+++ b/package-lock.json
@@ -0,0 +1,161 @@
+{
+  "name": "dw-chat-analyzer",
+  "version": "0.1.0",
+  "lockfileVersion": 3,
+  "requires": true,
+  "packages": {
+    "": {
+      "name": "dw-chat-analyzer",
+      "version": "0.1.0",
+      "dependencies": {
+        "pg": "^8.23.0"
+      }
+    },
+    "node_modules/pg": {
+      "version": "8.23.0",
+      "resolved": "https://registry.npmjs.org/pg/-/pg-8.23.0.tgz",
+      "integrity": "sha512-Ip2EQCngowJLGOfCwkFhPXU7/ljlhn6Rxlmy4XYfL2Y+vyRM59+8uR2xqRWKdYmbXmxCFOAmKxBuSUCdF34qLg==",
+      "license": "MIT",
+      "dependencies": {
+        "pg-connection-string": "^2.14.0",
+        "pg-pool": "^3.14.0",
+        "pg-protocol": "^1.16.0",
+        "pg-types": "2.2.0",
+        "pgpass": "1.0.5"
+      },
+      "engines": {
+        "node": ">= 16.0.0"
+      },
+      "optionalDependencies": {
+        "pg-cloudflare": "^1.4.0"
+      },
+      "peerDependencies": {
+        "pg-native": ">=3.0.1"
+      },
+      "peerDependenciesMeta": {
+        "pg-native": {
+          "optional": true
+        }
+      }
+    },
+    "node_modules/pg-cloudflare": {
+      "version": "1.4.0",
+      "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.4.0.tgz",
+      "integrity": "sha512-Vo7z/6rrQYxpNRylp4Tlob2elzbh+N/MOQbxFVWCxS7oEx6jF53GTJFxK2WWpKuBRkmiin4Mt+xofFDjx09R0A==",
+      "license": "MIT",
+      "optional": true
+    },
+    "node_modules/pg-connection-string": {
+      "version": "2.14.0",
+      "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.14.0.tgz",
+      "integrity": "sha512-XwWDGcLRGCXAR8F/AM5bG7Q+A3Wm2s6QeEjlOKZLlH3UYcguiqCWKyWXVag5TLTIjR7oOJUY8kcADaZgWPyLeg==",
+      "license": "MIT"
+    },
+    "node_modules/pg-int8": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+      "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+      "license": "ISC",
+      "engines": {
+        "node": ">=4.0.0"
+      }
+    },
+    "node_modules/pg-pool": {
+      "version": "3.14.0",
+      "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.14.0.tgz",
+      "integrity": "sha512-gKtPkFdQPU3DksooVLi9LsjZxrsBUZIpa+7aVx+LV5pNh0KzP4Zleud2po+ConrxbuXGBJ6Hfer6hdgpIBpBaw==",
+      "license": "MIT",
+      "peerDependencies": {
+        "pg": ">=8.0"
+      }
+    },
+    "node_modules/pg-protocol": {
+      "version": "1.16.0",
+      "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.16.0.tgz",
+      "integrity": "sha512-sILXutLVjCLjcDuOmvhX5e2Z4cS5qG/6Bu3VkpFwdf/633ElGLpEh9bgmuI5I4sqKqkifQiGyiCcx1HdtrK7tg==",
+      "license": "MIT"
+    },
+    "node_modules/pg-types": {
+      "version": "2.2.0",
+      "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+      "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+      "license": "MIT",
+      "dependencies": {
+        "pg-int8": "1.0.1",
+        "postgres-array": "~2.0.0",
+        "postgres-bytea": "~1.0.0",
+        "postgres-date": "~1.0.4",
+        "postgres-interval": "^1.1.0"
+      },
+      "engines": {
+        "node": ">=4"
+      }
+    },
+    "node_modules/pgpass": {
+      "version": "1.0.5",
+      "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+      "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+      "license": "MIT",
+      "dependencies": {
+        "split2": "^4.1.0"
+      }
+    },
+    "node_modules/postgres-array": {
+      "version": "2.0.0",
+      "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+      "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=4"
+      }
+    },
+    "node_modules/postgres-bytea": {
+      "version": "1.0.1",
+      "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+      "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/postgres-date": {
+      "version": "1.0.7",
+      "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+      "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/postgres-interval": {
+      "version": "1.2.0",
+      "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+      "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+      "license": "MIT",
+      "dependencies": {
+        "xtend": "^4.0.0"
+      },
+      "engines": {
+        "node": ">=0.10.0"
+      }
+    },
+    "node_modules/split2": {
+      "version": "4.2.0",
+      "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+      "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+      "license": "ISC",
+      "engines": {
+        "node": ">= 10.x"
+      }
+    },
+    "node_modules/xtend": {
+      "version": "4.0.2",
+      "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+      "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+      "license": "MIT",
+      "engines": {
+        "node": ">=0.4"
+      }
+    }
+  }
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..fc266d3
--- /dev/null
+++ b/package.json
@@ -0,0 +1 @@
+{"name":"dw-chat-analyzer","version":"0.1.0","private":true,"description":"DW Chat Traffic Analyzer — dashboard + leads + export over realestate.dw_chats","main":"server.js","scripts":{"start":"node server.js"},"dependencies":{"pg":"^8.23.0"}}
\ No newline at end of file
diff --git a/public/index.html b/public/index.html
new file mode 100644
index 0000000..c543396
--- /dev/null
+++ b/public/index.html
@@ -0,0 +1,127 @@
+<!doctype html>
+<html lang="en"><head>
+<meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
+<title>DW Chat Traffic Analyzer</title>
+<style>
+  :root{--bg:#0e1116;--panel:#161b22;--line:#232b36;--ink:#e6edf3;--dim:#8b98a8;--accent:#3fae9a;--lead:#e0a35f;--new:#5fd0a0;--rowh:34px;--fs:13px}
+  *{box-sizing:border-box}
+  body{margin:0;font:14px/1.4 -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,sans-serif;background:var(--bg);color:var(--ink)}
+  header{padding:14px 18px;border-bottom:1px solid var(--line);display:flex;align-items:baseline;gap:16px;flex-wrap:wrap}
+  header h1{font-size:16px;margin:0;font-weight:650}
+  .tabs{display:flex;gap:4px;margin-left:8px}
+  .tab{padding:5px 12px;border-radius:7px;cursor:pointer;color:var(--dim);font-size:13px;border:1px solid transparent}
+  .tab.on{background:var(--panel);color:var(--ink);border-color:var(--line)}
+  .sub{color:var(--dim);font-size:12px;margin-left:auto}
+  .wrap{padding:16px 18px}
+  /* dashboard */
+  .cards{display:flex;gap:12px;flex-wrap:wrap;margin-bottom:16px}
+  .card{background:var(--panel);border:1px solid var(--line);border-radius:10px;padding:14px 18px;min-width:150px}
+  .card .k{font-size:11px;color:var(--dim);text-transform:uppercase;letter-spacing:.5px}
+  .card .v{font-size:26px;font-weight:700;margin-top:4px}
+  .card.lead .v{color:var(--lead)} .card.new .v{color:var(--new)}
+  .grid2{display:grid;grid-template-columns:repeat(auto-fit,minmax(280px,1fr));gap:14px}
+  .box{background:var(--panel);border:1px solid var(--line);border-radius:10px;padding:12px 14px}
+  .box h3{margin:0 0 8px;font-size:12px;color:var(--dim);text-transform:uppercase;letter-spacing:.5px}
+  .row{display:flex;justify-content:space-between;gap:10px;padding:3px 0;font-size:13px;border-bottom:1px solid #1b222c}
+  .row:last-child{border:0} .row .n{color:var(--accent);font-variant-numeric:tabular-nums}
+  .bar{height:8px;background:#1b222c;border-radius:4px;overflow:hidden;margin-top:2px}.bar>i{display:block;height:100%;background:var(--accent)}
+  /* grid */
+  .bar2{display:flex;gap:10px;align-items:center;flex-wrap:wrap;padding:12px 18px;border-bottom:1px solid var(--line);position:sticky;top:0;background:var(--bg);z-index:5}
+  input,select{background:var(--panel);color:var(--ink);border:1px solid var(--line);border-radius:7px;padding:7px 9px;font-size:13px}
+  input#q{min-width:280px}
+  .chk{display:flex;align-items:center;gap:6px;color:var(--dim);font-size:12px}
+  .count{color:var(--dim);font-size:12px}.count b{color:var(--ink)}
+  .tblwrap{overflow:auto;height:calc(100vh - 118px)}
+  table{border-collapse:collapse;width:100%;font-size:var(--fs)}
+  thead th{position:sticky;top:0;background:var(--panel);border-bottom:1px solid var(--line);text-align:left;padding:8px 10px;white-space:nowrap;cursor:pointer;font-weight:600;color:#cdd6e0}
+  tbody td{border-bottom:1px solid var(--line);padding:0 10px;height:var(--rowh);white-space:nowrap;max-width:320px;overflow:hidden;text-overflow:ellipsis}
+  tbody tr:hover{background:#131922}
+  .badge{display:inline-block;padding:1px 6px;border-radius:9px;font-size:10px;font-weight:600}
+  .b-new{background:#12321f;color:var(--new)} .b-lead{background:#3a2c17;color:var(--lead)}
+  a.zd{color:#6cb7ff;text-decoration:none}a.zd:hover{text-decoration:underline}
+  .muted{color:var(--dim)} .pager{display:flex;gap:8px;align-items:center;padding:9px 18px;border-top:1px solid var(--line)}
+  .pager button{background:var(--panel);border:1px solid var(--line);color:var(--ink);border-radius:7px;padding:6px 12px;cursor:pointer}
+  .pager button:disabled{opacity:.4} .hidden{display:none}
+</style></head>
+<body>
+<header>
+  <h1>DW Chat Traffic Analyzer</h1>
+  <div class="tabs"><span class="tab on" data-t="dash">Dashboard</span><span class="tab" data-t="chats">Chats &amp; Leads</span></div>
+  <span class="sub" id="hsub">Zendesk Chat · designerwallcoverings.com</span>
+</header>
+
+<!-- DASHBOARD -->
+<div class="wrap" id="dash">
+  <div class="cards">
+    <div class="card"><div class="k">Total chats</div><div class="v" id="c-total">…</div></div>
+    <div class="card lead"><div class="k">Leads (intent/contact)</div><div class="v" id="c-leads">…</div></div>
+    <div class="card new"><div class="k">New (last 48h)</div><div class="v" id="c-new">…</div></div>
+  </div>
+  <div class="grid2">
+    <div class="box"><h3>Top search terms (how they found DW)</h3><div id="b-terms"></div></div>
+    <div class="box"><h3>Top landing pages</h3><div id="b-land"></div></div>
+    <div class="box"><h3>Referrer engines</h3><div id="b-eng"></div></div>
+    <div class="box"><h3>Department</h3><div id="b-dept"></div></div>
+    <div class="box"><h3>Visitor country</h3><div id="b-geo"></div></div>
+    <div class="box"><h3>Ratings</h3><div id="b-rating"></div></div>
+    <div class="box" style="grid-column:1/-1"><h3>Chats per day (recent)</h3><div id="b-day"></div></div>
+  </div>
+</div>
+
+<!-- CHATS -->
+<div class="hidden" id="chats">
+  <div class="bar2">
+    <input id="q" placeholder="Search intent, search terms, landing, visitor, tags…" autocomplete="off">
+    <label class="chk"><input type="checkbox" id="f-lead"> leads only</label>
+    <label class="chk"><input type="checkbox" id="f-new"> new (48h)</label>
+    <select id="department"></select>
+    <span class="count" id="ccount">…</span>
+  </div>
+  <div class="tblwrap"><table><thead><tr id="head"></tr></thead><tbody id="rows"></tbody></table></div>
+  <div class="pager"><button id="prev">‹ Prev</button><span class="count">Page <b id="page">1</b> / <b id="pages">1</b></span><button id="next">Next ›</button><span class="muted" id="range"></span></div>
+</div>
+
+<script>
+const $=id=>document.getElementById(id);
+function esc(s){return(s==null?'':String(s)).replace(/[&<>"]/g,c=>({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;'}[c]));}
+function bars(el,list){const mx=Math.max(1,...list.map(r=>r.n));$(el).innerHTML=list.map(r=>`<div class="row"><span title="${esc(r.v)}" style="overflow:hidden;text-overflow:ellipsis;max-width:78%">${esc(r.v)||'<span class=muted>—</span>'}</span><span class="n">${r.n.toLocaleString()}</span></div><div class="bar"><i style="width:${(r.n/mx*100).toFixed(1)}%"></i></div>`).join('')||'<div class="muted">no data yet</div>';}
+async function loadDash(){const d=await(await fetch('/api/stats')).json();
+  $('c-total').textContent=d.total.toLocaleString();$('c-leads').textContent=d.leads.toLocaleString();$('c-new').textContent=d.newc.toLocaleString();
+  bars('b-terms',d.terms);bars('b-land',d.land);bars('b-eng',d.eng);bars('b-dept',d.dept);bars('b-geo',d.geo);bars('b-rating',d.rating);bars('b-day',(d.byday||[]).slice().reverse());}
+
+// ---- chats grid ----
+const COLS=[['started_at','When'],['flags','⚑'],['visitor_city','Visitor loc'],['ref_terms','Search term'],['landing_page','Landing'],['visitor_intent','Intent (visitor msg)'],['department','Dept'],['rating','Rating'],['zd','Zendesk']];
+const st={q:'',department:'',lead:false,new:false,sort:'started_at',dir:'desc',page:1,limit:50};
+function head(){$('head').innerHTML=COLS.map(([k,l])=>`<th data-k="${k}">${l}${st.sort===k?(st.dir==='asc'?' ▲':' ▼'):''}</th>`).join('');
+  $('head').querySelectorAll('th').forEach(th=>th.onclick=()=>{const k=th.dataset.k;if(k==='flags'||k==='zd')return;if(st.sort===k)st.dir=st.dir==='asc'?'desc':'asc';else{st.sort=k;st.dir='desc';}st.page=1;loadChats();});}
+function fmt(s){if(!s)return'';try{return new Date(s).toLocaleString(undefined,{month:'short',day:'numeric',hour:'numeric',minute:'2-digit'});}catch{return s;}}
+function loc(r){return[r.visitor_city,r.visitor_region,r.visitor_country].filter(Boolean).join(', ');}
+async function loadChats(){const p=new URLSearchParams();if(st.q)p.set('q',st.q);if(st.department)p.set('department',st.department);
+  if(st.lead)p.set('is_lead','true');if(st.new)p.set('new','1');p.set('sort',st.sort);p.set('dir',st.dir);p.set('page',st.page);p.set('limit',st.limit);
+  const d=await(await fetch('/api/chats?'+p)).json();head();
+  $('rows').innerHTML=d.rows.map(r=>`<tr>
+    <td>${fmt(r.started_at)}</td>
+    <td>${r.is_new?'<span class="badge b-new">NEW</span> ':''}${r.is_lead?'<span class="badge b-lead">LEAD</span>':''}</td>
+    <td class="muted">${esc(loc(r))||'—'}</td>
+    <td>${esc(r.ref_terms)||'<span class=muted>—</span>'}</td>
+    <td class="muted" title="${esc(r.landing_page)}">${esc((r.landing_page||'').replace(/^https?:\/\/[^/]+/,''))||'—'}</td>
+    <td title="${esc(r.visitor_intent)}">${esc(r.visitor_intent)||'<span class=muted>—</span>'}</td>
+    <td class="muted">${esc(r.department)||'—'}</td>
+    <td>${esc(r.rating)||'<span class=muted>—</span>'}</td>
+    <td><a class="zd" href="${esc(r.zendesk_link||'https://dashboard.zopim.com/#chats/agent/history')}" target="_blank" rel="noopener">↗ open</a></td>
+  </tr>`).join('');
+  const pages=Math.max(1,Math.ceil(d.total/d.limit)),s=d.total?(d.page-1)*d.limit+1:0,e=Math.min(d.page*d.limit,d.total);
+  $('ccount').innerHTML=`<b>${d.total.toLocaleString()}</b> chats`;$('page').textContent=d.page;$('pages').textContent=pages.toLocaleString();
+  $('range').textContent=d.total?`showing ${s.toLocaleString()}–${e.toLocaleString()}`:'';$('prev').disabled=d.page<=1;$('next').disabled=d.page>=pages;}
+async function fillDept(){const d=await(await fetch('/api/stats')).json();$('department').innerHTML='<option value="">All departments</option>'+d.dept.filter(x=>x.v!=='(none)').map(x=>`<option>${esc(x.v)}</option>`).join('');$('department').onchange=()=>{st.department=$('department').value;st.page=1;loadChats();};}
+
+// tabs
+document.querySelectorAll('.tab').forEach(t=>t.onclick=()=>{document.querySelectorAll('.tab').forEach(x=>x.classList.remove('on'));t.classList.add('on');
+  const c=t.dataset.t==='chats';$('chats').classList.toggle('hidden',!c);$('dash').classList.toggle('hidden',c);if(c)loadChats();else loadDash();});
+let tmr;$('q').oninput=()=>{clearTimeout(tmr);tmr=setTimeout(()=>{st.q=$('q').value.trim();st.page=1;loadChats();},250);};
+$('f-lead').onchange=()=>{st.lead=$('f-lead').checked;st.page=1;loadChats();};
+$('f-new').onchange=()=>{st.new=$('f-new').checked;st.page=1;loadChats();};
+$('prev').onclick=()=>{if(st.page>1){st.page--;loadChats();}};$('next').onclick=()=>{st.page++;loadChats();};
+loadDash();fillDept();
+</script>
+</body></html>
diff --git a/pull.py b/pull.py
new file mode 100644
index 0000000..c34bdc1
--- /dev/null
+++ b/pull.py
@@ -0,0 +1,173 @@
+#!/usr/bin/env python3
+"""
+DW Chat Traffic Analyzer — pull.
+
+Pulls all Zendesk Chat (Zopim) conversations for Designer Wallcoverings via the
+list endpoint (GET /api/v2/chats, 40/page, paginated by next_url), extracts
+traffic + lead metadata (NOT full transcripts — only a trimmed visitor-intent
+snippet), and upserts into realestate.dw_chats keyed by chat id.
+
+Token: ZENDESK_CHAT_ACCESS_TOKEN from the secrets .env. Cost: $0 (Zendesk API
+is included in the plan). Idempotent (upsert on id) + resumable (cursor file).
+
+Usage:
+  python3 pull.py                 # full pull (all ~38.9k)
+  python3 pull.py --max-pages 3   # bounded test
+  python3 pull.py --resume        # continue from saved next_url cursor
+"""
+import os, re, sys, json, time, argparse, subprocess, urllib.request, urllib.error
+
+TOKEN = None
+BASE = "https://www.zopim.com/api/v2/chats"
+PGDB = os.environ.get("PGDATABASE", "realestate")
+PGHOST = os.environ.get("PGHOST", "/tmp")
+HERE = os.path.dirname(os.path.abspath(__file__))
+CURSOR = os.path.join(HERE, ".cursor")
+TABLE = "dw_chats"
+# Standalone Zopim Chat account (no Support subdomain) → link back to the Chat dashboard.
+ZENDESK_LINK_BASE = os.environ.get("ZENDESK_LINK_BASE", "https://dashboard.zopim.com/#chats/agent/history")
+
+LEAD_KW = re.compile(r"\b(quote|price|pricing|cost|buy|purchase|order|sample|swatch|"
+                     r"yard|roll|availab|lead time|trade|designer|install|ship)\b", re.I)
+
+def token():
+    global TOKEN
+    if TOKEN: return TOKEN
+    env = os.path.expanduser("~/Projects/secrets-manager/.env")
+    for line in open(env):
+        if line.startswith("ZENDESK_CHAT_ACCESS_TOKEN="):
+            TOKEN = line.split("=", 1)[1].strip().strip('"').strip("'"); break
+    if not TOKEN: sys.exit("no ZENDESK_CHAT_ACCESS_TOKEN in secrets .env")
+    return TOKEN
+
+def get(url):
+    for attempt in range(6):
+        req = urllib.request.Request(url, headers={"Authorization": "Bearer " + token()})
+        try:
+            with urllib.request.urlopen(req, timeout=60) as r:
+                return json.load(r)
+        except urllib.error.HTTPError as e:
+            if e.code == 429:                       # rate limited — back off
+                wait = int(e.headers.get("Retry-After", 2 ** attempt))
+                time.sleep(min(wait, 30)); continue
+            raise
+    raise SystemExit("too many 429s")
+
+def clean(v):
+    return re.sub(r"[\t\r\n]+", " ", "" if v is None else str(v)).strip()
+
+def visitor_intent(history):
+    """Concat visitor-authored messages only (skip agent), trimmed — for lead intent."""
+    if not isinstance(history, list): return ""
+    msgs = []
+    for h in history:
+        if not isinstance(h, dict): continue
+        if h.get("type") == "chat.msg" and (h.get("sender_type") == "visitor" or h.get("nick", "").startswith("visitor")):
+            m = h.get("msg")
+            if m: msgs.append(m)
+    return clean(" | ".join(msgs))[:500]
+
+def normalize(c):
+    v = c.get("visitor") or {}
+    wp = c.get("webpath") or []
+    def page(i):
+        try: x = wp[i]; return (x.get("to") if isinstance(x, dict) else str(x)) or ""
+        except Exception: return ""
+    intent = visitor_intent(c.get("history"))
+    ref_terms = clean(c.get("referrer_search_terms"))
+    is_lead = bool((v.get("email") or v.get("phone")) or LEAD_KW.search(intent + " " + ref_terms))
+    conv = c.get("conversions")
+    return {
+        "id": clean(c.get("id")),
+        "started_at": clean(c.get("timestamp"))[:19],
+        "ended_at": clean(c.get("end_timestamp"))[:19],
+        "duration": clean(c.get("duration")), "response_time": clean((c.get("response_time") or {}).get("avg") if isinstance(c.get("response_time"), dict) else c.get("response_time")),
+        "department": clean(c.get("department_name")),
+        "agents": clean(", ".join(c.get("agent_names") or [])),
+        "chat_type": clean(c.get("type")), "missed": "t" if c.get("missed") else "f",
+        "rating": clean(c.get("rating")), "comment": clean(c.get("comment"))[:300],
+        "tags": clean(", ".join(c.get("tags") or [])),
+        "conversions": str(len(conv) if isinstance(conv, list) else (conv or 0)),
+        "zendesk_ticket_id": clean(c.get("zendesk_ticket_id")),
+        "ref_engine": clean(c.get("referrer_search_engine")), "ref_terms": ref_terms,
+        "landing_page": clean(page(0))[:200], "exit_page": clean(page(-1))[:200],
+        "page_count": str(len(wp)),
+        "visitor_name": clean(v.get("name")), "visitor_email": clean(v.get("email")),
+        "visitor_phone": clean(v.get("phone")), "visitor_city": clean(v.get("city")),
+        "visitor_region": clean(v.get("region")), "visitor_country": clean(v.get("country")),
+        "visitor_intent": intent, "is_lead": "t" if is_lead else "f",
+        "zendesk_link": ZENDESK_LINK_BASE,
+    }
+
+COLS = ["id","started_at","ended_at","duration","response_time","department","agents","chat_type",
+        "missed","rating","comment","tags","conversions","zendesk_ticket_id","ref_engine","ref_terms",
+        "landing_page","exit_page","page_count","visitor_name","visitor_email","visitor_phone",
+        "visitor_city","visitor_region","visitor_country","visitor_intent","is_lead","zendesk_link"]
+
+DDL = f"""
+CREATE TABLE IF NOT EXISTS {TABLE} (
+  id text PRIMARY KEY, started_at timestamptz, ended_at timestamptz,
+  duration int, response_time int, department text, agents text, chat_type text,
+  missed bool, rating text, comment text, tags text, conversions int, zendesk_ticket_id text,
+  ref_engine text, ref_terms text, landing_page text, exit_page text, page_count int,
+  visitor_name text, visitor_email text, visitor_phone text, visitor_city text,
+  visitor_region text, visitor_country text, visitor_intent text, is_lead bool,
+  zendesk_link text, pulled_at timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_dwchats_started ON {TABLE}(started_at DESC);
+CREATE INDEX IF NOT EXISTS idx_dwchats_lead ON {TABLE}(is_lead);
+"""
+
+def flush(records):
+    if not records: return
+    tsv = os.path.join(HERE, "_batch.tsv")
+    with open(tsv, "w") as f:
+        for r in records:
+            f.write("\t".join(clean(r.get(c, "")) for c in COLS) + "\n")
+    # empty numeric/ts/bool -> NULL via a staging text table, then cast on insert
+    stg_cols = ", ".join(f"{c} text" for c in COLS)
+    sql = f"""{DDL}
+CREATE TEMP TABLE _s ({stg_cols});
+\\copy _s ({','.join(COLS)}) FROM '{tsv}' WITH (FORMAT text, DELIMITER E'\\t');
+INSERT INTO {TABLE} ({','.join(COLS)})
+SELECT id, NULLIF(started_at,'')::timestamptz, NULLIF(ended_at,'')::timestamptz,
+  NULLIF(duration,'')::numeric::int, NULLIF(response_time,'')::numeric::int, department, agents, chat_type,
+  missed::bool, rating, comment, tags, NULLIF(conversions,'')::int, zendesk_ticket_id,
+  ref_engine, ref_terms, landing_page, exit_page, NULLIF(page_count,'')::int,
+  visitor_name, visitor_email, visitor_phone, visitor_city, visitor_region, visitor_country,
+  visitor_intent, is_lead::bool, zendesk_link
+FROM _s
+ON CONFLICT (id) DO UPDATE SET rating=EXCLUDED.rating, comment=EXCLUDED.comment,
+  is_lead=EXCLUDED.is_lead, tags=EXCLUDED.tags, pulled_at=now();
+"""
+    p = subprocess.run(["psql","-h",PGHOST,"-d",PGDB,"-v","ON_ERROR_STOP=1"],
+                       input=sql, text=True, capture_output=True)
+    if p.returncode != 0:
+        sys.stderr.write(p.stderr); sys.exit(1)
+
+def main():
+    ap = argparse.ArgumentParser()
+    ap.add_argument("--max-pages", type=int, default=0)   # 0 = all
+    ap.add_argument("--resume", action="store_true")
+    a = ap.parse_args()
+    url = BASE
+    if a.resume and os.path.exists(CURSOR):
+        url = open(CURSOR).read().strip() or BASE
+    total, page, buf = 0, 0, []
+    while url:
+        d = get(url); page += 1
+        chats = d.get("chats", [])
+        buf.extend(normalize(c) for c in chats)
+        total += len(chats)
+        if page % 25 == 0 or not d.get("next_url"):
+            flush(buf); buf = []
+            open(CURSOR, "w").write(d.get("next_url") or "")
+            print(f"  page {page}: {total} chats loaded (of {d.get('count','?')})", flush=True)
+        url = d.get("next_url")
+        if a.max_pages and page >= a.max_pages: break
+        time.sleep(0.15)                                  # gentle on rate limits
+    flush(buf)
+    print(f"DONE: {total} chats pulled across {page} pages -> {TABLE}")
+
+if __name__ == "__main__":
+    main()
diff --git a/server.js b/server.js
new file mode 100644
index 0000000..93a71c9
--- /dev/null
+++ b/server.js
@@ -0,0 +1,77 @@
+#!/usr/bin/env node
+'use strict';
+/*
+ * dw-chat-analyzer — Dashboard + Leads + export over realestate.dw_chats
+ * (Zendesk Chat traffic for Designer Wallcoverings). Node http + pg (parameterized).
+ * Basic Auth admin / DW2024!  (override VIEWER_USER / VIEWER_PASS).
+ */
+const http = require('http'), fs = require('fs'), path = require('path'), crypto = require('crypto');
+const { Pool } = require('pg');
+const PORT = Number(process.env.PORT || 0), HOST = process.env.HOST || '127.0.0.1';
+const USER = process.env.VIEWER_USER || 'admin', PASS = process.env.VIEWER_PASS || 'DW2024!';
+const pool = new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'realestate', max: 6 });
+pool.on('error', (e) => console.error('pg pool error', e));
+const T = 'dw_chats';
+
+const SORTABLE = new Set(['started_at','duration','department','rating','is_lead','visitor_country','landing_page','ref_terms']);
+const FILTERS = ['department','is_lead','ref_engine','visitor_country'];
+const SEARCH = ['visitor_intent','ref_terms','landing_page','visitor_name','visitor_city','comment','tags'];
+
+const eq = (a,b)=>{const x=Buffer.from(a),y=Buffer.from(b);return x.length===y.length&&crypto.timingSafeEqual(x,y);};
+function authed(req){const h=req.headers.authorization||'';if(!h.startsWith('Basic '))return false;
+  const [u,p]=Buffer.from(h.slice(6),'base64').toString('utf8').split(':');return eq(u||'',USER)&&eq(p||'',PASS);}
+
+function where(qs){const c=[],p=[];
+  for(const col of FILTERS){const v=qs.get(col);if(v!=null&&v!==''){p.push(col==='is_lead'?v==='true':v);c.push(`${col}=$${p.length}`);}}
+  if(qs.get('new')==='1') c.push(`started_at > now() - interval '48 hours'`);
+  const q=(qs.get('q')||'').trim();
+  if(q){p.push(`%${q}%`);const i=p.length;c.push('('+SEARCH.map(s=>`${s} ILIKE $${i}`).join(' OR ')+')');}
+  return {w:c.length?'WHERE '+c.join(' AND '):'',p};}
+
+async function apiChats(qs){
+  const {w,p}=where(qs);
+  let sort=qs.get('sort')||'started_at'; if(!SORTABLE.has(sort))sort='started_at';
+  const dir=(qs.get('dir')||'desc').toLowerCase()==='asc'?'ASC':'DESC';
+  const limit=Math.min(Math.max(parseInt(qs.get('limit'),10)||50,1),200), page=Math.max(parseInt(qs.get('page'),10)||1,1);
+  const total=Number((await pool.query(`SELECT count(*)::bigint n FROM ${T} ${w}`,p)).rows[0].n);
+  const rows=(await pool.query(
+    `SELECT id,started_at,department,agents,rating,is_lead,ref_engine,ref_terms,landing_page,page_count,
+            visitor_name,visitor_city,visitor_region,visitor_country,visitor_intent,comment,tags,duration,
+            zendesk_link, (started_at > now() - interval '48 hours') AS is_new
+       FROM ${T} ${w} ORDER BY ${sort} ${dir} NULLS LAST, started_at DESC
+      LIMIT $${p.length+1} OFFSET $${p.length+2}`,[...p,limit,(page-1)*limit])).rows;
+  return {total,page,limit,sort,dir,rows};
+}
+
+let sc=null,sa=0;
+async function apiStats(){
+  if(sc&&Date.now()-sa<60000)return sc;
+  const one=async(q)=>(await pool.query(q)).rows;
+  const h=(await pool.query(`SELECT count(*)::int total, count(*) filter (where is_lead)::int leads,
+            count(*) filter (where started_at > now()-interval '48 hours')::int newc FROM ${T}`)).rows[0];
+  const tot=h.total, leads=h.leads, newc=h.newc;
+  const terms=await one(`SELECT ref_terms v, count(*)::int n FROM ${T} WHERE ref_terms<>'' GROUP BY 1 ORDER BY n DESC LIMIT 12`);
+  const land=await one(`SELECT regexp_replace(landing_page,'^https?://[^/]+','') v, count(*)::int n FROM ${T} WHERE landing_page<>'' GROUP BY 1 ORDER BY n DESC LIMIT 12`);
+  const eng=await one(`SELECT coalesce(nullif(ref_engine,''),'(direct/none)') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 8`);
+  const dept=await one(`SELECT coalesce(nullif(department,''),'(none)') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 8`);
+  const rating=await one(`SELECT coalesce(nullif(rating,''),'unrated') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC`);
+  const geo=await one(`SELECT coalesce(nullif(visitor_country,''),'?') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 10`);
+  const byday=await one(`SELECT to_char(date_trunc('day',started_at),'YYYY-MM-DD') v, count(*)::int n FROM ${T} WHERE started_at IS NOT NULL GROUP BY 1 ORDER BY 1 DESC LIMIT 30`);
+  return sc={total:tot,leads,newc,terms,land,eng,dept,rating,geo,byday}, sa=Date.now(), sc;
+}
+
+function send(res,code,body,type='application/json'){res.writeHead(code,{'Content-Type':type,'Cache-Control':'no-store'});
+  res.end(typeof body==='string'||Buffer.isBuffer(body)?body:JSON.stringify(body));}
+
+const server=http.createServer(async(req,res)=>{
+  if(!authed(req)){res.writeHead(401,{'WWW-Authenticate':'Basic realm="dw-chat"'});return res.end('Auth required');}
+  const url=new URL(req.url,'http://x');
+  try{
+    if(url.pathname==='/'||url.pathname==='/index.html')return send(res,200,fs.readFileSync(path.join(__dirname,'public','index.html')),'text/html; charset=utf-8');
+    if(url.pathname==='/api/stats')return send(res,200,await apiStats());
+    if(url.pathname==='/api/chats')return send(res,200,await apiChats(url.searchParams));
+    if(url.pathname==='/healthz')return send(res,200,{ok:true});
+    return send(res,404,{error:'not found'});
+  }catch(e){console.error(e);return send(res,500,{error:String(e.message||e)});}
+});
+server.listen(PORT,HOST,()=>console.log(`dw-chat-analyzer live: http://${HOST}:${server.address().port}  (login ${USER})`));
(oldest) · back to Dw Chat Analyzer · auto-data-snapshot: 2026-08-11T08:06:39 (1 data files) — .cu 5f98ada →