The Problem That Costs Real Budget
Marketing attribution is broken. Not because the math is hard, but because the plumbing is fragile. A CMO looks at a dashboard showing "Cost per Booking: $41" for Meta and "Cost per Booking: $82" for Google, and shifts budget. But if only 49% of those Meta bookings actually show up for their appointments, the real cost per occurred appointment is $87—barely cheaper than Google's $106 .
The gap between "booking" and "showing up" is where money disappears. I built a synthetic-data pipeline to trace this end-to-end: ad spend → lead → booking → occurred appointment. This is the story of the two bugs that almost made the dashboard lie, and the one that actually lost data.
The Stack (and Why)
Layer Tool Why
Intake n8n Visual orchestration, easy webhook setup
Validation API Flask Strict allowlist, testable logic
Operational DB SQLite Simple, local
Reporting Copy PostgreSQL (Docker) Metabase won't read SQLite
Dashboard Metabase Free, SQL-friendly
Bug #1: The Spend Fan-Out Trap
This is the classic SQL mistake that produces plausible-looking but wrong numbers. If you join a spend table directly to a leads table, each spend row repeats once per lead. Total spend inflates silently.
The fix: Aggregate spend and funnel counts separately, then join the totals.
-- WRONG: Spend fans out
SELECT l.channel, SUM(s.spend) as total_spend, COUNT(l.id) as leads
FROM leads l LEFT JOIN spend s ON l.channel = s.channel
GROUP BY l.channel; -- total_spend is inflated
-- RIGHT: Pre-aggregate both sides
WITH spend_agg AS (SELECT channel, SUM(spend) as total FROM spend GROUP BY channel),
lead_agg AS (SELECT channel, COUNT(*) as cnt FROM leads GROUP BY channel)
SELECT l.channel, s.total as spend, l.cnt as leads
FROM lead_agg l LEFT JOIN spend_agg s ON l.channel = s.channel;
I added a test that compares the view's total spend to the source table. Because this failure produces no error—just a wrong number—the test is the only guard.
Bug #2: The Lead I Lost (and the Retry That Didn't Fire)
n8n's "Never Error" setting routes HTTP error responses to a rejected branch. But when the API was completely stopped, there was no response at all. The node failed with a connection error, and the lead existed only in n8n's execution history.
I then tested n8n's Retry On Fail. No retries were observed. The lead was still lost.
Why? Because n8n's retry mechanism doesn't catch connection-level failures the way you'd expect. The community has built proper retry templates with exponential backoff, jitter, and dead-letter queues , but the default behavior is deceptive.
The lesson: "Never Error" only helps when the downstream service answers. No answer is a different failure mode. The fix requires a durable inbox and classified retries (422 = don't retry, 500 = retry), which is a separate engineering spec I wrote instead of pretending I'd fixed it.
The Result (Simulated)
Channel Show Rate Cost per Booking Cost per Occurred
Meta 49% $41.22 $87.13
Google 82% $81.99 $106.26
Meta looks 2× cheaper per booking. It's 1.2× cheaper per actual visit. The dashboard leads with the right metric.
Full code, tests, and specs: github.com/emadaed/intake-to-booking
Top comments (3)
The "compare the view's total to the source table" test is the right guard, since a fan-out never errors. A cheap version that also works on dashboards you didn't write: count(*) after the join against count(DISTINCT the parent id) and alert when they differ. It catches fan-out the day somebody adds a one-to-many join, not the day finance asks why spend is up 30%. Does your reporting copy in Postgres get rebuilt on a schedule, or can you run that check right after each rebuild?
The test comparing the view's total spend to the source table is the part I'd copy first, since a fan-out never throws an error. I'd also write the grain into the metric definition itself, like "cost per occurred appointment, one row per appointment," so anyone who joins spend to leads later sees the definition break, not just a failing test. The booking vs show-up gap is a good example of a number that looks finished and isn't.
the gap between booked and actually showed up is the same shape as a lead list where a chunk of the people already left the role, the total looks great and the usable part is much smaller. I wrote about checking that row by row recently, the number worth reporting is the rows that survive