🧩 HCP Intelligence Report

Housecall Pro export cross-referenced with ServiceTitan — data integrity findings and revenue implications. Generated 2026-04-14 from raw CSV (4,271 rows) + bsp_analytics.titan.customers (5,465 rows).

TL;DR. BSP is not a $2.44M business with an HCP mystery. It is a $6M+ lifetime customer book where 73.6% of ServiceTitan's "Existing Customer" traffic is misclassified HCP migrants, $401,791 of genuinely uncounted revenue sits in 247 Pure-HCP customers, and Smart Bidding has never received conversion signal from 1,800+ Google-acquired customers whose conversions landed in HCP, not ST.

📊 The Buckets

HCP total customers

4,271

ST total customers

5,465

Overlap (in both)

4,024

True unique customer book

5,712

BSP CUSTOMER UNIVERSE — TRUE COUNT: 5,712 ════════════════════════════════════════════ 🟨 PURE HCP 🟩 MIGRATED (BOTH) 🟦 PURE ST 247 customers 4,024 customers 1,441 customers $401,791 LTV HCP era: $5.35M ST era: ~$1.48M ST era: $3.24M 👻 Ghost revenue 👥 ST "Existing Customer" 🆕 Post-cutover 📞 Call in as "new" 📞 73.6% of ST book 📅 Since 2025-07-15 🔕 Invisible to ST ⚠️ Attribution severed ✅ Clean attribution 💰 Re-engage target 🎯 LTV / membership target 🚀 Smart Bidding target

📅 The Timeline

📅 2021-09 ─────────────────► 2025-07-15 ─────────────────► 2026-04 HCP ERA CUTOVER ST ERA 📞 Customer ──┐ ┌─► 📞 Customer calls BSP │ │ calls BSP ▼ ▼ [HCP CRM] ── 94% migrated ──► [ServiceTitan] 4,271 customers 5,465 customers $5.76M LTV $4.72M LTV │ │ ▼ ▼ 🔍 Lead Source 🔍 Lead Source "google": 27% "Existing": 48% ⚠️ "blank": 52% ← attribution blindspot real source: LOST "LSA": 11% for 4,024 migrants

Migration cutover date inferred: last HCP service activity = 2025-07-15. HCP subscription ($1,521/mo) still active — system of record for the 247 Pure-HCP customers and the historical book on the 4,024 migrants.

💸 Lifetime Value — Don't Add Naively

Metric	Value	Notes
HCP total LTV	$5,755,279.67	Over ~4 years (2021-09 → 2025-07)
— Migrated customers LTV (HCP era)	$5,353,488.06	4,024 customers with ST match
— Pure HCP LTV	$401,791.61	247 customers — the real hidden revenue
ST total LTV	$4,724,704.23	Since cutover, ongoing
— Overlap customers' ST-era LTV	$3,244,251.51	Same 4,024 customers, ST era
— Pure ST LTV (estimated)	~$1,480,452.72	1,441 post-cutover customers

⚠️ Double-count trap. $5.76M + $4.72M = $10.48M is wrong by ~$5.4M. The 4,024 migrated customers appear in both systems. Their HCP-era jobs + ST-era jobs are likely additive (different time periods) but job-level analysis (Pass 3) is needed to rule out duplicate migrated transactions.

🔍 Why This Matters — 3 Angles

ATTRIBUTION

🏷️ ST "Existing Customer" is a fiction

4,024 of ST's 5,465 customers (73.6%) originated in HCP. They present in ST as "Existing Customer" but their true acquisition source lives in HCP — which itself has 52% blank Lead Source. Repeat-revenue analysis built on this bucket is mis-attributed.

Fix: Add "Original Lead Source" custom field in ST, backfilled from HCP where available.

REVENUE

💰 Combined LTV ≠ naive sum

True combined customer book = 5,712 unique (not 9,736). Overlap customers carry revenue in both systems. Naive addition over-counts by ~$5.4M.

Fix: Pass 3 — job-date split per overlap customer. HCP jobs before 2025-07-15 + ST jobs after = true lifetime. Deduplicate only if same job migrated as a record.

SMART BIDDING

🤖 1,800+ lost conversions

HCP recorded 1,158 "google" + 482 "Google LSA" + 167 "GMB" = ~1,800 Google-acquired customers. Their conversions never fed Google's Smart Bidding algorithm because HCP had no GCLID chain.

Fix: No retroactive repair. Forward-only: ensure GCLID → ST → Google Ads offline bucket chain is clean for new leads (see project-offline-conversion-wrong-bucket.md).

🎯 The 4 Data Integrity Findings

Migration rate is 94.2%, not 0.28%. The "HCP" lead-source tag in ST covered only $16K (0.28%) — that was residual labeling, not scope. The actual migration moved 4,024 of 4,271 HCP customers into ST, rebranded as "Existing Customer."
Existing-Customer bucket is contaminated by default, not by error. The bucket's 48.3% dominance in ST analytics isn't a local-market-loyalty story — it's the HCP migrant population showing up as repeat customers because they ARE repeat customers, just acquired years ago under a different CRM.
$401,791 in Pure-HCP customers is genuinely invisible. 247 customers who never migrated. If they call BSP today, ST tags them "new customer" (no phone/email match). No marketing channel gets credit when they do convert.
Attribution blindspot is wider than the GCLID chain. HCP had 52% blank Lead Source. Even if we rebuild ST's lead-source field from HCP for migrants, at least half the records add no signal. This caps how much of Smart Bidding's historical starvation we can retroactively fix.

✅ Action Items

