What it was
One of the reports said somebody had bought a $40,000 battle pass. The pass had never cost anything close to that, a currency conversion had gone wrong. There were other discrepancies too, spread across the game's attribution, transaction and user data. I reconciled the sources and rebuilt the reporting in BigQuery, keeping the raw events, cleaned transactions and reporting views separate. There was also a 70 TB backlog to correct. We delivered it in two weeks, with daily demos as we worked through the data.
Build
- GA4 events from Firebase landed in BigQuery. I compared them with the mobile attribution, user and transaction data to find where the figures diverged.
- Exchange rates were ingested daily. Each purchase used the rate for the day it happened, rather than the rate on the day somebody ran a report.
- The BigQuery model used Bronze for filtered raw events, Silver for cleaned transactions in one currency and Gold views for reporting in Looker.
- I corrected bad revenue events and handled late-arriving data so there was a predictable way for the daily figures to settle.
- Checks and observability went into each layer, so we could investigate a discrepancy before it reached a dashboard.
- The historical backfill covered 70 TB. It needed care with the query shape to keep the BigQuery cost down.
Key decisions
Where currency conversion happens
I converted purchases into one currency in Silver. That let the reporting views use the cleaned values without each report doing its own conversion.
Keeping the raw events
We needed to be able to show what a correction had changed. Keeping the raw layer gave us something to compare the cleaned transactions against.
Handover
The client's team would be maintaining this after I left. I documented the model and the corrections as we went, alongside the daily demos.
Lesson learned
People wanted to see how we'd arrived at the numbers.the rest of it ↓fold it back up ↑
By this point the teams had seen the numbers disagree often enough that they were hesitant to use them. Fixing a conversion didn't answer all their questions about the rest of the report. I spent about as long making the corrections traceable as I did making them, so somebody could follow a figure back through the pipeline and see how we'd arrived at it.