← back to Lawyer Directory Builder
scripts/build-pitch-html.py
288 lines
#!/usr/bin/env python3
"""Build admin-gated, §6155-compliant pitch HTML from lawyer ad-signals CSV.
Pure data transform — no LLM. Re-runnable: re-pulls fresh CSV from DB.
"""
import csv, html, json, sys, os, subprocess
from datetime import datetime
from collections import Counter
ROOT = os.path.expanduser('~/Projects/lawyer-directory-builder')
CSV_PATH = f'{ROOT}/exports/lawyer-paid-ads-targets-2026-05-05.csv'
OUT_PATH = f'{ROOT}/exports/lawyer-sales-pitch-2026-05-05.html'
# Refresh CSV from DB (so overnight jobs pick up new firms as they crawl in)
REFRESH_SQL = """
SELECT
o.id AS firm_id,
o.name,
COALESCE(o.city, '') AS city,
COALESCE(o.state, '') AS state,
COALESCE(o.website, '') AS website,
COALESCE((fas.ad_signals->>'paid_ads_count')::int, 0) AS paid_pixels,
(fas.ad_signals->>'google_ads')::boolean AS google_ads,
(fas.ad_signals->>'meta_pixel')::boolean AS meta,
(fas.ad_signals->>'tiktok_pixel')::boolean AS tiktok,
(fas.ad_signals->>'linkedin_insight')::boolean AS linkedin,
(fas.ad_signals->>'bing_uet')::boolean AS bing,
(fas.ad_signals->>'twitter_pixel')::boolean AS twitter,
CASE
WHEN (fas.ad_signals->>'paid_ads_count')::int >= 3 THEN 'TIER1_HEAVY'
WHEN (fas.ad_signals->>'paid_ads_count')::int = 2 THEN 'TIER2_MODERATE'
ELSE 'TIER3_SINGLE'
END AS tier
FROM organizations o
JOIN firm_ad_signals fas ON fas.firm_id = o.id
WHERE (fas.ad_signals->>'paid_ads_count')::int > 0
ORDER BY (fas.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', 'lawyer_professional_directory', '-A', '-F,', '--csv', '-c', REFRESH_SQL],
stdout=f, check=True
)
with open(CSV_PATH) as f:
rows = list(csv.DictReader(f))
tier_counts = Counter(r['tier'] for r in rows)
city_counts = Counter(r['city'] for r in rows if r['city']).most_common(5)
plat_counts = {p: sum(1 for r in rows if r[p] == 't')
for p in ['google_ads','meta','tiktok','linkedin','bing','twitter']}
total = len(rows)
PLATFORM_LABELS = {
'google_ads': ('G', 'Google Ads', '#4285f4'),
'meta': ('M', 'Meta', '#1877f2'),
'tiktok': ('TT', 'TikTok', '#ff0050'),
'linkedin': ('LI', 'LinkedIn', '#0a66c2'),
'bing': ('B', 'Bing', '#008373'),
'twitter': ('TW', 'Twitter/X', '#1da1f2'),
}
def chips(r):
out = []
for p, (lbl, full, col) in PLATFORM_LABELS.items():
if r[p] == 't':
out.append(f'<span class="chip" style="background:{col}" title="{full}">{lbl}</span>')
return ''.join(out)
def plats_for_pitch(r):
return [PLATFORM_LABELS[p][1] for p in PLATFORM_LABELS if r[p] == 't']
data = []
for r in rows:
data.append({
'firm_id': r['firm_id'],
'name': r['name'],
'city': r['city'],
'state': r['state'],
'website': r['website'],
'paid_pixels': int(r['paid_pixels']),
'tier': r['tier'],
'platforms': plats_for_pitch(r),
'platforms_html': chips(r),
})
generated = datetime.now().strftime('%Y-%m-%d %H:%M PT')
HTML_OUT = f"""<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CA Law Firms — Paid-Ad Pixel Detection — INTERNAL</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:#f5b800;--card:#161616;--border:#2a2a2a;--banner:#3a1a00;--banner-fg:#ffaa44;}}
:root[data-theme="light"]{{--bg:#fafafa;--fg:#1a1a1a;--muted:#666;--accent:#b08000;--card:#fff;--border:#ddd;--banner:#fff4d6;--banner-fg:#7a4f00;}}
*{{box-sizing:border-box}}
body{{margin:0;font-family:system-ui,-apple-system,BlinkMacSystemFont,sans-serif;background:var(--bg);color:var(--fg);font-size:13px;line-height:1.4}}
.banner{{background:var(--banner);color:var(--banner-fg);padding:10px 20px;font-weight:600;font-size:13px;border-bottom:2px solid var(--banner-fg);display:flex;justify-content:space-between;align-items:center}}
.banner .lock{{font-size:18px}}
.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:20px}}
.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;font-size:14px}}
.theme-tog:hover{{border-color:var(--accent)}}
.stats{{padding:14px 20px;display:flex;gap:24px;flex-wrap:wrap;border-bottom:1px solid var(--border);background:var(--card)}}
.stat{{display:flex;flex-direction:column}}
.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;font-size:13px}}
.controls label{{font-size:12px;cursor:pointer;display:flex;align-items:center;gap:4px}}
.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;user-select:none;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:#5a1a1a;color:#ffaaaa}}
.tier-TIER2_MODERATE{{display:inline-block;padding:2px 6px;border-radius:3px;font-size:10px;font-weight:600;background:#5a3d00;color:#ffd180}}
.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:#ffd6d6;color:#8b0000}}
:root[data-theme="light"] .tier-TIER2_MODERATE{{background:#fff0c2;color:#7a4f00}}
: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:#000;border:none;padding:5px 10px;border-radius:3px;cursor:pointer;font-size:11px;font-weight:600}}
.btn-pitch:hover{{opacity:0.85}}
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;font-size:16px}}
.panel .firm-meta{{font-size:11px;color:var(--muted);margin-bottom:12px}}
.panel .pitch-script{{background:var(--bg);padding:12px;border-radius:4px;border:1px solid var(--border);font-size:12px;line-height:1.5;white-space:pre-wrap}}
.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}}
.compliance strong{{color:var(--fg)}}
</style>
</head>
<body>
<div class="banner">
<span><span class="lock">🔒</span> INTERNAL — Sales Targeting Data — Not For Public Distribution — §6155 Compliance Sensitive</span>
<span style="font-size:11px;opacity:0.8">Admin-only · Generated {generated}</span>
</div>
<div class="hd">
<div>
<h1>California Law Firms — Paid-Ad Pixel Detection</h1>
<div class="sub">{total} firms with at least one tracking pixel detected · directory enrichment data</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 firms</div></div>
<div class="stat"><div class="v" style="color:#ff7070">{tier_counts.get('TIER1_HEAVY', 0)}</div><div class="l">TIER1 — 3+ platforms</div></div>
<div class="stat"><div class="v" style="color:#ffaa44">{tier_counts.get('TIER2_MODERATE', 0)}</div><div class="l">TIER2 — 2 platforms</div></div>
<div class="stat"><div class="v">{tier_counts.get('TIER3_SINGLE', 0)}</div><div class="l">TIER3 — 1 platform</div></div>
<div class="stat"><div class="v">{plat_counts['google_ads']}</div><div class="l">Google Ads</div></div>
<div class="stat"><div class="v">{plat_counts['meta']}</div><div class="l">Meta</div></div>
<div class="stat"><div class="v">{plat_counts['linkedin']}</div><div class="l">LinkedIn</div></div>
</div>
<div class="controls">
<input type="text" id="filter" placeholder="Filter by name 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')">Firm</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">
<strong>Compliance:</strong> Source — tracking-pixel detection on public firm websites, ad-signals enrichment Pass 1, generated {generated}.
Data is for internal directory-enrichment use only and is not a public attribution of advertising activity. Not affiliated with the State Bar of California.
This page does not contain spend figures, ad budgets, or paid-claim data — only the presence or absence of tracking pixels on a publicly-accessible homepage.
Pixel detection is heuristic; "TIER1_HEAVY" indicates 3+ tracking platforms detected, not a verified spend level.
</div>
<script>
const DATA = {json.dumps(data)};
let sortKey = 'paid_pixels';
let sortAsc = false;
function toggleTheme(){{
const cur = document.documentElement.getAttribute('data-theme');
const next = cur === 'dark' ? 'light' : 'dark';
document.documentElement.setAttribute('data-theme', next);
localStorage.setItem('theme', next);
document.getElementById('themeBtn').textContent = next === '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 escapeHtml(s){{ return String(s).replace(/[&<>\"']/g, c => ({{'&':'&','<':'<','>':'>','\"':'"',\"'\":'''}})[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 filtered = DATA.filter(r =>
tiers.has(r.tier) &&
(!q || r.name.toLowerCase().includes(q) || r.city.toLowerCase().includes(q))
);
filtered.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;
}});
const tbody = document.getElementById('tbody');
tbody.innerHTML = filtered.map(r => `
<tr>
<td><strong>${{escapeHtml(r.name)}}</strong></td>
<td>${{escapeHtml(r.city)}}, ${{escapeHtml(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="${{escapeHtml(r.website)}}" target="_blank" rel="noreferrer">${{escapeHtml(r.website.replace(/^https?:\\/\\//,'').replace(/\\/$/,'').slice(0,40))}}</a>` : ''}}</td>
<td><button class="btn-pitch" onclick='openPitch(${{r.firm_id}})'>📋 Pitch</button></td>
</tr>`).join('');
document.getElementById('visible-count').textContent = `${{filtered.length}} of ${{DATA.length}} firms shown`;
}}
function openPitch(firmId){{
const r = DATA.find(x => x.firm_id == firmId);
if (!r) return;
const main = document.getElementById('main');
const panel = document.getElementById('panel');
main.classList.add('with-panel');
panel.style.display = 'block';
const platList = r.platforms.length ? r.platforms.join(', ') : '(no platforms detected)';
const intro = r.platforms.length > 1
? `we noticed your firm is running tracking pixels for ${{r.platforms.length}} platforms — including ${{r.platforms[0]}} and ${{r.platforms[1]}}`
: `we noticed your firm has a tracking pixel detected for ${{r.platforms[0] || 'one platform'}}`;
panel.innerHTML = `
<button class="close" onclick="closePitch()">×</button>
<h2>${{escapeHtml(r.name)}}</h2>
<div class="firm-meta">${{escapeHtml(r.city)}}, ${{escapeHtml(r.state)}} · <span class="tier-${{r.tier}}">${{r.tier.replace(/_/g,' ')}}</span> · ${{r.paid_pixels}} pixels</div>
<div style="margin-bottom:8px;font-size:11px;color:var(--muted);text-transform:uppercase;letter-spacing:0.5px">Platforms detected</div>
<div style="margin-bottom:14px">${{r.platforms_html}}</div>
<div style="margin-bottom:8px;font-size:11px;color:var(--muted);text-transform:uppercase;letter-spacing:0.5px">Outreach script</div>
<div class="pitch-script">Hi — I'm reaching out from a CA-firm directory project that maps law-firm digital presence across the state.
${{intro.charAt(0).toUpperCase() + intro.slice(1)}} on your firm's website (${{escapeHtml((r.website || '').replace(/^https?:\\/\\//,''))}}).
We're talking with firms that are already investing in their digital channels to compare notes on what's working — what kind of conversion they're seeing on intake forms, click-to-call response, and channel mix across ${{platList}}.
Would you have 10 minutes this week or next? Happy to share what we're seeing across the cohort.
— [Steve / your name]
[INTERNAL CALL CRIB]
- Active platforms: ${{platList}}
- Tier: ${{r.tier.replace(/_/g,' ')}}
- Website: ${{escapeHtml(r.website || 'N/A')}}
- Do NOT make spend, budget, or dollar-amount claims.
- Frame as peer-cohort conversation, not pitch-down.</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_OUT)
print(f'Wrote {OUT_PATH} · {len(HTML_OUT):,} bytes · {total} firms')