Find out how to calculate daily Revenues in Shopify across several accounts using the most relevant conversion rates in Google Data Studio.

The problem: You have got several Shopify accounts in various countries (and their respective currencies) and would like to see your consolidated data (in particular, Revenues and AOVs) in your native currency.

Example:

Imagine you have got three Shopify accounts: The main one is in the UK, and recently you also opened one in the US and one in Canada. This month’s revenues for each of the accounts look as follows:

  • The UK account: 45,000 GBP
  • The US account: 28,550 USD
  • Canada account: 14,395 CAD

Your business is based in the UK, so ideally you want to know how much money you made in pounds (GBP). There are two ways of doing it:

  • Manual way: Go to Google and manually calculate the US and Canada amounts based on today’s currency exchange rates. This will give you a rough idea, but will not be 100% accurate, as we all know that currency exchanges fluctuate daily.
  • Automated way:  Build a dashboard which considers daily currency exchanges. For instance, every time a user in Canada places an order on a specific day, revenues in pounds are calculated based on the CADGBP exchange rate on that specific date.

The ingredients:

  • Supermetrics for Google Data Studio
  • Your Shopify accounts as data sources
  • Google Sheets as a data source
  • Google Data Studio

The solution:

Step 1. Connect your Shopify stores to Google Data Studio through Supermetrics.

Step 2. Open Google Sheets and in cell A1 type in the following formula:

=GOOGLEFINANCE(“CURRENCY:USDGBP”, “price”, DATE(2020,1,1), DATE(YEAR(E1),MONTH(E1),DAY(E1)), “DAILY”)

Change all the components of the formula to fit your needs, more on this – here (Google Support page).  This formula will be updated for you daily – exactly what you need for the purpose of calculating revenues using the precise currency exchange rate on a given date.

Applying currency conversion rates to revenues Shopify
Applying currency conversion rates to revenues Shopify

Step 3. If you have more than one Shopify account which needs to be converted in your native currency, open another sheet in the same workbook and repeat the above process for another currency. In our case the formula would look as follows:

=GOOGLEFINANCE(“CURRENCY:CADGBP”, “price”, DATE(2020,1,1), DATE(YEAR(E1),MONTH(E1),DAY(E1)), “DAILY”)

Step 4. Connect your sheet / sheets to Google Data Studio. Ensure that you are using the “Date” format for your Dates (see below).

Date format in Google Data Studio


Step 5. On the main panel in Google Data Studio press “Resource” -> “Manage blended data” -> “Add a data view”. We are now going to blend our Shopify data sources and our Google Sheet(s) storing daily currency exchange rates to allow calculations within the dashboard.

Step 6. The final output of the blending process should look as follows (let’s name this blended data source “Blended Shopify”). Our join key is Date, no dimension selected, and the following metrics for Shopify data sources are of interest:
–          Orders
–          Total sales
–          Discounts
–          Returns

In this example we have got a filter applied to each of the Shopify datasets. This step is very specific to the client’s case (we needed to remove some Influencer activity from our calculations), so won’t be explained in this article. Apply whatever filters are necessary for your specific case though (or you might not need any filters at all).

Be sure to rename each metric, so that you can identify it when making calculations moving forward, e.g.:
–          Add “UK” in the naming of each metric under the UK Shopify account
–          Add “US” in the naming of each metric under the US Shopify account
–          Add “Canada” in the naming of each metric under the Canada Shopify account
–          Rename currency exchange metrics too (in our case we added “CADGBP” and “USDGBP” to the naming)

One more thing worth noting here is that at the time of writing this post Google Data Studio only allows left outer join, which essentially means that whatever Join key’s values you have for the very left dataset (in our case, Shopify UK), will be applied to the whole blending scenario. Be sure that your left dataset does not miss any Dates. Otherwise, you might be excluding some vital data from your calculations. To be on a safe side, you can always put one of your Daily currency exchange Sheets at the very left, as those data sources for sure will have each day of the required period.

Step 7. Let us calculate Total Sales (Revenues) across the three Shopify accounts. On the main panel of your dashboard select “Insert” -> “Scorecard”. Ensure that “Blended Shopify” is selected as the data source and then create a calculated metric with the following formula:
Close CADGBP*(case when Total sales Canada is NULL then 0 else Total sales Canada END)
+
Close USDGBP*(case when Total sales US is NULL then 0 else Total sales US end)
+
(case when Total sales UK is NULL then 0 else Total sales UK end)
Make necessary adjustments (call this calculated metric “Total Revenue”, Aggregation “SUM”, Type “Currency (GBP – British Pound Sterling (£))”).

Voila! Enjoy your correctly calculated revenue in GBP!

Step 8. Use the same principle to calculate your Discounts, Returns and even AOV (the AOV formula provided for your convenience):

SUM(Close CADGBP*(case when Total sales Canada is NULL then 0 else Total sales Canada END)
+
Close USDGBP*(case when Total sales US is NULL then 0 else Total sales US end)
+
(case when Total sales UK is NULL then 0 else Total sales UK end))
/
SUM((case when Orders Canada is NULL then 0 else Orders Canada END)
+
(case when Orders UK is NULL then 0 else Orders UK end)
+
(case when Orders US is NULL then 0 else Orders US end))Date format in Google Data Studio
Blending currencies and calculating Shopify revenue in Data Studio
Blending currencies and calculating Shopify revenue in Data Studio

It is worth noting that the more data processing / manipulations are done in Data Studio, the slower your dashboard will be. Ideally you want to avoid having multiple Scorecards or any other elements with such heavy calculations behind them. If you foresee that you will need a lot of calculations like that (maybe you would want to calculate Revenues per country in your local currency), then consider using Google Sheets or Big Query to blend your data and perform calculations outside of Google Data Studio.