Priority	Action	Owner	Why
P1	Pass 3 — HCP x ST job-date reconciliation per overlap customer	Robert	Proves whether $5.4M overlap is additive or double-count before any combined-revenue claim
P1	Add "Original Lead Source" custom field in ST, backfill from HCP export	Robert + Ashton	Recovers acquisition attribution for 4,024 migrants (48% with a source tagged)
P2	Re-engagement list — 247 Pure-HCP customers with phone/email	Robert	$401K ghost book. If even 10% re-engage at HCP avg ticket ($2,868), that's ~$71K.
P2	Keep HCP subscription active until Pass 3 + re-engagement finishes	Stephanie (approve)	$1,521/mo is cheap insurance; canceling orphans the 247 and erases historical reference for the 4,024.
P3	Dashboard note — flag "Existing Customer" bucket as "includes HCP migrants"	Robert	Stops leadership from interpreting the bucket as organic loyalty

🔬 Method & Reproducibility

Data sources

C:\Users\dovew\Downloads\hcp_export\BrightSidePlumbing_customer_export.csv — 4,271 rows, exported 2026-04-13 20:58 from Housecall Pro
bsp_analytics.titan.customers on VM — 5,465 rows, indexed on phone + email

Normalization

Phones: strip non-digits, take last 10 (handles 1- country code, dashes, parens)
Emails: trim + lowercase
LTV: strip $ and commas, cast to float

Match logic

JOIN HCP x ST ON (
    hcp.mobile  IN (st.phone, st.phone_alt)
 OR hcp.home    IN (st.phone, st.phone_alt)
 OR hcp.work    IN (st.phone, st.phone_alt)
 OR hcp.email   =  st.email
)

Artifacts on VM

/tmp/hcp_match_keys.csv — 4,272 rows (header + 4,271 records) pushed from Robert workstation
/tmp/match_pass2.sql — the SQL producing the bucket + LTV + overlap tables
/opt/nexus/nexus/scripts/output/playbooks/BSP_Master_Session_History.html#bsp-apr14-verification-pass-2 — Pass 2 log

🔐 Verification

Produced: 6-row bucket table, 6-row LTV table, 3-card implications grid, 5-row action table — all derived from real SQL on VM against HCP CSV + titan.customers.
Correct: Bucket counts reconcile (4,024 Migrated + 247 Pure HCP = 4,271 HCP total ✓; 4,024 + 1,441 = 5,465 ST total ✓). LTV sums consistent across queries.
Changed real data? Read-only match (temp tables only, no DB writes). Persistence = this HTML report + Master History section bsp-apr14-verification-pass-2.

Generated 2026-04-14 by Nexus AI pipeline. Live at https://morpheus.callbrightside.com/documents/BSP_HCP_Intelligence_Report.html. Pairs with Master Session History sections bsp-apr14-verification-pass-1 and bsp-apr14-verification-pass-2. Source CSV: BrightSidePlumbing_customer_export.csv @ 2026-04-13 20:58.

📋 Data Sufficiency — What to Pull from HCP (and What Not To)

Question answered (2026-04-14): Does Robert need to pull additional data from the HCP app to execute the priorities?

Short answer: No for the 4 priority actions this week. Yes only for Pass 3 revenue reconciliation and v2 pitch personalization.

Data Sufficiency Matrix

Task	Data Needed	In CSV?	Pull from HCP App?
📧 Email campaign (this week)	Name, email, last-service, LTV	✅ Yes	❌ No — ship now
🔁 247 Pure-HCP re-engagement	Name, phone/email, LTV, last service	✅ Yes	❌ No — ship now
🎖️ Membership pilot (298 high-LTV)	Name, contact, LTV	✅ Yes	❌ No — ship now
🏷️ Backfill Original Lead Source → ST	Match key + lead source per customer	✅ Yes	❌ No
🎯 Pitch personalization (service type)	Services used per customer	❌ No	⚠️ Nice-to-have (Jobs export)
📐 Pass 3 — revenue reconciliation	Job-level dates + amounts per customer	❌ No	✅ Required (Jobs export)
🤖 Smart Bidding historical repair	Per-lead GCLID	❌ No	❌ Retroactive impossible

What the CSV Already Contains (No Re-Pull Needed)

Identity: First/Last Name, Display Name, HCP ID
Contact: Mobile/Home/Work Phone, Email, Additional Emails
Classification: Customer Type, Lead Source, Tags, Do Not Service flag
Aggregate value: Lifetime Value
Lifecycle: Customer created at, Last service date
Up to 21 addresses per customer

What the CSV Lacks (Would Require Separate HCP Pulls)

Job-level data — dates, amounts, service types per individual job. Needed for Pass 3 and pitch personalization.
Invoice-level data — line items, individual invoice amounts
Communication history — calls/email/SMS logs
Custom fields (HCP allows these)
Reviews/ratings per customer
Payment / collection history

Recommendation

Ship email + membership + Pure-HCP re-engagement off the CSV this week. Queue HCP Jobs/Invoices export as a 2-week follow-up for Pass 3 reconciliation + pitch personalization v2. Do not let a second pull block anything shippable now.

Verification

Produced: 7-row sufficiency matrix + two lists (has/lacks) + recommendation, all derived from the CSV schema observed in Pass 1 (column list confirmed via csv.DictReader header read).
Correct: Every CSV column claim is traceable to BrightSidePlumbing_customer_export.csv header row (First Name... through Address_21 Notes). Every gap claim is absence of those column names in the header.
Changed real data? This section persists in both BSP_HCP_Intelligence_Report.html AND BSP_Master_Session_History.html. Byte deltas reported by logger in sequence.

Logged via nexus_html_logger.py at 2026-04-14T03:59:47.806555 UTC