← back to Ventura Corridor
src/score/seo_score.ts
219 lines
/**
* SEO scorer — Phase iii.
*
* Reads the latest front_page_audits row per business, computes a 12-signal
* weighted score (0-100), assigns a tier (A/B/C/D), and ranks each business
* within its top-level category. Inserts one row per scoring run.
*
* Mirrors the lawyer/doctor methodology v1.0 weights, adapted for storefront
* directory businesses (no §6155 layer; weights bias toward conversion-relevant
* signals: HTTPS, mobile, schema, title/meta, alt-text, performance bands).
*
* Run: npm run score
*/
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';
interface LatestAudit {
audit_id: number;
business_id: number;
category: string | null;
has_https: boolean | null;
has_viewport: boolean | null;
has_title: boolean | null;
title_text: string | null;
meta_desc: string | null;
has_h1: boolean | null;
h1_text: string | null;
schema_org_count: number | null;
schema_org_types: string[] | null;
og_image: string | null;
alt_coverage: number | null;
word_count: number | null;
outbound_links: number | null;
internal_links: number | null;
bytes_html: number | null;
bytes_total: number | null;
ttfb_ms: number | null;
load_ms: number | null;
status_code: number | null;
error_message: string | null;
}
// 12 signals × weights = 100. Each signal returns its earned points.
const SIGNAL_WEIGHTS = {
has_https: 10,
has_viewport: 10,
has_title: 8,
title_quality: 6, // length 30-65
meta_desc: 8, // present + length 70-160
has_h1: 8,
schema_org: 10, // count >= 1, +bonus for LocalBusiness/Organization
og_image: 5,
alt_coverage: 10, // 0..1 fraction
word_count: 8, // 80-2000 sweet spot
ttfb_band: 9, // <300ms = full
load_band: 8, // <2500ms = full
};
function scoreSignals(a: LatestAudit): { signals: Record<string, number>; total: number } {
const s: Record<string, number> = {};
s.has_https = a.has_https ? SIGNAL_WEIGHTS.has_https : 0;
s.has_viewport = a.has_viewport ? SIGNAL_WEIGHTS.has_viewport : 0;
s.has_title = a.has_title ? SIGNAL_WEIGHTS.has_title : 0;
// Title quality: 30-65 chars is the sweet spot for SERP truncation.
const tl = a.title_text?.length || 0;
s.title_quality = (tl >= 30 && tl <= 65) ? SIGNAL_WEIGHTS.title_quality
: (tl > 0) ? Math.round(SIGNAL_WEIGHTS.title_quality * 0.5)
: 0;
// Meta description: present + roughly within 70-160 chars
const ml = a.meta_desc?.length || 0;
s.meta_desc = (ml >= 70 && ml <= 200) ? SIGNAL_WEIGHTS.meta_desc
: (ml > 0) ? Math.round(SIGNAL_WEIGHTS.meta_desc * 0.5)
: 0;
s.has_h1 = a.has_h1 ? SIGNAL_WEIGHTS.has_h1 : 0;
// Schema.org: any LD-JSON earns half; LocalBusiness/Organization gets full.
const types = (a.schema_org_types || []).map((t) => t.toLowerCase());
const hasLocal = types.some((t) => /localbusiness|restaurant|store|organization|professionalservice/.test(t));
s.schema_org = (a.schema_org_count || 0) > 0
? (hasLocal ? SIGNAL_WEIGHTS.schema_org : Math.round(SIGNAL_WEIGHTS.schema_org * 0.5))
: 0;
s.og_image = a.og_image ? SIGNAL_WEIGHTS.og_image : 0;
// Alt coverage: linear from 0..1
const ac = a.alt_coverage ?? 0;
s.alt_coverage = Math.round(SIGNAL_WEIGHTS.alt_coverage * Math.max(0, Math.min(1, ac)));
// Word count: 80-2000 sweet spot; <80 anemic, >2000 likely text-spam (decays).
const wc = a.word_count || 0;
s.word_count = (wc >= 80 && wc <= 2000) ? SIGNAL_WEIGHTS.word_count
: (wc > 0 && wc < 80) ? Math.round(SIGNAL_WEIGHTS.word_count * (wc / 80))
: (wc > 2000) ? Math.round(SIGNAL_WEIGHTS.word_count * 0.6)
: 0;
// TTFB: <300 full, <800 half, else 0
const ttfb = a.ttfb_ms ?? 9999;
s.ttfb_band = ttfb <= 300 ? SIGNAL_WEIGHTS.ttfb_band
: ttfb <= 800 ? Math.round(SIGNAL_WEIGHTS.ttfb_band * 0.5)
: 0;
// Load: <2500 full, <5000 half, else 0
const load = a.load_ms ?? 99999;
s.load_band = load <= 2500 ? SIGNAL_WEIGHTS.load_band
: load <= 5000 ? Math.round(SIGNAL_WEIGHTS.load_band * 0.5)
: 0;
// If the page errored or returned non-2xx, force the score to a hard floor:
// technical attributes don't matter if the front page is broken.
if (a.error_message || (a.status_code && (a.status_code < 200 || a.status_code >= 400))) {
for (const k of Object.keys(s)) s[k] = 0;
}
const total = Object.values(s).reduce((acc, v) => acc + v, 0);
return { signals: s, total };
}
function scoreToTier(total: number): 'A' | 'B' | 'C' | 'D' {
if (total >= 80) return 'A';
if (total >= 60) return 'B';
if (total >= 40) return 'C';
return 'D';
}
async function main() {
console.log('[score] reading latest audit per business…');
const r = await query<LatestAudit>(`
WITH latest AS (
SELECT DISTINCT ON (business_id) *
FROM front_page_audits
ORDER BY business_id, audited_at DESC
)
SELECT l.id AS audit_id, b.id AS business_id, b.category,
l.has_https, l.has_viewport, l.has_title, l.title_text, l.meta_desc,
l.has_h1, l.h1_text, l.schema_org_count, l.schema_org_types,
l.og_image, l.alt_coverage, l.word_count,
l.outbound_links, l.internal_links, l.bytes_html, l.bytes_total,
l.ttfb_ms, l.load_ms, l.status_code, l.error_message
FROM latest l
JOIN businesses b ON b.id = l.business_id
WHERE b.on_corridor
`);
if (!r.rowCount) {
console.log('[score] no audits to score yet — run the crawler first.');
await pool.end();
return;
}
// Score in memory first, then rank within category, then bulk-insert.
const scored = r.rows.map((a) => {
const { signals, total } = scoreSignals(a);
return { audit_id: a.audit_id, business_id: a.business_id, category: a.category, signals, total };
});
// Rank within category (top-level bucket — split on ':' to collapse e.g.
// "amenity: restaurant" and "amenity: cafe" into the same competitor cohort).
const byBucket = new Map<string, typeof scored>();
for (const x of scored) {
const bucket = (x.category || 'uncategorized').split(':')[0].trim().toLowerCase();
if (!byBucket.has(bucket)) byBucket.set(bucket, []);
byBucket.get(bucket)!.push(x);
}
for (const arr of byBucket.values()) {
arr.sort((a, b) => b.total - a.total);
arr.forEach((x, i) => {
(x as any).category_rank = i + 1;
(x as any).category_n = arr.length;
});
}
console.log(`[score] scoring ${scored.length} businesses across ${byBucket.size} category buckets`);
let inserted = 0;
for (const x of scored) {
await query(`
INSERT INTO seo_scores (audit_id, business_id, total, signals, tier, category_rank, category_n)
VALUES ($1, $2, $3, $4, $5, $6, $7)
`, [x.audit_id, x.business_id, x.total, JSON.stringify(x.signals), scoreToTier(x.total),
(x as any).category_rank, (x as any).category_n]);
inserted++;
}
// Tier histogram + top 10 + bottom 10
const tier = await query<{ tier: string; n: string; avg: string }>(`
SELECT tier, COUNT(*)::text AS n, ROUND(AVG(total))::text AS avg
FROM seo_scores s
WHERE scored_at > NOW() - INTERVAL '5 minutes'
GROUP BY tier ORDER BY tier
`);
console.log('');
console.log(`[score] inserted ${inserted} score rows`);
console.log('[score] tier distribution (this run):');
for (const t of tier.rows) console.log(` ${t.tier} ${t.n.padStart(4)} · avg ${t.avg}`);
const top = await query<{ name: string; total: number; tier: string; category: string }>(`
SELECT b.name, s.total, s.tier, b.category
FROM seo_scores s JOIN businesses b ON b.id = s.business_id
WHERE s.scored_at > NOW() - INTERVAL '5 minutes'
ORDER BY s.total DESC, b.name LIMIT 10
`);
console.log('');
console.log('[score] top 10 by score:');
for (const r of top.rows) {
console.log(` ${String(r.total).padStart(3)} ${r.tier} ${r.name.padEnd(34)} ${r.category || ''}`);
}
await pool.end();
}
main().catch((e) => {
console.error('[score]', e);
process.exit(1);
});