A weekly KPI report: how did the last full week go?

The report

The week at a glance.

Every KPI for Nov 28 to Dec 4, 2011, next to the week before (Nov 21 to 27, 2011) and the same week of 2010 (Nov 29 to Dec 5, 2010).

Definitions

What each number means.

Week

Monday to Sunday, by the date on the invoice line. The report week is Nov 28 to Dec 4, 2011; the week before is Nov 21 to 27, 2011; the same week of 2010 is Nov 29 to Dec 5, 2010, 52 weeks earlier.

Net revenue

Sales before cancellations minus cancelled value: the value of lines on invoices that are not cancellations, minus the value of cancellation lines (invoices starting with C), which count in the week they are cancelled.

Sales before cancellations

The value of lines on invoices that are not cancellations.

Orders

Distinct invoices that are not cancellations.

Average order value (before cancellations)

Sales before cancellations divided by orders.

Identified customers

Distinct customer ids on orders. Lines with no customer id are in orders and revenue but not in this count.

New customers (identified)

Identified customers with no order in the previous 52 weeks (364 days). New plus repeat equals identified customers.

Repeat-customer share (of identified customers)

Identified customers who also had an order in the 52 weeks (364 days) before the week, divided by identified customers. Of 514 identified customers, 470 had also ordered in the previous 52 weeks.

Cancellation invoices, % of all invoices

Cancellation invoices divided by all invoices (orders plus cancellation invoices) in the week. This week: 112 cancellation invoices.

Cancelled value, % of sales before cancellations

Cancelled value divided by sales before cancellations. This week: £3,267 cancelled of £309,382 sales before cancellations.

Units sold

Quantity on lines that are not cancellations.

Lines with no customer id

Sales lines (lines that are not cancellations) with no customer id, as a share of all sales lines in the week.

Flat week

Net revenue within 5% of both the week before and the same week a year earlier.

Change

For counts and amounts, (this week minus the comparison week) divided by the comparison week. For shares, the change is in points: the difference between the two shares as shown.

Top movers

Net revenue this week minus the week before, in pounds. A product or country is ranked only if it had at least £1,000 of net revenue in one of the two weeks. I rank by pounds, not percent, so a tiny base cannot win.

Download

The same report, as a workbook.

A Summary sheet where you pick any of the 12 weeks and the KPIs, both changes, the notes, a chart and the top movers recalculate; a Weekly sheet with every week of the data (average order value before cancellations, new customers, shares and week-over-week change are formulas); Products and Countries sheets that rank the movers with formulas over the Data sheet; plus Definitions and Source. Built with Claude under Zachary's direction. Public data: UCI Online Retail II (CC BY 4.0).

I used Excel at Dasalla Trading Company (2014 to 2016), and I use Excel and Google Sheets in my own business; my Excel skills include pivot tables and VLOOKUP/XLOOKUP. This workbook is a new exercise on public data and holds no company's data.

Download weekly-kpi-report.xlsx ↓

Findings

What the charts say.

1. Net revenue was £306,115 in the week of Nov 28 to Dec 4, 2011: up 3.7% on the week before (£295,233) and down 0.9% on the same week of 2010 (£309,029).

   Chart 1

2. Against the same week of 2010 there were more identified customers (514 against 465) and more orders (663 against 652), but a smaller average order value before cancellations (£467 against £486).

   Chart 2

3. Rabbit night light added £4,168 on the week before and chilli lights lost £3,242; only 57 of the 2,602 products sold in either week had £1,000 or more of net revenue in one of them.

   Chart 3

4. The UK added £18,554; the Netherlands lost £10,694. Countries other than the UK are 9.3% of the week's net revenue, so single orders move them.

   Chart 4

5. The repeat-customer share was 91.4% (up 3.6 points on the week before). Cancellation invoices were 14.5% of all invoices (up 5.5 points on the week before) but 1.1% of sales value before cancellations.

   Chart 5

Method

How the figures were built.

The queries

The query runs on the table retail_orders (531,205 order lines, Dec 2010 to Dec 2011; it holds 13 months, so it has the report week and the week before but not the repeat share, and only Dec 1 to 5 of the same week of 2010). Open it in Live SQL, which runs DuckDB in your browser on the same public tables; the query loads ready to run.

-- The last 12 full weeks (Monday to Sunday), with the change from the week before (Live SQL table: Dec 2010 to Dec 2011)
WITH w AS (
    SELECT date_trunc('week', invoice_date)::DATE AS week_start,
           sum(line_revenue) AS net_revenue,
           count(DISTINCT invoice) FILTER (WHERE NOT is_cancelled) AS orders,
           sum(line_revenue) FILTER (WHERE NOT is_cancelled) / count(DISTINCT invoice) FILTER (WHERE NOT is_cancelled) AS avg_order_value,
           count(DISTINCT customer_id) FILTER (WHERE NOT is_cancelled) AS customers,
           100.0 * count(DISTINCT invoice) FILTER (WHERE is_cancelled) / count(DISTINCT invoice) AS cancel_invoice_pct
    FROM retail_orders
    WHERE invoice_date >= DATE '2011-09-05' AND invoice_date < DATE '2011-12-05'
    GROUP BY 1
)
SELECT week_start,
       round(net_revenue) AS net_revenue_gbp,
       round(100.0 * (net_revenue / lag(net_revenue) OVER (ORDER BY week_start) - 1), 1) AS vs_week_before_pct,
       orders,
       round(avg_order_value) AS avg_order_value_gbp,
       customers,
       round(cancel_invoice_pct, 1) AS cancel_invoice_pct
FROM w
ORDER BY week_start DESC
LIMIT 12

-- Biggest rises and falls in net revenue by product, Nov 28 to Dec 4, 2011 against the week before (Live SQL table; stock codes, no descriptions)
WITH p AS (
    SELECT upper(stock_code) AS code,
           coalesce(sum(line_revenue) FILTER (WHERE invoice_date BETWEEN DATE '2011-11-28' AND DATE '2011-12-04'), 0) AS this_week,
           coalesce(sum(line_revenue) FILTER (WHERE invoice_date BETWEEN DATE '2011-11-21' AND DATE '2011-11-27'), 0) AS week_before
    FROM retail_orders
    GROUP BY 1
), r AS (
    SELECT *, this_week - week_before AS change,
           row_number() OVER (ORDER BY this_week - week_before DESC, code) AS up_rank,
           row_number() OVER (ORDER BY this_week - week_before, code) AS down_rank
    FROM p
    WHERE this_week >= 1000 OR week_before >= 1000          -- the size rule: at least 1,000 in either week
)
SELECT code, round(this_week) AS this_week_gbp, round(week_before) AS week_before_gbp, round(change) AS change_gbp
FROM r
WHERE up_rank <= 5 OR down_rank <= 5
ORDER BY change DESC

Sources

Data and licence

Source

UCI Online Retail II, Chen, D. (2012), UCI Machine Learning Repository

Period

Dec 2009 to Dec 2011 (report week Nov 28 to Dec 4, 2011)

Licence

CC BY 4.0

Charts

5, each with its data table and CSV

Data file

data/analyses/kpi_pack.json

• UCI Online Retail II ↗Chen, D. (2012), UCI Machine Learning Repository. Licence CC BY 4.0.

Licence: CC BY 4.0. Public data.

Download the chart data

Each CSV is made in your browser from the rows in that chart's table.

• Chart 1: Weekly revenue
• Chart 2: Change by KPI
• Chart 3: Product movers
• Chart 4: Country movers
• Chart 5: Repeat and cancellations