Case study · Marketing agency (paid media, lead gen)
Agency ad data warehouse with alerts and a lead check
For a Dutch performance marketing agency with 1,000+ client accounts. Scattered Google Ads, Meta and GA4 tables turned into a BigQuery star schema, with Looker Studio alerts on spend, CPA, CTR and ROAS per ad group and a check that catches lead-tracking gaps.
Windsor.aiBigQuerySQLGA4 exportLooker Studio
0:00 / 0:45
What the animation shows, scene by scene:
- Scattered: 1,000+ client accounts with Google Ads, Meta and GA4 data in many separate tables; every dashboard stitched them together again.
- Blind spot: Leads reported by Meta on one side, leads recorded on the landing page on the other, and no reliable way to know if they matched.
- Star schema: One star schema in BigQuery: an ad performance fact with client, account, campaign and ad group dimensions, built as staging, views and marts.
- Alerts: Every ad and ad group scored on spend, CPA, CTR and ROAS over daily, 7, 14 and 30 day windows, in one alert report instead of opening each account.
- Lead check: Meta leads vs landing-page leads: above 90% for most of 2025, dropping to about 57% in Oct 2025 and again in Jan 2026.
- Result: 1,000+ client accounts in one model, and lead gaps caught the day they start.
One model for 1,000+ client accounts
Daily to 30-day alerts per ad and ad group
Lead check caught alignment dropping from 97% to ~57%
The problem
- Windsor.ai loaded the data into BigQuery, but it sat in many separate, inconsistent tables (per platform, per report). Every dashboard had to stitch them together again.
- 1,000+ client accounts, active and inactive: nobody could see account health across the whole portfolio.
- No reliable way to know whether the leads Meta reported actually arrived on the client’s landing page.
What I delivered
- Star schema in BigQuery: fact tables for ad performance with dimensions for client, account, campaign and ad group; staging → views → marts per platform, and one client table mapping accounts to clients.
- Alerting: daily, 7-, 14- and 30-day alert views that score every ad and ad group on spend, CPA, CTR and ROAS, plus a 7-day campaign delivery check.
- Looker Studio report: accounts overview, the alert pages, a Meta page and a clients overview, all filterable by client, account, campaign and ad group.
- Meta vs landing-page lead alignment: compares the leads Meta reports with the leads recorded on the landing page (GA4), per client, over 3, 6 and 12 months.
- Ownership: I owned the agency’s data layer from ingestion to dashboards for the length of the contract, and handed it over when they hired an in-house data person.
Results
- One model for 1,000+ client accounts across Google Ads, Meta and GA4.
- Media buyers check underperforming ads and ad groups from one alert report instead of opening each account.
- Lead-tracking gaps made visible: alignment ran above 90% for most of 2025, then fell to ~57% in Oct 2025 and again in Jan 2026. The 3-month view (79%) flagged it against the 12-month baseline (97%). Drops like these point to broken pixels, form changes or tracking issues before clients notice.