← back to Marketing Command Center
MCC Clients: show full address + phone + real domain + best contact/follow per firm
30afe18682f2b449b1d11a9b3482663981bb5385 · 2026-07-17 08:29:17 -0700 · Steve
- backfill address/state/category/mapsUrl from the source Google-Places CSVs
into the staged prospect JSONs (scripts/backfill-address-from-csv.py), with a
cross-CSV host fallback for the group whose source CSV is absent
- clients.js: render 📍 full address (Google Maps link), 📞 tap-to-call phone
chip, replace generic 'site ↗' with the actual domain (bsbdesign.com ↗), and
a ⭐ Best contact/follow recommendation (gold-rings the recommended channel)
- carry the richer location fields forward in crawl-csv-emails.py so future
re-crawls keep them; export CSV gains Address/State/Category/Best-contact cols
Files touched
M public/panels/clients.htmlM public/panels/clients.jsA scripts/backfill-address-from-csv.pyM scripts/crawl-csv-emails.py
Diff
commit 30afe18682f2b449b1d11a9b3482663981bb5385
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jul 17 08:29:17 2026 -0700
MCC Clients: show full address + phone + real domain + best contact/follow per firm
- backfill address/state/category/mapsUrl from the source Google-Places CSVs
into the staged prospect JSONs (scripts/backfill-address-from-csv.py), with a
cross-CSV host fallback for the group whose source CSV is absent
- clients.js: render 📍 full address (Google Maps link), 📞 tap-to-call phone
chip, replace generic 'site ↗' with the actual domain (bsbdesign.com ↗), and
a ⭐ Best contact/follow recommendation (gold-rings the recommended channel)
- carry the richer location fields forward in crawl-csv-emails.py so future
re-crawls keep them; export CSV gains Address/State/Category/Best-contact cols
---
public/panels/clients.html | 9 +++
public/panels/clients.js | 51 ++++++++++++---
scripts/backfill-address-from-csv.py | 118 +++++++++++++++++++++++++++++++++++
scripts/crawl-csv-emails.py | 10 +++
4 files changed, 179 insertions(+), 9 deletions(-)
diff --git a/public/panels/clients.html b/public/panels/clients.html
index 6143920..55e8a72 100644
--- a/public/panels/clients.html
+++ b/public/panels/clients.html
@@ -11,7 +11,16 @@
.cl-chip.ig.direct { background: linear-gradient(45deg,#f09433,#dc2743,#bc1888); color: #fff; border-color: transparent; }
.cl-chip.ig.search { color: #c13584; border-color: #e3a9c9; }
.cl-acted { font-size: 9.5px; color: #7a7a7a; white-space: nowrap; align-self: center; }
+ .cl-chip.phone { color: #146c43; border-color: #b7ddc6; }
+ .cl-chip.site { color: #444; }
+ .cl-chip.best { box-shadow: 0 0 0 1.5px #d4af37; } /* gold ring on the recommended channel */
+ .cl-addr { font-size: 11px; color: #666; }
+ .cl-addr a { color: #666; text-decoration: none; }
+ .cl-addr a:hover { text-decoration: underline; }
+ .cl-best { font-size: 10.5px; color: #7a5c00; margin-top: 3px; }
+ .cl-best b { color: #5a4600; font-weight: 600; }
#cl-list.cl-compact .cl-chip { font-size: 9.5px; padding: 1px 6px; }
+ #cl-list.cl-compact .cl-addr, #cl-list.cl-compact .cl-best { font-size: 9.5px; }
</style>
<div id="cl-root" style="max-width:960px;">
<div class="muted-banner" id="cl-banner">Loading client & prospect groups…</div>
diff --git a/public/panels/clients.js b/public/panels/clients.js
index c6a2df1..02dde7d 100644
--- a/public/panels/clients.js
+++ b/public/panels/clients.js
@@ -24,19 +24,41 @@ window.MCC_PANELS['clients'] = {
// Normalize a record from either a "clients" (FM) or "prospects" (CSV) dataset.
function norm(rec, kind) {
if (kind === 'prospects') {
+ // Full formatted address; fall back to composing one from the parts.
+ const address = rec.address || [rec.street, [rec.city, rec.state].filter(Boolean).join(', '), rec.postal].filter(Boolean).join(', ');
return { id: (rec.website || rec.title || '').toLowerCase(), label: rec.title || '(business)', sub: [rec.city, rec._group].filter(Boolean).join(' · '),
- email: rec.email || '', website: rec.website || '', phone: rec.phone || '', q: [rec.title, rec.city].filter(Boolean).join(' '),
+ email: rec.email || '', website: rec.website || '', phone: rec.phone || '', phoneRaw: rec.phoneUnformatted || rec.phone || '',
+ address, mapsUrl: rec.mapsUrl || '', category: rec.category || '', q: [rec.title, rec.city].filter(Boolean).join(' '),
_raw: rec, _all: Object.values(rec).filter(v => v && typeof v === 'string').join(' ').toLowerCase() };
}
const label = rec.name || rec.company || '(client)';
const sub = rec.name ? [rec.company, [rec.city, rec.state].filter(Boolean).join(', ')].filter(Boolean).join(' · ')
: [rec.city, rec.state].filter(Boolean).join(', ');
- return { id: rec.account || (rec.company + '|' + rec.name).toLowerCase(), label, sub, email: rec.email || '', website: '', phone: rec.phone || '',
+ return { id: rec.account || (rec.company + '|' + rec.name).toLowerCase(), label, sub, email: rec.email || '', website: '', phone: rec.phone || '', phoneRaw: rec.phone || '',
+ address: '', mapsUrl: '', category: '',
q: [rec.name, rec.company, rec.city].filter(Boolean).join(' '),
_raw: rec, _all: Object.values(rec).filter(v => v && typeof v === 'string').join(' ').toLowerCase() };
}
const liURL = q => 'https://www.linkedin.com/search/results/all/?keywords=' + encodeURIComponent(q);
const igURL = q => 'https://www.google.com/search?q=' + encodeURIComponent(q + ' instagram');
+ // Human-readable domain for the website chip — turns a raw URL into e.g. "bsbdesign.com".
+ const hostLabel = u => { try { return new URL(/^https?:\/\//.test(u) ? u : 'http://' + u).hostname.replace(/^www\./, ''); } catch { return 'website'; } };
+ const telHref = p => 'tel:' + String(p || '').replace(/[^\d+]/g, '');
+ // Recommend the single best way to CONTACT and to FOLLOW each firm, from the
+ // data we actually hold. Contact prefers a direct inbox; follow prefers a
+ // confirmed social profile over a search. Returns {contact, follow, keys}.
+ function bestChannels(r) {
+ let contact, ckey;
+ if (r.email) { contact = '✉️ Email — ' + r.email; ckey = 'mail'; }
+ else if (r.phone) { contact = '📞 Call — ' + r.phone; ckey = 'phone'; }
+ else if (r.website) { contact = '🌐 Website form — ' + hostLabel(r.website); ckey = 'site'; }
+ else { contact = '🔎 Google the firm'; ckey = ''; }
+ let follow, fkey;
+ if (r._raw.instagram) { follow = 'Instagram'; fkey = 'ig'; }
+ else if (r._raw.linkedin) { follow = 'LinkedIn'; fkey = 'in'; }
+ else { follow = 'search on LinkedIn / IG'; fkey = ''; }
+ return { contact, follow, ckey, fkey };
+ }
let MANIFEST = [], current = null, RECS = [];
@@ -100,7 +122,7 @@ window.MCC_PANELS['clients'] = {
$('#cl-emailcount').textContent = nEmail.toLocaleString() + ' email';
$('#cl-igcount').textContent = nIg ? nIg.toLocaleString() + ' IG' : '';
$('#cl-licount').textContent = nLi ? nLi.toLocaleString() + ' LinkedIn' : '';
- $('#cl-groupnote').textContent = `Source: ${data.source || d.file}${data.updated ? ' · updated ' + data.updated : ''}. LinkedIn opens a people/company search; IG opens a Google “<name> instagram” lookup — open the right match and follow.`;
+ $('#cl-groupnote').textContent = `Source: ${data.source || d.file}${data.updated ? ' · updated ' + data.updated : ''}. Each card shows the firm's full address (📍 opens Google Maps), phone (📞 tap to call), website domain, and a ⭐ Best channel to contact + follow. LinkedIn opens a people/company search; IG opens a Google “<name> instagram” lookup — open the right match and follow.`;
$('#cl-search').value = '';
$('#cl-search').oninput = render;
$('#cl-sort').value = sortMode();
@@ -188,8 +210,8 @@ window.MCC_PANELS['clients'] = {
const cell = v => { v = String(v == null ? '' : v); return /[",\n]/.test(v) ? '"' + v.replace(/"/g, '""') + '"' : v; };
let headers, line;
if (d.kind === 'prospects') {
- headers = ['Business', 'City', 'Phone', 'Website', 'Email', 'LinkedIn', 'Instagram', 'LinkedIn actioned', 'Instagram actioned'];
- line = r => [r._raw.title, r._raw.city, r._raw.phone, r._raw.website, r._raw.email, r._raw.linkedin || liURL(r.q), r._raw.instagram || igURL(r.q), actedIn(r), actedIg(r)];
+ headers = ['Business', 'Category', 'Address', 'City', 'State', 'Phone', 'Website', 'Email', 'Best contact', 'LinkedIn', 'Instagram', 'LinkedIn actioned', 'Instagram actioned'];
+ line = r => [r._raw.title, r.category || '', r.address || '', r._raw.city, r._raw.state || '', r._raw.phone, r._raw.website, r._raw.email, bestChannels(r).contact, r._raw.linkedin || liURL(r.q), r._raw.instagram || igURL(r.q), actedIn(r), actedIg(r)];
} else {
headers = ['Account', 'Company', 'Name', 'City', 'State', 'Email', 'Phone', 'LinkedIn Search', 'Instagram Search', 'LinkedIn actioned', 'Instagram actioned'];
line = r => [r._raw.account, r._raw.company, r._raw.name, r._raw.city, r._raw.state, r._raw.email, r._raw.phone, liURL(r.q), igURL(r.q), actedIn(r), actedIg(r)];
@@ -213,18 +235,29 @@ window.MCC_PANELS['clients'] = {
const liDir = !!r._raw.linkedin, igDir = !!r._raw.instagram;
const a = A[r.id] || {};
const sub = r.sub ? `<span class="muted" style="font-size:11px;">${esc(r.sub)}</span>` : '';
+ const best = bestChannels(r);
+ const bestOn = k => (k && (best.ckey === k || best.fkey === k)) ? ' best' : ''; // gold-ring the recommended channel
+ const addr = r.address
+ ? `<span class="cl-addr">📍 ${r.mapsUrl
+ ? `<a href="${esc(r.mapsUrl)}" target="_blank" rel="noopener" title="Open in Google Maps" onclick="event.stopPropagation()">${esc(r.address)}</a> ↗`
+ : esc(r.address)}${r.category ? ` · ${esc(r.category)}` : ''}</span>`
+ : '';
const stamp = (ch) => `<span class="cl-acted" id="acted-${ch}-${i}">${a[ch] ? '🕓 ' + esc(fmtWhen(a[ch])) : ''}</span>`;
const chips =
- (r.email ? `<a class="cl-chip mail" href="mailto:${esc(r.email)}" title="${esc(r.email)}" onclick="event.stopPropagation()">✉︎ ${esc(r.email)}</a>` : '') +
- (r.website ? `<a class="cl-chip" href="${esc(r.website)}" target="_blank" rel="noopener" onclick="event.stopPropagation()">🌐 site ↗</a>` : '') +
- `<a class="cl-chip in ${liDir ? 'direct' : 'search'}" data-ch="in" data-idx="${i}" href="${esc(r._raw.linkedin || liURL(r.q))}" target="_blank" rel="noopener" title="${liDir ? 'LinkedIn profile' : 'LinkedIn search'}">in · ${liDir ? 'Follow' : 'Find'} ↗</a>` + stamp('in') +
- `<a class="cl-chip ig ${igDir ? 'direct' : 'search'}" data-ch="ig" data-idx="${i}" href="${esc(r._raw.instagram || igURL(r.q))}" target="_blank" rel="noopener" title="${igDir ? 'Instagram profile' : 'Instagram search'}">IG · ${igDir ? 'Follow' : 'Find'} ↗</a>` + stamp('ig');
+ (r.email ? `<a class="cl-chip mail${bestOn('mail')}" href="mailto:${esc(r.email)}" title="Email — ${esc(r.email)}" onclick="event.stopPropagation()">✉︎ ${esc(r.email)}</a>` : '') +
+ (r.phone ? `<a class="cl-chip phone${bestOn('phone')}" href="${esc(telHref(r.phoneRaw))}" title="Call ${esc(r.phone)}" onclick="event.stopPropagation()">📞 ${esc(r.phone)}</a>` : '') +
+ (r.website ? `<a class="cl-chip site${bestOn('site')}" href="${esc(r.website)}" target="_blank" rel="noopener" title="${esc(r.website)}" onclick="event.stopPropagation()">🌐 ${esc(hostLabel(r.website))} ↗</a>` : '') +
+ `<a class="cl-chip in ${liDir ? 'direct' : 'search'}${bestOn('in')}" data-ch="in" data-idx="${i}" href="${esc(r._raw.linkedin || liURL(r.q))}" target="_blank" rel="noopener" title="${liDir ? 'LinkedIn profile' : 'LinkedIn search'}">in · ${liDir ? 'Follow' : 'Find'} ↗</a>` + stamp('in') +
+ `<a class="cl-chip ig ${igDir ? 'direct' : 'search'}${bestOn('ig')}" data-ch="ig" data-idx="${i}" href="${esc(r._raw.instagram || igURL(r.q))}" target="_blank" rel="noopener" title="${igDir ? 'Instagram profile' : 'Instagram search'}">IG · ${igDir ? 'Follow' : 'Find'} ↗</a>` + stamp('ig');
+ const bestLine = `<span class="cl-best">⭐ <b>Best:</b> ${esc(best.contact)} · <b>Follow</b> on ${esc(best.follow)}</span>`;
return `<label class="cl-row" style="display:flex;align-items:flex-start;gap:9px;padding:8px 0;border-bottom:1px solid var(--line,#eee);${on ? 'opacity:.5;' : ''}">
<input type="checkbox" data-id="${esc(r.id)}" ${on ? 'checked' : ''} style="width:16px;height:16px;flex:none;margin-top:2px;">
<span style="flex:1;display:flex;flex-direction:column;line-height:1.3;min-width:0;">
<b style="font-size:13px;${on ? 'text-decoration:line-through;' : ''}">${esc(r.label)}</b>
${sub}
+ ${addr}
<span class="cl-chips" style="display:flex;gap:6px;flex-wrap:wrap;align-items:center;margin-top:5px;">${chips}</span>
+ ${bestLine}
</span>
</label>`;
}).join('')}</div>${more > 0 ? `<div class="muted" style="font-size:11px;margin-top:8px;">Showing ${shown.length} of ${matches.length.toLocaleString()} — type in the filter to narrow.</div>` : ''}</div>`;
diff --git a/scripts/backfill-address-from-csv.py b/scripts/backfill-address-from-csv.py
new file mode 100644
index 0000000..b7d590a
--- /dev/null
+++ b/scripts/backfill-address-from-csv.py
@@ -0,0 +1,118 @@
+#!/usr/bin/env python3
+"""
+backfill-address-from-csv.py — enrich staged prospect JSONs with the full
+address / state / category fields that the email crawler dropped at ingest.
+
+The email crawler (crawl-csv-emails.py) only kept title/city/phone/website/
+email from the raw Google-Places CSV. This backfills the richer location
+fields straight from the same source CSV, keyed by website host (fallback:
+title|city), WITHOUT disturbing the expensive email / instagram / linkedin
+enrichment already in the JSON.
+
+ python3 scripts/backfill-address-from-csv.py # all prospect groups
+ python3 scripts/backfill-address-from-csv.py --dry-run # report only
+
+Each prospects-<slug>.json records its own `source` CSV basename, which we
+resolve against data/sources/. Groups whose source CSV is missing are skipped
+and reported. Nothing is uploaded to Constant Contact — local staging only.
+"""
+import csv, sys, os, re, json, glob, argparse, urllib.parse
+
+csv.field_size_limit(10_000_000)
+HERE = os.path.dirname(__file__)
+SRC_DIR = os.path.join(HERE, "..", "data", "sources")
+DATA_DIR = os.path.join(HERE, "..", "public", "data")
+
+# CSV column -> JSON field. Only filled if the JSON value is empty (non-destructive).
+FIELD_MAP = {
+ "address": "address", # full formatted address, e.g. "970 W 190th St Ste 250, Torrance, CA 90502"
+ "state": "state",
+ "postalCode": "postal",
+ "neighborhood": "neighborhood",
+ "categoryName": "category",
+ "url": "mapsUrl", # Google Maps place URL
+}
+
+def host_of(u):
+ u = (u or "").strip()
+ if not u:
+ return ""
+ try:
+ return urllib.parse.urlparse(u if u.startswith(("http://", "https://")) else "http://" + u).netloc.lower().replace("www.", "")
+ except Exception:
+ return ""
+
+def key_of(rec):
+ return host_of(rec.get("website")) or ((rec.get("title") or "").strip().lower() + "|" + (rec.get("city") or "").strip().lower())
+
+def build_lut(csv_path):
+ lut = {}
+ with open(csv_path, newline="", encoding="utf-8", errors="replace") as fh:
+ for row in csv.DictReader(fh):
+ k = key_of(row)
+ if k and k not in lut:
+ lut[k] = row
+ return lut
+
+def main():
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--dry-run", action="store_true")
+ ap.add_argument("--glob", default="prospects-*.json")
+ a = ap.parse_args()
+
+ srcmap = {os.path.basename(f): f for f in glob.glob(os.path.join(SRC_DIR, "*.csv"))}
+
+ # Global host->row fallback across EVERY present CSV (rows with an address).
+ # Used when a group's own source CSV is missing, or to fill records absent
+ # from their own crawl. LA firms overlap across the architect/ID lists.
+ glut = {}
+ for f in srcmap.values():
+ with open(f, newline="", encoding="utf-8", errors="replace") as fh:
+ for row in csv.DictReader(fh):
+ k = host_of(row.get("website"))
+ if k and k not in glut and (row.get("address") or "").strip():
+ glut[k] = row
+
+ total_files = total_matched = 0
+
+ for jf in sorted(glob.glob(os.path.join(DATA_DIR, a.glob))):
+ d = json.load(open(jf))
+ biz = d.get("businesses", [])
+ src = d.get("source")
+ path = srcmap.get(src)
+ base = os.path.basename(jf)
+ lut = build_lut(path) if path else {}
+ via = "own CSV" if path else "cross-CSV fallback (own source missing)"
+ matched = 0
+ for b in biz:
+ r = lut.get(key_of(b)) or glut.get(host_of(b.get("website")))
+ if not r:
+ continue
+ got = False
+ for col, field in FIELD_MAP.items():
+ val = (r.get(col) or "").strip()
+ if val and not (b.get(field) or "").strip():
+ b[field] = val
+ got = True
+ # fill phone if the crawler had none but Places did
+ if not (b.get("phone") or "").strip() and (r.get("phone") or "").strip():
+ b["phone"] = r["phone"].strip()
+ got = True
+ # a machine-diallable phone for tel: links
+ if not (b.get("phoneUnformatted") or "").strip() and (r.get("phoneUnformatted") or "").strip():
+ b["phoneUnformatted"] = r["phoneUnformatted"].strip()
+ if got:
+ matched += 1
+ with_addr = sum(1 for b in biz if (b.get("address") or "").strip())
+ d["with_address"] = with_addr
+ total_files += 1
+ total_matched += matched
+ print(f"{'· ' if a.dry_run else '✓ '}{base:44s} {matched:5d} enriched · {with_addr:5d}/{len(biz):5d} now have address [{via}]")
+ if not a.dry_run:
+ with open(jf, "w") as fh:
+ json.dump(d, fh)
+
+ print(f"\n{'DRY-RUN — no files written. ' if a.dry_run else ''}{total_files} groups, {total_matched} records enriched. [$0 local]")
+
+if __name__ == "__main__":
+ main()
diff --git a/scripts/crawl-csv-emails.py b/scripts/crawl-csv-emails.py
index e1754d3..6d2eeb8 100644
--- a/scripts/crawl-csv-emails.py
+++ b/scripts/crawl-csv-emails.py
@@ -84,7 +84,17 @@ def enrich(row):
website = norm_url(row.get("website"))
biz = {"title": (row.get("title") or "").strip(),
"city": (row.get("city") or "").strip(),
+ # richer location fields kept from the Google-Places export so the
+ # Clients panel can show a full address + Maps link (see
+ # scripts/backfill-address-from-csv.py which backfills older JSONs).
+ "address": (row.get("address") or "").strip(),
+ "state": (row.get("state") or "").strip(),
+ "postal": (row.get("postalCode") or "").strip(),
+ "neighborhood": (row.get("neighborhood") or "").strip(),
+ "category": (row.get("categoryName") or "").strip(),
+ "mapsUrl": (row.get("url") or "").strip(),
"phone": (row.get("phone") or "").strip(),
+ "phoneUnformatted": (row.get("phoneUnformatted") or "").strip(),
"website": website, "email": "", "emails": []}
if not website: return biz
site_host = host_of(website)
← 7877a92 composer: banner preview respects template aspect-ratio (#2)
·
back to Marketing Command Center
·
MCC Clients: per-contact notes field below each firm card 2aac093 →