Using the Currency Conversion Datasets
To help make it easier for you to convert your currency, we have created four specific datasets that bring in the up-to-date conversion rates for AUD, EUR, GBP, and USD.
- If you have a currency type column that specifies the currency being used in the report (e.g., USD, EUR, etc.), then bring in the dataset that shows the conversion rates for your desired currency.
- Next, add a Joined Report and add the report with your data as "Connection 1" and the dataset as "Connection 2." Make sure to select the column that has the type of currency (e.g., USD) as the common value for Connection 1 and "Currency Code" for the common value in Connection 2.
- After you've made the joined report, add a Calculated Column transform to multiply the currency value with the currency rate ("Currency Rate per ___") to get the converted value for the desired currency.
Getting Real-time Currency Conversion for Other Currencies
If you would like your currency converted into something other than AUD, EUR, GBP, or USD, here's a way to convert them in real time on your metrics.
In your metric, add a new report and select Custom Rest API for the data source.
In the Data Settings for the new report, enter the following:
URL: http://data.fixer.io/api/latest
Note: The default base currency in fixer.io is Euros (EUR). But you're in luck! You can modify the base currency to the currency by using the following Parameter:
- Key:
base
- Value:
USD
Check the Advanced options box, and make sure the Request method is set to GET. You will also need to check the Rotate table data checkbox.
If you press connect, this will give you the following response in your table:
Then, by using a substring, we can extract the date
line and column A to just the three-letter currency code.
In the builder, you can clean up the table of data by adding a SQL transform and enter the following SQL query:
SELECT SUBSTRING("success",7,10) as "currency", "true" AS "conversion_rate"
FROM report
WHERE "success" NOT IN ('timestamp', 'base', 'date')
And that should give you a chart similar to this:
This will allow Grow to match up the currency conversion in this report with the currencies in your original report.
To finish this out, use a Joined Report to join the two and display the converted currencies with your original data.
Getting Historical Data
What if you want to be able to see a currency conversion for the last month or the last year?
This does have a couple of limitations. First, you will not be able to make the date ranges dynamic. This means that you will have to set the dates to a specific range (i.e. June 1, 2017 to May 31, 2018).
Second, fixer.io limits the historical data to 12 months back from the end date. So if I set the end date to April 30 of this year, I could pull data up to May 1st of last year, regardless of today’s date.
To connect to historical rate data, add a new report. Select Custom REST API Connection as the data source.
Once you’ve done that, you’ll enter in the URL and Header sections:
- URL:
http://data.fixer.io/api/timeseries
- Key:
date
- Value:
YYYY-MM-DD
After you’ve entered the headers, you’ll enter the parameters. To add multiple parameters, click "Add Parameter."
- Key:
base
- Value:
AUD
- This value is the three-letter currency symbol that your data is in.
- Key:
symbols
- Value:
USD
- This value is the three-letter currency symbol that you are converting your data into.
- Key:
end_date
- Value:
2018-05-31
- The end date is the most recent date for which you want the currency conversion.
- Key:
start_date
- Value:
2017-06-01
- The start date is when you want to begin pulling the currency conversions back.
Once you have done all of this, click "Get Data" to bring in the data. This will return the exchange rate between those currencies, with each column representing one day within that date range.
To clean up the data, add a SQL transform and run this query:
SELECT SUBSTRING("success",7,10) as "date", "true" AS "conversion_rate"
FROM report
WHERE "success" NOT IN ('timeseries', 'start_date', 'end_date', 'base');
You can then join this report with another report that has the daily currency values you want to convert.
This example only uses the conversion rate from one currency to another. If you are pulling in historical data for multiple currencies, then you will want to adjust the SQL statement to create a currency
column as well:
SELECT SUBSTRING("success",7,10) as "date", SUBSTRING("success",18,3) as "currency", "true" AS "conversion_rate"
FROM report
WHERE "success" NOT IN ('timeseries', 'start_date', 'end_date', 'base');