Expanding to international markets provides opportunities and challenges for e-commerce companies. One challenge is to report the return on advertising and revenue in each country and overall. Adding more currencies to the mix complicates the effort.
Google Analytics can combine multiple currencies into one. Google explains in one Analytics Help Post how to include the currency field in the ecommerce tracking code to convert currencies to the default currency.
Google Analytics currency conversion is simple. However, setting the conversion date to the same date as the transaction may not be an option, without forcing traders to convert currencies manually. Google Data Studios' ability to mix data sources can solve this manual conversion barrier.
Adding more currencies to the mix complicates the effort.
In a post last year, I introduced a mix of data sources in Data Studio. This example mixed SKU costs on a Google Sheet with SKU sales in Google Analytics.
For this example of currency conversion I have created another Google sheet showing the US dollar conversion rates for prominent world currencies. We can then use the conversion data in Data Studio to convert from US dollars to other currencies and vice versa. I guess we have advertised on Facebook and we are converting the ad from dollars to euros.
The first step is to add the data sources to Data Studio. In this example, the sources are currency conversion Google sheets and data from Facebook's ads.
In the screenshot below, I mix data from Facebook ads and my Google Sheet to convert from dollars to euros. I have used "Date" as "Go with key" for both data sources. The metric I have chosen from the Facebook data is the ad "Spend". The metric from Google Sheet is "Euro" because our goal is to convert Facebook dollars into euros.
After saving this mix, I have included the date of the ad spend and renamed it to three other metrics.
The three explanations are:
- "Spend a Dollar." I renamed the method "Spend" to "Spend in Dollars" to clarify the currency, which also inject the dollar sign. We can rename statistics in Data Studio by clicking to the left of the name.
- “Euro / Dollar.” I renamed the “Euro” field to “Euro / Dollar” to be more descriptive. This field is the conversion factor between dollars and euros. It can be omitted from the report, but I included it for more details.
- "Spend in euros." I created this new calculated field with the following formula:
Spend in euros = sum (spend) * average (Euro)
The formula multiplies Spendan times the conversion factor in euros. I post "sum" because Data Studio requires it. I put euro into "avg" if I include a summary line from my table. You can also enter "sum" to produce this formula:
Spend in euros = sum (spend) * sum (euro)
However, keep in mind that if you use a table and include a summary row, the summary value will be off for this column.
If I had used "sum (Euro)" the individual lines would calculate correctly, but the summary line would be off.
Also change the field from a number to currency – euro in this case.
With the new currency conversion process, we can collect all data for sales and advertising expenses in a local currency for performance analysis.