Yesterday's ad spend kept landing on today. Two bugs were wearing one costume.
A dashboard that's reliably off by a day isn't rounding. It's a timezone day-boundary problem — plus a self-heal that invented an estimate and showed it as a booked fact.
The dashboard I built for a multi-market e-commerce group had a small, nagging bug. Yesterday's ad spend kept showing up on today. Not wrong by much. Just wrong enough that the client's headquarters and my dashboard never quite agreed, and "never quite agreeing" is how a reporting tool loses trust.
I'd have forgiven myself for filing it under rounding. I didn't, because a number that's reliably off by a day isn't a rounding error. It's a structural one. And when I went looking, I found two separate problems wearing the same costume.
Everyone agrees on the day. They just don't agree on when it starts
The reporting basis was never in dispute. Sales are booked on order date, in Central European time. That's how the client's finance team closes the books, so that's the truth the dashboard has to match.
The trouble is that "a day" is a local idea, and most data sources don't think locally. They think in UTC. A storefront order placed at half past midnight in Berlin is still the previous day to a system that slices the timestamp in UTC, because in UTC it hasn't ticked over yet. Do that across a marketplace feed, four regional storefronts, and a couple of ad platforms — each with its own idea of where the day boundary sits — and the late-night orders scatter across two dates. This is one of the oldest traps in analytics, and the standard advice exists for a reason: store everything in UTC and convert only at the edges, for presentation. The bug is what happens when one source converts and another doesn't.
Google's own analytics documentation spells out the same failure mode: an event timestamped at 22:00 UTC lands on the next calendar day once you view it in a UTC+2 property. Multiply that by every source that defines a day differently and you get a dashboard that's internally inconsistent without a single value being "wrong."
So the fix there was unglamorous and exactly right: pick one timezone — the client's — and force every source to bucket its days into it. One source of truth for where midnight is. I've been here before, in a different costume, when I merged four sales channels into one number and then had to prove it was right. Reconciliation work is 10% maths and 90% making sure everyone's counting the same way.
The worse bug was the number that wasn't real at all
Here's the one that actually bothered me. The reason yesterday's cost looked too low and today showed a suspiciously large cost wasn't only the timezone slip. The pipeline had a "self-heal" feature. When a day was missing its spend figure, it filled the gap with a ten-day average so the chart never had a hole in it.
That sounds helpful. It is the opposite of helpful. Because the current day always has no real figure yet — it hasn't happened — the self-heal was inventing an estimate for "today" and rendering it on the dashboard as if it were booked, actual spend. A made-up number, formatted identically to a real one, sitting in the same column.
A blank cell tells you the truth: we don't know yet. An estimate dressed as a fact tells you a lie in a confident font.
This is the more dangerous failure, and it's a cousin of something I keep running into. I've written about a number that was wrong in the most convincing way, and about how a dashboard can lie just by leaving things out. This was both at once: it filled a gap it should have left open, and it disguised the fill. The honest design is to either show the latest complete day, or to clearly flag a figure as estimated so no one builds a decision on it. Never let a gap-filler impersonate a measurement.
How I actually pinned it down
I didn't eyeball this. I ran a multi-agent audit — separate passes over each data source, then a synthesis, then a deliberately adversarial check whose only job was to try to break the conclusion. That last step earned its place: it flagged that one of the fixes I was confident about appeared to not exist in the code on disk. It was half right. The version on my local copy was stale; the live pipeline already had the behaviour. Both facts mattered, and only the adversarial pass surfaced the contradiction.
That's the texture of this work that doesn't make it into the highlight reel. The win wasn't the audit running fast. It was knowing that "the dashboard is off by a bit" deserved a real investigation, knowing which sources lie about time and which don't, and recognising a fabricated estimate for what it was instead of trusting the chart. None of that is the tool. It's the same point I keep landing on — the judgement is the leverage, the automation just lets you apply it across six data sources before lunch.
If your dashboard and your finance team are forever off by a little, don't reach for the rounding excuse. Find out where each source thinks the day starts. And check that none of your charts are quietly inventing the days that haven't happened yet.
Sources & further reading
External
Tinybird — Best practices for timestamps and time zones in databases · IIH Nordic — GA4: bridging UTC and local timezones · Webeyez — Reporting time zone, day boundaries and DST
Related posts
I merged four sales channels into one number. Then I proved it was right. · Your analytics dashboard is lying to you by leaving things out · The AI handed me a number that was wrong in the most convincing way