Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 56 ยท Projects

Project: E-commerce Analytics Dashboard

Build an e-commerce dashboard in Power BI: revenue, orders, average order value, repeat purchases, returns, a conversion funnel and product and channel analysis.

An online store produces data in several shapes at once: orders and order lines from the shop system, sessions and clicks from web analytics, refunds from customer service, and a product catalog. A good e-commerce dashboard joins them and answers the questions a merchandising or marketing manager asks every Monday: how much did we sell, how large is the typical basket, do customers come back, what do they return, where does traffic convert, and which products carry the business?

This project walks through the model, the measures, the report pages and the checks. Power BI cannot run on this site, so the steps are described and the DAX is read-only. The runnable examples are pandas stand-ins on a synthetic store (900 orders over six months, eight products, four channels). They compute the numbers your finished report should show.

What the dashboard answers

The Monday questions

Sales Revenue, orders and average order value.
Loyalty Returning customers and repeat purchase rate.
Returns What comes back, and how much it costs.
Funnel Where visitors drop off.
1 / 2

The model

Build a star schema (Star Schema) with these tables:

  • Order Lines (the fact table, one row per product in an order): OrderID, ProductID, Quantity, Unit Price, Returned Quantity, Refund Amount.
  • Orders: OrderID, CustomerID, Order Date, Channel. One row per order.
  • Products: ProductID, Product, Category, Price.
  • Customers: CustomerID, name or segment.
  • Date, as in Date Tables and Time Intelligence Setup.
  • Traffic: Date, Channel, Sessions, Product Views, Add to Cart, Checkouts (daily counts from web analytics).

Orders is both a dimension of the lines and a fact of its own, so relate Orders[OrderID] one-to-many to Order Lines[OrderID], 'Date'[Date] to Orders[Order Date] and to Traffic[Date], Customers to Orders, and Products to Order Lines. For Channel, build a small Channel dimension (Search, Email, Social, Direct) related to both Orders and Traffic so one slicer filters both.

The measures

DAX
Revenue = SUMX ( 'Order Lines', 'Order Lines'[Quantity] * 'Order Lines'[Unit Price] )

Orders = DISTINCTCOUNT ( Orders[OrderID] )

Average Order Value = DIVIDE ( [Revenue], [Orders] )

Customers = DISTINCTCOUNT ( Orders[CustomerID] )

Returning Customers =
VAR StartDate = MIN ( 'Date'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Orders[CustomerID] ),
            CALCULATE ( MIN ( Orders[Order Date] ), REMOVEFILTERS ( 'Date' ) ) < StartDate
        )
    )

Repeat Purchase Rate =
DIVIDE (
    COUNTROWS ( FILTER ( VALUES ( Orders[CustomerID] ), CALCULATE ( [Orders], REMOVEFILTERS ( 'Date' ) ) >= 2 ) ),
    [Customers]
)

Units Sold = SUM ( 'Order Lines'[Quantity] )
Returned Units = SUM ( 'Order Lines'[Returned Quantity] )
Return Rate = DIVIDE ( [Returned Units], [Units Sold] )
Refunds = SUM ( 'Order Lines'[Refund Amount] )
Net Revenue = [Revenue] - [Refunds]

Conversion Rate = DIVIDE ( [Orders], SUM ( Traffic[Sessions] ) )
Cart Rate = DIVIDE ( SUM ( Traffic[Add to Cart] ), SUM ( Traffic[Product Views] ) )

Notes on each:

  • Average Order Value (AOV) divides revenue by orders, not by lines. The base measures are separate so the ratio recalculates correctly at any level (the lesson DAX Basics explains why a ratio of sums beats an average of ratios).
  • Returning Customers counts customers in the selected period whose first-ever order is before the period. REMOVEFILTERS('Date') inside CALCULATE makes the MIN look at all history. The complement, [Customers] - [Returning Customers], is the number of new customers.
  • Repeat Purchase Rate is the share of customers with two or more orders in all of history. It is a lifetime figure, so quote it with the observation window ("69.9 percent of customers have ordered at least twice") and do not compare a six-month window with a three-year one.
  • Return Rate has two flavors: by units and by value. Report both, as the example below shows that they disagree.
  • Conversion Rate is orders divided by sessions. It depends on a clean Channel dimension shared by Orders and Traffic.

