Connect Shopify to Microsoft Power BI

Running a Shopify store, you need to be making informed decisions. Every sale, product listed, and customer generates lots of raw data that’s difficult to analyze by solely looking at Shopify reports. For that reason, many shop owners choose to connect Shopify to Microsoft Power BI and let the powerhouse crunch their numbers – with some great results!

We’ll explore different approaches to integrating both tools. We’ll also share some tips on getting started with Power BI and building your first chart.

Ways to set up a Shopify to Power BI integration

Shopify API opens up opportunities to connect the tool with many external applications. Power BI is one of them and many gladly take advantage of Shopify to Power BI integrations.

How you connect both tools very much depends on how you want to use the data. Your experience level with visualization tools and Microsoft Power BI, in particular, will also play its part.

There are plenty of tools that offer a direct Shopify to Power BI integration. They promise a quick setup and a smooth migration of data from your store to Microsoft’s tool. The exact functionalities differ between the tools, so if either appeal to you, do check out their individual pages.

Each tool comes with a subscription, and most (if not) also offer a free trial.

An alternative approach would be to import your Shopify data to an Excel workbook and then plug it into Power BI. This method takes a bit more time to set up but is advantageous because you can:

• Analyze the raw data in Excel – check the latest sales, look up which products have sold, and find trends you wouldn’t have otherwise found in your Shopify dashboard.
• Import data from other applications into a workbook and jointly load it into Power BI – these could include Airtable, Pipedrive, Hubspot, QuickBooks, and many others.
• Finally, if you haven’t worked with your Shopify data before, you’ll have easier time building charts in Power BI – simply because you’ll have the raw data in front of your eyes and will be able to pick the meaningful metrics and build charts around them.

Coupler.io offers a popular ETL tool for importing data into Excel, and Shopify is one of our most popular integrations. We’ll explain how to set up one of the Shopify integrations in the following chapter.

How to connect Shopify to Microsoft Power BI

Here’s what we’re going to do:

• We’ll set up automated imports of different Shopify entities into a single Excel workbook.
• We’ll create a schedule so that the latest available data is pulled into a workbook precisely when we need it.
• We’ll then connect the Excel file to Power BI and build some sample charts.

If you haven’t already done so, create a Coupler.io account. Then, click on the button to create a new importer.

Setting up a Shopify to Excel importer

The first step is to choose the source and destination applications that you wish to pull the data from and to. Of course, in our case, the source app will be Shopify. The destination app will be Microsoft Excel to use the Shopify to Excel connection.

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.

Coupler.io imports individual data entities into destinations such as Excel. Four are currently available:

• Customers
• Inventory 
• Products
• Orders

While you need to choose one, you’ll then be able to duplicate an importer and pull other available data into another worksheet. What’s more, all Orders data is already included in the bigger “Orders with line items” entity so you won’t need to import them all.

For now, let’s choose Customers.

Jump to the Destination settings. Coupler.io also enables various Shopify integrations with Google Sheets. Exporting Shopify to BigQuery and then to Power BI is also an option.

Simultaneously, create an Excel workbook and make sure it’s sitting in your OneDrive.

Click the Connect button to link your Microsoft account with Coupler.io.

A pop-up will appear for you to log in and approve the connection to your account. Accept and go back to Coupler.io. Select the workbook you just created and a sheet for the Customers import.

Finally, set up a schedule for the data refreshes. Your data could refresh as often as every 15 minutes but for many, daily sync will also work. Choose whichever option works best for you.

When you’re finished, click the Save and Run button to launch the importer. Give it a little while to load. Once it does, press the View Results button and it will take you to your Excel workbook with the latest import.

Now, let’s quickly duplicate our importer to fetch two other entities. Go to your importers list and click the three-dots menu next to the one you just set up. Choose to Copy.

In the Source settings, swap the data entity for either Products or Orders with line items.

Also update the worksheet in the Destination settings so that the new data is imported into a separate sheet. Then, Save and run the importer. Repeat the procedure for the last entity. 

As a reminder – you don’t need to pull the Orders entity as all available data will be pulled with the Orders with line items import.

Adding an Excel workbook to Power BI

With your Shopify data now in Excel, the next step is to add the dataset to a report in Microsoft Power BI. 

Doing so in the web version of Power BI is quite problematic, and lots of people report not being able to finish the process. We tried it as well while writing our Google Sheets to Power BI article and trying to import the Excel data but sadly we failed as well. 

Eventually, we chose to resort to Power BI Desktop, and we’ll continue on this tool in this article.

Launch Power BI and click the Import data from Excel icon on the home screen. You can also click the Excel Workbook button on the Home ribbon.

In your One Drive, find the Excel file with the Shopify data.

Important: Keep the Excel file in the OneDrive folder on your device to enable regular syncs with its equivalent in OneDrive storage. As the new data is imported from Shopify, the local Excel file will be updated and so will your Power BI charts.

In the Navigator window, mark one or more worksheets to load the data to Power BI. Click Load.

That’s it! Your Shopify data is now ready to be used in Power BI.

Analyze your Shopify data in Power BI

Now you can enjoy all the benefits of data visualization and analytics provided by Power BI. For starters, you’ll need to select fields from your dataset and choose the desired visualization. 

If you only select fields, a simple table will be generated containing your data. Naturally, you can customize its appearance, style, field formatting, and many other elements. Here is how it may look:

You can also filter the data you want to be displayed in the table. For example, we picked three fields from the Orders with line items Shopify import:

• customer.first_name
• customer.last_name
• total_price

However, we don’t need the list of all customers who have ever bought from us. Instead, we prefer to display only the customers with orders greater than $100. We applied this condition in the Filter section, and here’s the outcome:

It would be cool to also visualize this as, for example, a pie chart. To do so, click on the respective icon in the Visualizations section, and there you go:

Of course, you can do a lot more with the data at your disposal. Power BI provides plenty of amazing functions and features for reporting and analytics. Enjoy them and have fun building your Shopify analytics dashboard and other charts for your business.

Connecting Shopify to Power BI: Recap

As you can see, it’s easy to connect Shopify to Microsoft Power BI, whether you choose a direct integration or decide to import to Excel first. The latter in particular is very beneficial because you get to see the essential data of your store in your favorite spreadsheet tool.

And if Excel isn’t exactly your favorite, you’ll be happy to know that you can also connect Google Sheets and Power BI. In another article, we also described how to create a graph in Google Sheets – maybe that’s something that would work well for your Shopify data?

Finally, Google Data Studio is a popular choice with Shopify store owners. They love it for its simplicity and seamless connection with other Google services. You can find out a lot more about this integration in our Shopify to Data Studio guide.

Choose what works best for you  – there are plenty of possibilities. Thanks!