Case Study: Implementing the Shopify Sales and Customer Funnel Report using Power BI

This article details the comprehensive development of a real-time business intelligence project—the Shopify Sales and Customer Funnel Report. Designed for a small-scale company utilizing the Shopify e-commerce platform, the dashboard provides deep analytical insights into transaction performance, customer behavior, and long-term customer value, enabling data-driven decision-making and supporting future business growth.

I. Project Overview and Strategic Goal

The Shopify platform serves as a vital e-commerce engine, allowing individuals and businesses globally to establish online stores, manage sales, process payments, and execute marketing activities. Due to security and data privacy restrictions, the project was executed using a high-level, sample/dummy dataset covering one week of sales in the United States, totalling 7,431 transaction rows.

The overarching goal was to transform this raw sales data into actionable business intelligence by designing an interactive dashboard focused on three primary areas:

1. Transaction Performance: Focusing on the overall health and effectiveness of sales operations.
2. Customer Behavioral Patterns: Analyzing how customers interact with the business, acknowledging that the customer is “king”.
3. Long-term Customer Value (LTV): Crucial for sustaining the long-term growth of the business.

The final output comprised two interconnected dashboards: a primary Complete Analysis Dashboard and a Details Tab designed for granular data inspection via drill-through functionality.

II. Technical Implementation and Data Preparation

The project followed a structured methodology, progressing from requirement gathering and data walkthroughs through to DAX calculation and dashboard development.

Data Quality and Setup

Upon connecting the data (an Excel file) to Power BI, the Power Query Editor was used for necessary data quality assurance. Although the data was already cleaned, checks were performed to ensure 100% data validity and 0% errors across all columns. The project was built using a dark theme with green accents, chosen to align with the visual branding and colour palettes typically associated with the Shopify platform.

Advanced Calculated Columns

A critical implementation detail involved creating new columns using DAX to ensure geographical accuracy and detailed time analysis:

• Location Correction: Since Power BI struggled to correctly geo-locate cities, a calculated column named Location was created. This concatenated the City, Province, and Country (City + , + Province + , + Country) to precisely guide Power BI in plotting the density and bubble maps, ensuring all locations were correctly situated within the US.
• Time Binning: An Hour column was generated using the DAX HOUR function on the Invoice Date field, enabling granular analysis of customer activity and sales volume throughout the 24-hour cycle.
• Customer Identification: A Customer Name column was created by concatenating the first and last names of customers.

III. Core DAX Concepts and Metrics Development

The dashboard’s power derives from nine calculated Key Performance Indicators (KPIs), showcasing advanced DAX knowledge across three core business domains.

1. Transaction Performance KPIs

These metrics focus on high-level revenue and volume statistics, specifically utilising the Subtotal Price field, which represents revenue before tax:

• Net Sales: Calculated using SUM('Subtotal Price').
• Total Quantity: Calculated using SUM(Quantity).
• Net Average Order Value (Net AOV): Calculated as the AVERAGE('Subtotal Price'), representing the average revenue per transaction.

2. Customer Purchase Behavior KPIs

These required careful filtering to distinguish unique visitors from loyal customers:

• Total Customers: Calculated using DISTINCTCOUNT('Customer ID').
• Single Order Customers: This required a complex DAX formula using CALCULATE, COUNTROWS, VALUES, and FILTER functions to count unique customers only if their distinct count of orders (Order Number) was equal to one.
• Repeat Customers: Derived using the same complex logic as Single Order Customers, but filtering for orders where the distinct count was greater than one (> 1), providing an indicator of customer loyalty and trust in the business.

3. Retention and Value KPIs

These metrics provide insights into long-term customer viability:

• Lifetime Value (LTV): Calculated as the simple ratio: Net Sales / Total Customers.
• Repeat Rate: Expressed as a percentage, calculated as: Repeat Customers / Total Customers.
• Purchase Frequency: Calculated as: DISTINCTCOUNT('Order Number') / Total Customers.

IV. Dynamic Analysis and Visualization Techniques

A key technical learning from this project is the implementation of dynamic analysis across the dashboard elements.

Dynamic Measures and Titles

A New Parameter named Select Measure was created to allow users to switch between Net Sales, Total Quantity, Total Customers, and Repeat Customers. This parameter feeds all supporting visualizations, ensuring the entire dashboard adjusts instantaneously.

To complement this, dynamic chart titles were generated. This involved creating an intermediary calculated column (Dynamic Title) using nested IF statements based on the parameter’s order. Specific measures (Map Title, Trend Title, etc.) were then created using the SELECTEDVALUE function to concatenate the selected metric name with a static text string (e.g., “by gateway payment method”), ensuring the titles are always accurate and descriptive of the current view.

Chart Design and Insights Generated

The analysis dashboard featured five primary charts:

1. Regional Overview (Maps and Bar Chart):

   • Field Map (Province Level): Visually highlights top-performing states (e.g., Texas, California) through colour saturation.
   • Bubble Map (City Level): Uses size and density to identify geographical order hotspots.
   • Top Cities Bar Chart: Ranks cities by the selected measure (e.g., sales or customer count) and dynamically filters to show top cities within a selected province.

2. Sales Trend Over Time:

   • Area Chart (Day-wise): Showcased sales volatility over the week.
   • Column Chart (Hourly Trend): Identified peak hours, noting that high sales volumes occur between 8 AM and 11 PM, suggesting these are the optimal times for marketing and inventory management.

3. Payment Method (Donut Chart): Analysed customer preference for payment gateways. The insight derived was that Shopify Payment was the most preferred method, accounting for over 50% of transactions, followed by PayPal, confirming high customer trust in the platform’s native payment system.

4. Product Type (Column Chart): Identified the highest-selling product categories. The analysis revealed that footwear—specifically running shoes, tennis shoes, and walking shoes—were the dominant revenue drivers, suggesting the company specialises in sports-related accessories or clothing.

V. Interactivity, Drill-Through, and Technical Learnings

The dashboard was refined using several advanced Power BI interaction features:

• Drill-Through Implementation: A dedicated Details Tab was created as a grid visual. The drill-through action was enabled on key charts by adding the four dynamic measures (Net Sales, Total Quantity, Total Customers, Repeat Customers) to the drill-through pane. This allows stakeholders to click on a chart element (e.g., a city bubble or a peak hour column) and navigate instantly to the Details Tab, seeing the row-wise raw data responsible for that aggregate figure. This filtered data can then be exported for targeted operational use, such as providing specific city sales data to regional managers.
• Managing Edit Interactions: Crucial steps were taken to manage Edit Interactions across all visuals. When a user filters by selecting a chart member (e.g., clicking on a province in the map), the interaction was set explicitly to Filter rather than merely Highlight other visuals, ensuring all dependent charts and KPIs reflected the selected filtered context.
• Page Navigation: A Page Navigator button was inserted, allowing intuitive movement between the Shopify Analysis and Details Tab dashboards, with the current tab being highlighted in green.
• Filtering: Slicers for Gateway and Province were added to the left-hand panel, providing flexible filter controls for the entire dashboard.

In summary, the Shopify Sales and Customer Funnel Report serves as a comprehensive example of building an advanced, dynamic, and action-oriented BI solution, demonstrating expertise in complex DAX calculation, geo-spatial visualization, and robust interactivity design.