Google Ads (SEA) Deep Dive — Process & Methodology
End-to-end process notes for the Google Ads (DMS - SEA) lifecycle + performance analysis, run 2026-06-04. Mirrors the listings/websites methodology, with an ads-native conversion-tracking proof replacing the rank/traffic axis, and a DEA revenue split that separates FCR fee revenue from client media spend.
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
Section titled “What this analysis produces”GOOGLE_ADS_DEEP_DIVE.html— 2,131 businesses ever ran FCR-managed Google Ads, 544 alive today (26% — the highest-churn FCR product).
Two halves:
- Part 1 — Lifecycle & retention (§1–12): headline (with the DEA split), type breakdown (standard vs e-Store), existing vs net-new, standalone vs bundled, budget & fee-revenue concentration, survival curve, tenure bands, category leaderboard, prior portfolio, upsell, co-churn, locality.
- Part 2 — “Is the spend working?” (§13–15): conversion-tracking health bands, performance by type, named lists (zero-conversion-spending, paused, attach-a-product, strong-converter proof).
- Addendum — accounts managed outside subscriptions (agency / CFM-billed).
The DEA split — revenue ≠ media spend (read first)
Section titled “The DEA split — revenue ≠ media spend (read first)”Unlike listings and websites, SEA monthly_value is mostly ad-spend pass-through (DEA), not FCR revenue, and active_clients.monthly_value for SEA (~€713k/mo) is a contracted/budget figure that overstates actual spend ~4×. Never present it as MRR.
Derive the real numbers from SUPERSET_MASTER.2_Ads_Data_Monthly (campaign × month):
cost_micros= client media spend;retail_cost_micros= what the client pays FCR;management_fee_decimal= fee rate.- FCR fee revenue = retail − cost (the real MRR). Media = cost (client budget / DEA). Never conflated.
- Fee-rate convention = fee/retail (≈ 37%, matches “@38%” product names), not fee/media (= markup).
Actuals (last 3 full months, ~508–544 alive spending subs): client media €102k/mo, retail €163k/mo, FCR fee revenue €61k/mo (subscription book), avg €120/sub.
Agency / CFM-billed accounts — excluded from the cohort
Section titled “Agency / CFM-billed accounts — excluded from the cohort”Some managed-ad accounts are billed manually / through an agency (“bill-to-burn”: media + media-management invoiced monthly) and carry no DMS-SEA subscription line — so they’re absent from active_clients/Dynamics and excluded from the subscription cohort. They live only in 2_Ads_Data.
The dominant one is 12182968 = City Financial Marketing Group (“CFM”), billed via IOD Media (the agency fronts CFM only) — FCR’s largest ad account (~€36.5k/mo media, ~€6.45k/mo fee, ~8,350 conv/mo). 29 such off-subscription accounts total (€39k/mo media, ~€8.4k/mo fee; CFM = 90% of media / 76% of fee). Also exclude 35356922 = FCR Media Ltd (own internal account).
Effect: the retention curves (§1–12) are legitimately subscription-only (these accounts have no subscription tenure), but the revenue/performance headlines must carry them — true managed-ads fee ≈ €69–70k/mo (+~13%). For total managed-ads economics, union 2_Ads_Data accounts; don’t rely on the DMS-SEA subscription cohort. See the deck Addendum.
Data sources
Section titled “Data sources”| Table | Used for |
|---|---|
DYNAMICS_DATA.Accounts_and_related_data | Cohort anchor — recurring-subscription ads lines, first-billing, cancellations. |
fcr_operations.active_clients | ”Alive today” via latest snapshot + product_group = 'DMS - SEA' + ads-line match. |
SUPERSET_MASTER.2_Ads_Data_Monthly | The DEA split — management_fee_decimal, cost_micros, retail_cost_micros per campaign × month. |
SUPERSET_MASTER.2_Ads_Data | Performance — metrics_conversions, metrics_clicks, metrics_impressions, cost. Conversions are NOT in the _Monthly fee table. |
SUPERSET_MASTER.dim_listings_with_subscriber_id | Category, bizname, county. |
Pipeline (scripts/google-ads/)
Section titled “Pipeline (scripts/google-ads/)”| # | Script | Output |
|---|---|---|
| 01 | 01-ads-kb-build.cjs | docs/google-ads-deep-dive-kb.csv — cohort, type (standard/e-Store), fee_model, lifecycle dates, pre/post portfolio, fee-revenue + media split |
| 04 | 04-ads-analyze.cjs | .tmp_ads_metrics.json — lifecycle §1–12 (MRR section split into fee-revenue vs media-under-management) |
| 05 | 05-ads-performance.cjs | .tmp_ads_performance.json + 3 named CSVs — conversion-tracking health |
The HTML deck is hand-built from the two .tmp_ads_*.json files + the CFM addendum probe.
Methodology / definitions
Section titled “Methodology / definitions”- Cohort = subs who ever billed a Google Ads management recurring line (
standard_ads: Adwords / GA Perpetual / Campaign-or-Media Management Fee;estore_ads: e-Store Google Ads). Initial/setup fees excluded;subscription = TRUEfilter drops one-offs. - Tenure = months from first ads billing to today (alive) or last end (churned).
- Alive = active on an ads line in the latest
active_clientssnapshot. - Performance bands (last 3 full months of
2_Ads_Data):no_recent_spend(€0 in window),spending_zero_conv(spending but no conversions recorded — tracking gap),converting(low / strong ≥5 conv/mo).
Headline findings
Section titled “Headline findings”- 2,131 ever / 544 alive (26%) — highest-churn, shortest-tenure FCR product (17.8mo median); 43% gone in year one; permanent core of 250 at 8yr+.
- e-Store (ecommerce) ads has collapsed — 471 ever, 31 alive (7%).
- Bundling is the lever — standalone 17% → anchor 42%; upsold 41% vs 19%. The strongest retention swing of any product.
- 89% of spending accounts record conversions — the spend works for those who stay. Risk pools: 58 zero-conversion-but-spending (~€10.3k/mo, the AI Max amplification risk) + 33 paused-but-billing.
Caveats (ads-specific)
Section titled “Caveats (ads-specific)”- Spend is RETAIL — what the client pays FCR (incl. fee). Present as the client’s investment, never “media spend”.
active_clients.monthly_valuefor SEA is budget, not revenue — derive from the ads rollups.costPerConv = null/ zero conversions = “no conversions recorded” (tracking gap), not proven zero performance.- Never compute metrics yourself — use the pre-computed/rollup fields.
- CFM/agency accounts excluded (see above). Order history starts 2017. Small-n tiers (€2000+ budget n=10, €2500+ spend n=1) are directional only.
- Reconcile any client-facing € (incl. the corrected ~€69–70k/mo fee) to
/revenue-bridgefirst.
Outputs
Section titled “Outputs”docs/google-ads-deep-dive-kb.csv— the KBGOOGLE_ADS_DEEP_DIVE.html— the report (with CFM addendum)ADS_ZERO_CONVERSION_SPENDING.csv(58) ·ADS_NO_RECENT_SPEND.csv(33) ·ADS_STRONG_CONVERTERS.csv(344)