Segment Builder — Data Fixes & Category Review

Status: Live (2026-06-06). Owner: Cathal. Surfaces: Planning Studio → Segment Builder (/dashboard-segment-build), fcr_operations.segment_spine, fcr_operations.gp_category_segment.

This documents two corrections to the Segment Builder cohort engine and the manual-review loop that keeps the category→segment mapping clean. It started from a cohort that looked wrong: Home Services · No website · Email · Active client: No returned a business showing €25 MRR while the header said 0 paying — and taxis / car parks were appearing under Home Services.

1. is_active_client reconciliation (the contradiction)

Symptom. A “not a client” cohort included O’Connor Building & Carpentry (subscriber 38006923) — genuinely Active in CRM (live order, next invoice 2026-06-30) — and the sample row showed €25 while the headline said 0 paying.

Root cause. In sql/segment_spine.sql the is_active_client flag was copied straight from PROSPECT_LISTINGS, but MRR / products are overlaid separately from v_subscriber_mrr. The two sources were never reconciled, so a stale PROSPECT_LISTINGS flag (false) coexisted with a live subscription. 1,978 rows were affected — paying clients leaking into “Active client = No” cohorts.

Fix. is_active_client is now source flag OR present in v_subscriber_mrr (the authoritative current-month subscription view, reconciled to /revenue-bridge at 99.6%). OR, not replace, so DEA-only clients — real, but subscription=FALSE so absent from the MRR view — stay flagged via the source flag. Spine rebuilt.

Result: inactive-but-MRR rows 1,978 → 0; the example cohort 149 → 148 (O’Connor correctly dropped); rows showing MRR in a “not a client” cohort 1 → 0.

2. Filter-aware cohort MRR

Root cause. The spine’s build-time mrr_attributed pins each subscriber’s MRR to its global lowest listing_id. Any web-state / county filter that excluded that one row dropped the MRR and undercounted the headline for multi-listing clients.

Fix. worker/src/handlers/segment-build.js now dedups MRR per subscriber within the filtered cohort (ROW_NUMBER() … OVER (PARTITION BY subscriber_id ORDER BY listing_id) inside the filtered CTE), so SUM is correct under any filter. mrr_subscription is denormalised onto every row, so any surviving row carries the right figure.

3. Category misclassification → manual-review worklist

Symptom. Taxis (1,376), car parks (83), TV/cable (107) etc. shown under Home Services.

Root cause. The canonical gp_category_segment mapping is self-contradicting — it stores a sub_segment that disagrees with the assigned b_segment (e.g. Taxis & Taxicabs → b_segment=Home Services but sub_segment=Travel & Tourism; Car Parking & Garaging → Home Services but sub_segment=Vehicle Services).

Worklist: fcr_operations.segment_category_review (built by sql/segment_category_review.sql). High-confidence outliers only: for each sub_segment take its dominant b_segment; flag a category when the sub_segment is ≥80% concentrated on that dominant AND the category bucks it. Non-taxonomy sub_segments ("To be defined", "Unclassified") are excluded — their “Other” dominant only produces false suggestions (e.g. it would wrongly flag Nutritionists / Insurance). First pass: 24 categories / ~5,250 businesses. suggested_b_segment is a hint; a human approves or overrides. current_b_segment preserves the pre-change value as audit.

The rebuild is a MERGE, not CREATE OR REPLACE — safe to re-run mid-review. It refreshes the derived columns and inserts new candidates, never touches the human columns (status / approved_b_segment / reviewer / notes / reviewed_at), and deletes only never-reviewed (status='pending') rows that are no longer suspect. approved / applied / rejected rows persist as history.

Apply pipeline

scripts/segment/apply-category-review.cjs reads approved rows, validates approved_b_segment against the canonical 18 segments (an unknown value aborts the run — a typo must never coin a new segment), patches gp_category_segment (source='studio_review'), marks rows applied, and rebuilds segment_spine. Dry-run by default; --apply writes.

Running the loop

# 1. (re)build / refresh the worklist — safe anytime, preserves decisions
node scripts/crawl-tail/run-admin-sql.cjs --file=sql/segment_category_review.sql

# 2. review & approve the rows you accept (one per category), via bq-admin:
#    UPDATE …segment_category_review
#      SET status='approved', approved_b_segment='Travel', reviewer='cathal'
#      WHERE gp_category='Taxis & Taxicabs';

# 3. preview (dry-run, writes nothing)
node scripts/segment/apply-category-review.cjs

# 4. apply: patch mapping -> mark applied -> rebuild spine
node scripts/segment/apply-category-review.cjs --apply

Loop: rebuild worklist → review/approve → apply → rebuild — nothing gets clobbered between passes.

Applied so far: Car Parking & Garaging → Vehicle (83 businesses; the unambiguous proof case — its own sub_segment is “Vehicle Services”). The remaining 23, including the genuine judgment calls (Taxis → Travel vs Vehicle, Artists / Entertainers → B2B vs Leisure, Tourist Attractions → Travel vs Leisure), are pending review.

4. Product-fit for no-website businesses

Symptom (Cathal, 2026-06-06): “How is no website a better fit for SEO than SitePro?” A no-website cohort showed SEO fit (1,236) > SitePro fit (428).

Root cause. The fit_* flags are crawl-evidence only (prospect-crawl-enrich.js). No-website businesses are never crawled, so ~97% have NULL fit flags. The few that were set came from stale crawl records (a site that died → reclassified no_website), and SEO out-ranked SitePro only because SEO’s thresholds are looser (pageCount<5 + no schema vs SitePro’s pageCount<3). The ordering was an artifact, not a signal.

Fix. segment_spine.sql final SELECT overrides fit by web_state: no_website → fit_sitepro=TRUE, fit_seo=FALSE, fit_estore=FALSE (a site must exist before you can optimise or sell on it). fit_ads / fit_social stay crawl-derived (NULL when un-crawled, so they don’t inflate). Result: all 113,400 no-website rows are now SitePro fit, 0 SEO, 0 e-Store.

5. Phone reachability split — mobile vs fixed

segment_spine adds a phone_type column (‘mobile’ | ‘fixed’ | NULL), orthogonal to reachable_tier. Irish numbering: normalise to a leading-0 national number (strip non-digits, +353→0), then mobile = 08[3,5,6,7,8,9]; everything else (geographic 0xx, non-geographic 1800/1850, NI/intl) = fixed. segment-build.js accepts a phone_type filter and reports reach_phone_mobile / reach_phone_fixed counts.

Deploy status: the spine column is live (data ready). The phone_type filter needs a worker deploy, currently blocked by an unrelated issue (commit 15beab0 added an fcr-advisor-queue binding for a queue that doesn’t exist; any worker deploy fails until it’s created). The Studio UI “Phone type” chip is the last step, to add once the worker can deploy again.

Files

File	Role
sql/segment_spine.sql	Cohort base; is_active_client reconciliation
sql/subscriber_mrr.sql	v_subscriber_mrr — authoritative current-month MRR
worker/src/handlers/segment-build.js	/dashboard-segment-build; filter-aware MRR
sql/segment_category_review.sql	MERGE-based review worklist builder
scripts/segment/apply-category-review.cjs	Approve → patch mapping → rebuild spine

See also: Prospect Segment & Category Attributes.