Building a Sales Dashboard: A Hands-On Project

A dashboard project is one of the most useful things a data analyst can build for a portfolio, because it exercises the whole job: understanding what a business needs to know, cleaning messy data, defining metrics precisely, and presenting them so the right numbers are seen first. This walkthrough builds the analytical core of a sales dashboard from a real dataset: every transaction of a UK online gift retailer from December 2010 to December 2011 (UCI: Online Retail).

The calculations are done in Python with pandas, so each number can be checked. The same steps apply in Excel, Power BI or any BI tool.

Step 1: Start from questions, not charts

Imagine the sales manager's questions:

• How much are we selling, and is it growing?
• Is growth coming from more orders, bigger orders or more customers?
• Which countries and products drive revenue?
• Are returns or data problems distorting the picture?

Each question maps to a key performance indicator (KPI) with a precise definition. Write the definitions down before any chart:

Ambiguous definitions are the main reason two dashboards disagree. "Revenue" with or without postage, returns or discounts can differ by several percent.

Step 2: Clean the data, and count what you remove

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})

services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
is_cancellation = raw["InvoiceNo"].str.startswith("C")
is_service = raw["StockCode"].isin(services) | raw["StockCode"].str.startswith("gift_")
print(f"{len(raw)} lines | cancellations {is_cancellation.sum()} | postage, fees and vouchers {is_service.sum()} | "
      f"zero or negative price {(raw['UnitPrice'] <= 0).sum()} | no customer ID {raw['CustomerID'].isna().sum()}")
sales = raw[~is_cancellation & ~is_service & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]
sales = sales.assign(revenue=sales["Quantity"] * sales["UnitPrice"])
print(f"{len(sales)} product sale lines, revenue {sales['revenue'].sum():,.0f}")

541909 lines | cancellations 9288 | postage, fees and vouchers 2946 | zero or negative price 2517 | no customer ID 135080
527761 product sale lines, revenue 10,271,433

The dataset's documentation says invoice numbers starting with C are cancellations, and prices are in pounds sterling. Reading the stock codes that are not ordinary products shows postage, carriage, manual adjustments, discounts, samples, bank charges, marketplace fees, a charity commission and gift vouchers; these are not product sales and would distort product and order metrics. Lines with zero or negative prices or quantities are adjustments. A quarter of lines have no customer ID, which matters later. Recording each exclusion with its count is what lets someone else trust, or challenge, your numbers.

Step 3: The monthly KPI table

December 2011 is only partly covered by the data, so the dashboard period is January to November 2011, complete months only:

period = sales[(sales["InvoiceDate"] >= "2011-01-01") & (sales["InvoiceDate"] < "2011-12-01")]
monthly = period.groupby(period["InvoiceDate"].dt.to_period("M")).agg(
    revenue=("revenue", "sum"), orders=("InvoiceNo", "nunique"), customers=("CustomerID", "nunique"))
monthly["avg_order"] = monthly["revenue"] / monthly["orders"]
monthly["revenue_vs_prev"] = monthly["revenue"].pct_change()
print(monthly.round({"revenue": 0, "avg_order": 0, "revenue_vs_prev": 3}).to_string())

               revenue  orders  customers  avg_order  revenue_vs_prev
InvoiceDate                                                          
2011-01       671933.0    1081        739      622.0              NaN
2011-02       508886.0    1093        757      466.0           -0.243
2011-03       691258.0    1440        973      480.0            0.358
2011-04       516218.0    1235        853      418.0           -0.253
2011-05       741192.0    1668       1054      444.0            0.436
2011-06       738752.0    1525        990      484.0           -0.003
2011-07       689364.0    1452        946      475.0           -0.067
2011-08       725514.0    1339        933      542.0            0.052
2011-09      1030475.0    1818       1259      567.0            0.420
2011-10      1106670.0    2005       1361      552.0            0.074
2011-11      1457746.0    2751       1660      530.0            0.317

This table already answers the first two questions. Revenue is flat at roughly 500,000 to 740,000 a month through August, then climbs to almost 1.46 million in November, the run-up to Christmas for a gift retailer. The growth comes from more orders and more customers; average order value moves between about 420 and 620 with no upward trend. Month-on-month changes swing by 25% or more in spring, so the dashboard should show a trend line, not just the latest change, or a single bad month will cause needless alarm. Customer counts include only orders with an ID, a caveat to state on the dashboard.

Step 4: Breakdowns and data-quality measures

country_share = period.groupby("Country")["revenue"].sum().sort_values(ascending=False) / period["revenue"].sum()
print("revenue share by country:", country_share.head(4).round(3).to_dict())
print("top products:", period.groupby("Description")["revenue"].sum().nlargest(3).round(0).to_dict())
returns = raw[is_cancellation & ~is_service & raw["InvoiceDate"].between("2011-01-01", "2011-11-30 23:59")]
print(f"returns as share of revenue: {-(returns['Quantity'] * returns['UnitPrice']).sum() / period['revenue'].sum():.1%}")
print(f"revenue from orders with no customer ID: {period.loc[period['CustomerID'].isna(), 'revenue'].sum() / period['revenue'].sum():.1%}")

revenue share by country: {'United Kingdom': 0.841, 'Netherlands': 0.03, 'EIRE': 0.029, 'Germany': 0.021}
top products: {'REGENCY CAKESTAND 3 TIER': 140608.0, 'PARTY BUNTING': 97378.0, 'WHITE HANGING HEART T-LIGHT HOLDER': 92852.0}
returns as share of revenue: 3.2%
revenue from orders with no customer ID: 13.5%

The United Kingdom brings 84% of revenue, so a country chart should show the UK alongside a separate view of international markets, or the other countries become unreadable slivers. A three-tier cake stand is the top product. Returns take 3.2% of revenue, small but worth a KPI card. And 13.5% of revenue has no customer ID, which limits any customer-level analysis; a dashboard that shows customer metrics should say so.

Step 5: Model the data for a BI tool

In Power BI or a similar tool, load the cleaned data as a star schema: a fact table of sale lines with invoice, date, product, customer, quantity and revenue, surrounded by dimension tables for dates, products and customers (Microsoft Learn: star schema). Define each KPI once as a measure, following the definitions from step 1, so every visual uses the same logic. A date table makes month-over-month and year-to-date comparisons straightforward.

Step 6: Lay out the dashboard

Microsoft's dashboard design guidance advises considering who will use the dashboard and which metrics drive their decisions, putting the highest-level information at the top left and adding detail in reading order, and avoiding scroll bars (Microsoft Learn: dashboard design tips). For this project:

1. Top row, KPI cards: revenue, orders, average order value and return rate for the selected month, each with the change from the previous month.
2. Middle, trend: monthly revenue as a line chart, with orders on a second small chart below it.
3. Bottom left, where: revenue by country, UK and international views.
4. Bottom right, what: top products by revenue.
5. Filters: month range and country. Keep to a few; every filter is a question the user must answer.
6. Footnote: the exclusions from step 2 and the missing customer IDs.

Dashboard design principles covers layout, chart choice and colour in more depth.

Step 7: Check before you share

• Reconcile totals: the revenue on the dashboard should match the cleaned total from step 2 for the same period.
• Spot-check a few invoices by hand against the raw data.
• Test the filters: selecting a country should change every visual consistently.
• Ask a colleague to answer the four questions from step 1 using only the dashboard.

Dashboard creation in Excel and Power BI, and sales and revenue analysis, are part of the Data Analytics course.

Sources

• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Understand star schema and the importance for Power BI (Microsoft Learn) — Microsoft Learn
• Tips for designing a great Power BI dashboard (Microsoft Learn) — Microsoft Learn