It started like every other "we'll just automate it" idea: noble intentions, clean spreadsheets, and a promise to never again [manually reconcile campaign spend](https://dev3lop.com/blog/data-engineering-case-study-scaling-to-handle-1-billion-events-daily) with conversions at 7 a.m. We had a tidy pipeline-events flowed into our warehouse, a scheduled job calculated KPIs, and a bot posted a daily summary to Slack. Then, one Tuesday night at 11:47 p.m., the bot posted: "Revenue: $9,874,221. CPA: $0.03. Congrats team!"

For a solid thirty seconds, we celebrated. Then we [looked at the site](https://dev3lop.com/the-data-driven-mindset-how-to-think-like-a-modern-software-consultant) traffic. Then Stripe. Then the cold realization: the automation had gone rogue, and the numbers were lying-loudly.

## The Blow-Up: How a "Helpful" Script Manufactured a Miracle

Here's what actually happened. We had introduced a new event called purchase_completed_v2 and planned to deprecate purchase_completed. For one week, both events fired in parallel so downstream dashboards wouldn't go dark. Our automation, however, was built on a simple assumption: "Count purchases by event_name IN (purchase_completed, purchase_completed_v2)." That was fine... until our ETL also backfilled late-arriving events.

So purchases were double-counted in two ways:

1) Dual tracking: one real purchase produced two events.

2) Backfill + incremental overlap: the job reprocessed "yesterday + today" to catch stragglers, but our de-dupe key was session_id + timestamp rounded to the minute (don't do this). Result: the same purchase slid through twice when timestamps differed by seconds.

The Slack bot did what we trained it to do: amplify bad math at scale.

A practical smell test we ignored: the report showed CPA at $0.03 while spend hadn't changed. Any automated report should have basic sanity checks like "If CPA drops >70% day-over-day, verify before posting." Ours had none.

## The Debugging Playbook We Wish We Had (and Now Do)

We fixed it that night, but the lasting win was turning the incident into a set of guardrails:

1) Add true deduplication keys

Use a stable purchase_id / order_id from the source of truth. If you don't have one, create one upstream (e.g., hash of user_id + order_number). Deduping by rounded timestamps is basically asking for chaos.

2) Make jobs idempotent

If your pipeline re-runs a window (like "last 48 hours"), it must produce the same result every time. We switched to a merge/upsert strategy: write into a partitioned table and MERGE on purchase_id so reprocessing can't inflate totals.

3) Build anomaly gates before broadcasting

Before the bot posts, it now checks a few rules:

- Revenue change > ±30% day-over-day? Post "Needs review" instead of the number.
- Purchases > sessions? [Hard fail](https://dev3lop.com/how-to-remove-the-dall%c2%b7e-logo-from-the-bottom-of-the-image-using-python).
- Conversion rate > 20% for a non-checkout page? Hard fail.

These are simple heuristics, but they stop the worst lies.

4) Treat metrics like code: version and deprecate

When we introduced purchase_completed_v2, we should have shipped a metric version change too (Revenue_v2) and run it in parallel with explicit labels. Instead, we blended them and hoped for the best.

## What We Changed So It Doesn't Happen Again

The next day, we wrote a one-page "Metric Contract" for each KPI: definition, source tables, dedupe key, refresh window, and acceptable ranges. We also added a "quiet mode" to the Slack bot: if checks fail, it pings #data-oncall with a diff of which event names and counts spiked.

Automation isn't the villain-it's the megaphone. Once we taught our system to doubt itself a little, it went back to being the teammate we wanted: fast, consistent, and far less likely to throw a midnight victory parade for imaginary revenue.

---

**Related Reading:**
- [Supply Chain Transparency: Visualizing End-to-End Product Journeys](https://dev3lop.com/supply-chain-transparency-visualizing-end-to-end-product-journeys)
- [Send Auth0 data to Google BigQuery Using Node.js](https://dev3lop.com/send-auth0-data-to-google-bigquery-using-node-js)
- [tylers-blogger-blog](https://tylers-blogger-blog.blogspot.com/2026/04/local-llms-for-non-tech-teams-your-no.html)
- [A Hubspot (CRM) Alternative | Gato CRM](https://www.reddit.com/r/AnalyticsAutomation/comments/1ra4fqb/a_hubspot_crm_alternative_gato_crm)
- [A Trello Alternative | Gato Kanban](https://www.reddit.com/r/AnalyticsAutomation/comments/1r4mjsl/a_trello_alternative_gato_kanban)
- [A Slides or Powerpoint Alternative | Gato Slide](https://www.reddit.com/r/AnalyticsAutomation/comments/1ra0cmx/a_slides_or_powerpoint_alternative_gato_slide)
- [My own analytics automation application](https://www.reddit.com/r/AnalyticsAutomation/comments/1r4lrsd/my_own_analytics_automation_application)
- [A Quickbooks Alternative | Gato invoice](https://www.reddit.com/r/AnalyticsAutomation/comments/1r9ht8a/a_quickbooks_alternative_gato_invoice)

*Powered by* [AICA](https://aica.to) & [GATO](https://gato.to)

Need help with your next project? [a software engineering consultancy in Austin](https://dev3lop.com) can bring it to life.


The Night Our Analytics Automation Went Rogue: A Cautionary Tale (and a Fix-It Playbook)