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

说明与用途

仪表盘因 SQL fan-out 和 API 停机导致的潜在漏报导致预算决策错误。通过纠正支出聚合并实现可靠的重试机制，获得了准确的成本指标。

问题

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.

结果

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.

工作流

• 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.

证据

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

来源

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

来源

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.

来源

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

来源