SitePro / Websites Deep Dive — Process & Methodology

End-to-end process notes for the Websites (DMS - Websites) lifecycle + site-health analysis, run 2026-06-04. Mirrors the SnapSync/SayMore listings methodology but without the paid SerpAPI rank run — the “does it work” proof comes from GA4/GSC traffic rollups we already have, so the whole pipeline is free.

This doc is the canonical reference for re-running or extending the analysis. The findings live in the HTML report — see Outputs.

---

What this analysis produces

A single combined deep-dive treating SitePro brochure sites and Storefront/e-Store ecommerce as one website product family:

• SITEPRO_WEBSITES_DEEP_DIVE.html — 3,180 businesses ever sold an FCR website, 1,758 alive today (55%).

Two halves:

• Part 1 — Lifecycle & retention (§1–12): headline, generation breakdown (brochure↔ecommerce + migration), existing vs net-new, standalone vs bundled, MRR tier, survival curve, tenure bands, category leaderboard, prior portfolio, upsell + add-on depth, co-churn, locality.
• Part 2 — “Is the site working?” (§13–15): GA4/GSC site-health bands, health by generation, named AM lists (not-connected, ecommerce-tracking-gap, cross-sell pool, migrate-to-ecommerce, proof, standalone).

Data sources

Table	Used for
DYNAMICS_DATA.Accounts_and_related_data	Orders → salesOrderDetails → billingGroup → recurringInvoices — first-billing dates, MRR, cancellations. The KB SQL anchors the cohort here.
fcr_operations.active_clients	Daily snapshot; “alive today” via MAX(snapshot_date) + product_group = 'DMS - Websites' + a website-line match.
SUPERSET_MASTER.dim_listings_with_subscriber_id	Subscriber identity — AccountName, bizname, main_category.
fcr_operations.sitepro_listing_map	SitePro URL by subscriber (join key for site-health).
SUPERSET_MASTER.2_GA4_site_totals	Monthly GA4 sessions + ecommerce_purchases per subscriber — the connectivity + traffic signal.
SUPERSET_MASTER.2_GSC_data	Monthly GSC clicks + impressions per subscriber — the primary site-health band metric.

Pipeline (scripts/sitepro-websites/)

#	Script	Output
01	01-websites-kb-build.cjs	docs/websites-deep-dive-kb.csv — cohort, generation, lifecycle dates, MRR, pre/post portfolio, add-on depth, sitepro_url
04	04-websites-analyze.cjs	.tmp_websites_metrics.json — lifecycle §1–12
05	05-websites-sitehealth.cjs	.tmp_websites_sitehealth.json + 3 named CSVs — GA4/GSC bands
06	06-websites-build-pipeline.cjs	PARKED — Teamwork build-queue cross-ref (stuck-in-build / never-launched); written, not run

Numbering skips 02/03 deliberately (those were the listings keyword-preview + rank run, not used here). The HTML deck is hand-built from the two .tmp_*.json files — no auto-generator.

Methodology / definitions

• Cohort = subs who ever billed an actual website recurring line (brochure SitePro Website - Sitepro%/Renew SitePro/Multi, or ecommerce e-Store%/Storefront%/Ecommerce%). Add-ons excluded from the anchor (SSL, domain, email, payment-hub, calendar, GMB, custom-dev) but captured as web_addon_list/count (bundle depth). The subscription = TRUE filter drops one-off setup fees at source.
• Generation = brochure vs ecommerce, recorded at the first and current website line (so brochure→ecommerce migration is visible).
• Tenure = months from first website billing to today (if alive) or last end (if churned) — lifespan, not current age.
• Alive = active on a website line in the latest active_clients snapshot.
• Site-health bands (last 3 full months of GA4+GSC): not_connected (no GA4 and no GSC), zero_traffic, minimal/moderate/strong by avg monthly GSC clicks.

Headline findings

• 3,180 ever / 1,758 alive (55%) — much stickier than listings (38%); survival curve flattens at Y+8 48% vs listings 32%.
• Ecommerce beats brochure on every axis — retention 72% vs 51% (current generation), site-health strong 64% vs 37%. The 292 brochure→ecommerce migrators are the longest-tenured cohort (82 months).
• 94% of live sites are connected, 91% pull real traffic, zero “live ghosts.” The risk pool is the 104 not-connected sites (30 of them 5yr+ — billing for a site Google can’t see).
• Levers: bundle the 1,943 standalone subs (50% → 78% fully bundled); upsell the 2,439 not-upsold (52% → 65%); fix the 376 ecommerce sites with no purchase tracking.

Gotchas / caveats

• Worker returns BQ BOOL as the string "true" — test v === true || v === "true", never === true.
• Setup vs recurring — the subscription = TRUE filter keeps MRR to the recurring line; a raw active_clients product_name scan shows inflated setup totals.
• GA4 session totals are inflated on some large properties — band on GSC clicks; treat GA4 as a connectivity + ecommerce_purchases signal only.
• Ecommerce “no sales” (376) is a conversion-tracking gap, not real zero-sales.
• Order history starts 2017 — pre-2017 websites undercounted. “Net-new logo” (80%) is inflated by websites bundled-and-sold same-day as listings.

Outputs

• docs/websites-deep-dive-kb.csv — the KB (one row per website sub)
• SITEPRO_WEBSITES_DEEP_DIVE.html — the report
• WEBSITE_SITEHEALTH_NOT_CONNECTED.csv (104) · WEBSITE_ECOMMERCE_NO_SALES.csv (376) · WEBSITE_SITEHEALTH_ZERO_TRAFFIC.csv (0)
• Full run order + gotchas: scripts/sitepro-websites/README.md