`src/enrich/ad_signals.ts`

151 lines
/**
 * Ad / social-tracking signal detector — lawyer-directory port.
 *
 * Mines the persisted raw HTML files (raw_html/firm-NNN-TS.html, captured by
 * site_audit.ts) for known tracking-pixel / tag fingerprints. Result is a JSONB
 * blob stored in firm_ad_signals — one binary "this firm is paying to advertise
 * on platform X" signal per firm.
 *
 * No HTTP calls. No LLM. Pure regex + UPSERT.
 *
 * INTERNAL SALES-TARGETING ONLY.
 *   - Output is admin-gated; never surface in public profile pages, JSON-LD,
 *     SEO copy, or any directory listing.
 *   - Compliance: Cal §6155 (no LRS framing), Rule 7.x (no fabricated stats
 *     attached to real firm names). These regex hits are crude binary signals
 *     for outreach prioritization, not facts about the firm's spend.
 *
 * Detected signals (regexes verbatim from ventura-corridor reference impl):
 *   google_ads, google_analytics, meta_pixel, tiktok_pixel, pinterest_tag,
 *   linkedin_insight, twitter_pixel, reddit_pixel, bing_uet, snap_pixel,
 *   gtm_container (precision caveat — pixels may load indirectly via GTM)
 */
import 'dotenv/config';
import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { query, pool } from '../db/pool.ts';

interface AuditRow {
  firm_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;
  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.organization_id) a.organization_id AS firm_id, a.raw_html_path
    FROM site_audits a
    WHERE a.raw_html_path IS NOT NULL
    ORDER BY a.organization_id, a.audited_at DESC
  `);
  console.log(`[ad-signals] mining ${r.rowCount} HTML files…`);

  const counts = {
    google_ads: 0, meta: 0, tiktok: 0, pinterest: 0, linkedin: 0,
    twitter: 0, reddit: 0, bing: 0, snap: 0, ga: 0, anyPaid: 0, processed: 0, missing: 0,
  };
  const platformBreakdown: Record<string, number> = {};

  for (const row of r.rows) {
    let html: string;
    try {
      html = readFileSync(join(process.cwd(), row.raw_html_path), 'utf8');
    } catch {
      counts.missing++;
      continue;
    }
    const s = detect(html);
    if (s.google_ads) { counts.google_ads++; platformBreakdown.google_ads = (platformBreakdown.google_ads || 0) + 1; }
    if (s.meta_pixel) { counts.meta++; platformBreakdown.meta_pixel = (platformBreakdown.meta_pixel || 0) + 1; }
    if (s.tiktok_pixel) { counts.tiktok++; platformBreakdown.tiktok_pixel = (platformBreakdown.tiktok_pixel || 0) + 1; }
    if (s.pinterest_tag) { counts.pinterest++; platformBreakdown.pinterest_tag = (platformBreakdown.pinterest_tag || 0) + 1; }
    if (s.linkedin_insight) { counts.linkedin++; platformBreakdown.linkedin_insight = (platformBreakdown.linkedin_insight || 0) + 1; }
    if (s.twitter_pixel) { counts.twitter++; platformBreakdown.twitter_pixel = (platformBreakdown.twitter_pixel || 0) + 1; }
    if (s.reddit_pixel) { counts.reddit++; platformBreakdown.reddit_pixel = (platformBreakdown.reddit_pixel || 0) + 1; }
    if (s.bing_uet) { counts.bing++; platformBreakdown.bing_uet = (platformBreakdown.bing_uet || 0) + 1; }
    if (s.snap_pixel) { counts.snap++; platformBreakdown.snap_pixel = (platformBreakdown.snap_pixel || 0) + 1; }
    if (s.google_analytics) counts.ga++;
    if (s.paid_ads_count > 0) counts.anyPaid++;
    counts.processed++;

    await query(
      `
      INSERT INTO firm_ad_signals (firm_id, ad_signals, signals_updated_at)
      VALUES ($1, $2, now())
      ON CONFLICT (firm_id) DO UPDATE
      SET ad_signals = COALESCE(firm_ad_signals.ad_signals, '{}'::jsonb) || EXCLUDED.ad_signals,
          signals_updated_at = now()
    `,
      [row.firm_id, JSON.stringify(s)]
    );
  }

  console.log(`[ad-signals] processed ${counts.processed} firms (${counts.missing} HTML files missing on disk):`);
  console.log(`  google_ads      : ${counts.google_ads}`);
  console.log(`  meta_pixel      : ${counts.meta}`);
  console.log(`  tiktok_pixel    : ${counts.tiktok}`);
  console.log(`  pinterest_tag   : ${counts.pinterest}`);
  console.log(`  linkedin_insight: ${counts.linkedin}`);
  console.log(`  twitter_pixel   : ${counts.twitter}`);
  console.log(`  reddit_pixel    : ${counts.reddit}`);
  console.log(`  bing_uet        : ${counts.bing}`);
  console.log(`  snap_pixel      : ${counts.snap}`);
  console.log(`  any_paid_ads    : ${counts.anyPaid}`);
  console.log(`  google_analytics: ${counts.ga}  (not paid; just measurement)`);

  const top = Object.entries(platformBreakdown).sort((a, b) => b[1] - a[1]);
  if (top.length) {
    console.log('[ad-signals] top platforms:');
    for (const [k, v] of top.slice(0, 5)) console.log(`  ${k.padEnd(18)} ${v}`);
  }
  await pool.end();
}

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