← back to Ventura Corridor
src/enrich/ads_transparency.ts
243 lines
/**
* Google Ads Transparency Center scrape.
*
* For each business with the `google_ads` signal, opens
* https://adstransparency.google.com/?domain=<domain>®ion=US in headless
* Chromium, waits for the result list to render, and captures:
* - advertiser name (Google's verified name)
* - ad count
* - first / last seen dates
* - sample ad copy / creative thumbnails (top 3)
*
* Stored as JSONB in business_enrichment.ad_signals.transparency.
*
* Slow (5-10s per business). Cap with --limit. Default 20.
*
* No LLM. No paid API.
*/
import { chromium, Browser } from 'playwright';
import { query, pool } from '../../db/pool.ts';
interface Row {
business_id: number;
name: string;
website: string;
}
function hostOf(u: string): string | null {
try { return new URL(u).hostname.replace(/^www\./, '').toLowerCase(); } catch { return null; }
}
async function pickQueue(limit: number): Promise<Row[]> {
const r = await query<Row>(`
SELECT b.id AS business_id, b.name, b.website
FROM businesses b
JOIN business_enrichment e ON e.business_id = b.id
WHERE b.on_corridor
AND b.website IS NOT NULL
AND e.ad_signals->>'google_ads' = 'true'
AND (e.ad_signals->'transparency' IS NULL
OR e.ad_signals->'transparency'->>'fetched_at' IS NULL)
ORDER BY b.id
LIMIT $1
`, [limit]);
return r.rows;
}
/**
* Re-correction queue: rows that already have a transparency blob with ad_count > 0
* but were written with the buggy selectors (advertiser_name = "See more results",
* sample_creatives = []). Forces a fresh scrape and overwrites the stale data.
*/
async function pickRecorrectQueue(limit: number): Promise<Row[]> {
const r = await query<Row>(`
SELECT b.id AS business_id, b.name, b.website
FROM businesses b
JOIN business_enrichment e ON e.business_id = b.id
WHERE b.on_corridor
AND b.website IS NOT NULL
AND (e.ad_signals->'transparency'->>'ad_count')::int > 0
ORDER BY b.id
LIMIT $1
`, [limit]);
return r.rows;
}
/**
* How confident we are in ad_count.
* confirmed — .ads-count element was present and count > 0
* zero — .ads-count element was present and returned "0 ads"
* element_missing — .ads-count never appeared (page timeout / Google UI change)
* scrape_error — exception thrown before we could read anything
*/
type AdCountConfidence = 'confirmed' | 'zero' | 'element_missing' | 'scrape_error';
interface Transparency {
advertiser_name: string | null;
ad_count: number | null;
/** Distinguishes confirmed-zero from "scraper couldn't find the count element". */
ad_count_confidence: AdCountConfidence;
first_seen: string | null;
last_seen: string | null;
sample_creatives: Array<{ headline?: string; body?: string; thumb?: string; creative_id?: string }>;
fetched_at: string;
raw_url: string;
notes?: string;
}
/**
* Parse Google's ad-count text into a number.
* Handles: "~900 ads", "~7K ads", "~1.2M ads", "4 ads", "0 ads"
* The tilde prefix and K/M suffixes were the two things the old body-text regex missed.
*/
function parseAdCount(raw: string): number {
const s = raw.replace(/[~,]/g, '').trim();
const m = s.match(/^([\d.]+)\s*([KkMm]?)/);
if (!m) return 0;
let n = parseFloat(m[1]);
if (/k/i.test(m[2])) n *= 1_000;
if (/m/i.test(m[2])) n *= 1_000_000;
return Math.round(n);
}
async function scrapeOne(browser: Browser, domain: string): Promise<Transparency> {
const url = `https://adstransparency.google.com/?domain=${encodeURIComponent(domain)}®ion=US`;
const ctx = await browser.newContext({
viewport: { width: 1280, height: 1600 },
userAgent: 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
});
const page = await ctx.newPage();
const result: Transparency = {
advertiser_name: null,
ad_count: null,
ad_count_confidence: 'element_missing',
first_seen: null,
last_seen: null,
sample_creatives: [],
fetched_at: new Date().toISOString(),
raw_url: url,
};
try {
await page.goto(url, { waitUntil: 'domcontentloaded', timeout: 25_000 });
// .ads-count is rendered by the Angular SPA for EVERY domain — including zero-ad
// domains (returns "0 ads"). It is a more reliable settled-state signal than
// body.innerText phrases or creative-grid presence, both of which are absent for
// zero-ad pages. Fall through gracefully on timeout.
await page.waitForSelector('.ads-count', { timeout: 20_000 }).catch(() => {});
// Small buffer for creative-preview cards to render after the count div appears.
await page.waitForTimeout(1000);
const data = await page.evaluate(() => {
const out: Record<string, any> = {};
// ── Ad count ──────────────────────────────────────────────────────────
// DO NOT use body.innerText regex — it matches aria-labels like
// "Advertisement (1 of 80)" and returns the wrong number.
// Also, body.innerText for zero-ad pages does NOT contain "No ads to show"
// reliably; the actual phrase is "No ads found" inside the creative-grid.
const adsCountEl = document.querySelector('.ads-count');
out.adsCountRaw = adsCountEl ? (adsCountEl as HTMLElement).innerText.trim() : null;
out.hasCountEl = !!adsCountEl;
// ── Advertiser name ───────────────────────────────────────────────────
// [class*="advertiser"] was too broad — it matched wrapper divs whose
// innerText resolves to "See more results" (the load-more button text).
// The correct element is .advertiser-name inside a creative-preview card.
// Fallback to top-level .advertiser-name if cards haven't painted yet.
const advEl =
document.querySelector('creative-preview .advertiser-name') ||
document.querySelector('.advertiser-name');
out.advertiser_name = advEl ? (advEl as HTMLElement).innerText.trim() : null;
// ── Creative cards ────────────────────────────────────────────────────
// Custom element is `creative-preview` (not `creative-card` / `creative-result`).
const cards = Array.from(document.querySelectorAll('creative-preview'));
out.creatives = cards.slice(0, 3).map((c) => {
const a = c.querySelector('a');
const img = c.querySelector('html-renderer img, img');
const nameEl = c.querySelector('.advertiser-name');
const href = a?.getAttribute('href') || undefined;
const creativeIdMatch = href ? href.match(/creative\/(CR\w+)/) : null;
return {
headline: (a?.getAttribute('aria-label')) || undefined,
body: nameEl ? (nameEl as HTMLElement).innerText.trim() : undefined,
thumb: (img && (img as HTMLImageElement).src) || undefined,
creative_id: creativeIdMatch ? creativeIdMatch[1] : undefined,
};
});
return out;
});
// ── Resolve ad_count and confidence ────────────────────────────────────
if (!data.hasCountEl) {
// .ads-count was absent — page didn't load fully or Google changed the DOM
result.ad_count = null;
result.ad_count_confidence = 'element_missing';
result.notes = 'ads-count element not found in DOM (page timeout or UI change)';
} else {
const count = parseAdCount(data.adsCountRaw as string);
result.ad_count = count;
result.ad_count_confidence = count > 0 ? 'confirmed' : 'zero';
if (count === 0) {
result.notes = 'transparency-center reports 0 ads for this domain';
}
}
result.advertiser_name = data.advertiser_name as string | null;
result.sample_creatives = (data.creatives as Transparency['sample_creatives']) || [];
} catch (e: any) {
result.ad_count_confidence = 'scrape_error';
result.notes = 'scrape error: ' + (e.message || String(e)).slice(0, 200);
} finally {
await ctx.close().catch(() => {});
}
return result;
}
async function main() {
const argLimit = process.argv.find((a) => a.startsWith('--limit='));
const recorrect = process.argv.includes('--recorrect');
const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : (recorrect ? 100 : 20);
const queue = recorrect ? await pickRecorrectQueue(limit) : await pickQueue(limit);
if (recorrect) console.log(`[adst] recorrect mode — rewriting ${queue.length} verified-active rows with fixed selectors`);
console.log(`[adst] ${queue.length} businesses to scrape · headless chromium`);
const browser = await chromium.launch({ headless: true });
let done = 0, withAds = 0, errors = 0, noAds = 0, elementMissing = 0;
for (const row of queue) {
const domain = hostOf(row.website);
if (!domain) continue;
const t = await scrapeOne(browser, domain);
switch (t.ad_count_confidence) {
case 'confirmed': withAds++; break;
case 'zero': noAds++; break;
case 'element_missing': elementMissing++; break;
case 'scrape_error': errors++; break;
}
// Merge into existing ad_signals JSONB (same || pattern as ad_signals.ts:119-130).
// Only 'transparency' is overwritten; all other top-level keys are preserved.
await query(
`
UPDATE business_enrichment
SET ad_signals = COALESCE(ad_signals, '{}'::jsonb)
|| jsonb_build_object('transparency', $2::jsonb),
signals_updated_at = now()
WHERE business_id = $1
`,
[row.business_id, JSON.stringify(t)]
);
done++;
const conf = t.ad_count_confidence.padEnd(15);
console.log(` [${done}/${queue.length}] ${row.name.slice(0, 35).padEnd(35)} · ${domain.padEnd(28)} ads=${String(t.ad_count ?? '?').padStart(6)} conf=${conf}${t.notes ? ' · ' + t.notes.slice(0, 50) : ''}`);
}
await browser.close();
console.log(`[adst] done · ${done} processed · ${withAds} confirmed-ads · ${noAds} zero-ads · ${elementMissing} element-missing · ${errors} errors`);
await pool.end();
}
main().catch((e) => { console.error(e); process.exit(1); });