How to normalize ad spend across currencies in Google Sheets

What goes wrong without it

Three mistakes show up in multi-currency reports, and all of them produce numbers that look fine.

• Adding raw amounts. 800 EUR plus 1,200 USD is reported as 2,000. Every ratio built on that total is off.
• One rate for the whole period. Converting a quarter at today’s rate moves past months every time the report is opened. Last month’s cost per lead changes without anyone touching the campaigns.
• A different rate in each report. The agency converts at the monthly average, finance at the invoice-date rate, the dashboard at today’s. Three reports, three totals.

The fix for all three is the same rule, written once: each day’s spend is converted at that day’s rate, from one named source.

What you need

Two tables and one decision.

1. Ad spend by day, platform and currency. The currency has to be a column. If a platform’s export does not include it, add it from the account settings.
2. Daily exchange rates for each currency you spend in, covering the same dates.
3. A reporting currency. Usually the one your revenue is reported in.

Method 1: convert inside Google Sheets

For one account and a few months of data, a formula is enough.

Put spend on one tab with columns for date, platform, currency and spend. On a second tab build a rates table with one row per date and currency. The GOOGLEFINANCE function returns a historical rate:

=INDEX(
  GOOGLEFINANCE(
    "CURRENCY:EURUSD",
    DATE(2026,9,1)),
  2, 2)

GOOGLEFINANCE returns a small table with a header row, so INDEX picks the rate out of it.

Then look the rate up for each spend row and multiply. With a helper key of date and currency in both tabs:

=D2 * XLOOKUP(
  A2 & C2,
  Rates!D:D,
  Rates!C:C)

Column D is spend in the original currency, and the result is spend in dollars. Details of the function are in GOOGLEFINANCE for currency conversion.

Where the formula way stops working

The formula solves the cell. It does not solve the source.

• The spend still has to arrive. Somebody exports a CSV from each platform and pastes it in, and the lookups have to be dragged down again.
• Rates can be missing. GOOGLEFINANCE can return no value for a date, and the lookup then fails for that row.
• Nothing is fixed in place. The rule is a formula anyone can overwrite, and the sheet keeps no record of which rate a past figure used.
• Every copy diverges. The moment the file is duplicated for a client or a colleague, there are two conversion rules.

When the report is something you defend in front of a client or a finance team, those four are the difference between a number and an argument.

Method 2: convert in the warehouse, read in Sheets

The durable version moves the join out of the spreadsheet. Spend and rates are loaded into a data warehouse, converted there by one query, and the sheet receives a finished spend_usd column.

There is no direct pipe from an ad platform into a sheet in this setup, and that is deliberate. The sheet is where people read the data. The warehouse is where it is kept and joined.

Step 1: load ad spend into the warehouse

In OWOX Data Marts, each ad platform is loaded by a Data Mart of the Connector type into tables in your own warehouse: Google BigQuery, Snowflake, AWS Redshift, AWS Athena or Databricks. A first run backfills history and a trigger keeps it current. On OWOX Cloud the platform is connected by signing in. The self-managed open-source edition uses the platform’s own credentials.

Step 2: load daily exchange rates

Exchange rates are a data source like any other. OWOX Data Marts has connectors for Open Exchange Rates and Bank of Canada rates. Load them on the same daily schedule into the same warehouse.

Step 3: write the conversion once

This is the analyst’s part, and it is one join. The query below uses inline sample rows so it runs as written in Google BigQuery. In practice the two WITH blocks are your spend and rates tables.

WITH ad_spend AS (
  SELECT DATE '2026-09-01' AS date,
    'Google Ads' AS platform,
    'USD' AS currency,
    1200.00 AS spend
  UNION ALL
  SELECT DATE '2026-09-01',
    'Facebook Ads', 'EUR', 800.00
  UNION ALL
  SELECT DATE '2026-09-01',
    'TikTok Ads', 'GBP', 450.00
  UNION ALL
  SELECT DATE '2026-09-02',
    'Facebook Ads', 'EUR', 800.00
),
fx_rates AS (
  SELECT DATE '2026-09-01' AS date,
    'USD' AS currency,
    1.0 AS per_usd
  UNION ALL
  SELECT DATE '2026-09-01',
    'EUR', 0.92
  UNION ALL
  SELECT DATE '2026-09-01',
    'GBP', 0.79
  UNION ALL
  SELECT DATE '2026-09-02',
    'USD', 1.0
  UNION ALL
  SELECT DATE '2026-09-02',
    'EUR', 0.91
)
SELECT
  s.date,
  s.platform,
  s.currency,
  s.spend,
  ROUND(s.spend / r.per_usd, 2)
    AS spend_usd
FROM ad_spend AS s
LEFT JOIN fx_rates AS r
  ON r.date = s.date
  AND r.currency = s.currency
ORDER BY s.date, s.platform

The rates here are illustrative, not market data. The result:

The same 800 euros is worth a different dollar amount on each day, which is the point of using a daily rate.

Two details in the query matter:

• LEFT JOIN, not JOIN. If a rate is missing, the spend row stays in the result with an empty spend_usd. An inner join would drop the row and the total would shrink without a trace.
• The direction of the rate. Here the rate is units of the currency per one dollar, so spend is divided. If your source gives dollars per unit, multiply. Write which it is in the field’s description.

Step 4: publish it as a Data Mart

Save the query as a Data Mart, give it a description that states the rule (“each day’s spend at that day’s rate, source: Open Exchange Rates”) and describe the spend_usd field. OWOX Data Marts does not do the conversion for you. It publishes and governs the query the analyst wrote, so every report reads the same definition.

Step 5: read it in Google Sheets

Connect Google Sheets as a destination and create a report on the Data Mart, or pull the Data Mart into a sheet with the OWOX Data Marts extension. A trigger refreshes it on a schedule. The sheet now holds a column that is already in one currency, and no lookup formula.

Which method to use

Start in the sheet if you are checking one month for one account. Move when the same report is refreshed every week, when more than one person reads it, or when someone will compare it with another system’s total.

Checks before you share the number

Four quick tests catch most conversion errors.

1. Count the empty conversions. Any row with spend and no converted value is a missing rate.
2. Reconcile one currency by hand. Pick a day, convert it with the published rate and compare.
3. Compare with the platform. Total spend in the original currency must match the ad platform’s own report for the same dates.
4. Reopen it tomorrow. Last month’s total should not have moved.

Once spend is in one currency it can be set against revenue. For that step, see how to calculate ROI.

Data IntegrationWhat is ETL? The Process, Pipelines, ETL vs ELT and How to Choose a ToolOctober 1, 2026→Data IntegrationTop 20 ETL Tools for Marketing in 2026September 30, 2026→Data IntegrationHow to Connect Facebook Ads to Google Sheets with OWOX Data MartsSeptember 30, 2026→

See all articles →