Currency Conversion In SQL and Python - Issue 53

How to approach conversion rates for multiple currencies in SQL for revenue reporting

A common challenge for data analysts is to deal with multiple currency conversions for revenue reporting for internationally available products. In this article, I will describe 2 ways of how to generate an exchange rate table in SQL that will allow you to convert different local currency values into one target currency for your analysis. 

There are multiple ways to solve this challenge, ranging from easy workarounds for one-time ad-hoc requests or writing scripts that would allow automating revenue reporting. Depending on your applications, you could simply leverage the window function, utilize the built-in currency converter, or request support from your engineering team to run this conversion on a backend. If none of these are an option for you, below I’ll demonstrate an easy and quick workaround as a way to get it done on your own using only SQL. 

Problem

Let’s say you have all purchases loaded in a table with user id, user country, and main purchase details like plan id, plan name, amount, currency, and transaction date. You have to use this table to calculate MRR per country per period or get total revenue made internationally:

If your desired currency is dollars, the US revenue is easy to report. In your SELECT statement, you would just do sumSUMamount_paid). But what about International? 

You can’t simply pick the expected amount per plan and report revenue based on it, because the plan amount is different from the amount paid. The amount paid is actual money received from users, including discounts, promo codes, refunds, etc. That’s why you have to convert every paid amount into dollars or the baseline reporting currency at your company. If only 5 or 10 countries are supported, the conversion rate can be calculated manually in SQL. However, if you deal with many countries, then you’d better pick another approach.  

Solution

The obvious solution to this problem is to create a helper table with exchange rates per currency. Below I offer you 2 solutions on how to generate this table: 

1. We leverage public rates of exchange data and load them into a view or a table with a primary key on currency code, and then simply perform a join to map the currency we have to the appropriate conversion rate to calculate a dollar amount for every transaction. 

This solution will work for every database and SQL variation. It’s simple, easy, and fast. The downside is that it’s a static table that won’t work as a long-term solution, because the currency rates fluctuate. When you report revenue data, you obviously should be as precise as you can. But for ad-hoc or one-time data requests, the above is the best way to proceed. 

2. We use a Python script to access public exchange rates data via API, and then transform JSON files into a SQL INSERT statement and load it into a generated table.

This is a more complex approach similar to ETL. It gives you refreshed, current exchange rates and keeps the table updated for any long-term or automated reporting. 

There are multiple ways to achieve this. My workable approach was to leverage sqlalchemy and SQLite, and the destination table can be loaded right into Snowflake, Postgres, or any database.

Check Python code here.

Thanks for reading, everyone. Until next Wednesday!