← back to Dw Chat Analyzer
Chat analyzer: DTD verdict C — two-tier HOT/WARM lead flagging + 'Act now' (HOT∩recent) view + tier badges. Data note: WARM≈0 (keyword arm sparse), real signal is contact-left; only ~2 HOT are recent (contrarian's recency reframe confirmed)
89c8889a3356c864fb9622b8ef8a06d60959e9fd · 2026-08-11 08:09:19 -0700 · steve
Files touched
M .gitignoreM public/index.htmlM pull.pyM server.js
Diff
commit 89c8889a3356c864fb9622b8ef8a06d60959e9fd
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 11 08:09:19 2026 -0700
Chat analyzer: DTD verdict C — two-tier HOT/WARM lead flagging + 'Act now' (HOT∩recent) view + tier badges. Data note: WARM≈0 (keyword arm sparse), real signal is contact-left; only ~2 HOT are recent (contrarian's recency reframe confirmed)
---
.gitignore | 1 +
public/index.html | 15 +++++++++------
pull.py | 23 +++++++++++++++++------
server.js | 13 ++++++++-----
4 files changed, 35 insertions(+), 17 deletions(-)
diff --git a/.gitignore b/.gitignore
index 1a3d517..8113cb4 100644
--- a/.gitignore
+++ b/.gitignore
@@ -3,3 +3,4 @@ node_modules/
*.log
*.tsv
.DS_Store
+.cursor
diff --git a/public/index.html b/public/index.html
index c543396..94426ac 100644
--- a/public/index.html
+++ b/public/index.html
@@ -54,8 +54,9 @@
<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 class="card lead"><div class="k">HOT leads (contact left)</div><div class="v" id="c-hot">…</div></div>
+ <div class="card new"><div class="k">🔥 Hot & new (act now)</div><div class="v" id="c-hotnew">…</div></div>
+ <div class="card"><div class="k">New chats (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>
@@ -72,8 +73,9 @@
<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-lead"> HOT only</label>
<label class="chk"><input type="checkbox" id="f-new"> new (48h)</label>
+ <button id="actnow" class="pager" style="padding:6px 10px;cursor:pointer" title="HOT leads from the last 48h — the real call-first list">🔥 Act now</button>
<select id="department"></select>
<span class="count" id="ccount">…</span>
</div>
@@ -86,7 +88,7 @@ const $=id=>document.getElementById(id);
function esc(s){return(s==null?'':String(s)).replace(/[&<>"]/g,c=>({'&':'&','<':'<','>':'>','"':'"'}[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();
+ $('c-total').textContent=d.total.toLocaleString();$('c-hot').textContent=(d.hot||0).toLocaleString();$('c-hotnew').textContent=(d.hot_recent||0).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 ----
@@ -97,11 +99,11 @@ function head(){$('head').innerHTML=COLS.map(([k,l])=>`<th data-k="${k}">${l}${s
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);
+ if(st.lead)p.set('lead_tier','HOT');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>${r.is_new?'<span class="badge b-new">NEW</span> ':''}${r.lead_tier==='HOT'?'<span class="badge b-lead">HOT</span>':r.lead_tier==='WARM'?'<span class="badge" style="background:#242c18;color:#a9b06a">WARM</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>
@@ -121,6 +123,7 @@ document.querySelectorAll('.tab').forEach(t=>t.onclick=()=>{document.querySelect
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();};
+$('actnow').onclick=()=>{st.lead=true;st.new=true;st.page=1;$('f-lead').checked=true;$('f-new').checked=true;loadChats();};
$('prev').onclick=()=>{if(st.page>1){st.page--;loadChats();}};$('next').onclick=()=>{st.page++;loadChats();};
loadDash();fillDept();
</script>
diff --git a/pull.py b/pull.py
index c34bdc1..204e593 100644
--- a/pull.py
+++ b/pull.py
@@ -75,7 +75,18 @@ def normalize(c):
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))
+ has_contact = bool(v.get("email") or v.get("phone"))
+ blob = intent + " " + ref_terms
+ is_lead = bool(has_contact or LEAD_KW.search(blob))
+ # Tier (DTD verdict C, 2026-08-11): HOT = contact left OR explicit purchase language
+ # (the call-first list); WARM = keyword-only intent (analysis bucket). Data note: WARM
+ # is near-empty because visitor msg/search-terms are sparse — the real signal is contact.
+ if has_contact or re.search(r"\b(buy|order|purchase|invoice|checkout|place an order|proceed|pay)\b", blob, re.I):
+ lead_tier = "HOT"
+ elif LEAD_KW.search(blob):
+ lead_tier = "WARM"
+ else:
+ lead_tier = ""
conv = c.get("conversions")
return {
"id": clean(c.get("id")),
@@ -95,14 +106,14 @@ def normalize(c):
"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",
+ "visitor_intent": intent, "is_lead": "t" if is_lead else "f", "lead_tier": lead_tier,
"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"]
+ "visitor_city","visitor_region","visitor_country","visitor_intent","is_lead","lead_tier","zendesk_link"]
DDL = f"""
CREATE TABLE IF NOT EXISTS {TABLE} (
@@ -111,7 +122,7 @@ CREATE TABLE IF NOT EXISTS {TABLE} (
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,
+ visitor_region text, visitor_country text, visitor_intent text, is_lead bool, lead_tier text,
zendesk_link text, pulled_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_dwchats_started ON {TABLE}(started_at DESC);
@@ -135,10 +146,10 @@ SELECT id, NULLIF(started_at,'')::timestamptz, NULLIF(ended_at,'')::timestamptz,
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
+ visitor_intent, is_lead::bool, lead_tier, 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();
+ is_lead=EXCLUDED.is_lead, lead_tier=EXCLUDED.lead_tier, 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)
diff --git a/server.js b/server.js
index 93a71c9..bb9d981 100644
--- a/server.js
+++ b/server.js
@@ -14,7 +14,7 @@ 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 FILTERS = ['department','is_lead','lead_tier','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);};
@@ -35,7 +35,7 @@ async function apiChats(qs){
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,
+ `SELECT id,started_at,department,agents,rating,is_lead,lead_tier,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
@@ -47,9 +47,12 @@ 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,
+ const h=(await pool.query(`SELECT count(*)::int total,
+ count(*) filter (where lead_tier='HOT')::int hot,
+ count(*) filter (where lead_tier='WARM')::int warm,
+ count(*) filter (where lead_tier='HOT' and started_at > now()-interval '48 hours')::int hot_recent,
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 tot=h.total, leads=h.hot, hot=h.hot, warm=h.warm, hot_recent=h.hot_recent, 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`);
@@ -57,7 +60,7 @@ async function apiStats(){
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;
+ return sc={total:tot,leads,hot,warm,hot_recent,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'});
← 5f98ada auto-data-snapshot: 2026-08-11T08:06:39 (1 data files) — .cu
·
back to Dw Chat Analyzer
·
Chat analyzer: new-HOT-lead watcher (watch.js) — polls newes ef41f56 →