TK-10687: add DRE enrichment strategy memo

f794458254927621e1bb2040b5e4b89ce1cbe971 · 2026-08-23 04:19:32 -0700 · Steve Abrams

Documents current state (385k CA DRE licensees, 0.09% phone coverage), what DRE
public data actually provides (address but NOT phone/email), and the 3-tier $0
enrichment path: DRE address lookup → address-qualified SERP → DBA name search.
Identifies 5 large high-value firms (19k+ brokers between them) with found-but-unwritten
phones as the fastest quick win. Recommends resorting enrichment by active_broker_count
DESC rather than firm ID to maximize early yield.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

Files touched

A docs/dre-enrichment-strategy-TK10687.md

Diff

commit f794458254927621e1bb2040b5e4b89ce1cbe971
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Aug 23 04:19:32 2026 -0700

    TK-10687: add DRE enrichment strategy memo
    
    Documents current state (385k CA DRE licensees, 0.09% phone coverage), what DRE
    public data actually provides (address but NOT phone/email), and the 3-tier $0
    enrichment path: DRE address lookup → address-qualified SERP → DBA name search.
    Identifies 5 large high-value firms (19k+ brokers between them) with found-but-unwritten
    phones as the fastest quick win. Recommends resorting enrichment by active_broker_count
    DESC rather than firm ID to maximize early yield.
    
    Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 docs/dre-enrichment-strategy-TK10687.md | 114 ++++++++++++++++++++++++++++++++
 1 file changed, 114 insertions(+)

