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 our Power BI consultants created an ECommerce Power BI Dashboard was 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.
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:
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:
If you are running an e-commerce store you will also need to use many tools to drive traffic towards your website. For example, in a different project we worked with an e-commerce brand that sold through Shopify and used Facebook Ads and Google Ads to drive traffic. This brand also sold on Amazon and promoted products using Amazon Ads.
The Power BI ecommerce dashboard that we created combined the data from all of these sources together to keep an eye on the business performance as a whole.
It then analysed each source in more detail to drive process optimization in each area of the business.
The purpose of the daily overview dashboard is to show the combined revenue and number of orders from all the sales channels. It also helps to compare Shopify vs Amazon by using key metrics such as number of orders, revenue and net profit.
The idea is that the marketing team can open the dashboard and immediately see daily performance and assess profitability of their marketing efforts.
We then analysed how much of an impact the date/time trends had on all the key metrics. We analysed the difference in these metrics by months and weeks to get an assessment of the trends. We also analysed the performance by day of the week to see if this affects the sales and profitability.
The tables on this tab give the user a complete visibility on the profitability of their marketing efforts. We start with the number of orders, then subtract shipping, refunds and cost of goods sold. We then show how much was spent on Facebook, Google and Amazon Ads. Finally, we end up with the net profit and net profit margin.
For many ecommerce brands Facebook is the main source of traffic. This dashboard analyses the Facebook Ads activity in more detail helping to find actionable insights/
It analyses the overall effectiveness of Facebook Ads, displaying metrics such as Ad Spend, Revenue, Return On Ad Spend (ROAS) and Cost per Acquisition (CPA).
ROAS indicates how much revenue is earned for every dollar spent on advertising. It allows marketing managers to evaluate the effectiveness of ad channels by measuring the return generated from each dollar spent on advertising.
CPA measures the cost of getting a new customer from a certain marketing channel. It reveals how much a company is paying to get a new customer to complete a desired action such as a purchase or registration.
The table below shows all key metrics by a weekday. This provides a clearer picture of marketing channel performance trends.
Graphs above provide trends of key metrics including ROAS, CPA, CPM and CPC.
CPM refers to Cost Per Mille that shows the cost the company pays for the one thousand impressions of advertisement. CPM is useful when measuring the success of marketing campaigns that are focused on brand awareness.
CPC is a Cost Per Click that measures the cost a company pays when someone clicks on an ad. It analyses the cost a company spends to drive traffic to its website.
You can see that ROAS is also analysed against CPA for the last 30 and 90 days. This evaluates the profitability of marketing strategy. Low CPA means that marketing strategy is cost-effective while high ROAS means that strategy generates revenue. In the opposite situation, there may be a need to allocate budget more effectively.
The next page provides details about Facebook campaigns. The goal is to identify campaigns that are underperforming and analyse which ads and ad groups are responsible for the underperformance. The team can then turn them off keeping only the winning ads.
At the top total number of campaigns, Ads and Ads sets is shown. Using the table at the bottom you can analyse metrics for each marketing campaign separately. This table shows KPIs including number of purchases for each campaign, Ad spent, revenue, CPA, ROAS, CPM and Link Clicks. The grand total for each metric is provided under the table.
Here is a separate table where metrics for AdSet are analysed. AdSet is a grouping of ads in a Facebook Ad campaign that share the same targeting, budget and schedule. Analysing Ads using Ad Set helps to manage ads more efficiently since you can control when ads are displayed for a certain audience.
This table allows you to get insights about the Ads performance by analysing key metrics.
The next table allows a company to evaluate the effectiveness of each marketing campaign overall by analysing metrics such as revenue, ad spent, CPA, ROAS etc.
Choosing the right creative is almost the most important part of optimizing Facebook Ads marketing. The dashboard below aims to analyse every possible variable of the Facebook Ads creative and assess the impact on the performance.
Facebook Creative is visual or textual detail of an ad that is shown to users. The table above helps to split-test the creative to find the winning one. As a rule of thumb, the winning creative should drive more engagement and link clicks.
We then analysed the impact of each thumbnail, hook and advertised products on the overall ad performance.
We also analysed the impact of the video version and the length of the video on the overall performance of the Facebook Ads marketing.
Armed with those insights, the client could make informed decisions about the future creatives that needed to be produced.
This page shows KPIs of Google Ads including Revenue, Cost, Cost per Click, Number of Clicks, CTR, Impressions, CPM, Conversions and ROAS. This table provides a quick view of overall Google Ads effectiveness.
The page above analyses trends of ROAS by a period of time such as for the last 14, 30 and 90 days. Additionally, ROAS values briefs are provided at the top of the page.
ROAS analysis for 3 and 14 days provide immediate and short run performance while 30 and 90 days allow to evaluate marketing strategy in the medium and long run. Such analysis gives the full picture of Google Ads ROAS to marketers.
This page of the dashboard analyses campaign performance. You can see key metrics for each marketing campaign such as conversions, cost, revenue, cost/conversion, impressions, clicks etc.
Cost/Conversions ratio tells how much a company is spending to get a desired action, like a purchase. By monitoring this metric, marketers can identify which campaigns are generating conversions at the lowest cost.
The table above shows an Ad Group performance overview. You can analyse each Ad Group overall and by period of time such as by 7 days, 14 days and 30 days.
Since every ad group targets a specific audience or keywords, this table helps marketers evaluate which ad group drives best results. This is useful for more efficient resource allocation and marketing strategy evaluation.
The next page of the dashboard provides insights from Shopify. At the top of the page, you can get a quick overview of the main metrics such as Revenue, Net Profit and Average Order Value.
AOV refers to the average amount a customer spends per order.
The table at the bottom reveals product sales details. All products are analysed by the quantity sold, cost of goods sold (COGS), revenue, discount and refund.
This page allows marketers to analyse which product drives more revenue and spot underperforming items. This helps to optimise the list of products offered.
The next table includes the additional analysis of product sales where all products are divided by product type. This helps to analyse customer demand overall, not by certain products only.
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.
We have a customer segment that outlines various KPIs related to customers, including New and Returning users.
Additionally, we have KPIs that classify these users based on revenue and the number of orders.
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
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
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.
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).
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
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.
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.
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.
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.
