Prospect Listings — State Snapshot (2026-05-24, superseded)

Consolidated state of fcr_operations.PROSPECT_LISTINGS: what’s in it, how it got there, what’s enriched vs identity-only, the liveness crawl, the mis-mapping ops tables, and open items. Supersedes the row counts in docs/prospect-listings-refresh-plan.md (that doc is still correct on the Pleper cron, but pre-dates the expansion).

All numbers below are live as of 2026-05-24 via /dashboard-bq-execute.

---

TL;DR

The table is 71,365 rows and is really three different cohorts wearing one schema:

1. ~15.3K fully SERP/GBP-enriched — the original Home Services + B2B prospects (the only rows with has_serp_data). Ratings, reviews, services, GBP signals.
2. 42,451 identity + website + crawl only — the 1_Core_Listings backfill (all clients + all mapped listings, any class). Has a website and free crawl fit-flags, but no paid SERP/GBP enrichment. Gated off (enrich_enabled=FALSE).
3. 13,345 identity-only, barely crawled — the SayMore SERP competitor harvest (net-new competitors, no subscriber). Only ~896 crawled so far.

Every row has a place_id. 57,409 carry a subscriber_id; 13,956 don’t.

---

How it was built (the layering)

Reconstructed from created_at — matches the narrative exactly:

When	Rows	What it is	segment	subscriber_id	enrich_enabled
2026-03-22	14,958	Original seed: Home Services (7,142) + B2B (7,816) prospects with a GP-mapped place_id, SerpAPI/Pleper-enriched	Home Services / B2B	100%	NULL
May 1–21	~611	Live competitor-enrich trickle — net-new competitor pids written on LRC/grid scans	(null)	none	NULL
2026-05-23	42,451	Backfill from SUPERSET_MASTER.1_Core_Listings — all clients + all other mapped listings, independent of class. Identity-only.	(null)	all	FALSE
2026-05-24	13,345	SayMore deep-dive SERP competitor harvest — net-new place_ids recovered free from SerpAPI’s archive	saymore_serp_harvest	0	FALSE
	71,365	total		57,409 / 13,956

Notes on the layers:

• The place_id in 1_Core_Listings isn’t a column — it’s encoded in the |-delimited urls_urls/urls_types pair (the element typed GOOGLEMAPS). 213,815 GP listings → 62,450 with a valid place_id → 42,451 net-new vs the existing table.
• The SayMore harvest re-pulled every deep-dive SERP (4,706 archived search_ids in SAYMORE_RANK_ANALYSIS_V2.csv) at €0 — SerpAPI doesn’t charge to re-read an archived search. 19,799 unique competitor place_ids recovered; the cross-check (11-netnew-crosscheck.cjs) classified them net-new / already-present / active-client. The 13,345 net-new (no subscriber match) are the rows tagged segment='saymore_serp_harvest'; the ~6,454 that matched existing rows already had a subscriber and weren’t re-inserted — that’s why “some have a subscriber id and some don’t.”

---

Enriched vs identity-only — the key distinction

has_serp_data (paid SERP/GBP enrichment present) is the real divider:

Cohort	has_serp_data
Home Services	7,008
B2B	7,722
competitor trickle (null segment)	611
1_Core backfill	0
SayMore harvest	0
total	15,341

So only the original prospect cohort (~15.3K) carries ratings/reviews/services/GBP signals. The 42,451 backfill and 13,345 SayMore rows are identity + website + (partial) crawl until someone explicitly enriches them.

The enrich gate

enrich_enabled gates the paid crons (SerpAPI + Pleper) only. It does not gate the free shallow crawl — that runs on every has_website row.

enrich_enabled	rows	who
FALSE	55,796	the 42,451 backfill + 13,345 SayMore (explicitly gated off)
NULL	15,569	the original seed + trickle (crons treat COALESCE(enrich_enabled,TRUE) = enabled)
TRUE	0	nobody has flipped a paid-enrich slice on yet