Check them before you trust them

Python Stand-in: monthly revenue, orders, AOV, returning customers, repeat rate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

January shows zero returning customers, which is correct: the data starts in January, so nobody has an earlier order. Be careful with this in the first period of any dataset, and when someone asks why "everyone is new" in the first month. The check on the last line, that the months add up to the grand total, is the habit that catches broken date relationships.

Returns, channels and the product mix

Returns hide in plain sight. Revenue looks good until refunds are subtracted, and the categories that return most are not always the ones that cost the most. The second stand-in builds the return analysis, the channel funnel and the product ranking.

Python Stand-in: returns by category, channel conversion and a Pareto of products

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three findings stand out, each of which would become a visual:

  • Apparel is returned most often by units (17.3 percent), but Furniture costs the most in refunded money (11,300 against 8,220), because a returned desk is worth many returned T-shirts. A return report by units alone points at the wrong category.
  • Email converts best (7.5 percent of sessions end in an order) and Social converts worst (3.1 percent) even though Social brings seven thousand sessions. Direct has the highest basket (AOV 235.9). That is a different decision for each channel: grow Email, fix the landing pages for Social.
  • Two products, Desk and Chair, make 64 percent of revenue. This Pareto (80/20) view tells you where stock-outs and price changes hurt most. In Power BI a cumulative share needs a ranking measure and a running total: see Ranking, Top N and Percent of Total and the window functions in Time Intelligence.

The report pages

  1. Overview. Cards for Revenue, Net Revenue, Orders, Average Order Value and Return Rate; a line chart of revenue by month with a previous-year line (Time Intelligence); a date slicer and a channel slicer.
  2. Customers. New against returning customers by month (stacked column), the Repeat Purchase Rate card and a table of top customers by revenue.
  3. Products. A bar chart of revenue by product, a Pareto line (cumulative share), a treemap by category and a matrix of category by month with conditional formatting (Conditional Formatting, KPIs and Cards).
  4. Returns. Return rate by category in units and in value side by side, and a table of the most returned products. A drill-through page shows the individual returned lines (Drill-through, Tooltips, Bookmarks and Buttons).
  5. Funnel and channels. A funnel visual (sessions, product views, add to cart, checkouts, orders, see Choosing the Right Visual), conversion rate by channel and a scatter chart of sessions against conversion, sized by revenue.

Keep the same filters across pages with synced slicers (Filters, Slicers and Sync) and give each chart a title that states its point, such as "Email converts twice as well as Social".

Final checks

  • The sum of monthly revenue equals the card total, and net revenue equals revenue minus refunds, at every level.
  • Orders are counted with DISTINCTCOUNT of OrderID, never as rows of the lines table.
  • A returned line is not deleted from revenue history. Refunds are shown as their own measure, so the report can display gross and net.
  • Currency, time zone and the order date definition (placed, paid or shipped) are written on the report.
  • Large tables use Import mode with an incremental refresh policy (Query Folding and Incremental Refresh).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is Average Order Value defined as Revenue divided by Orders and not as the average of the line totals?

    AOV describes baskets. Dividing by the distinct count of orders keeps the unit of analysis right.
  2. 2. January shows zero returning customers. What does this tell you?

    A returning customer needs a first order before the period. With no history before January, the number is correctly zero.
  3. 3. Which finding does a return report by units alone hide?

    A category with a high unit return rate can cost less than one with fewer but more expensive returns, so report units and value.
  4. 4. What does a Pareto chart of products show?

    It ranks products and plots the cumulative percentage, showing how concentrated the business is.
  5. 5. Why do Orders and Traffic share a Channel dimension?

    Two fact tables that must be sliced by the same attribute need a shared dimension. Without it, a channel slicer filters only one of them.

Summary

  • Model orders, lines, products, customers, dates and traffic as a star schema, with a shared Channel dimension.
  • Measure revenue with an iterator, orders with DISTINCTCOUNT, and AOV as a ratio of sums; build returning customers and repeat rate with REMOVEFILTERS('Date').
  • Report returns by units and value, funnels and conversion by channel, and product concentration with a Pareto.
  • Verify with pandas and with totals that must add up, and remember that the first period of any history has no returning customers.

Next: Project: Risk and Fraud Monitoring Dashboard.