W3SKILLSET

Shopify Power BI Report

By /

Requirement Document for Shopify Power BI Report

1. Introduction

1.1 Purpose

The purpose of this document is to define the requirements for the Power BI Shopify Report to provide a detailed analysis of weekly, monthly, and yearly sales performance, material margins, and customer trends for retail and B2B sales.

1.2 Scope

The report should:

  • Provide an overview of Retail Sales, B2B Sales, and Total Sales.
  • Compare sales performance across different time periods (Week, Month, Year).
  • Track Material Margins for both Retail and B2B.
  • Highlight Top Customers, Top Products, and Department-wise Sales Trends.
  • Offer interactive filters for different periods, departments, and sales categories.

2. Data Sources

  • Shopify Sales Data (API or CSV exports)
  • Order Transactions Data
  • Customer & Product Data
  • Historical Sales & Margin Data

3. Report Features & Functionalities

3.1 Sales Performance Overview

  • Retail Sales:
    • Sales by Department (Tea, Honey, Hospitality, New Products)
    • Year-on-Year Weekly and Monthly Comparisons
    • Material Margin Analysis
    • Retail Sales excluding Delivery
  • B2B Sales:
    • Weekly and Monthly Sales Trends
    • Department-wise Analysis
    • Key Customers & Product Trends
    • Material Margin Analysis
  • Total Sales:
    • Consolidated Sales (Retail + B2B)
    • Year-to-Date (YTD) and Month-to-Date (MTD) Sales
    • Comparative Analysis with Previous Years

3.2 Key Metrics

  • Sales Revenue (Current vs. Last Year)
  • Material Margin %
  • Units Sold
  • Variance Analysis (Current vs. Previous Year)
  • Top Performing Customers & Products
  • Sales Delivery Performance (Retail & B2B)
  • Cumulative Sales Trends (Month-wise)

3.3 Data Visualizations

  • Line Charts for Yearly & Monthly Sales Trends
  • Bar Graphs for Department-wise Comparisons
  • Tables for Weekly & Monthly Sales Breakdown
  • KPI Cards for Key Metrics (Total Sales, Margin %)
  • Filters & Slicers:
    • Date Range (Week, Month, Year)
    • Sales Type (Retail, B2B, Total)
    • Product Category
    • Customer

4. Functional Requirements

4.1 Data Refresh & Automation

  • Data should be refreshed automatically from Shopify API/Database on a weekly basis.
  • Historical sales data should be maintained for trend analysis.

4.2 User Access & Security

  • Access should be role-based:
    • Admin: Full access (can modify filters, export data)
    • Sales Team: Read-only access with filter options
    • Finance Team: Access to margin reports

4.3 Export & Reporting

  • Ability to export data in Excel, CSV, and PDF formats.
  • Scheduled email reports to stakeholders.

5. Performance & Scalability

  • Reports should load within 5 seconds for any filter selection.
  • The report should be optimized to handle large datasets efficiently.

6. Assumptions & Dependencies

  • Shopify API access is available for automated data extraction.
  • Power BI Premium License is available for advanced features.
Shopify ReportDownload

1. Data Extraction & Storage

1.1 Data Sources

  • Shopify API (or CSV Exports)
  • SQL Database (if Shopify data is stored in a relational database)
  • Power Query (for data transformation)
  • Power BI (for reporting and visualization)

1.2 Extract Data using SQL Queries

If the Shopify data is stored in an SQL database, use the following queries to extract sales data:

Retail Sales Data Query

sqlCopySELECT 
    OrderID, OrderDate, CustomerID, ProductID, 
    Quantity, UnitPrice, TotalPrice, SalesChannel, 
    CASE 
        WHEN SalesChannel = 'Online' THEN 'Retail'
        ELSE 'B2B'
    END AS SalesType
FROM ShopifyOrders
WHERE OrderDate >= DATEADD(YEAR, -5, GETDATE());

Customer Data Query

sqlCopySELECT 
    CustomerID, FirstName, LastName, Email, 
    Country, Region, TotalSpent
FROM ShopifyCustomers;

Product Data Query

sqlCopySELECT 
    ProductID, ProductName, Category, CostPrice, 
    SellingPrice, StockQuantity
FROM ShopifyProducts;

2. Data Transformation using Power Query (M Language)

Once data is extracted, Power Query will be used for ETL (Extract, Transform, Load) operations.

2.1 Data Cleaning & Formatting

  • Remove duplicate entries.
  • Convert data types (Dates, Numbers, Currency).
  • Merge tables (e.g., Orders with Customer & Product Data).
  • Handle missing values (replace NULLs with defaults).

2.2 Merging Sales Data with Customers and Products

mCopyMergedSalesData = Table.NestedJoin(Orders, "CustomerID", Customers, "CustomerID", "CustomerDetails", JoinKind.LeftOuter)

