Use Data Studio to convert reports into multiple currencies


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.

Data Studio

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.

Mix data from Facebook ads and Google Sheets in Google Data Studio to convert from dollars to euros.

Mix data from Facebook ads and Google Sheets in Google Data Studio to convert from dollars to euros. Click on the image to enlarge.

After saving this mix, I have included the date of the ad spend and renamed it to three other metrics.

Include the ad spend date (left) and rename three titles:

Include the date of the ad spend (left) and rename three metrics: "Spend in dollars", "Euro / dollars" and "Spend in euros." Click on the image to enlarge.

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.
Rename statistics by clicking left.

Rename statistics by clicking left.

Currency selection also injects the dollar sign.

Currency selection also injects the dollar sign.

  • “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.

Accurate reporting of summary line when using "avg (Euro)".

Careful reporting of the summary line when using "avg (Euro)."

If I had used "sum (Euro)" the individual lines would calculate correctly, but the summary line would be off.

Accurate reporting of the summary line when using

Careful reporting of the summary line when using "sum (Euro)."

Also change the field from a number to currency – euro in this case.

Change the field from number to currency - euro in this case.

Change the field from number to currency – euro in this case.

A currency

With the new currency conversion process, we can collect all data for sales and advertising expenses in a local currency for performance analysis.



Source link

Leave a Comment