The Prospecting tab’s Enrich button (/dashboard-prospect-enable-enrich?category=&county=) is the controlled lever: dry-run → SerpAPI/Pleper cost estimate → flip a slice to TRUE (MAX_SLICE 5000, force-gate above). The Prospecting card list itself is gated on has_serp_data, so the dormant 55.8K don’t leak in as blank cards.

---

The liveness crawl (free, every-minute shallow sweep)

prospect-crawl-enrich.js runs one polite homepage crawl per domain (robots-respecting, identifies as GoldenPagesBot), deduped on crawl_root and fanned out to all sibling listings on that domain. Output: 34 crawl_*/fit_* columns.

36,182 crawled → 30,189 reachable (live) / 5,993 dead-or-errored. By cohort:

Cohort	live	dead/errored	not yet crawled
1_Core backfill	19,719	~3,668	19,675
B2B	5,470	~986	1,360
Home Services	4,361	~988	1,793
SayMore harvest	639	~355	12,351

The original prospects + backfill are well-swept. The SayMore set is essentially un-crawled (12,351 of 13,345 pending) — the cron is steadily draining it.

The ~16% dead URLs (5,993) are stale GP website strings — a data-hygiene win: these are GP listings pointing at websites that no longer resolve.

Fit-cohort sizes from the shallow sweep: fit_seo 15,245 · fit_estore 13,102 · fit_sitepro 8,281 · fit_ads 4,474 · fit_ads_crosssell 4,426 · no-website greenfield 3,176. (fit_social is currently unreliable on shallow-only rows — see open items.)

---

Mis-mapped / dead place_ids in the subscriber data

Two ops-review tables (separate from PROSPECT_LISTINGS):

Table	Rows	What
fcr_operations.prospect_suspect_place_ids	305	Likely-WRONG place_ids — SerpAPI-unresolvable (63 also fail Pleper = highest confidence). Pleper-only failures (~4,696) deliberately excluded — that’s “Pleper can’t scrape,” not “wrong place_id.”
fcr_operations.prospect_nof_ops_review	391	NOF reconciliation queue — no-subscriber residue matched back against dim_listings_with_subscriber_id (place_id exact + normalized name). Unmatched ≈ genuine net-new third parties.

Important distinction: “dead website URLs” (the 5,993 unreachable crawls) ≠ “wrong place_ids” (the 305 suspects). The first is a stale website string on a valid listing; the second is a place_id that doesn’t resolve to a real Google Business Profile.

competitor-enrich now persists phone+website on every write-back (commit bf2c0ba), so future NOFs are phone/URL-matchable (the existing batch wasn’t — earlier competitor-enrich kept only counts).

---

”Bot tripped” — what actually happened (it was NOT our Cloudflare)

The deep crawl is paused, not the shallow one. To be precise about the cause (code comment in prospect-crawl-enrich.js):

// DEEP DISABLED 2026-05-24 — backed off. Deep's multi-page burst (~12 inner pages/site) + mailto harvesting tripped a target site's bot-detection

This was a prospect’s own website flagging us — the opposite of our Cloudflare telling us to stop. Our crawler (running from the worker as GoldenPagesBot) hit a single target/prospect site with a rapid burst — homepage + robots + llms + sitemap probes + nested sitemaps + ≤12 inner pages, in fast parallel batches with no pacing, plus email harvesting — and that site’s anti-bot protection (its own WAF/bot-detection, which could itself be Cloudflare on their end) flagged the request pattern as a scraper.

So:

• It was one external prospect site, not a platform-wide block on us.
• We voluntarily paused via our own kill-switch (if (mode === "deep") return {disabled:true}), not because we were hard-blocked everywhere.
• The shallow sweep (one homepage GET per domain) is gentle and keeps running fine.

