← back to Nationalrealestate
scripts/enrich-firms-serp.js
269 lines
#!/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); });