Creating Combined Reports for Multiple Shopify Stores Using Google Data Studio

Today I’m going to be looking at how we can use Supermetrics (a very simple, marketing-focused integration solution) to create a report for a client that has multiple Shopify Plus stores and has a need to create a combined dashboard to use internally.

One of Shopify’s biggest known limitations is their lack of a multi-store architecture (instead using completely independent stores), presenting challenges around a number of key areas, including reporting. Although a lot of our clients would use BI tools or have their own dashboards built out of data warehouses or other business systems, there’s often a need from some clients to get a simple, digestible combined view of reports quickly and easily - which is where this solution comes in.

Although we’re focusing on sales data primarily here, in the actual report we’ve created we’ve actually used much broader metrics and dimensions, including data from Google Analytics and various reports around product performance. You can do a lot with Google Data Studio to help simplify reporting and reduce manual efforts - if you have any questions, feel free to just reach out to us.

Working with two separate stores with independent currencies does present some challenges around reporting when it comes to relaying accurate sales revenue when converted to the localised currency. To create the currency conversion we are going to be using Google Sheets with a few formulas. If we are working with Euros and USD we want the USD to EUR conversion rate.

We can use a super helpful formula in Google Sheets called Google Finance and we then want to get the USD to euro conversion. In the example below I’ve added in a cell for today and I am then getting data from the start of 2021 up until today so that in Google Data Studio I've got the most recent conversion rate as well as historic conversion rate.

Now I’ve got my conversion rate in a Google Sheet. I'm going to head into Google Data studio and set up my data sources. First I want to add in my conversion rate sheet.

Once we’ve added this in we need to into the data source and change the name of “close” to “USD vs EUR”.

This will help us in future when we are blending data sources.

When it comes to Shopify and Supermetrics, the client we’re working on in this instance has one store in EMEA and one in the USA. I used the Supermetrics integration for Shopify to add in three different data sets, one with both stores data and then each stores data individually.

The reason I’ve done it this way is to allow me to use a drop down box on the main page of the report to give the client the ability to switch between overall store data and individual store data.

The individual store integrations are used to create the total revenue as well as summary pages on the dashboard for each store.

To create a scorecard with an accurate revenue reading based in USD, create a blended data source that contains; revenue from the EU store, total sales from the EU store and the join key is the date. Make sure to rename the metrics so when creating the formula for the blended data you are able to tell which order value is meant to be for Euros.

The formula will look similar to this. (sum(Sales In Euros) * (1 / avg(Euro)))+sum(Gross sales) and that's the start of our Shopify Dashboard using multiple stores and currency conversion.