`src/enrich/ad_signals.ts`

153 lines
/**
 * Ad / social-tracking signal detector.
 *
 * Mines the already-crawled raw HTML files (data/raw/firm-NNN.html) for
 * known tracking-pixel and tag fingerprints. The result is a JSONB blob
 * stored in business_enrichment.ad_signals — a binary "this business is
 * paying to advertise on platform X" signal per business.
 *
 * No HTTP calls. No LLM. Pure regex + UPDATE.
 *
 * Detected signals:
 *   google_ads        — googleadservices.com, gtag(...AW-..), google_conversion_id
 *   google_analytics  — gtag(...G-..), analytics.js, GTM-...
 *   meta_pixel        — connect.facebook.net/...fbevents.js, fbq('init', '...
 *   tiktok_pixel      — analytics.tiktok.com, ttq.load
 *   pinterest_tag     — s.pinimg.com/ct.js, pintrk('load
 *   linkedin_insight  — snap.licdn.com/li.lms-analytics, _linkedin_partner_id
 *   twitter_pixel     — static.ads-twitter.com/uwt.js, twq('init
 *   reddit_pixel      — www.redditstatic.com/ads/pixel.js, rdt('init
 *   bing_uet          — bat.bing.com, uetq
 *   snap_pixel        — sc-static.net/scevent
 */
import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { query, pool } from '../../db/pool.ts';

interface AuditRow {
  business_id: number;
  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; // GTM detected — pixels may be loaded indirectly (precision caveat)
  // Derived
  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.business_id) a.business_id, a.raw_html_path
    FROM front_page_audits a
    WHERE a.raw_html_path IS NOT NULL
    ORDER BY a.business_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 remain (e.g. 'transparency' set by ads_transparency.ts),
    // detection keys overwrite. Without this, re-mining wipes out transparency data.
    await query(
      `
      INSERT INTO business_enrichment (business_id, ad_signals, signals_updated_at)
      VALUES ($1, $2, now())
      ON CONFLICT (business_id) DO UPDATE
      SET ad_signals = COALESCE(business_enrichment.ad_signals, '{}'::jsonb) || EXCLUDED.ad_signals,
          signals_updated_at = now()
    `,
      [row.business_id, JSON.stringify(s)]
    );
  }

  console.log(`[ad-signals] processed ${processed} businesses · 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);
});