← back to Nationalrealestate
TK-10681: add enrich-firms-serp.js — SERP-only residential firm enrichment (no Ollama, JSONL output)
05583db9281cc49a371021d87de11a288fe60827 · 2026-08-23 04:18:18 -0700 · Steve Abrams
Lighter SERP enrichment path alongside the existing enrich-firm-contacts.mjs (which requires
Ollama). This variant uses pure regex phone/email extraction from Bing + DDG HTML search results,
outputs a JSONL file instead of writing to the DB, and caps at 200 firms/run with 1 req/2s
rate limiting. 20-firm pilot: 0/20 actionable finds on the oldest license-number cohort
(likely defunct firms); false-positive rate on that cohort is high without Ollama validation.
Complement the existing enrichment run rather than replace it.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Files touched
A data/serp-enrich-2026-08-23T11-14-17.jsonlA scripts/enrich-firms-serp.js
Diff
commit 05583db9281cc49a371021d87de11a288fe60827
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 23 04:18:18 2026 -0700
TK-10681: add enrich-firms-serp.js — SERP-only residential firm enrichment (no Ollama, JSONL output)
Lighter SERP enrichment path alongside the existing enrich-firm-contacts.mjs (which requires
Ollama). This variant uses pure regex phone/email extraction from Bing + DDG HTML search results,
outputs a JSONL file instead of writing to the DB, and caps at 200 firms/run with 1 req/2s
rate limiting. 20-firm pilot: 0/20 actionable finds on the oldest license-number cohort
(likely defunct firms); false-positive rate on that cohort is high without Ollama validation.
Complement the existing enrichment run rather than replace it.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
data/serp-enrich-2026-08-23T11-14-17.jsonl | 20 +++
scripts/enrich-firms-serp.js | 268 +++++++++++++++++++++++++++++
2 files changed, 288 insertions(+)
diff --git a/data/serp-enrich-2026-08-23T11-14-17.jsonl b/data/serp-enrich-2026-08-23T11-14-17.jsonl
new file mode 100644
index 0000000..803ae51
--- /dev/null
+++ b/data/serp-enrich-2026-08-23T11-14-17.jsonl
@@ -0,0 +1,20 @@
+{"firm_id":398481,"name":"Pearson Realty","city":"FRESNO","state":"CA","license_no":"00020875","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:14:26.535Z","status":"not_found"}
+{"firm_id":398483,"name":"Carlgate Corporation","city":"VICTORVILLE","state":"CA","license_no":"00025885","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:14:39.298Z","status":"not_found"}
+{"firm_id":398484,"name":"The Alison Company","city":"NEWPORT BEACH","state":"CA","license_no":"00028311","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:14:45.055Z","status":"not_found"}
+{"firm_id":398485,"name":"Boydstun Realty Co Inc","city":"BAKERSFIELD","state":"CA","license_no":"00032002","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:14:52.101Z","status":"not_found"}
+{"firm_id":398489,"name":"First Security Investment Co Inc","city":"LOS ANGELES","state":"CA","license_no":"00043716","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:14:57.977Z","status":"not_found"}
+{"firm_id":398491,"name":"Curtis Coleman Co","city":"SAN DIEGO","state":"CA","license_no":"00081351","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:03.796Z","status":"not_found"}
+{"firm_id":398492,"name":"De Wolf Realty Co","city":"SAN FRANCISCO","state":"CA","license_no":"00084876","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:13.054Z","status":"not_found"}
+{"firm_id":398494,"name":"West Edge Inc","city":"SAN DIEGO","state":"CA","license_no":"01481919","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:18.697Z","status":"not_found"}
+{"firm_id":398495,"name":"Vanguard Properties, Inc.","city":"SAN FRANCISCO","state":"CA","license_no":"01486075","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:24.636Z","status":"not_found"}
+{"firm_id":398496,"name":"Sotheby's International Realty Inc","city":"BEVERLY HILLS","state":"CA","license_no":"00899496","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:29.938Z","status":"not_found"}
+{"firm_id":398497,"name":"Glendale Properties Inc","city":"GLENDALE","state":"CA","license_no":"00134795","phone_found":"(888) 204-3131","email_found":"","source_url":"https://forestlawn.com/parks/glendale/","urls_tried":4,"ts":"2026-08-23T11:15:34.757Z","status":"found"}
+{"firm_id":398502,"name":"Sunset Development Company","city":"SAN RAMON","state":"CA","license_no":"00164527","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:41.639Z","status":"not_found"}
+{"firm_id":398504,"name":"Ventura Investment Co","city":"CAMARILLO","state":"CA","license_no":"00168401","phone_found":"(201) 363-1292","email_found":"","source_url":"https://en.wikipedia.org/wiki/Ventura,_California","urls_tried":4,"ts":"2026-08-23T11:15:44.970Z","status":"found"}
+{"firm_id":398509,"name":"Elmer F Karpe Inc","city":"BAKERSFIELD","state":"CA","license_no":"00179232","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:52.401Z","status":"not_found"}
+{"firm_id":398510,"name":"William L Lyon & Associates Inc","city":"SACRAMENTO","state":"CA","license_no":"00182401","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:15:58.580Z","status":"not_found"}
+{"firm_id":398511,"name":"Squire Realty","city":"AZUSA","state":"CA","license_no":"00184544","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:16:04.277Z","status":"not_found"}
+{"firm_id":398512,"name":"Owens Financial Group Inc","city":"WALNUT CREEK","state":"CA","license_no":"00188862","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:16:15.331Z","status":"not_found"}
+{"firm_id":398513,"name":"Richley Inc","city":"LOS ANGELES","state":"CA","license_no":"00201043","phone_found":"","email_found":"","source_url":"","urls_tried":4,"ts":"2026-08-23T11:16:22.891Z","status":"not_found"}
+{"firm_id":398520,"name":"Coast Brokers Inc","city":"LAKE ELSINORE","state":"CA","license_no":"00222692","phone_found":"(626) 676-4652","email_found":"kip@coast2coastroasters.com","source_url":"https://coast2coastroasters.com/","urls_tried":4,"ts":"2026-08-23T11:16:27.824Z","status":"found"}
+{"firm_id":398521,"name":"Sail Realty","city":"MARINA DEL REY","state":"CA","license_no":"00223849","phone_found":"(361) 825-2692","email_found":"marcom@tamucc.edu","source_url":"https://www.tamucc.edu/sail/","urls_tried":4,"ts":"2026-08-23T11:16:31.667Z","status":"found"}
diff --git a/scripts/enrich-firms-serp.js b/scripts/enrich-firms-serp.js
new file mode 100644
index 0000000..7460760
--- /dev/null
+++ b/scripts/enrich-firms-serp.js
@@ -0,0 +1,268 @@
+#!/usr/bin/env node
+// enrich-firms-serp.js — TK-10681 free-SERP residential firm contact enrichment.
+// Lightweight variant of enrich-firm-contacts.mjs:
+// - No Ollama / no local model (pure regex extraction, faster, always works)
+// - Writes results to JSONL output file only, NO DB writes (safe to run anytime)
+// - Rate-limited: 1 req / 2s, max 200 firms per batch
+//
+// Usage:
+// node scripts/enrich-firms-serp.js --limit 20 # sample 20 firms, write JSONL
+// node scripts/enrich-firms-serp.js --limit 200 # full batch cap
+// node scripts/enrich-firms-serp.js --limit 50 --state CA --asset-class residential
+//
+// Output: data/serp-enrich-YYYYMMDD-HHMMSS.jsonl (one JSON per line per firm)
+// Each line: { firm_id, name, city, state, phone_found, email_found, source_url, ts, status }
+'use strict';
+
+import pg from 'pg';
+import fs from 'fs';
+import path from 'path';
+import { fileURLToPath } from 'url';
+
+const __dir = path.dirname(fileURLToPath(import.meta.url));
+const DB = process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp';
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
+
+const arg = (k, d) => { const i = process.argv.indexOf(k); return i > -1 ? (process.argv[i + 1] ?? true) : d; };
+const LIMIT = Math.min(+arg('--limit', 20) || 20, 200);
+const STATE = arg('--state', 'CA');
+const ASSET_CLASS = arg('--asset-class', 'residential');
+const DELAY_MS = 2000; // 1 req / 2s — free SERP rate limit
+
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+// ── regex phone/email extraction ─────────────────────────────────────────────
+const PHONE_RE = /(?:\+?1[\s.\-]?)?\(?([2-9]\d{2})\)?[\s.\-]?(\d{3})[\s.\-]?(\d{4})\b/g;
+const EMAIL_RE = /\b[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}\b/gi;
+const BAD_PHONE = /^\(?(000|111|123|555)/.test.bind(/^\(?(000|111|123|555)/);
+
+function normPhone(raw) {
+ const m = raw.match(/([2-9]\d{2})[^\d]*(\d{3})[^\d]*(\d{4})/);
+ return m ? `(${m[1]}) ${m[2]}-${m[3]}` : '';
+}
+
+function extractPhones(text) {
+ const out = new Set(); let m;
+ PHONE_RE.lastIndex = 0;
+ while ((m = PHONE_RE.exec(text))) {
+ if (!BAD_PHONE(`${m[1]}${m[2]}`)) out.add(`(${m[1]}) ${m[2]}-${m[3]}`);
+ }
+ return [...out].slice(0, 8);
+}
+
+function extractEmails(text) {
+ const out = new Set();
+ for (const e of (text.match(EMAIL_RE) || [])) {
+ const lo = e.toLowerCase();
+ if (/\.(png|jpg|jpeg|gif|webp|svg|css|js)$/.test(lo)) continue;
+ if (/(sentry|wix|squarespace|godaddy|@2x|example\.|domain\.com|noreply|no-reply)/.test(lo)) continue;
+ out.add(lo);
+ }
+ return [...out].slice(0, 6);
+}
+
+function stripHtml(html) {
+ return String(html || '')
+ .replace(/<script[\s\S]*?<\/script>/gi, ' ')
+ .replace(/<style[\s\S]*?<\/style>/gi, ' ')
+ .replace(/<[^>]+>/g, ' ')
+ .replace(/\s+/g, ' ')
+ .trim()
+ .slice(0, 8000);
+}
+
+// ── fetch with timeout ────────────────────────────────────────────────────────
+async function fetchText(url, timeout = 10000) {
+ try {
+ const ctl = new AbortController();
+ const to = setTimeout(() => ctl.abort(), timeout);
+ const r = await fetch(url, {
+ headers: { 'User-Agent': UA, 'Accept': 'text/html,*/*', 'Accept-Language': 'en-US,en;q=0.9' },
+ redirect: 'follow',
+ signal: ctl.signal,
+ });
+ clearTimeout(to);
+ if (!r.ok) return '';
+ return await r.text();
+ } catch {
+ return '';
+ }
+}
+
+// aggregator domains to deprioritize (they won't have the brokerage's OWN phone)
+const AGG_RE = /zillow\.com|trulia\.com|realtor\.com|redfin\.com|homes\.com|yelp\.com|yellowpages|whitepages|bbb\.org|manta\.com|linkedin\.com|facebook\.com|google\.com/i;
+
+// ── DuckDuckGo HTML search ────────────────────────────────────────────────────
+async function searchDDG(q) {
+ const html = await fetchText('https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q));
+ if (!html) return [];
+ const urls = [];
+ for (const m of html.matchAll(/<a[^>]+class="result__a"[^>]+href="([^"]+)"/gi)) {
+ let u = m[1];
+ const dd = u.match(/uddg=([^&]+)/);
+ if (dd) { try { u = decodeURIComponent(dd[1]); } catch {} }
+ if (/^https?:\/\//.test(u)) urls.push(u);
+ }
+ // Also try the plain href pattern
+ for (const m of html.matchAll(/href="(https?:\/\/[^"]+)"/gi)) {
+ if (!/duckduckgo\.com/.test(m[1])) urls.push(m[1]);
+ }
+ return [...new Set(urls)];
+}
+
+// ── Bing HTML search ─────────────────────────────────────────────────────────
+function decodeBing(u) {
+ const m = u.match(/[?&]u=a1([^&]+)/);
+ if (!m) return u;
+ try { return Buffer.from(m[1].replace(/-/g, '+').replace(/_/g, '/'), 'base64').toString('utf8'); } catch { return u; }
+}
+
+async function searchBing(q) {
+ const html = await fetchText('https://www.bing.com/search?q=' + encodeURIComponent(q));
+ if (!html) return [];
+ const urls = [];
+ for (const m of html.matchAll(/href="(https?:\/\/www\.bing\.com\/ck\/a\?[^"]*&(?:amp;)?u=a1[^"]+)"/g)) {
+ const u = decodeBing(m[1].replace(/&/g, '&'));
+ if (/^https?:\/\//.test(u) && !/bing\.com/.test(u)) urls.push(u);
+ }
+ for (const m of html.matchAll(/<h2><a[^>]+href="(https?:\/\/(?!www\.bing)[^"]+)"/g)) urls.push(m[1]);
+ return [...new Set(urls)];
+}
+
+// ── main enrichment for one firm ─────────────────────────────────────────────
+async function enrichFirm(firm) {
+ const { id, name, hq_city, hq_state, license_no } = firm;
+ const city = hq_city || '';
+ const q = `"${name}" ${city} California real estate brokerage phone`;
+
+ // Bing first (more resilient), DDG fallback
+ let urls = await searchBing(q);
+ await sleep(DELAY_MS);
+ if (!urls.length) {
+ urls = await searchDDG(q);
+ await sleep(DELAY_MS);
+ }
+
+ // Also try CA DRE license lookup if we have a license number
+ if (license_no) {
+ urls.push(`https://www2.dre.ca.gov/PublicASP/pplinfo.asp?License_id=${license_no}`);
+ }
+
+ // Prioritize own-site URLs over aggregators
+ const prioritized = [
+ ...urls.filter(u => !AGG_RE.test(u)),
+ ...urls.filter(u => AGG_RE.test(u)),
+ ].slice(0, 4);
+
+ let phone = '';
+ let email = '';
+ let source_url = '';
+
+ for (const url of prioritized) {
+ await sleep(500); // light throttle between page fetches
+ const html = await fetchText(url, 8000);
+ if (!html || html.length < 200) continue;
+ const text = stripHtml(html);
+
+ const phones = extractPhones(text);
+ const emails = extractEmails(text);
+
+ if (phones.length || emails.length) {
+ // Prefer a phone that appears near "office", "main", "call" in context
+ const officePhone = phones.find(p => {
+ const idx = text.indexOf(p.replace(/[()]/g, '\\(\\)')
+ .replace(/[\s.\-]/g, '[\\s.\\-]?'));
+ if (idx < 0) return false;
+ const ctx = text.slice(Math.max(0, idx - 60), idx + 60).toLowerCase();
+ return /office|main|call|contact|reach|headquarter/i.test(ctx);
+ });
+ phone = officePhone || phones[0] || '';
+
+ // Prefer a business email (info@, contact@, team@, etc.)
+ const bizEmail = emails.find(e => /^(info|contact|hello|sales|office|admin|team|support|inquir)@/.test(e));
+ email = bizEmail || emails[0] || '';
+ source_url = url;
+ break;
+ }
+ }
+
+ return {
+ firm_id: id,
+ name,
+ city,
+ state: hq_state || STATE,
+ license_no: license_no || '',
+ phone_found: phone,
+ email_found: email,
+ source_url,
+ urls_tried: prioritized.length,
+ ts: new Date().toISOString(),
+ status: phone || email ? 'found' : 'not_found',
+ };
+}
+
+// ── main ──────────────────────────────────────────────────────────────────────
+async function main() {
+ const pool = new pg.Pool({ connectionString: DB });
+
+ // Firms with active brokers, missing phone, with a name and city
+ const ACTIVE = `(license_status ILIKE 'active%' OR license_status ILIKE 'licensed%' OR license_status ILIKE 'current / active%')`;
+ const { rows: firms } = await pool.query(`
+ SELECT DISTINCT ON (f.id)
+ f.id, f.name, f.hq_city, f.hq_state, f.license_no
+ FROM firm f
+ WHERE f.asset_class = $1
+ AND f.license_state = $2
+ AND COALESCE(NULLIF(f.phone, ''), '') = ''
+ AND f.name IS NOT NULL AND f.name != ''
+ AND f.hq_city IS NOT NULL AND f.hq_city != ''
+ AND EXISTS (
+ SELECT 1 FROM broker b
+ WHERE b.firm_id = f.id
+ AND b.license_state = $2
+ AND ${ACTIVE}
+ )
+ AND (f.contact_attempts IS NULL OR f.contact_attempts < 3)
+ ORDER BY f.id
+ LIMIT $3
+ `, [ASSET_CLASS, STATE, LIMIT]);
+
+ await pool.end();
+
+ console.log(`[enrich-firms-serp] ${firms.length} firms queued (limit ${LIMIT}, state ${STATE}, asset_class ${ASSET_CLASS})`);
+ console.log(`[enrich-firms-serp] $0 — Bing HTML + DuckDuckGo HTML + CA DRE lookup (free)`);
+ console.log('');
+
+ const ts = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19);
+ const outPath = path.join(__dir, '..', 'data', `serp-enrich-${ts}.jsonl`);
+ const out = fs.createWriteStream(outPath, { flags: 'a' });
+
+ let found = 0;
+ let attempted = 0;
+
+ for (const firm of firms) {
+ attempted++;
+ process.stdout.write(`[${attempted}/${firms.length}] ${firm.name} (${firm.hq_city || '?'}) ... `);
+
+ const result = await enrichFirm(firm);
+ out.write(JSON.stringify(result) + '\n');
+
+ if (result.status === 'found') {
+ found++;
+ console.log(`FOUND: phone="${result.phone_found}" email="${result.email_found}" src=${result.source_url}`);
+ } else {
+ console.log('not found');
+ }
+
+ // Inter-firm delay (rate limit: 1 SERP req per 2s already handled in enrichFirm)
+ // Extra 500ms buffer between firms
+ await sleep(500);
+ }
+
+ out.end();
+ console.log('');
+ console.log(`DONE: found ${found}/${attempted} (${((found/attempted)*100).toFixed(1)}% yield) · $0 cost (local fetch + free SERP)`);
+ console.log(`Output: ${outPath}`);
+}
+
+main().catch(e => { console.error('FATAL', e); process.exit(1); });
← cd32cb6 auto-data-snapshot: 2026-08-23T03:15:26 (1 data files) — dat
·
back to Nationalrealestate
·
TK-10687: add DRE enrichment strategy memo f794458 →