Ecommerce Power BI Dashboard – Real World Case Study

A well-designed ecommerce Power BI dashboard can transform the e-shop data into insightful visualizations. Power BI integrates with various data sources like Facebook Ads, Shopify, enabling comprehensive data consolidation. Users can also track sales, monitor inventory, and analyze customer behavior in real-time.

In this article we will go through a real-world case study for how ECommerce Power BI Dashboard were used for driving informed decisions.

Power BI’s advanced analytics features, such as DAX (Data Analysis Expressions) offered deep insights into sales trends, customer preferences, and operational efficiency.  Additionally, its intuitive interface allowed for easy customization and sharing of reports, facilitating data-driven decision-making and enhancing overall business performance.

Why Create an Ecommerce Power BI Dashboard

There are lots of reasons why ecommerce brands may want to build Power BI dashboards.

The specific dashboard we are discussing here was built for a hemp oil brand that needed a single source of truth for the management team to make decisions. Having their data disjointed in Facebook, Google, Bing, Google Analytics, Klaviyo and Shopify was making it way too difficult to produce in-depth analysis of the business.

On top of that, this specific ecommerce brand in our case study a Power BI dashboard to answer the following business questions:

  • What customer acquisition channels offer cheaper cost per customer
  • How effective are we in selling more to returning customers
  • How effectively do we convert leads to paying customers
  • What is our monthly and annual recurring revenue
  • Which ones of our products are becoming more popular with our customers

How to connect to Ecommerce Data with Power BI

Power BI has native integrations with data sources like Google Analytics which are widely used in ecommerce analytics. However, if you want to build a highly functional Ecommerce Power BI dashboard, you also need to connect to sources like Shopify, Facebook Ads and many others. These sources don’t have a native integration but not to worry! There are lots of third-party integrations between these sources and Power BI.

We used the Vidi Corp Power BI Connectors for this case study to extract the ecommerce data. These connectors enabled us to automatically extract the data into an Azure SQL Server database and make it readily available for the analysis. We recommend these connectors for multiple reasons:

  • Ability to handle big data – This is important because we have seen cases where the Shopify data extract was around 30GB. The Vidi Corp Power BI connectors extract the data into Azure SQL Server database before loading it to Power BI. As a result the heavy loading is taken care of by SQL Server and Power BI is used for lighter data operations.
  • Ability to connect to multiple accounts – ecommerce brands often have multiple FB Ads accounts. We have seen cases where a client would have 1 account for US, another one for Canada, one more for Australia, etc. The Vidi Corp connectors can handle data consolidation from multiple accounts.

Ecommerce KPIs to Analyse in Power BI

Shopify Insights

ECommerce Power BI Dashboard with insights from Shopify


This section of the Power BI dashboard analyses the ecommerce sales data which primarily uses Shopify data. Let’s walk through it together to explore the KPIs and uncover insights from the data.

