How to Visualize Shopify Data in Data Studio?
Shopify is a fantastic platform for selling your products and fulfilling each upcoming order. However, many store owners may not find the analytics features sufficient for their needs. Instead, they choose to connect Shopify to Data Studio and visualize their store’s performance there.
With this tutorial, you’ll learn how to connect both services and how to build a Shopify Data Studio report with ease.
Why connect Shopify to Google Data Studio?
Google Data Studio is a popular platform used for visualization purposes. It connects to virtually any data and turns it into charts, graphs, tables, and other forms.
With those, you can build beautiful dashboards, connect them to multiple data sources, and refresh the data as new information flows in.
Data Studio is a perfect companion to Shopify. On your custom Shopify analytics dashboard, you can visualize your sales, monitor the fulfillment time of your orders, draw maps showcasing where purchases come from, and a lot more. The sky’s the limit.
Data Studio is also an excellent alternative to built-in Shopify reports, the availability of which depends on your subscription plan.
Those of you on Shopify and higher plans have (nearly) all reports available. On the other hand, those on Basic Shopify or Shopify Lite plans don’t have access to many reports, for example, reports on orders, sales, or customers.
Luckily, everyone can freely export their Shopify data and use it with services such as Data Studio, and we’ll explain in this article how to do it correctly.
How to add Shopify data to Google Data Studio
To get started, head over to https://datastudio.google.com/. You’ll need a Google account to access it, but chances are you already own a Gmail address or its business alternative. If somehow you don’t have one, create a free Gmail account right away.
From the home screen, let’s start by creating an empty report. You can also pick one of the available templates, but it will be easier to demonstrate things step by step if we use a blank dashboard.
To get one, click on the Blank Report box or hit the Report button from the Create dropdown menu.
To make Shopify and Data Studio work together, you need to connect them with a Shopify Data Studio connector. This will allow for the data to be moved into Data Studio automatically on a chosen schedule. As a result, you’ll always have fresh data at your disposal.
Data Studio features both the official Google Connectors built and maintained by Google in addition to hundreds of Partner Connectors. These are the 3rd party widgets that have been approved by Google but are offered by third parties.
Among them, there are currently nine widgets supporting data migration from Shopify.
Each comes with an individual paid subscription, and most (if not all) also offer a free trial.
The connection process is somewhat similar. You’ll need to:
- Authorize Data Studio to use a particular connector;
- Authorize Shopify to share your data with Google Data Studio.
We won’t go into details, as each connector has an individual setup and is also well-documented. Instead, we’ll describe an alternative method for connecting Shopify and Data Studio that involves exporting Shopify data into Google Sheets, and then pulling that data automatically into Data Studio with Google’s connector.
It may seem like a longer way, but it’s actually quite simple and takes just a few more minutes of your time. The advantage is that you get to see all your Shopify data in a spreadsheet, and will have an easier time building meaningful Data Studio charts.
We recommend this especially to those of you with little to no experience with Data Studio, or for moving Shopify data outside of the platform. If however, you decide to use one of the partner connectors and it suits your needs, you can jump directly to the Shopify dashboard template chapter.
Connecting Shopify with Data Studio via Google Sheets
Google Sheets are an excellent place for storing data from your Shopify store, or from any other source. Interactive spreadsheets are excellent for building automations, tracking important metrics and for team collaboration. They also integrate seamlessly with other Google products, including Data Studio.
The additional benefit of moving data to a spreadsheet is the ability to access the raw data at any time. It can serve as a backup for when Shopify is down. You can also use a spreadsheet to quickly look up any data you currently need – unfulfilled orders, customers from specific locations, stock levels, and so on.
Coupler.io has a Shopify to Google Sheets integration that allows it to automatically import data and refresh it according to a schedule. All of this, without any coding required.
We’ll use Coupler.io to pull Shopify data into a Google Sheets file. Then, we’ll set up a Google Sheets connector for Data Studio to pull this data into our Shopify sales dashboard.
Setting up a Shopify importer
To get started, create a Coupler.io account. On the home screen, click on the Add new importer button to set up a Shopify importer.
With Coupler.io, you can pull data from more than 30 sources. Obviously, ours in this case will be Shopify.
If you use any other tools from the list (e.g. Hubspot, Airtable, Pipedrive) however, it can be very valuable to create separate importers for each tool, and pull their data into the same spreadsheet. You could then combine all this information to enrich your Data Studio dashboard.
For now, though, select Shopify as a source and Google Sheets as a destination.
Click Connect to connect your Shopify shop to Coupler.io. You’ll need to enter your shop name in the format shop-name.myshopify.com and log in to your Shopify account if haven’t done before.
Choose Customers as a data entity. Of course, we’re also interested in other entities but we’ll create separate importers for them. Don’t worry, after the first one is set up, it will be as simple as duplicating the first importer and choosing a different entity.
Select Jump to destination settings. We’ve selected Google Sheets for this example but BigQuery would also be a viable choice as there’s a dedicated Google connector for Data Studio. Excel would work as a final destination too but since we’re going to move this data further, it’s better to stick with Google products.
However, if you plan to connect Shopify to Power BI, then choose Excel as a destination. It will act as an intermediary for your data exported from Shopify.
In the Destination account field, open the dropdown menu and click on +Add account. A pop-up will appear where you should log in with your Google account and permit Coupler.io to access it.
On your Google Drive, create a new spreadsheet. Then, go back to Coupler.io and find the new sheet via the Spreadsheet field. In the following field (Sheet), select from one of the existing sheets or type in the name – it will create a new sheet for your import.
Create a schedule for data refreshes that works best for you.
Finally, hit the Save and run button for your data to be imported. Once finished, click on the View results button to open a spreadsheet in a new tab. It might be a good idea to name the importer if you haven’t yet as we’ll create a few more in a moment.
If you see any errors, please verify if the API credentials are correct. Also, check if you gave the right permissions to your Shopify private app.
If you’d like to explore the topic in more depth, check out our dedicated Shopify to Google Sheets article.
Importing other Shopify entities
We’ve got the import of Customers sorted out. Now let’s fetch all the other available data from your store.
Click on Importers from the menu to the left. Then, from the three-dots menu to the right of your importer, select Copy to create a duplicate of this importer.
On the settings screens that will appear next, change just two things:
- In the Source section, change the Data entity to Orders with line items
- In the Destination section, keep the same spreadsheet as before but create a new sheet.
Hit the Save and run button and wait for the new data to be imported.
Repeat the last step for the third and last importer. Select Products as a Data Entity and create a new sheet once again.
Important: We’re currently experiencing an issue when pulling product data from Shopify that results in a duplicate product_id header. We’re investigating the possible solutions. In the meantime, please use the following workaround (for Product entity only!):
In the Source settings, scroll down to Advanced settings and click on Change.
Scroll down again to the Fields section and insert the following:
product_title, product_body_html, product_vendor, product_product_type, product_created_at, product_handle, product_updated_at, product_published_at, product_template_suffix, product_status, product_published_scope, product_tags, product_admin_graphql_api_id, id, title, price, sku, position, inventory_policy, compare_at_price, fulfillment_service, inventory_management, option1, option2, option3, created_at, updated_at, taxable, barcode, grams, image_id, weight, weight_unit, inventory_item_id, inventory_quantity, admin_graphql_api_id, inventory.id, inventory.sku, inventory.created_at, inventory.updated_at, inventory.requires_shipping, inventory.cost, inventory.country_code_of_origin, inventory.province_code_of_origin, inventory.harmonized_system_code, inventory.tracked, inventory.country_harmonized_system_codes, inventory.admin_graphql_api_id, options.Count, options.id, options.product_id, options.name, options.position, options.values, images.Count, images.id, images.product_id, images.position, images.created_at, images.updated_at, images.alt, images.width, images.height, images.src, images.variant_ids, images.admin_graphql_api_id, image.id, image.product_id, image.position, image.created_at, image.updated_at, image.alt, image.width, image.height, image.src, image.variant_ids, image.admin_graphql_api_id
After that, run the importer as usual.
There’s also a fourth data entity available that we haven’t imported yet – Orders. We won’t, however, need it as all the Orders fields were already fetched with the Orders with line items entity.
At this point, you should have three importers as shown below as well as three tabs in your spreadsheets for the three types of data we imported.
Connecting Google Sheets to Data Studio
Go back to Data Studio and open your (for now) empty report. Click on Add data.
We’re back on our familiar page with connectors. This time, select the Google Sheets connector.
On the following page, find the spreadsheet with your Shopify imports and select one of the newly-created tabs. Then, press Add and confirm on the next pop-up.
Data Studio will immediately attempt to draw some meaningful table with our new data. Most of the time it will fail but it’s not a problem. You may as well delete this table and we’ll create something more useful in the following chapter.
Now, press the Add data button once again and add the other tab from your spreadsheet. Repeat this step one more time for the third tab.
At any time you can verify if all three sources were added correctly by clicking on Resource -> Manage added data sources in the menu.
You can also add additional data sources from there. It doesn’t even have to be Google Sheets files – you could instead import, for example, Google Analytics data and blend it with your Shopify imports.
Shopify dashboard template
Once you connected a data source (or multiple sources), it’s time to start building a dashboard with the available data. We’ll only explain the very basics of Data Studio. If you’re new to the tool, check out our Data Studio tutorial.
Load the report you’ve previously created or just create a new one. Make sure you’re in an Edit view, otherwise you won’t be able to edit much. You switch between views by clicking on the View and Edit blue buttons in the top-right corner of your screen.
There’s plenty of options available right off the bat.
The most interesting ones from our perspective are:
- Add data – it’s how you add additional data sources. We’ve already added the data sources so won’t have to use this button in the near future.
- Add a chart – this is our main point of interest. Here, we select from dozens of different chart options and drop them onto a dashboard below.
- Add a control – controls are used to, well, control what’s happening on the chart (on the viewer’s end). Here, we’ll give viewers options to select data, choose from dropdowns, etc.
To the left of the screen, there’s also a toggle for switching between pages. You can create numerous pages, for the same or different data sources.
There are three common phrases that charts rely on that can be somewhat confusing.
Dimensions are things we’re measuring or counting. For example, we may be counting inventory quantity, the financial status of an order, products, customers, or virtually anything else.
Metrics are (usually) numerical values that describe the dimensions. It could be the number of orders meeting certain criteria, it could be sales or refunds for a specific product, or it could be something else.
Once you create a date control (to give your viewers a chance to adjust the time range of a report), the data range dimension of each chart comes into play. With it, you tell Data Studio which field it should take into consideration when pulling numbers for the specific time period.
A good example is with orders that have created_at and closed_at dates. If you go with the latter and users choose a certain time period, only orders closed between these particular dates will be taken into consideration, regardless of when they were created.
Pie and bar charts
Our goal is to create an eCommerce report for our store that will automatically refresh as the new data flows in. Let’s do it.
Disclaimer: In the examples below we use fields that were named when importing the data with Coupler.io as we explained in the Connecting Shopify with Data Studio via Google Sheets chapter. If you chose to use another tool to connect Shopify to Data Studio, the field names may differ. The good news, though, is that the naming will likely be very similar.
To add the first chart, press on Add a chart and select the type of chart you want to use. For example, for our first two charts, we picked a very common bar chart.
The chart demonstrates the current quantity of products in our warehouses. We used the following setup:
- Data source – here we chose the Products tab of our Google Sheets source
- Data range dimension – product_created_at is applied automatically, it’s an equivalent of created_at for products, let’s keep it
- Dimension – product_title
- Metric – inventory_quantity
- Sort – by default the bars are sorted by the dimension, descending. In our case, it makes sense to sort by inventory_quantity and in ascending order. This way, a chart will start with products with the lowest stock level, including those that have been oversold.
It’s optional but we also enabled metric sliders so that viewers of our report can adjust the range and, for example, only show items with 10 or less in their inventory_quantity.
Note also that all variants of a product are grouped into a single product_title. Our store has, for example, six types of cheeseburgers that won’t be distinguished separately on the chart above.
If you have product variants and want to monitor them in particular, it may make more sense to choose SKU as a dimension and then filter out all the results that aren’t, for example, cheeseburgers.
To do that, click on the chart, then Add a filter and on the following page click on Create a filter at the bottom of the screen.
Here you can specify the criteria that each record will have to meet. Let’s choose to include only items that have Cheeseburger value in the column product_title (note that names are case-sensitive). You can also name a filter (or use the default name) to reuse it for other charts.
The result is the chart above, ignoring all other products that are not cheeseburgers.
Another chart that we’ve added is a pie chart, demonstrating where our customers are from, split by the order count.
Data source – Customers tab of our source
Data range dimension – created_at
Dimension – default_address.country (here we take the default address saved in customers’ files in Shopify)
Metric – orders_count
By default, the function SUM is used for the metric so all orders are summed up and the countries that placed the most orders are ranked first.
It could make more sense to look at the average order for each country, instead. To do that, click on SUM in the metric field.
Then, choose average (or any other option that works best for you).
The chart will immediately adjust, showing completely different data.
We’ve still got this dark blue piece of a pie, representing null results – those that don’t have any value in the default_address.country field. If we want, we can quickly remove it by creating a filter excluding null entries.
Orders with line items import contains much more information on what interests us the most – sales. From there we can export the list of products once again but this time put it as the total sales for each item. We can also break down cheeseburgers into sales for its variants.
Data source – Orders with line items tab of our source
Data range dimension – created_at
Dimension – line_items.title
Breakdown dimension – line_items.name (these contain an individual name for each of the variants)
Metric – current_total price (we kept SUM but AVG would also make sense).
Tables with multiple dimensions and metrics
A table is one of the most straightforward types of charts in Data Studio. Here’s the one we’ve created to show the prices, inventory quantity as well as the price we pay for each product to our supplier.
Data source – Products tab of our source
Data range dimension – created_at
Dimension – product_title and inventory.sku (one would be perfectly sufficient but perhaps the title isn’t sufficient?)
Metric – price, inventory_quantity, and inventory.cost
There’s no limit as to how many dimensions metrics you can use, you’re only limited by the space on your report.
Pivot tables
Diving again into our orders data, we’ve created two pivot tables.
The first table shows the financial status of orders for particular products and the value of orders for each. We see, for example, that lots of crab cake and Italian beef sandwiches are getting partially refunded so it’s probably something to check out. At the same time, the veggie burger is doing just great.
Source – Orders with line items tab from our export
Data range dimension – created_at
Row dimension – line_items.title
Column dimension – financial_status
Metric – current_total_price (we left the default SUM but AVG could make sense here as well)
The second Pivot table is even simpler and it shows the fulfillment status for each of our products. There are only two possible values for fulillment_status – fulfilled or null.
Source – Orders with line items tab from our export
Data range dimension – created_at
Row dimension – line_items.title
Column dimension – fulfillment_status
Metric – Record Count. Record Count is a very common metric, it shows the number of rows meeting certain criteria.
Scorecards and blending data
Scorecards don’t require a dimension – they rely on a single metric. They’re often used in dashboards to demonstrate sales, conversions, and virtually anything else that can be described with a number.
We created a set of scorecards and then blended them together to get the right conversion rates.
The base for all of our calculations is the total number of orders. Based on that we’ll calculate how many of them were paid, fulfilled, refunded, etc.
All of this information sits in the orders with line items data source. Add a scorecard and select this source. Record Count will be automatically selected as a metric which is what we want.
Our store doesn’t yet have 29 orders – it’s got 18 to be exact. There are, however, 29 rows in our source and that’s exactly what a scorecard picked up.
A word on orders with line items – when you import this particular entity with Coupler.io (and likely with any other connector), a separate row will be created for each product bought. So if a customer bought from us three different variants of a cheeseburger as well as five thousand veggie burgers, it would result in four rows created in our file.
To get the unique records for our calculations, we need to change the metric and, instead of Record Count, use, for example, a field name id. Each order has a unique ID and it’s present in each row created for a particular order.
Change the metric to id. A scorecard will immediately turn into a very long number because each ID has 13 digits and they have just been summed up! To change it to something more meaningful, click on the SUM tab in the Metric block and choose Count Distinct. Our scorecard will update immediately and you can rename it to something more meaningful than id.
Now we’ve got a proper number for orders in our store. To create scorecards for different order statuses (as we did), simply right-click on our scorecard, then copy and paste it elsewhere on the report.
Now, all we need to do is create individual filter criteria for each chart. Same as we did before, click on Add a filter and insert the criteria for a scorecard. For example:
This will filter out all orders that don’t have the financial_status paid which is precisely what we were looking for. Repeat this for each metric you can to fetch and name each scorecard (by clicking to the left of a metric).
As a final step, we’ll want to blend two scorecards to get, for example, a percentage of fulfilled orders. As math would suggest, to get the rate of fulfilled orders, we need to divide fulfilled orders by all orders and express it as a percentage.
Hold the Cmd key on Mac, Ctrl on Windows, and select the scorecard with the count of fulfilled orders and then the one with all orders. Right-click on either of them and choose Blend data.
A new scorecard will appear, showing the percentage of fulfilled orders. If something went wrong (for example all orders were divided by fulfilled rather than the other way around), you can look up the formula behind a scorecard. Click on it and then click on the leftmost part of a metric, with an Fx sign.
Here you can see the formula and adjust it, with standard mathematical formulas and more advanced Data Studio functions. You can also choose for a number to be displayed as a percentage or as a currency, which will probably be your default option for all financial scorecards.
Blending data from different sources
One last chart we wanted to share uses data from two different sources. The inventory quantity is taken from the Products source while the sales of these products are from the Orders with line numbers source. The Current_total_price header may be confusing but, underneath, it’s the price of each order, summed up.
To achieve that we need to approach data blending from a different angle than we did with scorecards. Create a table chart, then click on the Blend data button to the right.
In the leftmost part of the screen, choose the data source, add dimension, and metric as if you were adding a regular chart. Then, select Add another data source.
A new box will appear where you can select the data source of your choice. Notice how the fields change: a new one, named Join Keys, appeared on top. Insert the desired dimension there, then ignore the dimensions field below and insert a metric as usual.
Your setup should look similar to what we’ve got:
For the blended data to make sense, each source needs to share a joint dimension – referred to as join keys. As the naming in different sources differs, even in those exported only from Shopify, this field is used to tell Data Studio what it is that we’re actually trying to measure.
Product_title and line_items.title have identical values and they represent precisely the same things so they make for a perfect couple of join keys.
Save and you’ll see a table using data from both sources. You can add more sources, or you can also use the blended data in virtually all charts. It’s all up to you.
Date control
Last but not least, date control is a vital element of nearly any dashboard. It allows viewers to change the date range and have the numbers auto-update for them.
To add this feature, click on the Add a control button in the menu and then Date range control. Place it on the chart You can leave it as it is or set a default date period – for example the previous month. Here’s how it looks in the edit mode:
And here’s a view mode:
How to import multiple Shopify stores data into Google Data Studio?
Data Studio allows you to use different data sources in the same report. Because of that, you can also connect multiple Shopify sports into a single report and freely blend the data.
Arguably it would be easier to create a single Google Sheets file and then import data from each store into separate tabs. Then, you would plug this file into Data Studio and use it according to your requirements.
We comprehensively covered the topic earlier in the article so jump directly to the respective sections:
- Importing Shopify data to Google Sheets
- Adding data to Data Studio
- Blending data from different sources
Shopify and Data Studio – final words
It’s quick and painless to move your Shopify data to Data Studio. There, you can choose from dozens of charts and can visualize virtually anything you have in mind. Thanks to Coupler.io, you’ll have an easy time analyzing your performance and making data-driven decisions.
We hope you enjoyed our tutorial. Come back to learn more soon. Thanks!
