Your Dashboard Is Lying: The Spend Fan-Out Trap and the Lead I Lost When the API Went Down

Description and use

Dashboard misled budget decisions due to SQL fan-out and lost leads when API failed. Correcting spend aggregation and adding robust retry logic yielded accurate cost metrics.

Problem

Marketing attribution dashboards can mislead budget decisions when spend is inflated by SQL join fan-out and when leads are lost due to unhandled connection failures.

Outcome

After correcting SQL aggregation and implementing robust retry logic, the dashboard accurately reflected cost metrics, revealing Meta was 1.2× cheaper per actual visit than Google.

Workflow

• 1. Build synthetic-data pipeline tracing spend → lead → booking → appointment.
• 2. Identify spend fan-out bug: join spend to leads inflates spend.
• 3. Fix by pre-aggregating spend and leads separately before joining.
• 4. Add test comparing view’s total spend to source table.
• 5. Detect lead loss bug: n8n’s “Never Error” fails on connection errors.
• 6. Test n8n’s Retry On Fail; observe no retries on connection errors.
• 7. Implement durable inbox and classified retries (422 vs 500).
• 8. Update dashboard to display Cost per Booking and Cost per Occurred.
• 9. Validate results with simulated data showing Meta 49% show rate, $41.22 Cost per Booking, $87.13 Cost per Occurred; Google 82% show rate, $81.99 Cost per Booking, $106.26 Cost per Occurred.

Evidence

Marketing attribution is broken. Not because the math is hard, but because the plumbing is fragile.

Source

If you join a spend table directly to a leads table, each spend row repeats once per lead. Total spend inflates silently.

Source

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.

Source

Full code, tests, and specs: github.com/emadaed/intake-to-booking

Source