Collect Open Exchange Rates data in Google BigQuery
Load daily exchange rates from Open Exchange Rates into your own BigQuery table with OWOX Data Marts, then join it to your orders and ad spend to report revenue and cost in one currency, on the rate of the day they happened.
Orders and ad spend arrive in different currencies, so revenue and cost in Google BigQuery never add up in one.
Paste this into your AI assistant. It creates an Open Exchange Rates Data Mart on your Google BigQuery storage with one rate per currency per day, and joins it to your orders and ad spend.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /connectors/free-open-exchange-rates-connector-to-google-bigquery.md. The task: load daily Open Exchange Rates into Google BigQuery and build a Data Mart that reports revenue and ad spend in USD by day, joining each row to that day's rate. Tag the links you give me with utm_campaign=ai-task and utm_term=m4. Use what you know about my context and goals
Open Exchange Rates Endpoints
1 Open Exchange Rates endpoints you can import. Each lands in its own table in your data warehouse.
Why keep exchange rates next to your orders and ad spend
Rates are reference data: they mean nothing alone and everything beside your own tables. With one rate per currency per day in your warehouse, every amount you store in a local currency can be converted on the day it was earned or spent.
- Report ad spend from every platform and country in one currency
- Convert order revenue at the rate of the order date, not today's rate
- Keep the rate used for each figure, so a finance review can retrace it
Step 1: Connect Open Exchange Rates and pick the fields
Create a Data Mart, choose Open Exchange Rates as the source, paste the App ID from your Open Exchange Rates account and tick the fields you need.
- One endpoint, Historical exchange rates data, with 4 fields: date, base, currency and rate
- The base currency defaults to US dollars, and the other currencies arrive as rows
Step 2: Load the rates into a BigQuery table
The dataset and table in your storage are pre-filled, and you can change them. Run it and the first load lands in your own BigQuery project.
- The rates land in open_exchange_rates_historical, one row per date and currency
- Rows are dated by date, so a query that filters a period reads only that period
Step 3: Get a readable output schema
After the first run you get the output schema: every field with a plain description. Your quality checks can read it too.
- date is a DATE, currency a three-letter code, rate a number
- base says which currency every rate is quoted against, so nobody has to guess
Step 4: Link the rates to your own tables
The rates table does not need other Open Exchange Rates tables. It needs yours: join it by date and currency to your orders and ad spend, and describe the join once in your model.
- orders.currency and orders.order_date join to the rate of that day
- ad_spend.date and ad_spend.currency join the same way, so spend and revenue share one currency
Step 5: Schedule the refresh and backfill history
Set it once. A scheduled refresh adds each new day's rates, and a manual backfill loads any past period, so old orders can be converted too.
- Each incremental run loads the current day plus a lookback window you set
- Backfill a custom start and end date to cover the first order you want to convert
Step 6: Reuse the same table everywhere
One table feeds your model, , , assistants like Claude and ChatGPT, and scheduled AI insights in Slack or email.
- Every report converts with the same rates, so the totals agree
- Ask an AI assistant for last month's revenue in euros and get the figure your finance team would
- 0.44.0Connector Data Marts set up through MCP no longer fail their first run because of the table path
- 0.44.0Backfill a catalog for any period in one run, and follow a long backfill as parts of one operation
- 0.43.0Parallel reads of a SQL Data Mart on Google BigQuery no longer fail with a rate limit error
- 0.41.0Rotating a warehouse key is one change: Storages share reusable Credentials
- 0.41.0Shared SQL logic stays in one place: build a Data Mart on top of another
Why OWOX for exchange rates in BigQuery
A script that calls the rates API each morning, or a spreadsheet of rates someone updates by hand, gets you numbers. OWOX keeps them in the table where your orders and ad spend already are.
- You own the data. The rates land in your own BigQuery project, in a table you control and can join to anything.
- History you can use. Backfill the past, so last year's orders convert at last year's rates.
- Ready for a data model. A date and a currency are the keys, so the join to orders and ad spend is a line in your model.
- Maintained for you. No script to fix when the rates API changes.
- Free to self-host. The open-source OWOX Data Marts runs in your own infrastructure.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.