`scripts/ad_signals_dedup.cjs`

171 lines
#!/usr/bin/env node
// READ-ONLY canonical-firm DEDUP pass over the existing ad-signals artifact.
// Fixes the Panera (and 27 other chains) double-listing: the pass-1 analyzer
// (ad_signals_report.cjs) treats every data/raw/firm-*.html file as a distinct
// firm, so a chain crawled at N Ventura Blvd storefronts counts as N firms.
//
// Input : data/ad-signals-2026-06-14.json   (the pass-1 artifact, NOT re-crawled)
// Output: data/ad-signals-2026-06-15-deduped.{json,csv}   (NEW files, never overwrites)
//
// No network, no DB writes, no paid APIs, no crawl. $0 (local).
//
// Canonical-firm key (priority order):
//   1. Failed-crawl junk (Cloudflare/loading interstitial + no domain + 0 paid) is
//      DROPPED -- not firms; they inflate the denominator.
//   2. dom:<registrable-domain>  e.g. locations.panerabread.com -> panerabread.com
//      Collapses multi-location chains (one corporate ad program = one advertiser).
//      GUARD: two rows sharing a domain whose DB-source names materially disagree
//      (likely a source mis-mapping, e.g. "Digital Sputnik" -> anajakthai.com) are
//      kept SEPARATE and flagged, so firm count is never silently under-counted.
//   3. name:<normalized-name>  when no domain (skips generic titles like "Home").
//   4. file:<firm_file>        fallback so distinct no-domain firms stay distinct.
//
// chain paid_ads_count = UNION of platforms across its locations. All location
// files preserved on the firm record (location_files).

const fs = require('fs');
const path = require('path');

const DATA = path.join(process.env.HOME, 'Projects/ventura-corridor/data');
const IN = path.join(DATA, 'ad-signals-2026-06-14.json');
const OUT_JSON = path.join(DATA, 'ad-signals-2026-06-15-deduped.json');
const OUT_CSV = path.join(DATA, 'ad-signals-2026-06-15-deduped.csv');

const PLATS = ['google_ads','meta_pixel','tiktok_pixel','pinterest_tag','linkedin_insight','twitter_pixel','reddit_pixel','bing_uet','snap_pixel'];
const JUNK_TITLE = /^(checking your browser|로딩\s*중|just a moment|please wait|attention required|access denied)/i;
const GENERIC_NAME = /^(home|home page|untitled|loading|menu|index)$/;

function regdomain(d) {
  if (!d) return '';
  d = d.toLowerCase().replace(/^www\./, '');
  const p = d.split('.');
  if (p.length <= 2) return d;
  return p.slice(-2).join('.'); // Ventura set is all single-label .com/.net/.org
}
const normName = s => (s || '').toLowerCase().replace(/[^a-z0-9]+/g, ' ').trim();

const report = JSON.parse(fs.readFileSync(IN, 'utf8'));
const rows = report.businesses;

const dropped = [];
const kept = rows.filter(x => {
  const junk = JUNK_TITLE.test(x.business) && !x.domain && x.paid_ads_count === 0;
  if (junk) dropped.push(x.firm_file);
  return !junk;
});

const flaggedMismatch = [];
function nameTokens(s) { return new Set(normName(s).split(' ').filter(t => t.length > 2)); }
function namesAgree(a, b) {
  const na = normName(a), nb = normName(b);
  if (!na || !nb) return true;
  if (na === nb) return true;
  const ta = nameTokens(a), tb = nameTokens(b);
  for (const t of ta) if (tb.has(t)) return true;
  return false;
}
function canonKey(x) {
  const rd = regdomain(x.domain);
  if (rd) return 'dom:' + rd;
  const nn = normName(x.business);
  if (nn && !GENERIC_NAME.test(nn) && nn.length >= 3) return 'name:' + nn;
  return 'file:' + x.firm_file;
}

const groups = {};
for (const x of kept) { const k = canonKey(x); (groups[k] = groups[k] || []).push(x); }

const finalGroups = {};
for (const [k, locs] of Object.entries(groups)) {
  if (!k.startsWith('dom:') || locs.length === 1) { finalGroups[k] = locs; continue; }
  const anchor = locs.find(l => l.identity_source === 'db') || locs[0];
  const core = [], peeled = [];
  for (const l of locs) {
    if (l === anchor) { core.push(l); continue; }
    if (l.identity_source === 'db' && !namesAgree(anchor.business, l.business)) {
      peeled.push(l);
      flaggedMismatch.push({ domain: k.slice(4), anchor: anchor.business, peeled: l.business, peeled_file: l.firm_file });
    } else core.push(l);
  }
  finalGroups[k] = core;
  for (const p of peeled) finalGroups['file:' + p.firm_file] = [p];
}

