I made an agency's ad spend chase real customers, not form-fills.
A performance-marketing agency ran Google Ads for ~30 real-estate investor clients, but half the "conversions" were junk leads, and the truth about which leads became customers lived in offline spreadsheets. I built the data platform that ties every dollar of spend to qualified leads and signed contracts (down to the keyword) across every client.
Anonymized: specifics under NDA, framing and lessons in the open.
Google optimizes toward form-fill "conversions", but for this agency, roughly half of those were junk. The truth about which leads qualified (and which signed) lived offline, scattered across a spreadsheet per client, loosely keyed by UTM tags and click IDs. There was no system tying ad spend to qualified outcomes across ~30 accounts, and every client report was assembled by hand.
A serverless marketing-analytics platform. It ingests Google Ads (down to keyword, ad group, and ad copy), GA4, the website, and each client's offline lead-qualification tracker into one governed data lake. Two merge engines join ad spend to the offline qualified-lead and signed-contract data by keyword and click ID. A REST API and a React dashboard surface true cost-per-qualified-lead and cost-per-contract, wasted spend, and which headlines drive qualified leads, per client. Qualified conversions are fed back to Google to train Smart Bidding, and the whole warehouse is queryable in plain English.
(by keyword)
qualification
Real data from 30-plus clients and 1.8M+ rows, dashboards answering in ~4–5 seconds, and the whole stack reproducible as infrastructure-as-code. Query cost dropped ~95% ($5 → $0.25). The numbers finally matched each client's own Google Ads console. And, the point, the agency could optimize toward qualified leads and contracts instead of vanity form-fills, with self-serve dashboards replacing the manual spreadsheets.
[ specifics under NDA ]
What runs on top of the lake.
Six capabilities running on top of the governed data lake: from ingestion to the offline-conversion loop to plain-English querying.
Multi-source ingestion
Google Ads, GA4, the website, and each client's offline tracker, pulled in parallel and fault-isolated, so one bad source can't stall the rest.
Offline-conversion loop
Join ad spend to qualified leads and signed contracts (matched by keyword and click ID) to close the loop between a click and a customer.
Governed data lake
Raw → curated layers with a data catalog and query engine, and partition projection so queries stay fast and cheap as the data grows.
Self-serve client dashboards
Per-client KPIs, wasted spend, and the ad copy that actually drives qualified leads, replacing reports that used to be built by hand.
Smart-Bidding feedback
Qualified conversions are pushed back to Google, so Smart Bidding learns to optimize toward real outcomes instead of junk form-fills.
Ask in plain English
An AI assistant queries the whole warehouse in natural language, connected via MCP, so anyone can ask the data a question and get an answer.
Keeping the numbers honest.
The part most automation work skips: the bugs I hunted down and the engineering underneath the metrics. Anonymized, but real.
The "14× spend" bug
A dashboard once showed ~14× the real spend. A query was recursively reading every date folder in the lake. Fixed with partition projection, with no data reprocessing.
Cost-engineered queries
Partition pruning, pre-aggregation, columnar storage, and caching cut data scanned by ~95%, dropping cost from $5 to $0.25 per query.
Tables as code, not guesswork
Auto schema-crawling caused drift and broken joins. Defining the tables explicitly, as code, killed it for good.
Attribution from real joins
Hardcoded qualification percentages were replaced with real joins to the offline tracker. One config fix unlocked 100% keyword→qualified-lead attribution.
[ anonymized: specifics under NDA ]
Optimizing for the wrong number?
Tell me where your ad data lives and what you're optimizing for today. I'll map out the build that ties every dollar of spend to qualified leads and signed contracts, down to the keyword.