2.3 Calculate Profit Margin in Power Query

mCopyAddedColumn = Table.AddColumn(Orders, "ProfitMargin", each ([SellingPrice] - [CostPrice]) / [SellingPrice], type number)

3. Data Modeling & Relationships in Power BI

After transformation, tables are loaded into Power BI and linked via relationships:

  • Orders Table → Linked to Customers Table (CustomerID)
  • Orders Table → Linked to Products Table (ProductID)
  • Date Table → Linked to OrderDate (for time intelligence)

4. DAX Calculations for KPIs and Insights

DAX (Data Analysis Expressions) is used to create custom calculations and measures.

4.1 Sales KPIs

Total Sales

DAXCopyTotalSales = SUM(Orders[TotalPrice])

Total Units Sold

DAXCopyTotalUnitsSold = SUM(Orders[Quantity])

Average Order Value

DAXCopyAvgOrderValue = DIVIDE([TotalSales], DISTINCTCOUNT(Orders[OrderID]))

4.2 Year-over-Year (YoY) Comparisons

Current Year Sales

DAXCopyCurrentYearSales = 
CALCULATE(
    [TotalSales], 
    YEAR(Orders[OrderDate]) = YEAR(TODAY())
)

Last Year Sales

DAXCopyLastYearSales = 
CALCULATE(
    [TotalSales], 
    YEAR(Orders[OrderDate]) = YEAR(TODAY()) - 1
)

Year-over-Year Growth

DAXCopyYoYGrowth = DIVIDE([CurrentYearSales] - [LastYearSales], [LastYearSales])

4.3 Material Margin Analysis

Retail Material Margin

DAXCopyRetailMargin = 
SUMX(
    FILTER(Orders, Orders[SalesType] = "Retail"),
    Orders[TotalPrice] - Orders[CostPrice]
)

B2B Material Margin

DAXCopyB2BMargin = 
SUMX(
    FILTER(Orders, Orders[SalesType] = "B2B"),
    Orders[TotalPrice] - Orders[CostPrice]
)

Overall Margin Percentage

DAXCopyMarginPercentage = DIVIDE(SUM(Orders[TotalPrice] - Orders[CostPrice]), SUM(Orders[TotalPrice]), 0)

4.4 Customer Insights

Top 10 Customers by Revenue

DAXCopyTopCustomers = 
TOPN(10, SUMMARIZE(Customers, Customers[CustomerID], "Total Spent", SUM(Orders[TotalPrice])), [Total Spent], DESC)

4.5 Time Intelligence for Monthly & Weekly Trends

Month-to-Date (MTD) Sales

DAXCopyMTDSales = TOTALMTD([TotalSales], Orders[OrderDate])

Year-to-Date (YTD) Sales

DAXCopyYTDSales = TOTALYTD([TotalSales], Orders[OrderDate])

Week-over-Week Sales Growth

DAXCopyWoWSalesGrowth = 
VAR CurrentWeek = MAX(Orders[WeekNum])
VAR LastWeek = CurrentWeek - 1
RETURN 
DIVIDE(
    CALCULATE([TotalSales], Orders[WeekNum] = CurrentWeek) - 
    CALCULATE([TotalSales], Orders[WeekNum] = LastWeek), 
    CALCULATE([TotalSales], Orders[WeekNum] = LastWeek), 
    0
)

5. Power BI Report Visualization

5.1 Dashboards & Reports

  • Sales Overview (Total Sales, Margin %, YoY Growth)
  • Retail vs B2B Sales Comparison
  • Product Performance (Top Products by Sales & Margin)
  • Customer Analysis (Top Customers by Revenue & Orders)
  • Time-Based Trends (Weekly, Monthly, Yearly Sales)
  • Delivery & Shipping Performance

5.2 Filters & Slicers

  • Date Range (Week, Month, Year)
  • Sales Type (Retail, B2B)
  • Product Category
  • Customer Segments

6. Deployment & Automation

6.1 Data Refresh

  • Automated Refresh Schedule (Daily/Weekly)
  • Power BI Service for Online Reporting
  • Data Alerts & Notifications for key sales milestones

6.2 User Access & Security

  • Role-based access control (Admin, Sales Team, Finance Team)
  • Restricted Access to Sensitive Data (Margins, Customer Info)

7. Conclusion

This completion method ensures a fully automated, interactive, and insightful Shopify sales report in Power BI. By leveraging SQL queries for data extraction, Power Query for transformation, and DAX for advanced analytics, this report enables data-driven decision-making.

Sample data files for Power BI Shopify Report, including the following datasets:

  1. Orders.csv – Contains order transactions
  2. Customers.csv – Includes customer details
  3. Products.csv – List of products with pricing
OrdersDownload
CustomersDownload
ProductsDownload
Scroll to Top