Resume plan (open): ~4–5 targeted pages instead of 12, 1–2s pacing between requests, a lower sitemap cap, homepage-only email extraction. Re-enable by removing the if (mode === "deep") guard. ~15,094 qualified domains await; 2,403 done.

---

Email sources — outreach_email vs crawl_email (not redundant)

There are two independent email columns and they cover different things — the harvested outreach_email is not something the crawler has already got or will reproduce.

	outreach_email	crawl_email
Source	One-shot SayMore harvest (12-website-email-harvest.cjs) — mailto + plain-text across real-domain sites	Free shallow-crawl cron — homepage only
Stability	Durable — the pipeline never touches it	Volatile — the crawl MERGE overwrites it (incl. with NULL) on every 90-day recrawl
Scope	SayMore net-new only	Whole crawlable book (in principle)

Live coverage on the SayMore cohort (13,345 rows):

	Emails
outreach_email (harvest)	5,829
crawl_email (cron)	348
Harvest has it, cron doesn’t	5,533
Both	296
Reachable either way (COALESCE)	5,881

The harvest is ~94% additive — 5,533 of its 5,829 emails are not in crawl_email. And crawl_email is 0 across Home Services, B2B, and the 42K backfill despite 36K of those rows being crawled, because the email-extraction fix only fires on new crawls (the book was swept before it landed).

The crawler will not catch up even after it finishes the SayMore backlog: (1) shallow is homepage-only, so it misses /contact-page emails the harvest caught — and the deep crawl that samples inner pages is paused; (2) crawl_email is overwritten on recrawl, which is the whole reason the harvest emails were parked in outreach_email.

Takeaway: keep the SayMore emails. Outreach reads COALESCE(outreach_email, crawl_email) → 5,881 reachable SayMore prospects today vs 348 from the crawler alone. The crawler’s job is to eventually own emails for the rest of the book (currently empty everywhere) via the pending shallow re-sweep — not to replace the harvest.

---

Open items

1. Deep crawl paused — needs the throttled re-enable above before resuming. ~15K domains waiting.
2. crawl_email / crawl_phone only on new crawls — the ~35K already-shallow-crawled rows have them NULL until a shallow re-sweep (~2h, one homepage GET/domain) or their 90-day recrawl. Decision pending.
3. fit_social unreliable on shallow-only rows (~27.9K) — social was deep-only when the book was swept; fixed in code, needs the same re-sweep to correct book-wide.
4. 16% crawl failures (5,993 stale URLs) — a data-hygiene cleanup opportunity (flag/refresh dead website values on real subscribers).
5. SayMore crawl backlog — 12,351 net-new domains still need the free shallow crawl to get fit-flags.
6. SayMore emails — 5,829 in outreach_email (durable, ~94% not in crawl_email — see “Email sources” above); 802 low-trust other_domain emails withheld in competitor_emails_FOUND.csv. Outreach reads COALESCE(outreach_email, crawl_email). The whole rest of the book has no captured email — that’s what the shallow re-sweep (item #2) would fix.
7. SnapSync archive — same free-archive harvest not yet run (SNAPSYNC_RANK_ANALYSIS.csv).
8. 42k backfill is unsegmented — still segment=NULL. Segmenting it (Home Services / B2B / …) is the prerequisite for flipping enrich_enabled=TRUE in controlled, costed batches.

---

Source map

• This snapshot — current state, all cohorts.
• docs/prospect-listings-refresh-plan.md — the Pleper refresh cron (accurate; pre-expansion row counts).
• CRAWL_ENRICH_HANDOFF_2026-05-24.md (repo root, commit 486caaf) — the crawl-enrich layer: 34 columns, website backfill, sweep results, deep-pause detail.
• scripts/snapsync-saymore/10–12-*.cjs (commit a282012) — SayMore harvest / net-new cross-check / email harvest.
• Memory: project_prospect_db_expansion, project_saymore_serp_competitor_harvest, project_crawl_website_upgrade, project_serpapi_enrich_loop_fix.