`scripts/build-pitch-html.py`

180 lines
#!/usr/bin/env python3
"""Build pitch HTML for doctor paid-ad targets. Pure data transform, no LLM."""
import csv, json, os, sys, subprocess
from datetime import datetime
from collections import Counter

ROOT = os.path.expanduser('~/Projects/professional-directory')
CSV_PATH = f'{ROOT}/exports/doctor-paid-ads-targets-2026-05-05.csv'
OUT_PATH = f'{ROOT}/exports/doctor-sales-pitch-2026-05-05.html'

REFRESH_SQL = """
SELECT o.id, o.name, COALESCE(o.type,'') AS org_type, COALESCE(o.city,'') AS city,
       COALESCE(o.state::text,'CA') AS state, COALESCE(o.website,'') AS website,
       COALESCE((oe.ad_signals->>'paid_ads_count')::int,0) AS paid_pixels,
       (oe.ad_signals->>'google_ads')::boolean AS google_ads,
       (oe.ad_signals->>'meta_pixel')::boolean AS meta,
       (oe.ad_signals->>'tiktok_pixel')::boolean AS tiktok,
       (oe.ad_signals->>'bing_uet')::boolean AS bing,
       (oe.ad_signals->>'reddit_pixel')::boolean AS reddit,
       (oe.ad_signals->>'pinterest_tag')::boolean AS pinterest,
       CASE WHEN (oe.ad_signals->>'paid_ads_count')::int >= 3 THEN 'TIER1_HEAVY'
            WHEN (oe.ad_signals->>'paid_ads_count')::int = 2 THEN 'TIER2_MODERATE'
            ELSE 'TIER3_SINGLE' END AS tier
FROM organizations o JOIN org_enrichment oe ON oe.organization_id = o.id
WHERE (oe.ad_signals->>'paid_ads_count')::int > 0
ORDER BY (oe.ad_signals->>'paid_ads_count')::int DESC, o.name
"""

if '--refresh-csv' in sys.argv or not os.path.exists(CSV_PATH):
    with open(CSV_PATH, 'w') as f:
        subprocess.run(['psql','-d','doctor_professional_directory','-A','-F,','--csv','-c',REFRESH_SQL], stdout=f, check=True)

with open(CSV_PATH) as f:
    rows = list(csv.DictReader(f))

PLATFORM_LABELS = {
    'google_ads': ('G','Google Ads','#4285f4'),
    'meta': ('M','Meta','#1877f2'),
    'tiktok': ('TT','TikTok','#ff0050'),
    'bing': ('B','Bing','#008373'),
    'reddit': ('R','Reddit','#ff4500'),
    'pinterest': ('P','Pinterest','#e60023'),
}

def chips(r):
    out = []
    for p,(lbl,full,col) in PLATFORM_LABELS.items():
        if r.get(p) == 't':
            out.append(f'<span class="chip" style="background:{col}" title="{full}">{lbl}</span>')
    return ''.join(out)

def plats(r): return [PLATFORM_LABELS[p][1] for p in PLATFORM_LABELS if r.get(p) == 't']

tier_counts = Counter(r['tier'] for r in rows)
type_counts = Counter(r['org_type'] for r in rows if r['org_type']).most_common(8)

data = [{'id':r['id'],'name':r['name'],'org_type':r['org_type'],'city':r['city'],
         'state':r['state'],'website':r['website'],'paid_pixels':int(r['paid_pixels']),
         'tier':r['tier'],'platforms':plats(r),'platforms_html':chips(r)} for r in rows]

generated = datetime.now().strftime('%Y-%m-%d %H:%M PT')
total = len(rows)

