analytics: add guest-topics word-frequency cloud + ad airing history table (search-all/sortable/column-toggle/CSV-export/reset, per-airing deep-links)

39e94f3a573b1fa0d8c40014f34ca9c63eabbbf4 · 2026-08-07 11:26:25 -0700 · Steve Abrams

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M public/charts.html

Diff

commit 39e94f3a573b1fa0d8c40014f34ca9c63eabbbf4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Aug 7 11:26:25 2026 -0700

    analytics: add guest-topics word-frequency cloud + ad airing history table (search-all/sortable/column-toggle/CSV-export/reset, per-airing deep-links)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 public/charts.html | 73 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 73 insertions(+)

diff --git a/public/charts.html b/public/charts.html
index f6af919..6b67880 100644
--- a/public/charts.html
+++ b/public/charts.html
@@ -77,6 +77,21 @@
     <div class="panel" id="guestSplit"></div>
     <div class="panel" id="guestCos"></div>
   </div>
+
+  <h2>Guest topics <span class="muted" style="text-transform:none;font-weight:400">— what guests talked about (size = frequency)</span></h2>
+  <div class="panel" id="topics"></div>
+
+  <h2>Ad airing history
+    <span class="muted" style="text-transform:none;font-weight:400">— every detected spot; click ▶ to jump, click a header to sort</span></h2>
+  <div class="panel">
+    <div style="display:flex;gap:10px;align-items:center;flex-wrap:wrap;margin-bottom:10px">
+      <input id="tq" placeholder="Search airings (space = AND)…" style="flex:1;min-width:220px;background:#0e1116;border:1px solid var(--line);color:var(--ink);border-radius:8px;padding:7px 10px">
+      <span id="cols" style="display:flex;gap:6px;flex-wrap:wrap"></span>
+      <a id="csv" style="cursor:pointer">⬇ CSV</a>
+      <a id="reset" style="cursor:pointer">Reset</a>
+    </div>
+    <div style="max-height:520px;overflow:auto"><table id="airtbl"></table></div>
+  </div>
 </main>
 
 <script>
@@ -113,6 +128,8 @@ async function boot(){
   renderTrends(clients.slice(0,8),airings,rv);
   renderRevenue(clients,airings);
   renderGuests(guests);
+  renderTopics(guests);
+  renderAiringTable(airings,rv);
 }
 
 // ── LIQUID FILL GAUGES (animated wave) ──
