How to Build a Shopify Sales Funnel Dashboard in Power BI

TL;DR

A Shopify sales and customer funnel report in Power BI is built as two linked pages: an analysis dashboard with KPI cards, a province map, a city bar chart, day and hour trend charts, and gateway and product type breakdowns, plus a row-level details page reached by drill through and exportable. The build runs from business requirement gathering through data walkthrough, connection, and cleaning.

Transcript

Hey guys, welcome back to my channel data tutorials and I'm back with another PowerBI project Shopify sales and customerfunnel report for funnel analysis. Right. So this is a brand new project and you can consider this as a kind of a realtime project also because everybody knows what is Shopify and those who don't know I will explain you what exact... Read More

Key Insights

• Shopify is an e-commerce platform that lets any individual run a business by creating an online store, selling products online and processing payments on that website itself. It also supports managing physical stores and marketing, and it offers a free trial on its official site.
• Net sales is treated as a distinct metric from total sales in this report, and the transaction performance KPI group pairs it with total quantity and net average order value. The distinction is called out explicitly as something that will be explained during the build.
• Customer purchase behavior in the dashboard is measured by splitting total customers into single order customers and repeat customers. These metrics are described as very important for any e-commerce website or any customer facing B2C business.
• Retention and value are captured through three KPIs: LTV, meaning lifetime value, the repeat rate expressed as a percentage, and the frequency of purchase. These sit alongside the transaction and customer behavior KPI groups at the top of the dashboard.
• The regional overview uses color saturation on a province map, where province means state in the United States, so darker areas identify where the most selling products and quantities occur. A companion density or bubble map shows where most customers are concentrated.
• Clicking a province filters a city level bar chart, revealing the top city inside that state. Florida resolves to Miami, Texas resolves to Houston, and California resolves to Los Angeles, which gives both a high level and a more granular view of demand.
• The dataset covers one week, running from 18th of June to 24th of June, which supports a day wise area chart of sales and quantity volume so weekly customer behavior and day patterns can be identified across that seven day window.
• An hourly chart breaks the day into 24 hours to expose peak and lowest peak selling times. The stated business use is to concentrate marketing in the highest selling hours and to manage more inventory then, since most orders arrive during those hours.
• Map tooltips return a full record for a hovered location, showing location, total sales, average order value, quantity, customers, customers with one order, and LTV, so a single hover answers several questions for any state of interest.
• The details tab is an exportable sheet giving row wise information for each order, and it doubles as a drill through target. Selecting a province such as Florida and using drill through filters the sheet to just those records for export.

Questions & Answers

Q: What is Shopify and how does it work for a business?

Shopify is an e-commerce platform that allows any individual to do business by creating online stores where products are sold online and payments are processed on that website itself. It also allows managing physical stores and doing marketing. Users can personalize their site, modifying theme and color codes to build their own website on top of the platform. Its official website offers a free trial, and smaller businesses all over the world use it to sell different products and make money.

Q: What KPIs are included in a Shopify Power BI funnel dashboard?

The dashboard groups KPIs into three families. Transaction performance includes net sales, which is different from total sales, total quantity, and net average order value. Customer purchase behavior includes total customers, how many are single order customers, and how many are repeat customers. Retention and value includes LTV, meaning lifetime value, the repeat rate percentage, and the frequency of purchase. These are described as high level KPIs that matter for any e-commerce website or customer facing B2C business.

Q: How do you make a Power BI dashboard fully dynamic?

The dashboard uses a measure changer filter at the chart level, so a viewer can switch which metric drives the visuals and see net sales, quantity, or total customers in the same map and bar chart. Beyond that, clicking any element cross filters everything else: selecting a state, a specific day such as the 19th, a payment gateway such as Shopify payments or PayPal, or a product such as running shoes refilters the entire page. Slicer style filters for gateway and province are added as well.

Q: What does the regional overview section of the dashboard show?

The regional overview presents provinces, which are states in the United States since the data is for the USA, using color saturation so the states with the most selling products and quantities stand out. A second density or bubble map shows where most customers are coming from. Alongside these sits a bar chart of cities within a selected province. Hovering a location on the map produces a tooltip with location, total sales, average order value, quantity, customers, customers with one order, and LTV.

Q: Which cities lead sales in Florida, Texas and California in this dataset?

Clicking a province in the map filters the city bar chart to that state, which surfaces the leading city for orders. In this dataset, selecting Florida shows Miami as the city where most orders come from. Selecting Texas shows Houston. Selecting California shows Los Angeles. The instructor uses these three examples to demonstrate how the same interaction moves the analysis from a high level state view down to a more granular city level view of where demand concentrates.

Q: Why analyze sales by hour of the day in an e-commerce dashboard?

An hourly chart splits the day into 24 hours so the peak hours and the lowest peak hours become visible as taller and shorter bars. The business reason given is operational: during the highest selling hours a company should market more and manage more inventory, because most orders will be received during those specific hours. Combined with the day wise area chart, this gives both a weekly pattern across the seven days of data and an intraday pattern within each day.

Q: How does drill through to a details page work in this Power BI project?

The dashboard has a navigation bar with a details tab. That details page is an exportable sheet showing row wise information for every order in the data. It also serves as a drill through target, so if you want to see all the data for a province such as Florida, you use the drill through feature on that selection and the details sheet is filtered to only the Florida records. From there the filtered rows can be exported wherever they are needed.

Q: What data is used in this Shopify Power BI project and is it real?

The data comes from a small scale company whose name cannot be revealed. Because of security reasons and data privacy, the entire dataset is not shown. Instead, a sample covering one week is used, running from 18th of June to 24th of June, and some values inside it were changed so that it is not the complete original data but dummy data suitable for analysis. This is why the project is presented as a kind of realtime project rather than a purely synthetic exercise.

Summary & Key Takeaways

• The project builds a Shopify sales and customer funnel report in Power BI, presented as a realtime-style project because the underlying data comes from an actual small scale company. The name of the company cannot be revealed, and the shared file is one week of data with some values changed, so it is dummy data suitable for analysis without exposing private information.

• Two dashboards are produced. The first is a complete analysis dashboard carrying KPIs, a regional map, a city bar chart, time trend charts and payment gateway and product type views. The second is a details page holding row wise information for every order, reachable through drill through and exportable, so a user can pull the underlying records for a selected province such as Florida.

• The KPIs are grouped into three families. Transaction performance covers net sales, which the instructor notes is different from total sales, plus total quantity and net average order value. Customer purchase behavior covers total customers, single order customers and repeat customers. Retention and value covers LTV, repeat rate percentage and frequency of purchase.

• Interactivity is the core design idea. A measure changer filter swaps which metric drives the regional charts, so the same map and bar chart can show net sales, quantity or total customers. Clicking a state, a day, a payment gateway such as Shopify payments or PayPal, or a product such as running shoes filters the whole dashboard to that selection.

• The stated project steps begin with requirement gathering from the customer, followed by a data walkthrough of the raw data, connecting the data to Power BI, and data cleaning where required. Every step of the dashboard build is promised to be shown from start to end, along with how insights are generated and how each business requirement is met.