const firms = [];
for (const [k, locs] of Object.entries(finalGroups)) {
  const platforms = PLATS.filter(p => locs.some(l => l[p]));
  const ga = locs.some(l => l.google_analytics);
  const rep = [...locs].sort((a, b) =>
    (b.identity_source === 'db') - (a.identity_source === 'db') || b.bytes - a.bytes)[0];
  const socials = {};
  for (const l of locs) for (const [sk, sv] of Object.entries(l.socials || {}))
    socials[sk] = [...new Set([...(socials[sk] || []), ...sv])].slice(0, 3);
  firms.push({
    canonical_key: k, business: rep.business, domain: rep.domain,
    identity_source: rep.identity_source,
    locations: locs.length, location_files: locs.map(l => l.firm_file),
    paid_ads_count: platforms.length, google_analytics: ga,
    paid_platforms: platforms, socials,
  });
}

const platformTotals = {};
for (const p of PLATS) platformTotals[p] = firms.filter(f => f.paid_platforms.includes(p)).length;

const withPaid = firms.filter(f => f.paid_ads_count > 0).length;
const gaOnly = firms.filter(f => f.paid_ads_count === 0 && f.google_analytics).length;
const chains = firms.filter(f => f.locations > 1);
const collapsed = chains.reduce((s, f) => s + f.locations - 1, 0);
const ranked = [...firms].sort((a, b) => b.paid_ads_count - a.paid_ads_count);

const out = {
  generated: new Date().toISOString(),
  source: 'READ-ONLY canonical-firm dedup over data/ad-signals-2026-06-14.json (no crawl, no API, no DB write)',
  dedup_key: 'registrable-domain primary; normalized-name fallback; file fallback; mis-map guard splits disagreeing DB names',
  baseline: { firm_count: report.businesses_analyzed, paid_advertiser_count: report.businesses_running_paid_ads },
  failed_crawl_dropped: dropped,
  domain_mismatch_kept_separate: flaggedMismatch,
  businesses_analyzed: firms.length,
  businesses_running_paid_ads: withPaid,
  businesses_with_ga_only: gaOnly,
  multi_location_chains: chains.length,
  chain_location_rows_collapsed: collapsed,
  platform_breakdown: platformTotals,
  top10_ad_spenders: ranked.filter(f => f.paid_ads_count > 0).slice(0, 10).map(f => ({
    business: f.business, domain: f.domain, locations: f.locations,
    paid_ads_count: f.paid_ads_count, platforms: f.paid_platforms, socials: Object.keys(f.socials),
  })),
  chains_collapsed: chains.sort((a, b) => b.locations - a.locations).map(f => ({
    business: f.business, domain: f.domain, locations: f.locations,
    paid_ads_count: f.paid_ads_count, location_files: f.location_files,
  })),
  firms,
};
fs.writeFileSync(OUT_JSON, JSON.stringify(out, null, 2));

const cols = ['canonical_key','business','domain','identity_source','locations','location_files','paid_ads_count','google_analytics']
  .concat(PLATS.map(p => 'has_' + p))
  .concat(['paid_platforms','instagram','facebook','twitter','tiktok','linkedin','youtube','pinterest']);
const esc = v => { const s = String(v == null ? '' : v); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
const lines = [cols.join(',')];
for (const f of firms) {
  lines.push(cols.map(c => {
    if (c === 'location_files') return esc(f.location_files.join('|'));
    if (c === 'paid_platforms') return esc(f.paid_platforms.join('|'));
    if (c.startsWith('has_')) return esc(f.paid_platforms.includes(c.slice(4)));
    if (['instagram','facebook','twitter','tiktok','linkedin','youtube','pinterest'].includes(c))
      return esc((f.socials[c] || []).join('|'));
    return esc(f[c]);
  }).join(','));
}
fs.writeFileSync(OUT_CSV, lines.join('\n'));

console.log('=== BASELINE vs CORRECTED ===');
console.log('firm count:            482 -> ' + firms.length + '  (delta ' + (firms.length - 482) + ')');
console.log('paid-advertiser count: 127 -> ' + withPaid + '  (delta ' + (withPaid - 127) + ')');
console.log('failed-crawl junk dropped:', dropped.length);
console.log('multi-location chains:', chains.length, '| location-rows collapsed:', collapsed);
console.log('domain-mismatch kept separate (guard):', flaggedMismatch.length, '->', flaggedMismatch.map(m => m.peeled).join('; '));
console.log('wrote', OUT_JSON);
console.log('wrote', OUT_CSV);