`src/enrich/ad_signals.ts`

143 lines
/**
 * Ad / social-tracking signal detector — lacountyeats Pass 1.
 *
 * Mines crawled raw HTML files (data/raw/facility-<id>-<ts>.html) for known
 * tracking-pixel / ad-tag fingerprints. Result is a JSONB blob written to
 * facility_enrichment.ad_signals — a binary "this restaurant is paying to
 * advertise on platform X" signal per facility.
 *
 * No HTTP, no LLM. Pure regex + UPDATE.
 *
 * Detection regexes are verbatim from ventura-corridor's reference impl
 * (~/Projects/ventura-corridor/src/enrich/ad_signals.ts).
 */
import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { query, pool } from '../../db/pool.ts';

interface AuditRow {
  facility_id: string;
  raw_html_path: string;
}

interface Signals {
  google_ads: boolean;
  google_analytics: boolean;
  meta_pixel: boolean;
  tiktok_pixel: boolean;
  pinterest_tag: boolean;
  linkedin_insight: boolean;
  twitter_pixel: boolean;
  reddit_pixel: boolean;
  bing_uet: boolean;
  snap_pixel: boolean;
  gtm_container: boolean;
  paid_ads_count: number;
}

function detect(html: string): Signals {
  const s: Signals = {
    google_ads: /googleadservices\.com|gtag\([^)]*['"]AW-|google_conversion_id|googletagmanager\.com\/gtag\/js\?id=AW-/i.test(html),
    google_analytics: /gtag\([^)]*['"]G-|googletagmanager\.com\/gtm\.js|google-analytics\.com\/analytics\.js|googletagmanager\.com\/gtag\/js\?id=G-|gtag\([^)]*['"]UA-/i.test(html),
    meta_pixel: /connect\.facebook\.net\/[^"']*\/fbevents\.js|fbq\(['"]init['"]|fbq\(['"]track['"]|facebook\.com\/tr\?id=/i.test(html),
    tiktok_pixel: /analytics\.tiktok\.com|business-api\.tiktok\.com|ttq\.load|TiktokAnalyticsObject/i.test(html),
    pinterest_tag: /s\.pinimg\.com\/ct\.js|pintrk\(['"]load|pintrk\(['"]track/i.test(html),
    linkedin_insight: /snap\.licdn\.com\/li\.lms-analytics|_linkedin_partner_id|_linkedin_data_partner_ids/i.test(html),
    twitter_pixel: /static\.ads-twitter\.com\/uwt\.js|analytics\.twitter\.com\/i\/adsct|t\.co\/i\/adsct|twq\(['"]init|twq\(['"]track/i.test(html),
    reddit_pixel: /www\.redditstatic\.com\/ads\/pixel\.js|rdt\(['"]init|rdt\(['"]track/i.test(html),
    bing_uet: /bat\.bing\.com|bat\.r\.msn\.com|window\.uetq|window\["uetq"\]|uet\.gif/i.test(html),
    snap_pixel: /sc-static\.net\/scevent|snaptr\(['"]init/i.test(html),
    gtm_container: /googletagmanager\.com\/gtm\.js\?id=GTM-/i.test(html),
    paid_ads_count: 0,
  };
  s.paid_ads_count =
    Number(s.google_ads) +
    Number(s.meta_pixel) +
    Number(s.tiktok_pixel) +
    Number(s.pinterest_tag) +
    Number(s.twitter_pixel) +
    Number(s.reddit_pixel) +
    Number(s.bing_uet) +
    Number(s.snap_pixel) +
    Number(s.linkedin_insight);
  return s;
}

async function main() {
  const r = await query<AuditRow>(`
    SELECT DISTINCT ON (a.facility_id) a.facility_id, a.raw_html_path
    FROM front_page_audits a
    WHERE a.raw_html_path IS NOT NULL
    ORDER BY a.facility_id, a.audited_at DESC
  `);
  console.log(`[ad-signals] mining ${r.rowCount} HTML files…`);

  let withGoogleAds = 0,
    withMeta = 0,
    withTikTok = 0,
    withPinterest = 0,
    withLinkedIn = 0,
    withTwitter = 0,
    withReddit = 0,
    withBing = 0,
    withSnap = 0,
    withGA = 0,
    anyPaid = 0,
    processed = 0;

  for (const row of r.rows) {
    const rel = row.raw_html_path.startsWith('data/') ? row.raw_html_path : 'data/' + row.raw_html_path;
    let html: string;
    try {
      html = readFileSync(join(process.cwd(), rel), 'utf8');
    } catch {
      continue;
    }
    const s = detect(html);
    if (s.google_ads) withGoogleAds++;
    if (s.meta_pixel) withMeta++;
    if (s.tiktok_pixel) withTikTok++;
    if (s.pinterest_tag) withPinterest++;
    if (s.linkedin_insight) withLinkedIn++;
    if (s.twitter_pixel) withTwitter++;
    if (s.reddit_pixel) withReddit++;
    if (s.bing_uet) withBing++;
    if (s.snap_pixel) withSnap++;
    if (s.google_analytics) withGA++;
    if (s.paid_ads_count > 0) anyPaid++;
    processed++;

    // Merge: existing keys preserved (e.g. future 'transparency' from pass 2),
    // detection keys overwrite. Without || merge, re-mining wipes pass-2 data.
    await query(
      `
      INSERT INTO facility_enrichment (facility_id, ad_signals, signals_updated_at)
      VALUES ($1, $2, now())
      ON CONFLICT (facility_id) DO UPDATE
      SET ad_signals = COALESCE(facility_enrichment.ad_signals, '{}'::jsonb) || EXCLUDED.ad_signals,
          signals_updated_at = now()
    `,
      [row.facility_id, JSON.stringify(s)],
    );
  }

  console.log(`[ad-signals] processed ${processed} facilities · paid-ad signals:`);
  console.log(`  google_ads      : ${withGoogleAds}`);
  console.log(`  meta_pixel      : ${withMeta}`);
  console.log(`  tiktok_pixel    : ${withTikTok}`);
  console.log(`  pinterest_tag   : ${withPinterest}`);
  console.log(`  linkedin_insight: ${withLinkedIn}`);
  console.log(`  twitter_pixel   : ${withTwitter}`);
  console.log(`  reddit_pixel    : ${withReddit}`);
  console.log(`  bing_uet        : ${withBing}`);
  console.log(`  snap_pixel      : ${withSnap}`);
  console.log(`  any_paid_ads    : ${anyPaid}`);
  console.log(`  google_analytics: ${withGA}  (not paid; just measurement)`);
  await pool.end();
}

main().catch((e) => {
  console.error('[ad-signals]', e);
  process.exit(1);
});