Analyze Your Marketing Metrics from Shopify in Power BI
How to connect Shopify to Power BI in 2023
Table of content
- Ways to set up a Shopify to Power BI integration
- How to connect Shopify to Power BI in 2023
- Another way to connect Shopify to Power BI
- How to analyze your Shopify data in Power BI
- Connect Shopify to Power BI for your e-commerce store using Power BI Connector (a Shopify app)
- Power BI and its capabilities to analyze in-depth Shopify data
- FAQ: Shopify Power BI Integration
Ways to set up a Shopify to Power BI integration
The best way to connect Shopify with Power BI will depend on how you intend to use the data and how experienced you are with visualization tools such as Microsoft Power BI. You can either use a third-party application or import your Shopify data to an Excel workbook before plugging it into Power BI.
Shopify API lets you connect with many external apps, including Power BI. A Shopify to Power BI integration has many benefits you can enjoy with the help of third-party tools. These tools allow you to export data from your store to Microsoft’s tool quickly.
The setup is simple, but each tool offers unique functionalities, and it would be best to check out their pages before choosing a tool to use. You may have to pay a fee to use these tools, but most offer a free trial.
Another way to set up a Shopify to Power BI integration is to import your Shopify data to an Excel workbook before plugging it into Power BI. While this method consumes more time, it offers the following benefits:
- It enables you to analyze the raw data in Excel: you can quickly find trends that are not available in your Shopify dashboard, look up which products have sold, and stay up-to-date on the latest sales.
- You can get data from other apps into a workbook and jointly import it into Power BI: such apps include QuickBooks, Pipedrive, Airtable, Hubspot, and many more.
A Shopify Power BI integration is ideal for visualization and analytics of data from multiple sources. It makes data export easier as you can create many data sources to export your data.
This integration also widens your data landscape when you merge your data with other data sources.
Connecting Shopify to Power BI also provides more convenience in terms of functionality. You can easily explore key data like inventory, customers, and orders and use filters to choose the required data.
What’s more? You can get data promptly by scheduling automated refresh.
Power BI provides informative dashboards, enabling you to visualize and create reports with your store data. This feature makes data analytics seamless, improving your efficiency and profitability.
How to connect Shopify to Power BI in 2023
Step 1: Start by creating a free Windsor.ai account.
Step 2: Click on Shopify App Store and add the app to your store on Shopify
Then it should appear as in the image below
Step 3: Select the fields
Step 4: Select power bi as destination and copy the API URL. In Power bi, you can get data from the web by copying the url.
Then in Power bi, go to Get data -> From the web and paste the API URL there.
You’re all set, just wait for your data to load
Try Windsor.ai today
Access all your data from your favorite sources in one place.
Get started for free with a 30 - day trial.
Another way to connect Shopify to Power BI
Step 1: Navigate to the Destination settings and choose Microsoft Excel for the Shopify to Excel connection.
Windsor.ai also enables many different Shopify integrations with Google Sheets. It allows you to export Shopify to Power BI.
Step 2: Create an Excel workbook and ensure that it is sitting in your
OneDrive. Link your Microsoft account with Windsor.ai by clicking on the Connect button.
Step 3: Log in to your account and approve the connection. Once done, click on Accept and return to Windsor.ai.
Step 4: Choose the workbook you created and a sheet.
You’ll need to schedule the data refreshes. You can have the data refreshed daily or as often as every 15 minutes.
Once done, click on the Save and Run button and launch the importer. Be patient as the launch could take a while to complete.
Step 5: Once done, click on the View Results button to access your Excel workbook with the latest import.
Now, you need to duplicate your importer to fetch two other entities. Navigate to the importer list and click on the three dots menu beside the one you set up. Choose to Copy.
Go to the Source settings and replace the data entity with Orders or Products with line items.
You’ll also need to update the worksheet to allow the new data to import into a separate sheet. You can do this in the Destination settings.
Once done, click on Save, run the importer, and repeat this procedure for the last entity. There’s no need to pull the Orders entity because all available data will come with the Orders with line items import.
Now, you’ll need to add an Excel workbook to Microsoft Power BI. The Shopify data is now in Excel, and it is time to add the dataset to a report in Power BI.
You may be unable to do this with the web version of Power BI as Google Sheets to Power BI. Your best bet when importing Excel data to Power BI will be Power BI Desktop. Here are the steps to follow:
Open Power BI and click on the Import data from Excel button on the home screen. You may also click on the Excel Workbook icon on the Home ribbon.
Go through your One Drive and locate the Excel file with the Shopify data.
It’s crucial that the Excel file stays in the OneDrive folder for regular syncs with related files in OneDrive storage. Once you import the new data from Shopify, your local Excel file and Power BI charts will be updated.
Go to the Navigator window and mark the worksheets to load the data to Power BI. Once done, click on Load, and your Shopify data will be ready for use in Power BI.
You’ll now enjoy the benefits of Power BI, including data visualization and analytics.
How to analyze your Shopify data in Power BI
You can start by selecting fields from your dataset and the desired visualization.
Once you finish selecting fields, a table will be created containing your data. The table is customizable, and you can change its field formatting, style, appearance, and many other elements. Here’s what it may look like:
The option to filter the data you want displaying on the table is also available. You can also apply certain conditions in the filter section. For example, you may choose to display only orders above $100.
If you want to visualize this data, there are various ways to go about it, such as pie charts. Navigate to the Visualizations section, click on the respective icon, and your data will appear as a pie chart.
The possibilities are endless with the data at your disposal when using Microsoft Power BI. You have numerous features and functions for analytics and reporting, allowing you to create excellent charts for your business.
Connect Shopify to Power BI for your e-commerce store using Power BI Connector (a Shopify app)
Once you install the app, you’ll need to set it up and import the data you want to analyze. Once your Shopify Power BI integration is complete, you’ll have endless features and functions for analytics and reporting.
Running a successful e-commerce business requires dealing with tons of information. There are many things to do, and you need multiple data sources to make the best decisions. A Shopify to Power BI connection is an excellent solution.
Read on to see how to better your business analytics with a Shopify Power BI integration.
Introduction to Shopify Data Analysis to drive more sales
Shopify provides a dashboard under Analytics where you can view your data for free. The dashboard contains information about total sales, average order value, conversion rate, traffic sources, and more. You’ll also find sales, customers, behavior, acquisition, finances, and marketing reports.
Shopify allows you to create custom reports. Stores selling exclusively on Shopify will get reports covering all their data. With Shopify’s analytics and reports, you’ll have insights into your store’s recent activity and visitors.
It creates the means to analyze your online store’s speed and transactions. Shopify allows everyone to access key financial reports and the Analytics page. A Live View feature enables online store owners to gain insights into their business and drive more sales.
You can even access more reports with the Basic Shopify plan or higher. Upgrading to a different plan gives you additional reports starting from the first time you used Shopify.
Here are the available Shopify plans:
- Basic ($29 per month)
Best for new e-commerce businesses with occasional in-person sales
- Shopify ($79 per month)
Best for growing businesses selling online or in-store
- Advanced ($299 per month)
Best for scaling businesses that require advanced reporting.
Store owners can handle most of their reporting and analysis needs on the dashboard. However, you can manipulate and view your data differently by exporting the data to a spreadsheet program, using a third-party app, or setting up third-party analytics offerings.
Power BI and its capabilities to analyze in-depth Shopify data
Power BI Shopify integration is possible thanks to the Power BI Connector app. It enables the export of key e-commerce data from a Shopify store to Microsoft Power BI for further analytics.
With the Power BI Connector app, you can:
- Visualize important Shopify store data to Power BI and convert raw data into an understandable format
- Merge your store data with other key data sources
- Schedule automated refresh to update your data to a particular time and date
- Create convenient dashboards to track your orders, inventory, prices, and customers
- Rest assured that your store data is safe (the app does not collect customer data).
Power BI Connector is handy for Shopify store managers and owners looking to extract and analyze e-commerce store data to make them up-to-date.
It also helps with customer offers, pricing, making corrections in assortment, etc. The Power BI Connector app is easy to use as it requires no special skills or additional competence to get started.
You’ll be able to create URL connections easily with the app, even as a first-time user. Power BI Connector encourages better decision-making as it provides actual and detailed information to base your strategies on.
Thanks to its transparent reporting, you can quickly measure the KPIs and boost your margin and profit.
Try Windsor.ai today
Access all your data from your favorite sources in one place.
Get started for free with a 30 - day trial.
Analyzing your e-commerce data helps you make more informed business decisions to drive sales. It’s easy when you have the right tools. With Power BI Connector, you can connect Shopify to Power BI, visualize the data and build interactive reports and dashboards.
A Shopify Power BI integration is a simple process, and most store owners choose Looker Studio. It has a simplistic interface and offers a seamless connection with many other Google services.
Once you connect your Shopify account to Power BI, you’ll have access to many features and functions to better analyze and build your business.
Windsor.ai enables marketers to connect all their data to tools like Looker Studio, Google Sheets, and Power BI. This integration helps you to make accurate decisions and significantly increase your ROI. You’ll save a lot of time through automation.
FAQ: Shopify Power BI Integration
Can you connect Shopify to Power BI?
Power BI Connector for Shopify is a Power BI Shopify integration app enabling easy Shopify data export to Microsoft Power BI for further visualization and data analysis.
How to connect Shopify to Microsoft Power BI?
Connecting Shopify to Microsoft Power BI requires a few steps. Firstly, you must install the Power BI Connector for Shopify and create a Data Source.
The next step is to prepare data for the integration process. You’ll need to input a data source name and description, select the preferred option and set up a start time.
Once done, click on Save to apply changes. You’ll see the progress displayed in the Status column. Please wait for it to finish, and then click on Connect.
Once the data appears, choose the tables you need and click on Load.
See the value and return on every marketing touchpoint Providing 50+ marketing data streams, we make sure that all the data we integrate is fresh and accessible by marketeers, whenever they want. Spend less time on manual data loading and spreadsheets. Focus on delighting your customers.
Shopify metrics and dimensions available for streaming into Power BI
name type description Account ID TEXT Shopify Account URL Account Name TEXT Shopify Account URL Marketing Event Breadcrumb ID TEXT The breadcrumb ID of the event. Marketing Event Budget Type TEXT The budget type of the event. Customer Average Order Value NUMERIC Average order value is the average dollar amount customer spend each time an order is placed on your ecommerce website. Customer Created at (Timestamp) TIMESTAMP The date and time (ISO 8601 format) when the customer was created. Customer Currency TEXT The three-letter code (ISO 4217 format) for the currency that the customer used when they paid for their last order. Defaults to the shop currency. Returns the shop currency for test orders. Customer Created At (Date) DATE The date and time (ISO 8601 format) when the customer information was last updated. Customer Default Address OBJECT The customer's default address. Customer E-Mail Address TEXT The unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error. Customer First Name TEXT The customer's first name. Customer ID TEXT A unique identifier for the customer. Customer Last Name TEXT The customer's last name. Customer Last Order ID TEXT The ID of the customer's last order. Customer Last Order Name TEXT The name of the customer's last order. This is directly related to the name field on the Order resource. Customer Multipass Identifier NUMERIC A unique identifier for the customer that's used with Multipass login. Customer Note TEXT A note about the customer. Customer Orders Count NUMERIC The number of orders associated with this customer. Customer Phone Number TEXT The unique phone number (E.164 format) for this customer. Customer state TEXT The state of the customer's account with a shop (Can be disabled, invited, enabled and declined) Customer Tags TEXT Tags that the shop owner has attached to the customer, formatted as a string of comma-separated values. Customer Tax Exempt BOOLEAN Whether the customer is exempt from paying taxes on their order. If true, then taxes won't be applied to an order at checkout. If false, then taxes will be applied at checkout. Customer Total Spent NUMERIC The total amount of money that the customer has spent across their order history. Customer Updated at (Timestamp) TIMESTAMP The date and time (ISO 8601 format) when the customer information was last updated. Customer Email Verified BOOLEAN Whether the customer has verified their email address. Date DATE The date and time (ISO 8601 format) of the queried object. Order Email TEXT The customers email address. Marketing Ended At TIMESTAMP The event end datetime. Marketing Event Type TEXT The event type. Line Item Discount Allocations TEXT An ordered list of amounts allocated by discount applications. Eachdiscount allocation is associated with a particular discount application.amount: The discount amount allocated to the line in the shop currency.discount_application_index: The index of the associated discount application in the order'sdiscount_applications list.amount_set: The discount amount allocated to the line item in shop and presentmentcurrencies. Line Item Duties TEXT A list of duty objects, each containing information about a duty on the line item. Line Item Fulfillable Quantity NUMERIC The amount available to fulfill Line Item Fulfillment Status TEXT How far along an order is in terms line items fulfilled. Valid values:null, fulfilled, partial, and not_eligible. Line Item Grams NUMERIC The weight of the item in grams. Line Item Id TEXT The ID of the line item. Line Item Name TEXT The name of the product variant. Line Item Price NUMERIC The price of the item before discounts have been applied in the shop currency. Line Item Product Id TEXT The ID of the product that the line item belongs to. Can be null ifthe original product associated with the order is deleted at a later date. Line Item Properties TEXT An array of custom information for the item that has been added to the cart. Often used to provide product customization options. Line Item Quantity NUMERIC The number of items that were purchased. Line Item Requires Shipping TEXT Whether the item requires shipping. Line Item SKU TEXT The item's SKU (stock keeping unit). Line Item Tax Lines TEXT A list of tax line objects, each of which details a tax applied to the item.title: The name of the tax.price: The amount added to the order for this tax in the shop currency.price_set: The amount added to the order for this tax in shop and presentment currencies.rate: The tax rate applied to the order to calculate the tax price.channel_liable: Whether the channel that submitted the tax line is liable for remitting. Avalue of null indicates unknown liability for the tax line. Line Item Taxable TEXT Whether the item was taxable. Line Item Tip Payment Gateway TEXT The payment gateway used to tender the tip, such asshopify_payments. Present only on tips. Line Item Title TEXT The title of the product. Line Item Total Discount Set NUMERIC The total amount allocated to the line item in the shop currency.Instead of using this field, Shopify recommends using discount_allocations, which provides the sameinformation. Line Item Variant ID TEXT The ID of the product variant. Line Item Variant Title TEXT The title of the product variant. Line Item Vendor TEXT The name of the item's supplier. Marketing Event Manage URL TEXT The manage URL of the event. Marketing Event Activity ID TEXT The activity ID of the event. Marketing Event Channel TEXT The markerting channel of the event. Marketing Event Budget NUMERIC The budget of the event. Marketing Event Currency TEXT The currency of the event. Marketing Event Description TEXT The event description. Marketing Event Paid BOOLEAN The flag that indicates if an event is paid. Order App ID TEXT The id of the application. Order App name TEXT The name of the application. Order Billing Address TEXT The orders billing address. Order Billing Address City CITY The orders billing address city. Order Billing Address Country COUNTRY The orders billing address country. Order Cancel Reason TEXT The reason why the order was canceled. Order Cancelled At TIMESTAMP The date and time ( ISO 8601 format) when the order was canceled. Order Closed At TIMESTAMP The date and time (ISO 8601 format) when the order was closed. Order Count NUMERIC The number of orders. Default value is 1, but for refunds, the value is zero. Order Created At TIMESTAMP The autogenerated date and time (ISO 8601 format) when the order was created in Shopify. The value for this property cannot be changed. Order Currency TEXT The three-letter code (ISO 4217 format) for the shop currency. Order Current Subtotal Price NUMERIC The current subtotal price of the order in the shop currency. The value of this field reflects order edits, returns, and refunds. Order Current Total Discounts NUMERIC The current total discounts on the order in the shop currency. The value of this field reflects order edits, returns, and refunds. Order Current Total Price NUMERIC The current total price of the order in the shop currency. The value of this field reflects order edits, returns, and refunds. Order Current Total Tax NUMERIC The current total taxes charged on the order in the shop currency. The value of this field reflects order edits, returns, or refunds. Order Email TEXT The customers email address. Order Payment Status TEXT The status of payments associated with the order. Can only be set when the order is created. Order Fullfillable Quantity NUMERIC The fullfillable quantity of the order. Order Fulfillment Service TEXT The order fulfillment service. Order Gross Sales NUMERIC The order gross sales (before taxes, shipping, discounts, and returns). Order ID TEXT The unique identifier of the order Total inventory item cost of an order NUMERIC Total inventory item cost of an order. Order Line Items OBJECT The line items of the order. Order name TEXT The name of the order Order Net Sales NUMERIC The order net sales. Order note TEXT An optional note that a shop owner can attach to the order. Order Product Price NUMERIC The order price. Order Quantity NUMERIC The order quantity. Order Refunds TEXT Id of refund assigned to the order. Aggregated Order Refunds Net NUMERIC (Equates to order_gross_sales - order_total_discounts - order_net_sales). Order Refunds total NUMERIC The order total refunds. Aggregated Order Refunds Total Tax NUMERIC The total tax on the refund line items. Order Sales Channel (A duplicate of `order_app_name`) TEXT The name of the application. Order Shipping Address TEXT The orders shipping address. Order Shipping Address City CITY The orders shipping address city. Order Shipping Address Country COUNTRY The orders shipping address country. Order tags TEXT Tags attached to the order Order Total Discounts NUMERIC The total discounts applied to the price of the order in the shop currency. Order Total Discounts Set NUMERIC The total discounts applied to the price of the order in shop and presentment currencies. Order Total Price NUMERIC The sum of all line item prices, discounts, shipping, taxes, and tips in the shop currency. Must be positive. Order Updated At TIMESTAMP The date and time (ISO 8601 format) when the order was last modified. Marketing Event Preview URL TEXT The preview URL of the event. Product Created At Datetime TIMESTAMP The date and time the product was created. Product Description TEXT Description of the product. Product handle TEXT The product handle. Product ID TEXT The product variant SKU number. Product Image URL IMAGE_URL The image URL of the product. Product Inventory Quantity NUMERIC The product quantity in inventory. Product Inventory Value NUMERIC The sell value of the product inventory. Product Price NUMERIC The price of the product. Product Tags TEXT Tags attached to the product. Product Title TEXT The title of the product. Product Type TEXT The type of the product. Product Vendor TEXT The vendor of the product. Marketing Event Referring Domain TEXT The referring domain of the event. Marketing Remote ID TEXT The remote id. Marketing Event Scheduled To End At TIMESTAMP The scheduled end date of the event. Variant SKU number TEXT The product variant SKU number. Marketing Started At TIMESTAMP The event start datetime. Marketing Event UTM Campaign TEXT The UTM campaign of the event. Marketing Event UTM Medium TEXT The UTM medium of the event. Marketing Event UTM Source TEXT The UTM source of the event. Variant Title TEXT The title of the product variant. Today DATE Today's date
Easy and safe Power BI + Shopify integration with Windsor.ai
See the value and return on every marketing touchpoint
Providing 50+ marketing data streams, we make sure that all the data we integrate is fresh and accessible by marketeers, whenever they want.
Spend less time on manual data loading and spreadsheets. Focus on delighting your customers.