diff --git a/docs/dre-enrichment-strategy-TK10687.md b/docs/dre-enrichment-strategy-TK10687.md
new file mode 100644
index 0000000..cc0818e
--- /dev/null
+++ b/docs/dre-enrichment-strategy-TK10687.md
@@ -0,0 +1,114 @@
+# DRE Broker/Firm Enrichment Strategy — TK-10687
+_Generated 2026-08-23 by re-enrich-agent, $0 path only_
+
+## Current State
+
+**Database:** `usre` (host=/tmp)
+
+| Table | Scope | Count | has_phone | has_email | has_website |
+|---|---|---|---|---|---|
+| `broker` (source=ca_dre) | CA DRE licensees | 385,454 | 346 (0.09%) | 288 (0.07%) | 0 (0%) |
+| `firm` (license_state=CA) | CA RE firms | 35,041 | 537 (1.5%) | 289 (0.8%) | 304 (0.9%) |
+| `firm` (residential, CA, active broker) | scope for coverage | 21,954 | 527 (2.4%) | — | — |
+
+**Fleet-health verdict:** FAIL (broker callable: 28.9% — target 99%)
+
+## What CA DRE Bulk Data Provides
+
+Source: `https://data.ca.gov/dataset/ca-dre-licensing-data` (free, refreshed ~monthly)
+
+**Provided:** name, license_no, license_type, license_status, city, county, license_date, expiration_date, firm affiliation (for salesperson → broker)
+
+**NOT provided:** phone, email, website, street address
+
+## What CA DRE Public Lookup Provides (Per-License, Free)
+
+URL: `https://www2.dre.ca.gov/PublicASP/pplinfo.asp?License_id=<license_no>`
+
+**Provided for FIRMS (corporations):**
+- Mailing address (street + city + state + zip) ✓ 
+- Main office address ✓
+- DBA names ✓
+- Licensed officers (names + license IDs) ✓
+
+**NOT provided:** phone, email, website
+
+**Feasibility:** All 35,041 CA firms have `license_no` → this lookup works for all.
+Rate: 1 req/3s = ~29 hours for all 35k, or ~3 hours for the 21k residential subset.
+Currently: 34,737/35,041 (99.1%) missing street address in our DB.
+**This is the highest-ROI $0 enrichment: address fills from DRE, then use address in targeted SERP.**
+
+## $0 Enrichment Strategy — Three-Tier Stack
+
+### Tier 1: CA DRE Address Lookup (Best ROI)
+- Script: `scripts/enrich-firm-address-dre.js` (to build)
+- Fetch `https://www2.dre.ca.gov/PublicASP/pplinfo.asp?License_id=<license_no>`
+- Extract mailing address with regex
+- Write to JSONL → pending-approval for DB apply
+- Yield: ~95%+ for active firms (DRE has them all)
+- Cost: $0 (free government site, plain HTTP)
+- Rate: 1 req/3s
+- Value: Address enables better SERP queries ("Vanguard Properties 2501 Mission St San Francisco phone")
+
+### Tier 2: SERP with Address-Qualified Queries
+- After Tier 1, use `"<firm_name>" "<city>" real estate phone` + optionally the street address
+- Current issue: false positives on old/defunct firms with generic names
+- Fix: add domain ownership check — a result only counts if the found phone appears on a page whose domain contains the firm name or city
+- The existing `enrich-firm-contacts.mjs` + Ollama already does this better than pure regex
+- For pure-$0 (no Ollama), the key is: **order firms by active_broker_count DESC** so well-known firms (eXp, Compass, Real Brokerage — thousands of agents) go first. These are findable on first Bing hit.
+
+### Tier 3: DBA Name Search
+- CA DRE lookup gives DBA names for each firm (from Tier 1 output)
+- DBA names are often more searchable than the legal entity name
+- Example: Vanguard Properties, Inc. → DBA "Dream Big Homes", "Redwood Empire Realty", "Vanguard Properties - Castro"
+- Run SERP on the DBA name for smaller firms where legal name hits nothing
+
+## Specific Gaps Identified
+
+### Gap A: Large Active Firms (High Value)
+Firms with 1,000+ active brokers but no phone in our DB:
+
+| Firm | Active Brokers | License No |
+|---|---|---|
+| Real Brokerage Technologies | 4,965 | 02022092 |
+| eXp Realty of California, Inc. | 4,957 | 01878277 |
+| Compass California II, Inc. | 3,688 | 01527235 |
+| NRT West, Inc. (Coldwell Banker) | 3,262 | 01908304 |
+| Pickford Real Estate Inc | 2,313 | 01317331 |
+
+All 5 have `phone_status='found'` from a prior run (Aug 18) but the phone was NOT written to `firm.phone`. **Likely cause: the Aug 18 run had the Ollama model mark them as found (a candidate appeared in search results) but the phone wasn't from the firm's OWN site** — it hit Yelp/Realtor/Compass-aggregator pages. Action: manual spot-check for each, then `UPDATE firm SET phone=X WHERE id=Y` (pending-approval).
+
+### Gap B: Old/Defunct Firms (Low Value, Skip for Now)  
+Firms with license_no < 00200000 (pre-1980s vintage) are often defunct:
+- SERP yields false positives (names match unrelated businesses)
+- CA DRE address lookup still works (DRE keeps historical records)
+- Recommend: run Tier 1 (address) on all, but defer SERP (Tier 2) until broker count ≥ 2
+
+### Gap C: DRE Provides No Phone — Root Cause
+CA DRE is not a contact directory. Under CA Business & Professions Code § 10166,
+firms must provide DRE with address for license maintenance but phone/email is optional.
+**This will not change.** The $0 path is indirect (address → SERP) or website crawl.
+
+## Recommended Immediate Actions (All $0)
+
+1. **Build `scripts/enrich-firm-address-dre.js`** — fetch DRE public lookup for all 35k CA firms
+   in batches of 500/day, extract mailing address + DBA names, write to JSONL pending-approval.
+   Estimated: 34,737 addresses recoverable, ~3 weeks at 500/day batches.
+
+2. **Fix the 5 large firms manually** — they have `phone_status='found'` from Aug 18. 
+   Quick manual SERP for each, confirm on their OWN domain, and `UPDATE` via pending-approval.
+   These 5 alone make 14,870 brokers callable (eXp 4,957 + Real 4,965 + Compass 3,688 + NRT 3,262 + Pickford 2,313 = 19,185 active brokers, minus overlap) — highest-leverage quick win.
+
+3. **Re-order SERP enrichment by active_broker_count DESC** — the existing `enrich-firm-contacts.mjs`
+   currently processes by ID (oldest first). Sorting by active broker count puts the large well-known
+   firms first, where SERP yield is near 100%. Change one `ORDER BY` clause in the query.
+
+4. **Use DBA names from Tier 1** in Tier 2 searches — minor but helps smaller firms.
+
+## Files
+
+- `scripts/enrich-firms-serp.js` — lightweight SERP-only, JSONL output (TK-10681, built today)
+- `scripts/enrich-firm-contacts.mjs` — full enrichment (SERP + Ollama + DB writes, already running)
+- `scripts/firm-phone-coverage.mjs` — coverage tracker (reads from usre.firm)
+- `data/firm-phone-coverage.json` — latest snapshot: 527/21,954 firms (2.4%), 61,067/211,185 brokers callable (28.9%)
+- `data/serp-enrich-2026-08-23T11-14-17.jsonl` — pilot 20-firm run output

← 05583db TK-10681: add enrich-firms-serp.js — SERP-only residential f · back to Nationalrealestate · auto-data-snapshot: 2026-08-23T15:28:58 (1 data files) — dat 0195411 →