@@ -287,6 +304,62 @@ function renderGuests(guests){
       <div class="bar-val">${v}</div></div>`).join("");
 }
 
+// ── GUEST TOPICS word-frequency cloud ──
+function renderTopics(guests){
+  const freq={};
+  guests.forEach(g=>(g.topics||[]).forEach(t=>{const k=String(t).trim().toLowerCase();if(k.length>2)freq[k]=(freq[k]||0)+1;}));
+  const items=Object.entries(freq).sort((a,b)=>b[1]-a[1]).slice(0,60);
+  if(!items.length){$("#topics").innerHTML='<div class="muted">No guest topics extracted.</div>';return;}
+  const max=Math.max(...items.map(i=>i[1])),min=Math.min(...items.map(i=>i[1]));
+  const el=$("#topics"); el.innerHTML='<div style="display:flex;flex-wrap:wrap;gap:8px 14px;align-items:baseline;line-height:1.7"></div>';
+  const box=el.firstChild;
+  items.forEach(([w,n],i)=>{
+    const sz=13+((n-min)/((max-min)||1))*26;
+    const s=document.createElement("span");
+    s.textContent=w; s.title=`${n} guest${n>1?"s":""}`;
+    s.style.cssText=`font-size:${sz.toFixed(0)}px;color:${PAL[i%PAL.length]};font-weight:${sz>26?700:500};cursor:default`;
+    box.appendChild(s);
+  });
+}
+
+// ── AD AIRING HISTORY table (search-all + sortable + toggle cols + CSV + reset) ──
+function renderAiringTable(airings,rv){
+  const meta={};Object.entries(rv||{}).forEach(([vid,r])=>{if(r.ep!=null)meta[vid]=r.ep;});
+  const rows=airings.map(a=>({time:a.timecode,start:a.start,client:a.advertiser,
+    episode:(a.episode_title||"").replace(/RENTV |CRE Talk /g,"").slice(0,34),ep:meta[a.episode_id]??9999,
+    copy:(a.spoken_copy||"").slice(0,140),deeplink:a.deeplink}));
+  const COLS=[["time","Time"],["client","Client"],["episode","Episode"],["copy","Spoken copy"]];
+  const LS=(k,d)=>{try{return JSON.parse(localStorage.getItem("rae_air_"+k))??d}catch{return d}};
+  const setLS=(k,v)=>localStorage.setItem("rae_air_"+k,JSON.stringify(v));
+  let sort=LS("sort",{key:"episode",dir:1}), vis=LS("vis",{time:1,client:1,episode:1,copy:1});
+  const esc2=s=>(s==null?"":String(s)).replace(/[&<>"]/g,m=>({"&":"&amp;","<":"&lt;",">":"&gt;",'"':"&quot;"}[m]));
+  function draw(){
+    const toks=$("#tq").value.toLowerCase().split(/\s+/).filter(Boolean);
+    let L=rows.filter(r=>{const h=(r.client+" "+r.episode+" "+r.copy+" "+r.time).toLowerCase();return toks.every(t=>h.includes(t));});
+    const k=sort.key;
+    L.sort((a,b)=>{let x=a[k],y=b[k];
+      if(k==="time"){x=a.start;y=b.start}if(k==="episode"){x=a.ep;y=b.ep}
+      return (x>y?1:x<y?-1:0)*sort.dir;});
+    const cols=COLS.filter(c=>vis[c[0]]);
+    const th=cols.map(([kk,lab])=>`<th data-k="${kk}" style="position:sticky;top:0;background:var(--panel);cursor:pointer;padding:8px 10px;border-bottom:1px solid var(--line);text-align:left;white-space:nowrap">${lab}${sort.key===kk?(sort.dir>0?" ▲":" ▼"):""}</th>`).join("");
+    const body=L.map(r=>`<tr>${cols.map(([kk])=>{
+      if(kk==="time")return `<td style="padding:6px 10px;border-bottom:1px solid var(--line)"><a href="${esc2(r.deeplink)}" target="_blank" rel="noopener" class="mailbtn" style="text-decoration:none;padding:3px 8px">▶ ${esc2(r.time)}</a></td>`;
+      if(kk==="copy")return `<td style="padding:6px 10px;border-bottom:1px solid var(--line);color:var(--dim);max-width:520px">${esc2(r[kk])}</td>`;
+      return `<td style="padding:6px 10px;border-bottom:1px solid var(--line)">${esc2(r[kk])}</td>`;}).join("")}</tr>`).join("");
+    $("#airtbl").innerHTML=`<thead><tr>${th}</tr></thead><tbody>${body||'<tr><td style="padding:14px;color:var(--dim)">No matches</td></tr>'}</tbody>`;
+    $("#airtbl").querySelectorAll("th").forEach(el=>el.onclick=()=>{const kk=el.dataset.k;sort=(sort.key===kk)?{key:kk,dir:-sort.dir}:{key:kk,dir:1};setLS("sort",sort);draw();});
+    $("#cols").innerHTML=COLS.map(([kk,lab])=>`<label style="font-size:12px;color:var(--dim);display:inline-flex;gap:4px;align-items:center"><input type="checkbox" data-k="${kk}" ${vis[kk]?"checked":""}>${lab}</label>`).join("");
+    $("#cols").querySelectorAll("input").forEach(cb=>cb.onchange=()=>{vis[cb.dataset.k]=cb.checked?1:0;setLS("vis",vis);draw();});
+    $("#csv").onclick=()=>{const cs=COLS.filter(c=>vis[c[0]]);const head=cs.map(c=>c[1]).join(",");
+      const lines=L.map(r=>cs.map(([kk])=>`"${String(kk==="time"?r.time:r[kk]).replace(/"/g,'""')}"`).join(","));
+      const blob=new Blob([head+"\n"+lines.join("\n")],{type:"text/csv"});const u=URL.createObjectURL(blob);
+      const a=document.createElement("a");a.href=u;a.download="rentv-ad-airings.csv";a.click();URL.revokeObjectURL(u);};
+  }
+  $("#tq").value=LS("q","");$("#tq").oninput=()=>{setLS("q",$("#tq").value);draw();};
+  $("#reset").onclick=()=>{["sort","vis","q"].forEach(k=>localStorage.removeItem("rae_air_"+k));sort={key:"episode",dir:1};vis={time:1,client:1,episode:1,copy:1};$("#tq").value="";draw();};
+  draw();
+}
+
 boot();
 </script>
 </body>

← d81a056 analytics page: add per-client trend sparklines (rising/easi · back to Rentv Ad Engine · analytics: per-client airing-history CSV (one download per a 59cc977 →