HTML = f"""<!doctype html>
<html lang="en"><head><meta charset="utf-8">
<title>Healthcare/Wellness — Paid-Ad Targets — {total}</title>
<script>(function(){{const t=localStorage.getItem('theme')||'dark';document.documentElement.setAttribute('data-theme',t);}})();</script>
<style>
:root[data-theme="dark"]{{--bg:#0a0a0a;--fg:#e8e8e8;--muted:#888;--accent:#0ea5e9;--card:#161616;--border:#2a2a2a;--banner:#1a2a3a;--banner-fg:#88ccff}}
:root[data-theme="light"]{{--bg:#fafafa;--fg:#1a1a1a;--muted:#666;--accent:#0369a1;--card:#fff;--border:#ddd;--banner:#dbeafe;--banner-fg:#1e3a5f}}
*{{box-sizing:border-box}}body{{margin:0;font-family:system-ui,-apple-system,sans-serif;background:var(--bg);color:var(--fg);font-size:13px}}
.banner{{background:var(--banner);color:var(--banner-fg);padding:10px 20px;font-weight:600;border-bottom:2px solid var(--banner-fg);display:flex;justify-content:space-between;align-items:center;font-size:13px}}
.hd{{padding:18px 20px;border-bottom:1px solid var(--border);display:flex;justify-content:space-between;align-items:flex-start;gap:20px;flex-wrap:wrap}}
h1{{margin:0 0 4px;font-size:22px}}.sub{{color:var(--muted);font-size:12px}}
.theme-tog{{background:none;border:1px solid var(--border);color:var(--fg);padding:6px 12px;border-radius:4px;cursor:pointer}}
.stats{{padding:14px 20px;display:flex;gap:24px;flex-wrap:wrap;border-bottom:1px solid var(--border);background:var(--card)}}
.stat .v{{font-size:22px;font-weight:600;color:var(--accent)}}.stat .l{{font-size:11px;color:var(--muted);text-transform:uppercase;letter-spacing:0.5px}}
.controls{{padding:12px 20px;display:flex;gap:12px;align-items:center;flex-wrap:wrap;border-bottom:1px solid var(--border)}}
.controls input[type=text]{{flex:1;min-width:200px;padding:8px;background:var(--bg);color:var(--fg);border:1px solid var(--border);border-radius:4px}}
.controls label{{font-size:12px;display:flex;align-items:center;gap:4px;cursor:pointer}}
.main{{display:grid;grid-template-columns:1fr;gap:0}}.main.with-panel{{grid-template-columns:1fr 380px}}
table{{width:100%;border-collapse:collapse;font-size:12px}}
thead th{{text-align:left;padding:8px 10px;background:var(--card);border-bottom:1px solid var(--border);cursor:pointer;position:sticky;top:0}}
thead th:hover{{color:var(--accent)}}tbody td{{padding:8px 10px;border-bottom:1px solid var(--border)}}tbody tr:hover{{background:var(--card)}}
.tier-TIER1_HEAVY{{display:inline-block;padding:2px 6px;border-radius:3px;font-size:10px;font-weight:600;background:#1a3a5a;color:#88ccff}}
.tier-TIER2_MODERATE{{display:inline-block;padding:2px 6px;border-radius:3px;font-size:10px;font-weight:600;background:#3d3a00;color:#dddd80}}
.tier-TIER3_SINGLE{{display:inline-block;padding:2px 6px;border-radius:3px;font-size:10px;font-weight:600;background:#333;color:#aaa}}
:root[data-theme="light"] .tier-TIER1_HEAVY{{background:#cfe7ff;color:#1a3a5a}}
:root[data-theme="light"] .tier-TIER2_MODERATE{{background:#f5f5cc;color:#5a5a00}}
:root[data-theme="light"] .tier-TIER3_SINGLE{{background:#eee;color:#444}}
.chip{{display:inline-block;padding:2px 5px;border-radius:3px;font-size:10px;font-weight:600;color:#fff;margin-right:3px}}
.btn-pitch{{background:var(--accent);color:#fff;border:none;padding:5px 10px;border-radius:3px;cursor:pointer;font-size:11px;font-weight:600}}
a{{color:var(--accent);text-decoration:none}}a:hover{{text-decoration:underline}}
.panel{{background:var(--card);border-left:1px solid var(--border);padding:18px;height:calc(100vh - 200px);overflow-y:auto}}
.panel h2{{margin:0 0 8px}}.panel .pitch-script{{background:var(--bg);padding:12px;border-radius:4px;border:1px solid var(--border);white-space:pre-wrap;font-size:12px;line-height:1.5}}
.panel .close{{float:right;background:none;border:none;color:var(--muted);cursor:pointer;font-size:18px}}
.compliance{{padding:12px 20px;background:var(--card);border-top:1px solid var(--border);font-size:11px;color:var(--muted);line-height:1.6}}
</style></head><body>
<div class="banner">
  <span>🏥 INTERNAL — Healthcare & Wellness Sales Targeting — Not For Public Distribution</span>
  <span style="font-size:11px;opacity:0.8">Generated {generated}</span>
</div>
<div class="hd">
  <div><h1>Healthcare & Wellness — Paid-Ad Pixel Detection</h1>
  <div class="sub">{total} healthcare/wellness orgs with at least one tracking pixel · salons, chiropractic, dental, TCM, beauty</div></div>
  <button class="theme-tog" onclick="toggleTheme()" id="themeBtn">☀ light</button>
</div>
<div class="stats">
  <div class="stat"><div class="v">{total}</div><div class="l">Total</div></div>
  <div class="stat"><div class="v" style="color:#88ccff">{tier_counts.get('TIER1_HEAVY',0)}</div><div class="l">TIER1 (3+)</div></div>
  <div class="stat"><div class="v" style="color:#dddd80">{tier_counts.get('TIER2_MODERATE',0)}</div><div class="l">TIER2 (2)</div></div>
  <div class="stat"><div class="v">{tier_counts.get('TIER3_SINGLE',0)}</div><div class="l">TIER3 (1)</div></div>
</div>
<div class="controls">
  <input type="text" id="filter" placeholder="Filter by name, type, or city…" oninput="render()">
  <label><input type="checkbox" data-tier="TIER1_HEAVY" checked onchange="render()"> TIER1</label>
  <label><input type="checkbox" data-tier="TIER2_MODERATE" checked onchange="render()"> TIER2</label>
  <label><input type="checkbox" data-tier="TIER3_SINGLE" checked onchange="render()"> TIER3</label>
  <span id="visible-count" style="margin-left:auto;color:var(--muted);font-size:11px"></span>
</div>
<div class="main" id="main">
  <div style="overflow-x:auto"><table><thead><tr>
    <th onclick="sortBy('name')">Organization</th><th onclick="sortBy('org_type')">Type</th>
    <th onclick="sortBy('city')">City</th><th onclick="sortBy('tier')">Tier</th>
    <th onclick="sortBy('paid_pixels')">Pixels</th><th>Platforms</th>
    <th>Website</th><th></th>
  </tr></thead><tbody id="tbody"></tbody></table></div>
  <div class="panel" id="panel" style="display:none"></div>
</div>
<div class="compliance">Source: tracking-pixel detection on public org websites · ad-signals enrichment Pass 1 · {generated}. Heuristic detection — no spend claims. Internal directory enrichment data only.</div>
<script>
const DATA = {json.dumps(data)};
let sortKey='paid_pixels',sortAsc=false;
function toggleTheme(){{const c=document.documentElement.getAttribute('data-theme');const n=c==='dark'?'light':'dark';document.documentElement.setAttribute('data-theme',n);localStorage.setItem('theme',n);document.getElementById('themeBtn').textContent=n==='dark'?'☀ light':'☾ dark';}}
document.getElementById('themeBtn').textContent=document.documentElement.getAttribute('data-theme')==='dark'?'☀ light':'☾ dark';
function sortBy(k){{if(sortKey===k)sortAsc=!sortAsc;else{{sortKey=k;sortAsc=false;}}render();}}
function eh(s){{return String(s).replace(/[&<>\"']/g,c=>({{'&':'&amp;','<':'&lt;','>':'&gt;','\"':'&quot;',\"'\":'&#39;'}})[c]);}}
function render(){{
  const q=document.getElementById('filter').value.toLowerCase();
  const tiers=new Set([...document.querySelectorAll('input[data-tier]:checked')].map(i=>i.dataset.tier));
  const f=DATA.filter(r=>tiers.has(r.tier)&&(!q||r.name.toLowerCase().includes(q)||r.org_type.toLowerCase().includes(q)||r.city.toLowerCase().includes(q)));
  f.sort((a,b)=>{{const va=a[sortKey],vb=b[sortKey];let r=(typeof va==='number')?(va-vb):String(va).localeCompare(String(vb));return sortAsc?r:-r;}});
  document.getElementById('tbody').innerHTML=f.map(r=>`<tr>
    <td><strong>${{eh(r.name)}}</strong></td><td>${{eh(r.org_type)}}</td>
    <td>${{eh(r.city)}}, ${{eh(r.state)}}</td><td><span class="tier-${{r.tier}}">${{r.tier.replace(/_/g,' ')}}</span></td>
    <td>${{r.paid_pixels}}</td><td>${{r.platforms_html}}</td>
    <td>${{r.website?`<a href="${{eh(r.website)}}" target="_blank">${{eh(r.website.replace(/^https?:\\/\\//,'').slice(0,30))}}</a>`:''}}</td>
    <td><button class="btn-pitch" onclick='openPitch("${{r.id}}")'>🏥 Pitch</button></td>
  </tr>`).join('');
  document.getElementById('visible-count').textContent=`${{f.length}} of ${{DATA.length}}`;
}}
function openPitch(id){{
  const r=DATA.find(x=>x.id===id);if(!r)return;
  document.getElementById('main').classList.add('with-panel');
  const panel=document.getElementById('panel');panel.style.display='block';
  const platList=r.platforms.length?r.platforms.join(', '):'(none)';
  panel.innerHTML=`<button class="close" onclick="closePitch()">×</button>
    <h2>${{eh(r.name)}}</h2>
    <div style="font-size:11px;color:var(--muted);margin-bottom:12px">${{eh(r.org_type)}} · ${{eh(r.city)}}, ${{eh(r.state)}} · <span class="tier-${{r.tier}}">${{r.tier.replace(/_/g,' ')}}</span></div>
    <div style="margin-bottom:14px">${{r.platforms_html}}</div>
    <div class="pitch-script">Hi ${{eh(r.name)}} team —

We're building a directory of healthcare and wellness businesses across CA and noticed your site is running tracking pixels for ${{r.platforms.length}} ad platform${{r.platforms.length!==1?'s':''}} (${{platList}}).

We're talking with practices already investing in their digital channels to compare notes — what's working in your channel mix, what intake-form conversion looks like, and where attribution is breaking down.

10 minutes to swap notes? Happy to share what we're seeing across the cohort.

— [Steve]

[CRIB] platforms: ${{platList}} · pixels: ${{r.paid_pixels}} · website: ${{eh(r.website||'N/A')}} · NO spend claims, peer-cohort framing only.</div>`;
}}
function closePitch(){{document.getElementById('main').classList.remove('with-panel');document.getElementById('panel').style.display='none';}}
render();
</script></body></html>"""

with open(OUT_PATH,'w') as f:
    f.write(HTML)
print(f'Wrote {OUT_PATH} · {len(HTML):,} bytes · {total} orgs')