← back to La Socrata Ingester
yoloforever c6 FIX (Cody): drop invalid per-ZIP ratio, add valid county supply/demand + fix trade map
4aad89615e7087a38219a5ba8fea5f2ed565adc1 · 2026-08-12 07:33:08 -0700 · steve
Cody c6: permits_per_contractor by ZIP was a residential-ness artifact (wealthy ZIPs
have few contractor BUSINESS addresses, not undersupply) — a caveat can't rescue a
'90272 underserved' headline that's false. Dropped it. Section 2 now county-level
supply vs permit demand by trade (Gen Building 2.7, Electrical 8.4, Plumbing 9.4
permits/contractor/yr — no geo proxy). Trade-code map fixed (norm dash-stripped).
Files touched
M scripts/contractor-intel.js
Diff
commit 4aad89615e7087a38219a5ba8fea5f2ed565adc1
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 07:33:08 2026 -0700
yoloforever c6 FIX (Cody): drop invalid per-ZIP ratio, add valid county supply/demand + fix trade map
Cody c6: permits_per_contractor by ZIP was a residential-ness artifact (wealthy ZIPs
have few contractor BUSINESS addresses, not undersupply) — a caveat can't rescue a
'90272 underserved' headline that's false. Dropped it. Section 2 now county-level
supply vs permit demand by trade (Gen Building 2.7, Electrical 8.4, Plumbing 9.4
permits/contractor/yr — no geo proxy). Trade-code map fixed (norm dash-stripped).
---
scripts/contractor-intel.js | 69 +++++++++++++++++++++++----------------------
1 file changed, 35 insertions(+), 34 deletions(-)
diff --git a/scripts/contractor-intel.js b/scripts/contractor-intel.js
index 23b56f1..917b4bf 100644
--- a/scripts/contractor-intel.js
+++ b/scripts/contractor-intel.js
@@ -14,14 +14,19 @@ import { fileURLToPath } from 'url';
const __dirname = path.dirname(fileURLToPath(import.meta.url));
const OUT = path.resolve(__dirname, '../tmp/leads');
const money = v => '$' + Math.round(Number(v || 0)).toLocaleString();
-// CSLB classification code -> readable trade (common ones)
+// CSLB classification -> readable trade. Data mixes 'C10' and 'C-7' forms; key by
+// normalized (dash-stripped, upper) code so both match (validated vs cslb_raw codes).
const TRADE = {
- B: 'General Building', 'B-2': 'Residential Remodeling', A: 'General Engineering',
- 'C-8': 'Concrete', 'C-10': 'Electrical', 'C10': 'Electrical', 'C-36': 'Plumbing', 'C36': 'Plumbing',
- 'C-20': 'HVAC', 'C20': 'HVAC', 'C-33': 'Painting', 'C33': 'Painting', 'C-27': 'Landscaping',
- 'C-5': 'Framing/Carpentry', 'C-9': 'Drywall', 'C-4': 'Boiler', 'C-7': 'Low Voltage',
+ B: 'General Building', B2: 'Residential Remodel', A: 'General Engineering',
+ C10: 'Electrical', C36: 'Plumbing', C20: 'HVAC', C33: 'Painting', C27: 'Landscaping',
+ C15: 'Flooring', C39: 'Roofing', C7: 'Low Voltage', C54: 'Ceramic/Tile', C8: 'Concrete',
+ C6: 'Cabinets/Millwork', C53: 'Swimming Pool', C17: 'Glazing', C35: 'Plastering',
+ C29: 'Masonry', C5: 'Framing/Carpentry', C9: 'Drywall', C13: 'Fencing', C12: 'Earthwork',
+ C43: 'Sheet Metal', C61: 'Limited Specialty', C34: 'Pipeline', C23: 'Ornamental Metal',
+ C42: 'Sanitation', C51: 'Structural Steel', C46: 'Solar', C31: 'Construction Zone Traffic',
};
-const trade = c => TRADE[c] || TRADE[c.replace('-', '')] || c;
+const norm = c => String(c).toUpperCase().replace(/-/g, '').trim();
+const trade = c => TRADE[norm(c)] || norm(c);
async function main() {
const stamp = (await q(`SELECT to_char(now(),'YYYY-MM-DD') d`)).rows[0].d;
@@ -34,39 +39,35 @@ async function main() {
WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND trim(c) <> ''
GROUP BY 1 ORDER BY 2 DESC LIMIT 15`)).rows;
- // 2. DEMAND vs SUPPLY per ZIP: permit activity (180d) + contractors business-located in ZIP
- const zips = (await q(`
- WITH demand AS (
- SELECT zip_code AS zip, count(*)::int AS permits, round(sum(valuation))::bigint AS value
- FROM la_building_permits_raw
- WHERE dataset_id='pi9x-tg5x' AND status_desc='Issued'
- AND issue_date > now()-interval '180 days' AND zip_code IS NOT NULL
- GROUP BY 1
- ),
- supply AS (
- SELECT left("ZIPCode",5) AS zip, count(*)::int AS local_contractors
- FROM cslb_raw WHERE "County"='Los Angeles' AND "PrimaryStatus"='CLEAR' AND "ZIPCode" IS NOT NULL
- GROUP BY 1
- )
- SELECT d.zip, d.permits, d.value, COALESCE(s.local_contractors,0) AS local_contractors,
- round(d.permits::numeric / NULLIF(s.local_contractors,0), 2) AS permits_per_contractor
- FROM demand d LEFT JOIN supply s USING (zip)
- WHERE d.permits >= 20
- ORDER BY permits_per_contractor DESC NULLS LAST LIMIT 60`)).rows;
+ // 2. COUNTY-level supply vs demand by trade — VALID (no business-ZIP proxy; Cody c6
+ // dropped the per-ZIP ratio as a residential-ness artifact). Match key trades to their
+ // permit datasets: General Building->building, C10->electrical, C36->mech/plumbing.
+ const supplyByClass = Object.fromEntries(classes.map(r => [norm(r.class), r.contractors]));
+ const TRADE_DEMAND = [
+ { class: 'B', label: 'General Building', ds: 'pi9x-tg5x' },
+ { class: 'C10', label: 'Electrical', ds: 'ysqd-apz7' },
+ { class: 'C36', label: 'Plumbing/Mech', ds: '67is-svtd' },
+ ];
+ const market = [];
+ for (const t of TRADE_DEMAND) {
+ const demand = Number((await q(`SELECT count(*) c FROM la_building_permits_raw WHERE dataset_id=$1 AND issue_date > now()-interval '365 days'`, [t.ds])).rows[0].c);
+ const supply = supplyByClass[norm(t.class)] || 0;
+ market.push({ ...t, supply, demand_1y: demand, permits_per_contractor_yr: supply ? Math.round(demand / supply * 10) / 10 : null });
+ }
fs.writeFileSync(path.join(OUT, `contractor-intel-classes-${stamp}.csv`),
['class,trade,contractors', ...classes.map(r => `${r.class},"${trade(r.class)}",${r.contractors}`)].join('\n'));
- fs.writeFileSync(path.join(OUT, `contractor-intel-zips-${stamp}.csv`),
- ['zip,permits_180d,permit_value,local_contractors,permits_per_contractor',
- ...zips.map(r => `${r.zip},${r.permits},${r.value},${r.local_contractors},${r.permits_per_contractor ?? ''}`)].join('\n'));
+ fs.writeFileSync(path.join(OUT, `contractor-intel-market-${stamp}.csv`),
+ ['class,trade,active_la_contractors,permits_1y,permits_per_contractor_yr',
+ ...market.map(r => `${r.class},"${r.label}",${r.supply},${r.demand_1y},${r.permits_per_contractor_yr ?? ''}`)].join('\n'));
- console.log(`\nContractor-market intelligence — ${stamp} (INTERNAL; contractor ZIP = business address, a supply proxy)\n`);
+ console.log(`\nContractor-market intelligence — ${stamp} (INTERNAL; LA County active CSLB contractors)\n`);
console.log(' ACTIVE LA COUNTY CONTRACTORS by trade (supply):');
- classes.slice(0, 8).forEach(r => console.log(` ${String(r.class).padEnd(5)} ${trade(r.class).padEnd(22)} ${r.contractors.toLocaleString().padStart(7)}`));
- console.log('\n HIGHEST DEMAND-PER-LOCAL-CONTRACTOR ZIPs (permit activity vs local contractor base — opportunity signal):');
- zips.slice(0, 8).forEach(r => console.log(
- ` ${r.zip} ${String(r.permits).padStart(5)} permits/180d ${money(r.value).padStart(15)} ${String(r.local_contractors).padStart(4)} local contractors → ${r.permits_per_contractor ?? 'n/a'} permits/contractor`));
- console.log(`\n CSVs → tmp/leads/contractor-intel-classes-${stamp}.csv, contractor-intel-zips-${stamp}.csv\n`);
+ classes.slice(0, 10).forEach(r => console.log(` ${String(r.class).padEnd(5)} ${trade(r.class).padEnd(22)} ${r.contractors.toLocaleString().padStart(7)}`));
+ console.log('\n COUNTY SUPPLY vs 1-YEAR PERMIT DEMAND by trade (valid county-level ratio):');
+ market.forEach(r => console.log(
+ ` ${r.label.padEnd(20)} ${String(r.supply).toLocaleString().padStart(7)} active contractors vs ${r.demand_1y.toLocaleString().padStart(7)} permits/yr → ${r.permits_per_contractor_yr} permits per contractor/yr`));
+ console.log(`\n CSVs → tmp/leads/contractor-intel-classes-${stamp}.csv, contractor-intel-market-${stamp}.csv\n`);
}
main().catch(e => { console.error('contractor-intel error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← e534fb9 yoloforever c6: contractor-market intelligence (scripts/cont
·
back to La Socrata Ingester
·
yoloforever c7: RE-news deals-digest markdown generator (scr 02d9b82 →