Connector

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.

Open Exchange R…ConnectedHistorical excha…
Data MartPublishedone definition
Google BigQueryDateBaseCurre…Daily 09:00
Refreshed on a schedule. No exports, no copy-paste.

Get Started Free

Hand this to your AI

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

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

Endpoints

Open Exchange Rates Endpoints

Documentation

1 Open Exchange Rates endpoints you can import. Each lands in its own table in your data warehouse.

S00 · Why

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 data 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
Open Exchange…Daily rates
Ad platformsSpend in EUR, GBP, PLN
Shop ordersRevenue in local currency
Your data warehouseBigQueryAmountRate that d…In one curr…Joined by idOrder paid in EURReported in USDOne join on date and currency converts every amount.

S01 · Step 1

Step 1: Connect Open Exchange Rates and pick the fields

Create a Data Mart, choose Open Exchange Rates as the data 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
New Data MartChoose a sourceGoogle AdsOpen Exchange…Facebook AdsAuthorizeConnectedEndpointHistorical exchan…
Historical exchan…4 fieldsdatebasecurrencyrate

S02 · Step 2

Step 2: Load the rates into a BigQuery table

The dataset and table in your data 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
DestinationBigQueryDatasetopen_exchange_ratesTablehistoricalRun
Run HistoryPendingRunningSuccessIncremental load
Tableopen_exchange_rates.historical

S03 · Step 3

Step 3: Get a readable output schema

After the first run you get the output schema: every field with a plain description. Your data 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
open_exchange_rates.historicalOutput schemaFieldDescriptiondateDate of exchange ratebaseBase currencycurrencyTarget currencyrateExchange rate

S05 · Step 5

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
ScheduleRun on a scheduleDaily 09:00
BackfillRun backfill
Run HistoryDaily scheduleManual backfillDaily schedule

S06 · Step 6

Step 6: Reuse the same table everywhere

One table feeds your data model, spreadsheets, dashboards, AI 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
Open Exchan…Data MartPublished
Revenue in one curren…
Google Sheets
Data Studio dashboards
Claude, ChatGPT
Slack, email

Recent updates

Why OWOX

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.

Book a callGet Started Free

Trust

The Data Partner You Can Trust

users

166K Source: Internal OWOX data as of 2026-11-15

Executives and data analysts across 20+ industries

G2 rating

4.9★2

“A trusted data analytics partner” — verified review

npm installs

171K3

In Open Source We Trust

Customers