One currency, one timezone (normalise multi-account reporting first)

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. two corruptors, one cure

Normalise once, read everywhere

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.

Problem 1: mixed currencies

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:

Problem 2: mixed day boundaries

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

The reporting mart

Frequently Asked Questions

Should revenue be converted at the order-date rate or today's rate?

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.