This view highlights various KPIs, including Orders, Unique Customers, Sales, Discounts Offered, Average Order Value (AOV), New Customers, Leads, and Conversions

    Crucially this dashboard compares these important metrics for new and returning customers. It is obvious from the dashboard that returning customers have a 20% higher AOV and account for 42% of revenue. This highlights the importance of marketing to the returning customers through the newsletter and PPC remarketing campaigns.

    • Order: Number of orders within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • Unique Customers: Unique customers within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • Sales: Total sales at company level within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • Discounts Offered: Total Discount offered by company within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • Average Order Value (AOV): Average order value within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • New Customers: New customers added within the selected time frame and the percentage change compared to the data from the previous 30 days.
    • Leads : Number of people who left their email by signing up for the newsletter. This data was pulled together from
    • Conversions: Number of Conversions within the selected time frame and the percentage change compared to the data from the previous 30 days.

     We have a customer segment that outlines various KPIs related to customers, including New and Returning users. 

    • New: New users are those who made their first purchase.
    • Return: Returning users are those who made a subsequent purchase. 

    Additionally, we have KPIs that classify these users based on revenue and the number of orders.

    Bonus: Shopify Products Insights

    Power BI Ecommerce Dashboard - Shopify Customer Analytics

    We also create a loyalty and retention Power BI dashboards for some of our ecommerce clients using the data from Shopify. This dashboard is designed to help the users find specific customers by loyalty level and their stage of life cycle.

    The loyalty metric is a score from 0 to 1 which is assigned to every customer from Shopify. It is calculated as a factor of average order value, shopping frequency and how many different products the customer bought.

    The Retention metric is calculated as the latest time when the purchase was made by a particular customer. A customer is then assigned a label based on multiple conditions

    • New – they purchased an item for the first time in the last 30 days,
    • Active – they are a recurring customer and purchase the item in the last 30 days
    • Lapsed -they purchased an item in the last 60 days but not the last 30 days
    • Reactivated – they used to be a dormant customer but purchased again in the last 30 days
    • Dormant – they haven’t purchased an item for more than 60 days

    The dashboard allows the users to filter by loyalty and retention group and find the exact emails of customers to target in emails. This way the users can extract the list of dormant customers and work to reactivate them through email

    Power BI Shopify Product Analytics

    The next 2 pages of the dashboard analyse which products new customers buy and which products the most loyal customers buy. These products can be used to reactivate dormant customers through remarketing campaigns.

    Revenue by Geo Analysis

    This view presents various KPIs such as revenue, orders, and Average Order Value (AOV) by geographical location. In this case study It displays data by province according to business requirements, allowing the business to identify which provinces generate more sales than others.

      A map visualization helps in identifying sales in each province and  table visuals helps in detailing the performance of each province in terms of Price, Orders, and Average Order Value (AOV).

      Power BI Ecommerce Sales by Location Dashboard

      Recurring Revenue Analysis

      Power BI Email Marketing Dashboard

      This view represents the email marketing metrics from Klaviyo. The website visitors can sign up for the newsletter which is a massive source of revenue from returning customers. Every email marketing campaign is analysed for performance metrics

        • New Subscriber: Number of newsletter subscribers added within the selected time frame
        • Churn (Unsubscribe): the number of people who unsubscribed from the newsletter
        • Email conversion funnel: Number of sent, opened and clicked emails are analysed per campaign
        • Click-Through Rate (CTR):It  measures the percentage of users who click on a campaign link as compared to those who received an email.

        Ad Channel Analytics

        Power BI Ecommerce Sales Channels Dashboard

        This view provides insights into ad performance and Customer Acquisition Cost/Lifetime Value from various sources.CAC (Customer Acquisition Cost) is the cost of acquiring a new customer through marketing and sales efforts where LTV (Customer Lifetime Value is the total revenue a business expects to earn from a customer over their entire relationship.

          • Ad Performance:Ad performance summarizes revenue and leads generated from different advertising investments. 
          • CAC/LTV from sources: CAC/LTV analysis helps identify metrics such as clicks, cost per click, conversion rate, and other KPIs across different data sources

          Recurring Revenue and High Rising Product Analysis

          Product Recurring Revenue Analysis
          1. This view displays four key KPIs in table format. Before getting into the details of KPIs let’s understand the definition of MRR and ARR.
          • Product Revenue: Shows total sales and quantity sold for each product, including changes from the previous time frame.
          • High-Rising Product: Identifies products with a significant sales increase compared to the previous month, potentially marking them as premium items.
          • Company MRR: Details the company’s Monthly Recurring Revenue (MRR) over the last 9 months, along with total customers and Average Order Value (AOV).
          • Company ARR: Provides the company’s Annual Recurring Revenue (ARR) for each year, along with total customers and AOV.

          MRR represents regular monthly income and it helps track short-term revenue trends.

          ARR reflects steady annual income and it offers an annualized view of recurring revenue for future financial forecasting.

          Should you create a Power BI Ecommerce Dashboard?

          Overall we can conclude that, Analyzing sales data with Power BI enhances decision-making by providing clear insights through interactive dashboards and reports.

          It helps identify trends, track performance, and optimize strategies, leading to improved sales, better customer understanding, and more informed business decisions, ultimately driving growth and efficiency.