Prospect place_id — Sources, Audit Flags & Hygiene Cohorts

Status: Review process live; correction is human-reviewed, never auto-applied. Snapshot: Counts in this page are as of 2026-06-07 — re-query before trusting, the spine grows.

Every prospect listing is anchored to a Google place_id. That anchor is what feeds InSites (Digital Footprint / Local Rank Check), competitor enrichment, GBP scraping and the deal brief. A wrong place_id silently poisons all of them — you enrich the wrong business. This page documents where each place_id came from (place_id_source), how we flag suspect ones (prospect_listings_audit_flags), and the four hygiene cohorts that need human review.

See also: Prospect Hub — Data Model, Prospect Listings Refresh, Prospect DB — Composition, Segment Builder — Fixes & Review (the worklist/apply pattern this borrows).

1. The place_id_source taxonomy

fcr_operations.PROSPECT_LISTINGS is ~241K rows. Every row carries a place_id and a place_id_source recording how that anchor was assigned. Trust decreases down the table.

place_id_source	Rows	What it is	Trust
null	122,879	Original on-file GP / master-book place_id (golden-pages listing as supplied)	High
cl_urls_urls_match	57,405	Matched by walking the book’s urls_urls → resolving the site’s place_id	Low — geo errors live here
nof_harvest	29,916	Harvested from the SerpAPI archive for not-on-file (NOF) businesses	Medium
nof_harvest_dropped	12,639	NOF harvest rows later de-prioritised	Medium
serpapi_promoted	18,638	Live SerpAPI resolve, promoted onto the spine (see §3 tiers)	Mixed — tiered

Whitespace gotcha: master-book place_ids in urls_urls are space-padded (" ChIJ… "). Always TRIM(place_id) before matching, or ~40K rows read as “not in prospect”. Live ingest trims; only ad-hoc queries get bitten. (reference_book_placeid_whitespace.)

2. The audit-flag layer — fcr_operations.prospect_listings_audit_flags

A correctness pass writes one row per suspect (place_id × check). Schema:

Column	Notes
listing_id, subscriber_id, place_id, bizname	identity of the flagged listing
audit_check	geo_distant | ni_eircode | phone_mismatch
severity	high | medium | low
evidence	JSON — for geo_distant: {distance_m, cl_lat, cl_lon, snap_lat, snap_lon}

Current flag volume:

check	severity	flags
phone_mismatch	medium	15,086
geo_distant	high	4,124
ni_eircode	high	1,681
geo_distant	medium	1,571
geo_distant	low	921

The geo_distant null-baseline trap (read before triaging)

geo_distant compares the client’s known coordinate (cl_lat/cl_lon) to the snapped place_id coordinate. Many flags have cl_lat = 0, cl_lon = 0 — the client baseline was missing, so the ~5,900 km “distance” is an artifact, not a real error. Filter these out before believing a distance:

SAFE_CAST(JSON_VALUE(evidence,'$.cl_lat') AS FLOAT64) NOT IN (0)   -- real baseline only

Of the 4,124 high-severity geo_distant flags: 1,927 have a real baseline (genuine wrong-place candidates); 2,197 are null-baseline (unverifiable until we source a baseline coordinate). ni_eircode (snapped place sits in Northern Ireland) is eircode-derived, so it’s real regardless of baseline.

3. The four hygiene cohorts

Built 2026-06-07. Review CSVs are local-only (data/prospect-hygiene/, not loaded to BigQuery — the live dataset is what the advisor/card/dashboard read). Active book = 3,772 distinct subscribers.

#	Cohort	Count	Definition
1	Active clients, no GP listing	0	no subscriber missing from dim_listings_with_subscriber_id
2	Active clients, no place_id	72	active subscriber with no non-null place_id in PROSPECT_LISTINGS
3	On-file GP, wrong place_id	~1,971 real	place_id_source='cl_urls_urls_match' + severity='high' (minus null-baseline)
4	Auto-assigned SerpAPI place_ids	18,638	place_id_source='serpapi_promoted'

Note on #1: this uses the join-dim, which has a row per subscriber by construction, so “0” means a listing record exists for every active client — not necessarily a live/published GP product. That’s a different cut if needed.

#2 — active clients with no place_id (72)

WITH ac AS (SELECT CAST(subscriber_id AS STRING) sid FROM active_clients
            WHERE snapshot_date=(SELECT MAX(snapshot_date) FROM active_clients) AND subscriber_id IS NOT NULL),
     haspid AS (SELECT DISTINCT CAST(subscriber_id AS STRING) sid FROM PROSPECT_LISTINGS
                WHERE place_id IS NOT NULL AND TRIM(place_id)!='')
SELECT * FROM ac WHERE sid NOT IN (SELECT sid FROM haspid)

Most have only an account_name (no prospect row at all) — resolve from scratch via search-places on name + county.

#3 — on-file GP, wrong place_id (review tiers)

The cl_urls_urls_match source is where URL-matching put a geographically wrong anchor. The high-severity worklist tiers as:

review_tier	rows	meaning
1_cross_border_NI	44	snapped place_id is in Northern Ireland — real cross-border error
2_real_geo_distant	1,927	real client coord vs distant place_id — the actionable list
3_no_baseline_unverifiable	2,197	cl_lat=0 — can’t judge until a baseline coord is sourced

Medium/low (separate, lower-priority file): medium/phone_mismatch 5,303, medium/geo_distant 1,556, low/geo_distant 894. Phone-mismatch is the weakest signal (a different listed phone ≠ a wrong place).

#4 — auto-assigned SerpAPI place_ids, by confidence

serpapi_promoted confidence is encoded in segment. Review lowest-confidence first:

confidence_tier	segment	rows
1_loose_REVIEW_FIRST	core_resolve_loose	873
2_name_addr_phone	core_resolve_nap	6,714
3_exact	core_resolve	9,323
4_saymore_harvest	saymore_serp_harvest	1,665
5_other	(null / misc)	63

1,637 of the 18,638 already carry an audit flag (surfaced in the CSV’s audit_flags column).

4. Review & apply discipline

• place_id corrections are human-reviewed, never auto-MERGEd. A manual process owns the ~1,119 GMB-vs-spine place_id gaps and the cohorts above; ~480 of those “look like” clean UPDATE candidates but aren’t (feedback_gmb_to_pl_place_id_human_reviewed). Enrichment-column MERGEs into the spine are still fine — it’s only the place_id anchor that needs sign-off.
• Pattern to follow: the Segment Builder category review (§3) already implements review-worklist → approve rows → --dry-run preview → apply → rebuild. The place_id hygiene worklists should adopt the same shape (writes through the sanctioned admin path, idempotent, confirm-first).
• Suggested work order across the backlog: #2 (72) → #3 tiers 1+2 (1,971) → #4 loose (873) → #3 medium/geo_distant (1,556) → #4 nap (6,714) → the rest.

5. Regenerating the cohorts

The export is a small paginating script against dashboard-bq-execute (the endpoint caps at 500 rows/call, so cohorts >500 page with LIMIT/OFFSET). Outputs:

data/prospect-hygiene/hygiene_2_active_clients_no_placeid.csv          (72)
data/prospect-hygiene/hygiene_3_onfile_gp_wrong_placeid_HIGH.csv       (4,168, tiered)
data/prospect-hygiene/hygiene_3_onfile_gp_wrong_placeid_MEDIUM_LOW.csv (7,753)
data/prospect-hygiene/hygiene_4_serpapi_autoassigned_placeids.csv      (18,638, tiered)