One Currency, One Timezone: Normalising Multi-Account Reporting Before It Lies to You
The 60-second version
USD and INR spend in one column, UTC and account-timezone days in one join. The exchange-rate table, the convert-then-truncate rule, and the view every report should read.
- What happened, in one line
- What to do about it this week
- What you can safely ignore
Your US ad account spends dollars. Your India account spends rupees. Your Meta account reports in Pacific Time and your Google account in IST — while your warehouse stores everything in UTC. Sum that raw data into one report and you get a number that is precisely wrong: exchange-rate drift baked into trends, and day boundaries that move revenue between dates depending on who is asking.
Every multi-account, multi-currency reporting setup needs a normalisation layer: one currency, one timezone, applied the same way by every query. This post builds it — the exchange-rate table, the timezone conversion, and the SQL view everything else reads from.
The short version: normalise once, read everywhere
The Normalisation Layer
Data JourneyRaw platform data
Spend in account currency, timestamps in account timezone. Correct per-account, incomparable across accounts.
Normalisation view
A single view converts every row to reporting currency and reporting timezone. All reports read from here, never from raw.
Every report agrees
Blended CAC, MER, pacing and ad-hoc questions share one definition of 'spend on Tuesday in INR'. Disputes become data questions, not logic disputes.
A reporting timezone is the single timezone all reporting is expressed in — usually the business's home timezone. Reporting currency is the same idea for money. The layer itself is boring infrastructure: a rates table, a view, a convention. Its absence is the cause of a remarkable share of "the dashboard is wrong" tickets.
Normalise at query time, not at load time. Storing converted values in raw tables destroys the original and freezes an exchange rate into history. Keep raw spend and raw timestamps immutable; convert in a view so the rate methodology can improve without reloading anything.
Problem 1: currency — the trend that was just the rupee
Summing USD and INR spend in one column is obviously wrong, and everyone does a version of it anyway — usually by converting once in a spreadsheet with "the current rate" applied to all of history. That rewrites the past every time the rate moves: last quarter's CAC changes because this week's rupee did.
The rates table
You need one row per currency per day. Sources in ascending order of rigour: your payment processor's settlement rates (best — it is what you actually paid), a daily ECB/RBI reference feed, or a manual monthly average documented as such. What matters is not the source's perfection but that it is daily, versioned, and shared:
Show query
Document the rate source on the report, not in a wiki. A footnote — "Spend converted at daily settlement rates" vs "at monthly average rates" — changes how finance reads every number on the page. Monthly-average conversion can shift a volatile month's CAC by 3–5% versus daily rates; that is larger than some optimisations you will debate all quarter.
Problem 2: timezones — the Tuesday that was two days
Ad accounts report in their own timezone; warehouses store UTC; your business thinks in IST. An order at 11:30 PM IST on Tuesday is Wednesday in UTC. A Meta account on Pacific Time ends its "Tuesday" 13.5 hours after IST does. Compare daily spend (account timezone) against daily revenue (UTC date) and every single day is misaligned by the overlap gap.
The 9 PM order that moved days
The rule: convert, then truncate — never the reverse
-- WRONG: truncates in UTC, then labels it a business day.
DATE(order_created_at) -- a UTC day, not your Tuesday
-- RIGHT: shift to reporting timezone, then take the date.
DATE(order_created_at, 'Asia/Kolkata') -- your Tuesday
Apply the same to spend timestamps from each account's native timezone into the reporting timezone, and daily joins finally compare like with like. Keep the account timezone per account in a tiny dimension table — accounts do get recreated in different timezones, and hardcoding America/Los_Angeles in forty queries is how the bug returns.
Putting it together: the reporting mart
Three Layers, Each Owned Once
Reporting HierarchyRaw (immutable)
Exactly what the APIs delivered: native currency, native timestamps, UTC warehouse times. Append-only; never edited.
Normalised view
Currency and timezone conversion in one reviewed place. The only logic downstream is allowed to assume.
Reports (thin)
MER, CAC, pacing — all plain aggregations over the views. A report bug is now a filter bug, never a conversion bug.
Building the Layer in an Afternoon
Process FlowDeclare reporting currency and timezone
INR and Asia/Kolkata for most readers here. Write it down; it is a business decision, not a technical default.
Backfill the FX table
Daily rows per currency from settlement data or a reference feed. One table, shared by every query from now on.
Build the two normalisation views
Spend and orders, converted but otherwise untouched. Review the SQL once, carefully — everything downstream inherits it.
Point reports at the views and footnote the method
Migrate one report, reconcile against the old logic for two weeks, then migrate the rest.
Frequently Asked Questions
Should revenue be converted at the order-date rate or today's rate?
Order-date rate for performance reporting (what was the economics when the decision was made), current rate only for cash-position snapshots (what is it worth now). Mixing the two in one report is the most common FX bug after summing raw currencies.
What about crypto-like intraday FX swings?
Daily granularity is enough for marketing reporting — your attribution windows are days wide and your spend decisions are daily. Sub-daily FX precision matters for treasury, not for CAC. Do not build hourly FX infrastructure for a dashboard read once a morning.
How does this interact with data freshness?
Independently — and that is the point. Freshness tells you whether Tuesday's data has arrived; normalisation tells you what Tuesday means. Debug them separately: first confirm both sides are complete for the window, then confirm both sides cut the window identically.
Summary & Next Steps
Multi-account reporting has exactly two silent corruptors — mixed currencies and mixed day boundaries — and one cure: a declared reporting currency and timezone, a daily FX table, and views that every report shares.
- Use reporting timezone conversion before any date truncation.
- Use schema mapping discipline so every table documents its native currency and timezone.
- Use freshness checks to separate "late data" from "wrong day" when numbers look off.
Blended CAC Calculator
Total spend across every channel, divided by total new customers — the acquisition cost number that reconciles with what you actually spent.
Chinmay Raibagkar
About author →Founder of DataLens AI. He helps non-technical teams read their ad and database numbers with confidence — which number to trust, what to do next, and what to ignore.