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

115 lines
# 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