Handling currency conversion in a marketing warehouse

Store the amount as the platform issued it, store the currency code next to it, and never overwrite either. Convert in the reporting layer, using a rate keyed to the date of the transaction, not the date of the query.

That one sentence prevents almost every currency failure I have had to unpick. The rest of this article is why each clause is there, because teams that skip one of them usually skipped it for a reason that sounded sensible at the time.

The tell that a warehouse got this wrong is simple. Re-run last quarter's report today and compare it to the copy that was sent to the client in January. If the totals moved, the conversion is happening in the wrong place.

A Canadian company running a US market usually ends up with more than one ad account, and the accounts do not agree on currency. This is not sloppiness. Google Ads treats the account currency as immutable, a subset of the ISO 4217 codes, set when the account is created.

So the currency you have is the currency somebody picked years ago, possibly by accepting a default. It is not a preference you can revisit, and the only way to change it is a new account, which means losing the account history that makes automated bidding work.

Manager account rollups will show you a converted total, and that total is fine for a glance. It is not fine as a source for the warehouse, because you did not choose the rate, you cannot see the rate, and you cannot reproduce the figure six months later. A number you cannot reproduce is not evidence.

The same split shows up in the CRM, where deal values are often entered in the currency of the customer, and in call tracking, where cost is billed in the vendor's currency. By the time three sources land in one dataset you have three currencies and no policy.

The tempting shortcut is to convert during ingestion so every downstream table is already in one currency. It removes a join, it makes the raw tables readable, and it is the wrong call.

Converting on ingest destroys the source value. When somebody later asks what the account actually spent in USD, the answer is gone, and the only record of it sits in a platform whose retention window is shorter than your reporting history. You also bake in a rate decision that you cannot revise. Pick the wrong date convention and every historical row carries the mistake permanently.

It also makes reconciliation impossible. The finance team compares your figure to an invoice denominated in the source currency. If your raw table already holds a converted number, the two can never be matched line for line, and the conversation ends with somebody deciding the warehouse is unreliable.

Keep the converted amount as a derived column in the reporting layer, computed from the raw amount and the rate table. That is the same argument that puts every other metric definition in one place, which is the point of a semantic layer for marketing metrics rather than a pile of dashboard formulas.

Row one is the default that arrives by accident, because it is what a spreadsheet formula pulling a live rate does. It means a report reprinted in March disagrees with the copy sent in January, and the disagreement grows over time. Anyone who has had to explain that to a client learns this row once.

Row two is the right default for performance reporting. Spend on the fourth of March is converted at the rate for the fourth of March, forever. Cost per acquisition then compares across months honestly, because each month's cost was converted at the rates that applied when the money was spent.

Row five looks unsophisticated and is often the most useful of the five. If the annual budget was approved in one currency at one rate, measuring against it using a different rate measures currency movement rather than media performance. Two columns, actual at transaction rate and actual at budget rate, separate the two questions cleanly.

The mistake is not picking the wrong row. It is having no row, and different dashboards silently landing on different ones.

You need one row per currency pair per calendar date, with no gaps. Not per business day. Per calendar date.

Published rates have gaps by design. The Bank of Canada publishes daily average exchange rates once each business day by 16:30 Eastern, expressed as one unit of the foreign currency converted into Canadian dollars. Weekends and bank holidays return no rate, and the data is offered as CSV, JSON and XML for automated pulls.

Ad spend does not observe bank holidays. So a naive join drops every Saturday and Sunday from the converted total, and nobody notices until a month total is roughly two sevenths light. Build the rate table against a full date spine and carry the last published rate forward into the gaps, with a flag column recording that the row was filled rather than published.

The direction of the quote matters as much as the value. Write down which way the multiplication goes and put it in the column description, because a rate expressed as foreign into Canadian dollars and a rate expressed the other way are both plausible and one of them is wrong by a factor of roughly two. Naming the column usd_to_cad rather than fx_rate is the cheapest control available here, and it is the same discipline as putting the grain in the table name.

Treat the published figures for what they are. The Bank of Canada states its rates are indicative, drawn from averages of aggregated price quotes. If the client needs the exact rate their bank charged, that comes from the bank statement, not from a central bank feed, and it belongs in a separate column rather than replacing the reference rate.

Money multiplied by a rate in a floating point column will eventually produce a total that is off by a cent, and the first person to notice will be the one you least want to explain it to.

BigQuery's NUMERIC type carries a precision of 38 digits and a scale of 9, represents decimal fractions exactly, and is documented as suitable for financial calculations. Store amounts and rates in it. FLOAT64 is for ratios and model outputs, not for currency.

Round once, at the end, at the presentation layer. Rounding each row before summing produces a total that does not match the sum of the unrounded rows, and reconciling that difference is a bad afternoon.

Keep both columns in the reporting table: cost in the source currency and cost converted. Dropping the source column to keep the table tidy is how the next person loses the ability to check your arithmetic.

Convert the numerator, never the ratio. Cost per acquisition is cost divided by conversions, so convert the cost and then divide. Converting an already computed cost per acquisition works for a single account and silently breaks the moment you aggregate two accounts with different conversion counts.

The same rule kills blended return on ad spend across currencies. Two accounts with different currencies cannot have their ROAS averaged, because the weighting is by revenue, and the revenue is in different units until it is converted. Sum the converted revenue, sum the converted cost, then divide.

Percentages, rates and scores never get converted at all. It sounds obvious written down, and I have still found a dashboard multiplying a conversion rate by an exchange rate because the formula was copied down a column.

Impression and click counts are also currency free, which matters because a report that converts every numeric column is easier to write than one that converts the right three.

Currency belongs in the modelling phase, not in a later fix. Adding a currency dimension to a warehouse that has been converting on ingest for two years means reloading history from platforms whose retention may have already expired, which is the expensive version of this problem.

If you are deciding whether to build the warehouse at all, currency is one more entry on the list of reasons the answer might be yes, and the honest threshold for that decision is set out in warehouse or dashboard. A single account in a single currency does not need any of this.

If you are building, put the rate table in the same repository as the transformations and load it on the same schedule, so a missing rate fails the run rather than publishing a converted total that quietly excludes yesterday. That gate is what makes a monthly reporting cycle safe to automate.

The general shape of the work, a dated dimension joined to facts and a definition that lives in one place, is the same shape as most of the modelling in a marketing data warehouse build. Multi currency is not a special case so much as a case where getting the model wrong is visible to the finance team. A consolidation of 154 data sources across a public sector reporting estate had the same property: the modelling decisions that looked academic during design were the ones that decided whether the numbers survived scrutiny afterwards.