← back to Rentv Adintel
db/seed/scores.js
318 lines
'use strict';
/**
* Seed opportunity_scores and opportunity_stages.
*
* For every seeded org, compute a score via scoring.explainScore() with
* sensible per-org factors and INSERT into opportunity_scores.
* Also seeds opportunity_stages (Prospect/Contacted/Proposal Sent/Won/Lost).
*
* Idempotent: replaces scores on re-run (delete+reinsert per org), stages via
* ON CONFLICT (key) DO UPDATE.
*/
const { explainScore } = require('../../lib/scoring');
const { normalizeName } = require('../../lib/types');
// Opportunity stages (§deliverable)
const STAGES = [
{ key: 'prospect', label: 'Prospect', sort_order: 1 },
{ key: 'contacted', label: 'Contacted', sort_order: 2 },
{ key: 'proposal_sent', label: 'Proposal Sent', sort_order: 3 },
{ key: 'won', label: 'Won', sort_order: 4 },
{ key: 'lost', label: 'Lost', sort_order: 5 },
];
/**
* Factor inputs per org name (normalized_name key → input object).
* Verified advertisers get high verifiedAdvertising + recency + evidenceQuality.
* Panelists get moderate categoryFit + californiaFit but low verifiedAdvertising.
* LIKELY_PROSPECT get higher scores than SPEAKER_OR_PANELIST_ONLY.
*/
const ORG_FACTOR_INPUTS = {
// ---- VERIFIED_ADVERTISER (high verified advertising, high recency) --------
'hanley investment': {
verifiedAdvertising: 95,
verifiedConferenceSpendSignal: 60,
recency: 98, // observed 2026-07-07, very recent
repeatActivity: 70,
californiaFit: 100,
arizonaFit: 20,
categoryFit: 90,
rentvAudienceFit: 90,
contactCompleteness: 55,
evidenceQuality: 95,
},
'chase partners': {
verifiedAdvertising: 95,
verifiedConferenceSpendSignal: 90,
recency: 96, // observed 2026-06-29
repeatActivity: 70,
californiaFit: 100,
arizonaFit: 20,
categoryFit: 90,
rentvAudienceFit: 88,
contactCompleteness: 50,
evidenceQuality: 95,
},
'fidelity mortgage lenders': {
verifiedAdvertising: 95,
verifiedConferenceSpendSignal: 90,
recency: 96,
repeatActivity: 70,
californiaFit: 100,
arizonaFit: 20,
categoryFit: 88,
rentvAudienceFit: 88,
contactCompleteness: 50,
evidenceQuality: 95,
},
'rockefeller': {
verifiedAdvertising: 95,
verifiedConferenceSpendSignal: 90,
recency: 96,
repeatActivity: 70,
californiaFit: 90,
arizonaFit: 30,
categoryFit: 85,
rentvAudienceFit: 85,
contactCompleteness: 50,
evidenceQuality: 95,
},
'provident savings bank': {
verifiedAdvertising: 95,
verifiedConferenceSpendSignal: 90,
recency: 96,
repeatActivity: 65,
californiaFit: 100,
arizonaFit: 10,
categoryFit: 87,
rentvAudienceFit: 87,
contactCompleteness: 50,
evidenceQuality: 95,
},
// ---- VERIFIED_CONTENT_PARTNER (not a paid sponsor) -----------------------
'costar': {
verifiedAdvertising: 20, // content partner only, NOT paid advertiser
verifiedConferenceSpendSignal: 70,
recency: 90,
repeatActivity: 60,
californiaFit: 95,
arizonaFit: 70,
categoryFit: 80,
rentvAudienceFit: 80,
contactCompleteness: 40,
evidenceQuality: 90,
},
// ---- LIKELY_PROSPECT orgs ------------------------------------------------
'cbre': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 50,
recency: 75,
repeatActivity: 40,
californiaFit: 98,
arizonaFit: 80,
categoryFit: 95,
rentvAudienceFit: 95,
contactCompleteness: 35,
evidenceQuality: 60,
},
'trammell crow': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 50,
recency: 70,
repeatActivity: 35,
californiaFit: 90,
arizonaFit: 70,
categoryFit: 90,
rentvAudienceFit: 88,
contactCompleteness: 30,
evidenceQuality: 55,
},
'rexford industrial': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 45,
recency: 70,
repeatActivity: 30,
californiaFit: 98,
arizonaFit: 20,
categoryFit: 85,
rentvAudienceFit: 85,
contactCompleteness: 30,
evidenceQuality: 55,
},
'nai capital': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 45,
recency: 70,
repeatActivity: 30,
californiaFit: 98,
arizonaFit: 20,
categoryFit: 90,
rentvAudienceFit: 90,
contactCompleteness: 30,
evidenceQuality: 55,
},
'cushman wakefield': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 55,
recency: 70,
repeatActivity: 40,
californiaFit: 95,
arizonaFit: 75,
categoryFit: 93,
rentvAudienceFit: 92,
contactCompleteness: 35,
evidenceQuality: 60,
},
'western alliance bank': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 45,
recency: 70,
repeatActivity: 30,
californiaFit: 80,
arizonaFit: 90,
categoryFit: 85,
rentvAudienceFit: 83,
contactCompleteness: 30,
evidenceQuality: 55,
},
'commonwealth land title': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 40,
recency: 65,
repeatActivity: 25,
californiaFit: 90,
arizonaFit: 30,
categoryFit: 82,
rentvAudienceFit: 82,
contactCompleteness: 25,
evidenceQuality: 50,
},
'george smith partners': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 45,
recency: 65,
repeatActivity: 30,
californiaFit: 95,
arizonaFit: 20,
categoryFit: 85,
rentvAudienceFit: 84,
contactCompleteness: 30,
evidenceQuality: 50,
},
'ipa': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 45,
recency: 65,
repeatActivity: 30,
californiaFit: 90,
arizonaFit: 50,
categoryFit: 88,
rentvAudienceFit: 88,
contactCompleteness: 25,
evidenceQuality: 50,
},
'colliers': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 50,
recency: 70,
repeatActivity: 35,
californiaFit: 95,
arizonaFit: 75,
categoryFit: 93,
rentvAudienceFit: 90,
contactCompleteness: 30,
evidenceQuality: 55,
},
'lyon stahl investment real estate': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 40,
recency: 65,
repeatActivity: 25,
californiaFit: 95,
arizonaFit: 10,
categoryFit: 85,
rentvAudienceFit: 83,
contactCompleteness: 25,
evidenceQuality: 50,
},
// ---- SPEAKER_OR_PANELIST_ONLY (lower scores, no verified advertising) ----
// Default for any org not specifically listed above
'_default_speaker': {
verifiedAdvertising: 0,
verifiedConferenceSpendSignal: 35,
recency: 60,
repeatActivity: 20,
californiaFit: 80,
arizonaFit: 15,
categoryFit: 70,
rentvAudienceFit: 70,
contactCompleteness: 20,
evidenceQuality: 45,
},
};
function getFactorInput(normalizedName) {
// Try exact match, then prefix match on the normalized name
if (ORG_FACTOR_INPUTS[normalizedName]) return ORG_FACTOR_INPUTS[normalizedName];
for (const key of Object.keys(ORG_FACTOR_INPUTS)) {
if (key === '_default_speaker') continue;
if (normalizedName.startsWith(key) || key.startsWith(normalizedName.split(' ')[0])) {
return ORG_FACTOR_INPUTS[key];
}
}
return ORG_FACTOR_INPUTS['_default_speaker'];
}
async function seedScores(client) {
let stages_inserted = 0;
let stages_updated = 0;
let scores_inserted = 0;
// Seed opportunity_stages
for (const s of STAGES) {
const res = await client.query(
`INSERT INTO opportunity_stages (key, label, sort_order)
VALUES ($1,$2,$3)
ON CONFLICT (key) DO UPDATE SET label=EXCLUDED.label, sort_order=EXCLUDED.sort_order
RETURNING (xmax = 0) AS is_insert`,
[s.key, s.label, s.sort_order]
);
if (res.rows[0].is_insert) stages_inserted++; else stages_updated++;
}
// Fetch all seeded orgs
const orgsRes = await client.query(
`SELECT id, display_name, normalized_name FROM organizations`
);
for (const org of orgsRes.rows) {
// Delete existing score(s) for this org (re-seed is a full replace)
await client.query(`DELETE FROM opportunity_scores WHERE organization_id=$1`, [org.id]);
const factorInput = getFactorInput(org.normalized_name);
const explained = explainScore(factorInput);
await client.query(
`INSERT INTO opportunity_scores (organization_id, score, factors, computed_at)
VALUES ($1,$2,$3::jsonb,now())`,
[
org.id,
explained.score,
JSON.stringify({
input: factorInput,
weights: explained.weights,
factors: explained.factors,
score: explained.score,
note: 'Computed by seed/scores.js. Factors are initial estimates — update with real evidence.',
}),
]
);
scores_inserted++;
}
return { stages_inserted, stages_updated, scores_inserted };
}
module.exports = { seedScores };