Explain an online shop's sales

What you will be able to do

Turn a real online shop’s export of a million invoice lines into a small database that finance can trust. You find the traps in the export, build four tables with keys, and reconcile three different revenue totals to one. Then you answer three planning questions with SQL: when the Christmas season starts, what is behind the return spikes, and whether new customers come back. At the end you deliver a checked monthly reporting table with a lineage note.

Case studyPortfolio piece

Dataset

Online Retail II, UK 2009–2011 (UCI)

Licence

CC BY 4.0

Time

About 2.5 hours

Runs in

Colab, Kaggle or VS Code

Skills used

• Profiling an export
• Keys and orphan checks
• Reconciling totals
• Window functions (LAG, ROW_NUMBER, RANK)
• Cohorts with CTEs
• Validation and lineage

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. To use VS Code, download the notebook from GitHub. Stuck? See the solution notebook.

The notebook has every query of this page, in the same order, with three tasks and their checks. Its setup cells install pandas and download the data (15 MB), so it needs no local setup. It runs in about one minute on a laptop. The database is too large for the SQL boxes in the browser, so this case study runs only in the notebook. Read the explanations here, and run the code there.

Before you startWhat to know and to have readyAbout 150 minutes

What you need to know

• Grain, keys and orphans, from Dataset anatomy and Relational concepts.
• Why a join can add rows, from Join tables: the join adds rows.
• CASE inside an aggregate, from Aggregate correctly.
• CTEs, from Readable queries, and dates, LAG, ROW_NUMBER and RANK, from Time and ranking.
• Rules as queries that must return no rows, from Validation rules, and the lineage of a dataset, from Track lineage.

What you need on your computer (only for the local route; Colab and Kaggle need none of it)

• Python 3.12 or later with pandas 3.0.6. The .venv of your project from Transform with Python has it. The notebook’s first cell installs it if it is missing.
• A new, empty folder for this case study, for example ~/projects/case-studies. Open the notebook there. Its setup cell makes a folder explain-an-online-shops-sales and downloads the data into it. The notebook then builds shop.db (about 100 MB) in that folder. To start again, delete the folder and run the notebook from the top.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4) and pandas 3.0.6, in Jupyter. Window functions need SQLite 3.25 or later. Windows, Colab and Kaggle are not tested.

The situation

A UK online shop sells gifts and homeware. Many of its customers are small shops that buy to resell. It is December 2011, and two people need answers from the sales data.

Ines, the merchandising lead, plans next Christmas. The shop buys its Christmas stock from suppliers months ahead. If she orders too little, the shelves are empty in the best weeks of the year. If she orders too much, the shop pays for stock that nobody buys. She asks: “When does our Christmas season really start, how big is it, and is it growing? Which products sell best in each country?”

Kwame, a finance analyst, has three problems. Three reports give three different revenue totals for the same two years, and he cannot say which one is right. The board saw a return rate of 13.6% in January 2011 and wants a stricter returns policy. And a dashboard says that new customers come back less and less each month. He asks: “Which numbers can I trust, and what do they really say?”

Ines, Kwame and their questions are made up for this case study. The data is real: it is the shop’s own invoice data, published for research.

What you will build

You will build a reporting database. It holds the shop’s sales in four tables with keys, the checks that the tables are complete, and one view of the sales lines. From it, you deliver monthly_sales, a table with one row per month that finance can use, with six validation rules and a lineage note. On the way, you answer each question of Ines and Kwame with a query and its real output.

The case study uses these skills from the course:

• the grain of a table, keys and orphans (Relational concepts);
• joins that add rows without an error (Join tables);
• CTEs that split a long query into steps (Readable queries);
• dates, LAG, ROW_NUMBER and RANK (Time and ranking);
• a profile before any change (Profile before changing);
• validation rules and lineage (Validation rules, Track lineage).

The case study Segment online-shop customers in Machine Learning: From Problem to Reliable Model uses the same data to group the customers with machine learning. This case study comes first in a real project: it makes the numbers trustworthy.

The data

The data is Online Retail II from the UCI Machine Learning Repository. It has every invoice line of the shop from 1 December 2009 to 9 December 2011. Daqing Chen published it under the Creative Commons Attribution 4.0 licence (CC BY 4.0), which allows reuse with credit.

Chen, D. (2012). Online Retail II [Dataset]. UCI Machine Learning Repository. https://doi.org/10.24432/C5CG6D. CC BY 4.0.

The course keeps a fixed copy in its labs repository, with a SHA-256 checksum. The source is an Excel file with two sheets, one per year. The course joined them, in order, into one compressed CSV file and changed nothing else. The dataset card lists this, as the licence requires.

One row of the file is one invoice line: one product on one invoice. The columns, with the names that this case study gives them in SQLite:

Column	What it means
invoice	The invoice number. A number that starts with C is a return (a cancellation). A number that starts with A is an accounting adjustment.
stock_code	The product code, usually five digits, sometimes with a letter (85123A).
description	The product name.
quantity	Units on the line. Negative on a return.
invoice_date	Date and time of the invoice, as text YYYY-MM-DD HH:MM:SS.
price	Price of one unit, in pounds (£).
customer_id	The customer’s number. Empty for some lines.
country	The customer’s country.

Load the export into SQLite

The export is one large table. Before you change anything, you load it as it is, so that you can trace every later number back to it. pandas reads the file, and its to_sql writes the table into a new SQLite database. The code adds one column, line_no: the position of the line in the file. The file has no ID for a line, and line_no becomes that ID.

import sqlite3
from pathlib import Path

import pandas as pd

raw = pd.read_csv("data/online_retail_ii.csv.gz",
                  dtype={"Invoice": str, "StockCode": str, "Customer ID": "Int64"})
raw.columns = ["invoice", "stock_code", "description", "quantity",
               "invoice_date", "price", "customer_id", "country"]
raw.insert(0, "line_no", range(1, len(raw) + 1))  # the line's position in the file

Path("shop.db").unlink(missing_ok=True)  # start from an empty database
con = sqlite3.connect("shop.db")
raw.to_sql("raw_lines", con, index=False)
print(f"raw_lines: {len(raw):,} lines, {raw.shape[1]} columns")

raw_lines: 1,067,371 lines, 9 columns

What this tells us. The table raw_lines holds the 1,067,371 lines of the file, unchanged. invoice and stock_code are read as text, because some contain letters. Int64 keeps the customer number a whole number, with empty values as NULL. From now on, every query in this case study is SQL. In the notebook, sql(query) runs a query and shows the result, like q.py in the course.

Now look at the size of the export:

SELECT COUNT(*)                   AS lines,
       COUNT(DISTINCT invoice)    AS invoices,
       COUNT(DISTINCT stock_code) AS stock_codes,
       COUNT(DISTINCT customer_id) AS customers,
       MIN(invoice_date)          AS first_line,
       MAX(invoice_date)          AS last_line
FROM raw_lines

lines    invoices  stock_codes  customers  first_line           last_line
-------  --------  -----------  ---------  -------------------  -------------------
1067371  53628     5305         5942       2009-12-01 07:45:00  2011-12-09 12:50:00
1 row

And at the first four lines:

SELECT line_no, invoice, stock_code, description, quantity,
       invoice_date, price, customer_id, country
FROM raw_lines
WHERE line_no <= 4

line_no  invoice  stock_code  description                          quantity  invoice_date         price  customer_id  country
-------  -------  ----------  -----------------------------------  --------  -------------------  -----  -----------  --------------
1        489434   85048       15CM CHRISTMAS GLASS BALL 20 LIGHTS  12        2009-12-01 07:45:00  6.95   13085        United Kingdom
2        489434   79323P      PINK CHERRY LIGHTS                   12        2009-12-01 07:45:00  6.75   13085        United Kingdom
3        489434   79323W       WHITE CHERRY LIGHTS                 12        2009-12-01 07:45:00  6.75   13085        United Kingdom
4        489434   22041       RECORD FRAME 7" SINGLE SIZE          48        2009-12-01 07:45:00  2.1    13085        United Kingdom
4 rows

What this tells us. The export covers two years and nine days: 53,628 invoices, 5,305 product codes and 5,942 customers. The first four lines are one invoice, 489434: customer 13085 bought four products at 07:45 on 1 December 2009. So the grain of the export is one line of an invoice. Facts about the invoice, such as the time, the customer and the country, repeat on every line. Line 3 already shows a small surprise: its description starts with a space.

Frame the questions

Each question needs a clear unit and a clear definition, or two people will compute two different answers.

• Revenue. Kwame’s reports must count the same thing. Here, revenue is net sales of goods: the value of the goods sold, minus the value of the goods returned, in pounds. Postage, fees and accounting adjustments are real money, but they are not sales of products. You decide this before you write the query.
• The unit of each answer. Monthly sales and returns use one row per month. The retention question uses one row per customer, and only customers with an ID. A line without a customer is still a sale, so it counts in revenue but not in customer numbers.
• Complete periods. The data stops on 9 December 2011. Do not compare a month of nine days with a full month. The same is true for the start: the shop sold before December 2009, and the data does not show it.
• Customer IDs. They are numbers that the shop gave. There are no names or addresses. Do not try to find out who a customer is.

First checks: find the traps

A profile counts the problems before you change anything, as in Profile before changing. Each query below asks one question.

SELECT COUNT(*)                                          AS lines,
       SUM(customer_id IS NULL)                          AS no_customer,
       ROUND(100.0 * SUM(customer_id IS NULL) / COUNT(*), 1) AS pct_no_customer,
       SUM(invoice LIKE 'C%')                            AS return_lines,
       SUM(invoice LIKE 'A%')                            AS adjustment_lines,
       SUM(price <= 0)                                   AS price_0_or_below
FROM raw_lines

lines    no_customer  pct_no_customer  return_lines  adjustment_lines  price_0_or_below
-------  -----------  ---------------  ------------  ----------------  ----------------
1067371  243007       22.8             19494         6                 6207
1 row

What this tells us. 22.8% of the lines have no customer ID. They are probably guests who did not register, and their purchases are real sales. 19,494 lines are returns and 6 are adjustments. 6,207 lines have a price of 0 or below. None of these is an error yet; each one needs a decision.

The repeated lines

Next, look for lines that are identical in every column:

WITH repeated AS (
    SELECT invoice_date, COUNT(*) AS copies
    FROM raw_lines
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
    HAVING COUNT(*) > 1
)
SELECT COUNT(*)        AS groups,
       SUM(copies - 1) AS extra_lines,
       SUM(CASE WHEN invoice_date >= '2010-12-01' AND invoice_date < '2010-12-10'
                THEN copies - 1 ELSE 0 END) AS extra_lines_1_to_9_dec_2010
FROM repeated

groups  extra_lines  extra_lines_1_to_9_dec_2010
------  -----------  ---------------------------
32907   34335        22844
1 row

What this tells us. 32,907 lines appear more than once, which gives 34,335 extra lines. Two thirds of the extra lines (22,844) are from nine days, 1 to 9 December 2010. Mistakes do not cluster in nine days by chance. Remember how the course made the file: one Excel sheet per year, joined in order. If both sheets contain those nine days, the file holds them twice.

You can test this idea with a window function. The lines are in time order inside each sheet. So time should go backwards once in the file: where the second sheet starts. LAG(invoice_date) OVER (ORDER BY line_no) gives each line the time of the line before it, in file order:

WITH in_file_order AS (
    SELECT line_no, invoice, invoice_date,
           LAG(invoice_date) OVER (ORDER BY line_no) AS previous_date
    FROM raw_lines
)
SELECT line_no, invoice, previous_date, invoice_date
FROM in_file_order
WHERE invoice_date < previous_date

line_no  invoice  previous_date        invoice_date
-------  -------  -------------------  -------------------
525462   536365   2010-12-09 20:01:00  2010-12-01 08:26:00
1 row

What this tells us. In more than a million lines, time goes backwards exactly once: at line 525,462, from 9 December 2010 back to 1 December 2010. That is where the second sheet starts. Now compare the nine days in the two sheets, line by line:

WITH nine_days AS (
    SELECT *, CASE WHEN line_no < 525462 THEN 1 ELSE 2 END AS sheet
    FROM raw_lines
    WHERE invoice_date >= '2010-12-01' AND invoice_date < '2010-12-10'
),
per_line AS (
    SELECT SUM(sheet = 1) AS in_sheet_1, SUM(sheet = 2) AS in_sheet_2
    FROM nine_days
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
)
SELECT (SELECT COUNT(*) FROM nine_days WHERE sheet = 1) AS lines_sheet_1,
       (SELECT COUNT(*) FROM nine_days WHERE sheet = 2) AS lines_sheet_2,
       COUNT(*)                                     AS different_lines,
       (SELECT ROUND(SUM(quantity * price)) FROM nine_days WHERE sheet = 2) AS amount_sheet_2
FROM per_line
WHERE in_sheet_1 <> in_sheet_2

lines_sheet_1  lines_sheet_2  different_lines  amount_sheet_2
-------------  -------------  ---------------  --------------
22523          22523          0                377488.0
1 row

What this tells us. Each sheet has 22,523 lines for those nine days, and not one line is different. Even a line that appears twice in the first sheet appears twice in the second. So the second sheet’s nine days are an exact copy, worth £377,488. Any report that sums the raw lines counts them twice. You remove these 22,523 lines.

What about the other repeats? Look at what is left after you remove the copy:

WITH one_copy AS (
    SELECT * FROM raw_lines
    WHERE NOT (line_no >= 525462 AND invoice_date < '2010-12-10')
),
repeated AS (
    SELECT COUNT(*) AS copies, quantity * price AS amount
    FROM one_copy
    GROUP BY invoice, stock_code, description, quantity,
             invoice_date, price, customer_id, country
    HAVING COUNT(*) > 1
)
SELECT COUNT(*) AS groups, SUM(copies - 1) AS extra_lines,
       ROUND(SUM((copies - 1) * amount)) AS extra_amount,
       (SELECT ROUND(SUM(quantity * price)) FROM one_copy) AS all_lines_amount
FROM repeated

groups  extra_lines  extra_amount  all_lines_amount
------  -----------  ------------  ----------------
11001   11812        54228.0       18909762.0
1 row

What this tells us. 11,812 repeats are inside one sheet. They may be the same product typed twice on one invoice, which a shop’s system can record on purpose. Nobody can prove that they are copies, and together they are £54,228, 0.3% of the amount. So you keep them and write the choice down as a limit. This is the difference between a proven copy and a suspicious repeat. SELECT DISTINCT * would remove both without telling you.

Invoices and products

To split the export into tables, each fact must belong to one table. An invoice should have one customer, one country and one time. Check it:

WITH per_invoice AS (
    SELECT invoice,
           COUNT(DISTINCT customer_id)  AS customer_ids,
           COUNT(DISTINCT country)      AS countries,
           COUNT(DISTINCT invoice_date) AS times,
           MIN(invoice_date)            AS first_time,
           MAX(invoice_date)            AS last_time
    FROM raw_lines
    GROUP BY invoice
)
SELECT COUNT(*)                AS invoices,
       SUM(customer_ids > 1)   AS several_customers,
       SUM(countries > 1)      AS several_countries,
       SUM(times > 1)          AS several_times,
       MAX(ROUND((julianday(last_time) - julianday(first_time)) * 24 * 60)) AS max_minutes_apart,
       SUM(substr(first_time, 1, 7) <> substr(last_time, 1, 7)) AS cross_a_month
FROM per_invoice

invoices  several_customers  several_countries  several_times  max_minutes_apart  cross_a_month
--------  -----------------  -----------------  -------------  -----------------  -------------
53628     0                  0                  83             9.0                0
1 row

What this tells us. Every invoice has one customer and one country, so these facts can move to an invoices table. 83 invoices have two times, at most 9 minutes apart. They were probably entered over a few minutes. None of them crosses into another month, so the choice of time cannot move sales between months. You keep the first time.

A product should have one code and one name:

SELECT COUNT(DISTINCT stock_code)        AS codes,
       COUNT(DISTINCT UPPER(stock_code)) AS codes_ignoring_case,
       (SELECT COUNT(*) FROM (
            SELECT stock_code FROM raw_lines
            GROUP BY stock_code
            HAVING COUNT(DISTINCT description) > 1)) AS codes_with_several_descriptions
FROM raw_lines

codes  codes_ignoring_case  codes_with_several_descriptions
-----  -------------------  -------------------------------
5305   5132                 1232
1 row

What this tells us. There are 5,305 codes, but only 5,132 when you ignore upper and lower case. So the same product appears as 15056BL and 15056bl, for example. And 1,232 codes have more than one description. Look at one of them, the three-tier cake stand:

SELECT description, COUNT(*) AS lines,
       SUM(price > 0)           AS lines_with_a_price,
       SUM(customer_id IS NULL) AS lines_without_customer
FROM raw_lines
WHERE stock_code = '22423'
GROUP BY description
ORDER BY lines DESC

description               lines  lines_with_a_price  lines_without_customer
------------------------  -----  ------------------  ----------------------
REGENCY CAKESTAND 3 TIER  4412   4408                635
damaged                   3      0                   3
smashed                   2      0                   2
faulty                    2      0                   2
damages                   2      0                   2
wonky bottom/broken       1      0                   1
broken, uneven bottom     1      0                   1
NULL                      1      0                   1
8 rows

What this tells us. The real name is on 4,412 lines. The other “names” are stock notes: damaged, smashed, faulty. They are on lines with no price and no customer, so the warehouse used the sales system to record broken goods. These lines are not sales. A products table keeps the name used most often.

Last, the codes that do not start with five digits. GLOB matches text against a pattern, like LIKE, but it is case-sensitive and uses other wildcards: [0-9] is one digit and * is any text. So '[0-9][0-9][0-9][0-9][0-9]*' means “five digits, then anything”:

SELECT stock_code, COUNT(*) AS lines, MIN(description) AS example,
       ROUND(SUM(quantity * price)) AS amount
FROM raw_lines
WHERE stock_code NOT GLOB '[0-9][0-9][0-9][0-9][0-9]*'
GROUP BY stock_code
ORDER BY lines DESC

stock_code    lines  example                              amount
------------  -----  -----------------------------------  ---------
POST          2122   POSTAGE                              112341.0
DOT           1446   DOTCOM POSTAGE                       322647.0
M             1421   Manual                               -82796.0
C2            282    CARRIAGE                             13386.0
D             177    Discount                             -13485.0
S             104    SAMPLES                              -6066.0
BANK CHARGES  102     Bank Charges                        -35563.0
ADJUST        67     Adjustment by Peter on 24/05/2010 1  6835.0
AMAZONFEE     43     AMAZON FEE                           -260764.0
DCGS0058      31     MISO PRETTY  GUM                     35.0
gift_0001_30  29     Dotcomgiftshop Gift Voucher £30.00   610.0
gift_0001_20  29     Dotcomgiftshop Gift Voucher £20.00   471.0
DCGSSGIRL     25     GIRLS PARTY BAG                      296.0
DCGSSBOY      23     BOYS PARTY BAG                       251.0
PADS          19     PADS TO MATCH ALL CUSHIONS           -37.0
gift_0001_10  16     Dotcomgiftshop Gift Voucher £10.00   126.0
CRUK          16     CRUK Commission                      -7933.0
TEST001       15     This is a test product.              203.0
DCGS0076      15     SUNJAR LED NIGHT NIGHT LIGHT         292.0
DCGS0003      14     BOXED GLASS ASHTRAY                  33.0
62 rows (showing 20; use limit=None to see all)

What this tells us. Most of these codes are not goods. They are postage (POST, DOT, C2), manual corrections (M), discounts, samples, bank charges, marketplace fees, a charity commission (CRUK), test lines and gift vouchers. Amazon fees alone are −£260,764. They belong in the accounts, but not in sales of products. DCGS0058 (chewing gum) and PADS are real products with unusual codes. So a rule such as “five digits only” would be wrong. Instead, you list the non-goods codes by name. The full list is in the next section.

Build four tables with keys

Each check above led to a decision. Kwame agrees with them, because each one follows from his definition of revenue:

Problem found	Decision	Reason
The second sheet repeats 1 to 9 Dec 2010	Remove the 22,523 copied lines	They are a proven copy.
11,812 repeats inside one sheet	Keep, and record as a limit	They may be real; £54,228 at most.
Codes in upper and lower case	Use UPPER(stock_code)	15056bl and 15056BL are one product.
Several descriptions for a code	Keep the name used most often	Stock notes are not names.
Two times on one invoice	Keep the first time	At most 9 minutes; no month changes.
Postage, fees, adjustments, vouchers	Keep in the tables, with is_goods = 0	Real money, but not sales of goods.
No customer ID	Keep, with customer_id NULL	A guest’s purchase is a sale.

The tables keep everything that happened, except the copy. The business rules (goods only, price above 0) go into a view later. This way, a new rule never needs a new load.

The diagram shows the four tables. Unlike Larkfield’s export in Relational concepts, you build these tables yourself, so you can give them real constraints.

The reporting database: customers 1 to many invoices, invoices 1 to many lines, products 1 to many lines. Each box shows its grain and its keys.

Text description of the diagram

The diagram has four boxes. customers (one row is one customer) has customer_id as its primary key (PK) and the country. invoices (one row is one invoice) has invoice (PK), invoiced_at, customer_id as a foreign key (FK) with the note “NULL for a guest”, the country, and kind, with the values sale, return or adjustment. invoice_lines (one row is one line) has line_no (PK, the position in the file), invoice (FK), stock_code (FK), quantity and unit_price. products (one row is one product) has stock_code (PK), description and is_goods, with the note “0 for postage, fees”. A line labelled “places” connects customers to invoices: an invoice has zero or one customer, and a customer has zero or more invoices. A line labelled “has 1 or more” connects invoices to invoice_lines. A line labelled “is on” connects products to invoice_lines: a product is on zero or more lines.

The first step makes a view of the lines without the copy, and the four empty tables. A view is a saved query that you can use like a table: source_lines stores no rows, and every query that reads it runs its SELECT again. UPPER() writes a text in capital letters. In the tables, CHECK (...) is a constraint like PRIMARY KEY: SQLite refuses a row for which the condition is false.

-- One copy of each line: sheet 2 repeats 1 to 9 December 2010 of sheet 1.
-- Codes are upper-cased: '15056bl' and '15056BL' are one product.
CREATE VIEW source_lines AS
SELECT line_no, invoice, UPPER(stock_code) AS stock_code, description,
       quantity, invoice_date, price, customer_id, country
FROM raw_lines
WHERE NOT (line_no >= 525462 AND invoice_date < '2010-12-10');

CREATE TABLE products (
    stock_code  TEXT PRIMARY KEY,
    description TEXT,
    is_goods    INTEGER NOT NULL CHECK (is_goods IN (0, 1))
);
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    country     TEXT NOT NULL
);
CREATE TABLE invoices (
    invoice     TEXT PRIMARY KEY,
    invoiced_at TEXT NOT NULL,
    customer_id INTEGER REFERENCES customers (customer_id),
    country     TEXT NOT NULL,
    kind        TEXT NOT NULL CHECK (kind IN ('sale', 'return', 'adjustment'))
);
CREATE TABLE invoice_lines (
    line_no    INTEGER PRIMARY KEY,
    invoice    TEXT NOT NULL REFERENCES invoices (invoice),
    stock_code TEXT NOT NULL REFERENCES products (stock_code),
    quantity   INTEGER NOT NULL,
    unit_price REAL NOT NULL
);

The second step fills them. A primary key must be unique, so each INSERT makes exactly one row per key. GROUP BY invoice gives one row per invoice. ROW_NUMBER() ... WHERE pick = 1 keeps one row per product and per customer, as in Time and ranking. If a step made two rows for one key, SQLite would stop with an error. That is the point of the constraint: a quiet mistake becomes a loud one.

-- products: the name used most often; 0 for postage, fees and adjustments
INSERT INTO products
SELECT stock_code, description,
       CASE WHEN stock_code IN ('POST', 'DOT', 'C2', 'C3', 'M', 'D', 'S', 'B', 'BANK CHARGES', 'ADJUST', 'ADJUST2', 'AMAZONFEE', 'CRUK', 'TEST001', 'TEST002')
              OR stock_code LIKE 'GIFT%' THEN 0 ELSE 1 END
FROM (
    SELECT stock_code, description,
           ROW_NUMBER() OVER (PARTITION BY stock_code
                              ORDER BY description IS NULL, COUNT(*) DESC, description) AS pick
    FROM source_lines
    GROUP BY stock_code, description
)
WHERE pick = 1;

-- customers: the country of the customer's latest invoice
INSERT INTO customers
SELECT customer_id, country
FROM (
    SELECT customer_id, country,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY invoice_date DESC, line_no DESC) AS pick
    FROM source_lines
    WHERE customer_id IS NOT NULL
)
WHERE pick = 1;

-- invoices: one row each; the first time if the lines have two
INSERT INTO invoices
SELECT invoice, MIN(invoice_date), MAX(customer_id), MAX(country),
       CASE WHEN invoice LIKE 'C%' THEN 'return'
            WHEN invoice LIKE 'A%' THEN 'adjustment'
            ELSE 'sale' END
FROM source_lines
GROUP BY invoice;

INSERT INTO invoice_lines
SELECT line_no, invoice, stock_code, quantity, price
FROM source_lines;

CREATE INDEX lines_by_invoice ON invoice_lines (invoice);
CREATE INDEX lines_by_product ON invoice_lines (stock_code);
CREATE INDEX invoices_by_customer ON invoices (customer_id);

The notebook runs both steps with con.executescript(...) and then counts the rows of each table. The indexes make the later joins fast.

raw_lines      1,067,371 rows
products           5,132 rows
customers          5,942 rows
invoices          53,628 rows
invoice_lines  1,044,848 rows

What this tells us. Every count matches a number from the profile. There are 5,132 products (the codes ignoring case), 5,942 customers and 53,628 invoices. The lines are 1,067,371 − 22,523 = 1,044,848. 13 customers bought from more than one country; the table keeps the country of their latest invoice.

Check the keys

SQLite checks the primary keys while it inserts. It checks foreign keys only when you turn that on for each connection, so check them yourself with the orphan queries of Relational concepts. The fourth check uses NOT EXISTS (subquery), which is true when the subquery returns no row: here, when no line has the invoice’s number. Each row counts problems, so 0 is good:

SELECT 'lines without their invoice' AS check_name, COUNT(*) AS problems
FROM invoice_lines AS l LEFT JOIN invoices AS i ON i.invoice = l.invoice
WHERE i.invoice IS NULL
UNION ALL
SELECT 'lines without their product', COUNT(*)
FROM invoice_lines AS l LEFT JOIN products AS p ON p.stock_code = l.stock_code
WHERE p.stock_code IS NULL
UNION ALL
SELECT 'invoices with an unknown customer', COUNT(*)
FROM invoices AS i LEFT JOIN customers AS c ON c.customer_id = i.customer_id
WHERE i.customer_id IS NOT NULL AND c.customer_id IS NULL
UNION ALL
SELECT 'invoices without lines', COUNT(*)
FROM invoices AS i
WHERE NOT EXISTS (SELECT 1 FROM invoice_lines AS l WHERE l.invoice = i.invoice)
UNION ALL
SELECT 'raw lines not accounted for',
       (SELECT COUNT(*) FROM raw_lines) - (SELECT COUNT(*) FROM invoice_lines) - 22523

check_name                         problems
---------------------------------  --------
lines without their invoice        0
lines without their product        0
invoices with an unknown customer  0
invoices without lines             0
raw lines not accounted for        0
5 rows

What this tells us. No orphans in any direction, and every raw line is either in invoice_lines or one of the 22,523 copies. The tables are complete. Now see what kinds of lines they hold:

SELECT i.kind, p.is_goods, COUNT(*) AS lines,
       COUNT(DISTINCT i.invoice) AS invoices,
       SUM(i.customer_id IS NULL) AS lines_without_customer
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
GROUP BY i.kind, p.is_goods
ORDER BY i.kind, p.is_goods DESC

kind        is_goods  lines    invoices  lines_without_customer
----------  --------  -------  --------  ----------------------
adjustment  0         6        6         6
return      1         17974    7406      332
return      0         1191     1024      387
sale        1         1021061  44749     232794
sale        0         4616     4437      1768
5 rows

What this tells us. 1,021,061 lines are sales of goods, and 17,974 are returns of goods. Most lines without a customer are sales: guests buy, but rarely return. In the notebook, your first task counts the sale invoices of guests.

Reconcile the revenue

Now Kwame’s first problem: three reports, three totals. Start with a clear definition in one place. The view goods_lines holds only the lines that count as sales of goods, with the facts of their invoice on each line:

-- The lines that count as sales of goods, with the facts of their invoice.
CREATE VIEW goods_lines AS
SELECT l.line_no, l.invoice, i.kind, i.invoiced_at,
       strftime('%Y-%m', i.invoiced_at) AS month,
       i.customer_id, i.country, l.stock_code, l.quantity, l.unit_price,
       l.quantity * l.unit_price AS amount
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
WHERE p.is_goods = 1
  AND l.unit_price > 0
  AND i.kind IN ('sale', 'return');

Like source_lines, it is a view. Every later query reads goods_lines, so the definition of revenue lives in one place. If finance changes the rule, you change one view.

Now put every pound of invoice_lines into one of five parts:

SELECT CASE WHEN i.kind = 'adjustment' THEN '5 bad-debt adjustment'
            WHEN p.is_goods = 0        THEN '4 postage, fees, manual'
            WHEN l.unit_price <= 0     THEN '3 goods at price 0'
            WHEN i.kind = 'return'     THEN '2 goods returned'
            ELSE                            '1 goods sold' END AS part,
       COUNT(*) AS lines,
       ROUND(SUM(l.quantity * l.unit_price), 2) AS amount
FROM invoice_lines AS l
JOIN invoices AS i ON i.invoice = l.invoice
JOIN products AS p ON p.stock_code = l.stock_code
GROUP BY part
ORDER BY part

part                     lines    amount
-----------------------  -------  -----------
1 goods sold             1015088  19700954.46
2 goods returned         17974    -719692.94
3 goods at price 0       5973     0.0
4 postage, fees, manual  5807     76114.68
5 bad-debt adjustment    6        -147614.08
5 rows

And the three totals that Kwame’s reports used:

SELECT 'every raw line' AS step, COUNT(*) AS lines,
       ROUND(SUM(quantity * price), 2) AS amount
FROM raw_lines
UNION ALL
SELECT 'one copy of each line', COUNT(*), ROUND(SUM(quantity * unit_price), 2)
FROM invoice_lines
UNION ALL
SELECT 'net sales of goods', COUNT(*), ROUND(SUM(amount), 2)
FROM goods_lines

step                   lines    amount
---------------------  -------  -----------
every raw line         1067371  19287250.57
one copy of each line  1044848  18909762.12
net sales of goods     1033062  18981261.52
3 rows

What this tells us. Each report was right about something different:

• £19,287,250.57 sums every raw line. It counts the nine copied days twice: £19,287,250.57 − £18,909,762.12 = £377,488.45, the copy that you found.
• £18,909,762.12 sums each line once. But it mixes the goods with postage and fees (+£76,114.68 in total) and bad-debt adjustments (−£147,614.08).
• £18,981,261.52 is net sales of goods: £19,700,954.46 sold minus £719,692.94 returned.

The five parts add up to the second total to the penny, so no pound is lost. This is a reconciliation: you explain the difference between two totals, part by part. Kwame now reports £18,981,261.52, with this table as the explanation. Guests made £2,569,367 of it, 13.5%, which a “customers only” report would miss.

Common mistake

A total that grows when you join

Symptom: Kwame wants the net sales and the number of lines per country. He has a small table of invoice totals and joins it to the lines to count them. The query runs without an error:

WITH invoice_totals AS (
    SELECT invoice, country, SUM(amount) AS total
    FROM goods_lines
    GROUP BY invoice, country
)
SELECT t.country,
       ROUND(SUM(t.total))  AS net_sales,
       COUNT(g.line_no)     AS lines
FROM invoice_totals AS t
JOIN goods_lines AS g ON g.invoice = t.invoice
GROUP BY t.country
ORDER BY net_sales DESC
LIMIT 3

country         net_sales     lines
--------------  ------------  ------
United Kingdom  1018481201.0  950526
Netherlands     30517437.0    5017
EIRE            26863665.0    17404
3 rows

The UK alone shows more than £1 billion. The shop’s whole net sales are £19 million.

Cause: the invoice totals have one row per invoice, and the lines have many rows per invoice. The join repeats each invoice’s total once for each of its lines. One invoice shows it:

WITH invoice_totals AS (
    SELECT invoice, ROUND(SUM(amount), 2) AS total
    FROM goods_lines
    WHERE invoice = '489434'
    GROUP BY invoice
)
SELECT t.invoice, t.total, g.line_no, g.stock_code, ROUND(g.amount, 2) AS amount
FROM invoice_totals AS t
JOIN goods_lines AS g ON g.invoice = t.invoice

invoice  total  line_no  stock_code  amount
-------  -----  -------  ----------  ------
489434   505.3  1        85048       83.4
489434   505.3  2        79323P      81.0
489434   505.3  3        79323W      81.0
489434   505.3  4        22041       100.8
489434   505.3  5        21232       30.0
489434   505.3  6        22064       39.6
489434   505.3  7        21871       30.0
489434   505.3  8        21523       59.5
8 rows

The invoice’s total is £505.30, but SUM(total) over these 8 rows gives 8 × £505.30 = £4,042.40. An invoice with 50 lines counts its total 50 times, so the UK total is about 63 times too large. This is the fan-out of Join tables, with money.

Fix: sum at the grain where the number lives. The amounts live on the lines, so sum the lines:

SELECT country,
       ROUND(SUM(amount)) AS net_sales,
       COUNT(*)           AS lines
FROM goods_lines
GROUP BY country
ORDER BY net_sales DESC
LIMIT 3

country         net_sales   lines
--------------  ----------  ------
United Kingdom  16221081.0  950526
EIRE            603407.0    17404
Netherlands     546097.0    5017
3 rows

Then reconcile: the sum over all countries must equal £18,981,261.52. Notice that the order of the countries changed too. With the wrong totals, the Netherlands looked bigger than Ireland (EIRE).

Consequence in real work: a number that is 63 times too large is easy to see. A join that repeats only some rows, for example invoices with a second delivery row, gives a total that is 3% too large, and nobody notices. A model trained on such features learns from wrong values. The protection is the same each time: count the rows before and after a join, and reconcile every total with a checked one.

Monthly sales and the Christmas peak

Now Ines’s question: when does the season start, and is it growing? First, the sales of each month. CASE inside SUM splits sales and returns in one pass:

SELECT month,
       COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders,
       ROUND(SUM(CASE WHEN kind = 'sale'   THEN amount ELSE 0 END)) AS sales,
       ROUND(-SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END)) AS returns,
       ROUND(SUM(amount)) AS net_sales
FROM goods_lines
GROUP BY month
ORDER BY month

month    orders  sales      returns   net_sales
-------  ------  ---------  --------  ---------
2009-12  1666    801103.0   19561.0   781542.0
2010-01  1049    613851.0   7466.0    606385.0
2010-02  1189    539745.0   12584.0   527160.0
2010-03  1647    764334.0   9657.0    754677.0
2010-04  1435    648994.0   10025.0   638969.0
2010-05  1484    645645.0   37294.0   608351.0
2010-06  1612    699316.0   26381.0   672935.0
2010-07  1507    634897.0   17133.0   617764.0
2010-08  1402    676061.0   12419.0   663642.0
2010-09  1789    871878.0   27778.0   844100.0
2010-10  2244    1098018.0  25550.0   1072468.0
2010-11  2719    1435680.0  35121.0   1400559.0
2010-12  1550    777932.0   17608.0   760324.0
2011-01  1081    671933.0   91526.0   580407.0
2011-02  1093    508886.0   8376.0    500510.0
2011-03  1440    691258.0   10675.0   680582.0
2011-04  1235    516218.0   33317.0   482901.0
2011-05  1668    741192.0   8948.0    732244.0
2011-06  1525    738752.0   13761.0   724991.0
2011-07  1452    689364.0   11409.0   677955.0
2011-08  1339    725514.0   22930.0   702584.0
2011-09  1818    1030475.0  17045.0   1013431.0
2011-10  2005    1106670.0  43992.0   1062678.0
2011-11  2751    1457746.0  25011.0   1432735.0
2011-12  816     615493.0   174127.0  441367.0
25 rows

The chart puts the two trading years on top of each other. A trading year here runs from December to November, so each year ends with its Christmas season and has twelve full months.

• Dec 2009 to Nov 2010
• Dec 2010 to Nov 2011

Net sales of goods per month, December to November, for the two trading years. Both years are flat from December to August, then rise in September, October and November.

Text description of the chart

A line chart with two lines, one per trading year, from December to November. Both lines stay between about £480,000 and £780,000 from December to August. Then both rise steeply: in the first year to £844,000 in September, £1,072,000 in October and £1,401,000 in November. In the second year to £1,013,000 in September, £1,063,000 in October and £1,433,000 in November. The second year is lower in January to April and higher from May to September.

What this tells us. The peak is November, not December. The customers are shops: they buy in September to November, to sell to their own customers in December. December 2011 looks like a collapse, but its row has only nine days.

To compare a month with the same month a year earlier, use LAG(net_sales, 12): the value 12 rows back. But LAG counts rows, not months. If one month were missing, 12 rows back would be 13 months back, without an error. So the query also shows LAG(month, 12), and you can see that each comparison is the right month:

WITH monthly AS (
    SELECT month, SUM(amount) AS net_sales
    FROM goods_lines
    GROUP BY month
),
with_last_year AS (
    SELECT month, net_sales,
           LAG(month, 12)     OVER (ORDER BY month) AS month_12_rows_back,
           LAG(net_sales, 12) OVER (ORDER BY month) AS net_sales_last_year
    FROM monthly
)
SELECT month, ROUND(net_sales) AS net_sales, month_12_rows_back,
       ROUND(net_sales_last_year) AS net_sales_last_year,
       ROUND(100.0 * (net_sales - net_sales_last_year) / net_sales_last_year, 1) AS change_pct
FROM with_last_year
WHERE month >= '2010-12'

month    net_sales  month_12_rows_back  net_sales_last_year  change_pct
-------  ---------  ------------------  -------------------  ----------
2010-12  760324.0   2009-12             781542.0             -2.7
2011-01  580407.0   2010-01             606385.0             -4.3
2011-02  500510.0   2010-02             527160.0             -5.1
2011-03  680582.0   2010-03             754677.0             -9.8
2011-04  482901.0   2010-04             638969.0             -24.4
2011-05  732244.0   2010-05             608351.0             20.4
2011-06  724991.0   2010-06             672935.0             7.7
2011-07  677955.0   2010-07             617764.0             9.7
2011-08  702584.0   2010-08             663642.0             5.9
2011-09  1013431.0  2010-09             844100.0             20.1
2011-10  1062678.0  2010-10             1072468.0            -0.9
2011-11  1432735.0  2010-11             1400559.0            2.3
2011-12  441367.0   2010-12             760324.0             -42.0
13 rows

What this tells us. Every row compares the same month of two years, so LAG is safe here. The year started weak (April −24.4%) and recovered from May. September 2011 was 20.1% above September 2010, but October and November were almost flat. Without the removal of the copy, December 2010 would count £377,488 of copied lines a second time. Every comparison with it would be wrong.

The −42.0% of December 2011 is a false alarm. Compare the same nine days of each year instead:

SELECT substr(invoiced_at, 1, 4) AS year,
       ROUND(SUM(amount)) AS net_sales_1_to_9_dec,
       COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders
FROM goods_lines
WHERE substr(invoiced_at, 6, 5) BETWEEN '12-01' AND '12-09'
GROUP BY year

year  net_sales_1_to_9_dec  orders
----  --------------------  ------
2009  379913.0              841
2010  402364.0              830
2011  441367.0              816
3 rows

What this tells us. On the same nine days, December 2011 was 9.7% above 2010. It was not a collapse. Mark a partial period in any report, and compare it only with the same part of another period.

Last, how big is the season? Compare September to November with the whole trading year:

SELECT CASE WHEN month <= '2010-11' THEN 'Dec 2009 to Nov 2010'
            ELSE 'Dec 2010 to Nov 2011' END AS trading_year,
       ROUND(SUM(amount)) AS net_sales,
       ROUND(SUM(CASE WHEN substr(month, 6, 2) IN ('09', '10', '11') THEN amount END)) AS sep_to_nov,
       ROUND(100.0 * SUM(CASE WHEN substr(month, 6, 2) IN ('09', '10', '11') THEN amount END)
             / SUM(amount), 1) AS sep_to_nov_pct
FROM goods_lines
WHERE month <= '2011-11'
GROUP BY trading_year

trading_year          net_sales  sep_to_nov  sep_to_nov_pct
--------------------  ---------  ----------  --------------
Dec 2009 to Nov 2010  9188553.0  3317127.0   36.1
Dec 2010 to Nov 2011  9351342.0  3508843.0   37.5
2 rows

What this tells us. Three months bring more than a third of a year’s net sales: 36.1% and then 37.5%. The trading year grew 1.8%, and the season grew 5.8% (£3,508,843 against £3,317,127). For Ines, the Christmas stock must be in the warehouse by the end of August. The season also grows slightly faster than the rest of the year.

Returns: a rate, and the orders behind the spikes

Kwame’s second problem: the board saw 13.6% returns in January 2011. The return rate is the value of goods returned divided by the value of goods sold in the same month. Next to it, the query shows the largest single return line of each month, found with ROW_NUMBER():

WITH return_lines AS (
    SELECT month, line_no, invoice, customer_id, stock_code, quantity,
           -amount AS value,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY -amount DESC, line_no) AS place
    FROM goods_lines
    WHERE kind = 'return'
),
monthly AS (
    SELECT month,
           SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END) AS sales,
           -SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END) AS returns
    FROM goods_lines
    GROUP BY month
)
SELECT m.month, ROUND(m.returns) AS returns,
       ROUND(100.0 * m.returns / m.sales, 1) AS return_rate_pct,
       ROUND(r.value) AS largest_line,
       ROUND(100.0 * r.value / m.returns) AS largest_line_pct
FROM monthly AS m
JOIN return_lines AS r ON r.month = m.month AND r.place = 1
ORDER BY m.month

month    returns   return_rate_pct  largest_line  largest_line_pct
-------  --------  ---------------  ------------  ----------------
2009-12  19561.0   2.4              932.0         5.0
2010-01  7466.0    1.2              424.0         6.0
2010-02  12584.0   2.3              3631.0        29.0
2010-03  9657.0    1.3              210.0         2.0
2010-04  10025.0   1.5              640.0         6.0
2010-05  37294.0   5.8              1700.0        5.0
2010-06  26381.0   3.8              1332.0        5.0
2010-07  17133.0   2.7              2971.0        17.0
2010-08  12419.0   1.8              850.0         7.0
2010-09  27778.0   3.2              1653.0        6.0
2010-10  25550.0   2.3              3168.0        12.0
2010-11  35121.0   2.4              1621.0        5.0
2010-12  17608.0   2.3              1275.0        7.0
2011-01  91526.0   13.6             77184.0       84.0
2011-02  8376.0    1.6              1224.0        15.0
2011-03  10675.0   1.5              526.0         5.0
2011-04  33317.0   6.5              6539.0        20.0
2011-05  8948.0    1.2              442.0         5.0
2011-06  13761.0   1.9              1155.0        8.0
2011-07  11409.0   1.7              990.0         9.0
2011-08  22930.0   3.2              2433.0        11.0
2011-09  17045.0   1.7              3825.0        22.0
2011-10  43992.0   4.0              1836.0        4.0
2011-11  25011.0   1.7              716.0         3.0
2011-12  174127.0  28.3             168470.0      97.0
25 rows

The monthly return rate, December 2009 to December 2011. Two spikes stand out; in each, one return line is most of the month's returns.

Text description of the chart

A line chart of the return rate in each of the 25 months. In most months the rate is between 1.2% and 4.0%. Two months are a little higher: May 2010 at 5.8% and April 2011 at 6.5%. Two months are far higher: January 2011 at 13.6%, where one return line is 84% of the month’s returns, and December 2011 at 28.3%, where one line is 97%.

What this tells us. In half of the 24 complete months, the rate is 2.3% or less. Two months jump. In each, a single return line explains almost all of it: £77,184 in January 2011 and £168,470 in December 2011. Find the order behind each of these two lines. The join looks for a sale of the same product to the same customer, in the same quantity. The sale must be at most one hour before the return:

SELECT r.invoice AS return_invoice, s.invoice AS sale_invoice, r.customer_id,
       r.stock_code, s.quantity, s.invoiced_at AS ordered_at, r.invoiced_at AS returned_at,
       ROUND(s.amount) AS value
FROM goods_lines AS r
JOIN goods_lines AS s
  ON  s.customer_id = r.customer_id
  AND s.stock_code  = r.stock_code
  AND s.quantity    = -r.quantity
  AND s.kind = 'sale'
  AND s.invoiced_at <= r.invoiced_at
  AND r.invoiced_at <= datetime(s.invoiced_at, '+1 hour')
WHERE r.kind = 'return' AND -r.amount > 10000

return_invoice  sale_invoice  customer_id  stock_code  quantity  ordered_at           returned_at          value
--------------  ------------  -----------  ----------  --------  -------------------  -------------------  --------
C541433         541431        12346        23166       74215     2011-01-18 10:01:00  2011-01-18 10:17:00  77184.0
C581484         581483        16446        23843       80995     2011-12-09 09:15:00  2011-12-09 09:27:00  168470.0
2 rows

What this tells us. Customer 12346 ordered 74,215 storage jars and cancelled the order 16 minutes later. Customer 16446 ordered 80,995 paper craft kits and cancelled 12 minutes later. These are mistaken orders: probably a wrong quantity, corrected at once, before any goods left the warehouse. Remove both sides of each order and compute the rate again:

WITH monthly AS (
    SELECT month,
           SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END) AS sales,
           -SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END) AS returns,
           SUM(CASE WHEN invoice IN ('541431', '581483', 'C541433', 'C581484')
                    THEN ABS(amount) ELSE 0 END) / 2 AS mistaken
    FROM goods_lines
    GROUP BY month
)
SELECT month,
       ROUND(100.0 * returns / sales, 1) AS return_rate_pct,
       ROUND(100.0 * (returns - mistaken) / (sales - mistaken), 1) AS without_mistaken_orders_pct
FROM monthly
WHERE month IN ('2011-01', '2011-12')

month    return_rate_pct  without_mistaken_orders_pct
-------  ---------------  ---------------------------
2011-01  13.6             2.4
2011-12  28.3             1.3
2 rows

What this tells us. Without the two mistaken orders, January 2011 had 2.4% returns and December 2011 had 1.3%: normal months. A stricter returns policy would not have prevented either spike, because no customer sent goods back. The real fix is a check in the order system: a warning for a very large quantity. The mistaken orders do not change net sales, because each order and its return cancel out. They change only the gross numbers and the rate.

Do new customers come back?

Kwame’s third problem: the dashboard says that new customers come back less and less. A cohort is a group of customers who started in the same month. The question for each cohort: what share bought again within the next three months?

The query has three CTE steps, and you can run each one alone, as in Readable queries:

WITH active AS (          -- one row per customer and month with a purchase
    SELECT DISTINCT customer_id, month
    FROM goods_lines
    WHERE kind = 'sale' AND customer_id IS NOT NULL
),
first_month AS (          -- the month each customer first appears
    SELECT customer_id, MIN(month) AS cohort
    FROM active
    GROUP BY customer_id
),
came_back AS (            -- did they buy again in the next three months?
    SELECT f.cohort, f.customer_id,
           MAX(a.month >  f.cohort AND
               a.month <= strftime('%Y-%m', f.cohort || '-01', '+3 months')) AS back
    FROM first_month AS f
    JOIN active AS a ON a.customer_id = f.customer_id
    GROUP BY f.cohort, f.customer_id
)
SELECT cohort, COUNT(*) AS customers,
       ROUND(100.0 * AVG(back), 1) AS back_within_3_months_pct
FROM came_back
WHERE cohort <= '2011-08'
GROUP BY cohort
ORDER BY cohort

cohort   customers  back_within_3_months_pct
-------  ---------  ------------------------
2009-12  951        63.0
2010-01  368        55.4
2010-02  375        52.3
2010-03  441        44.2
2010-04  294        38.1
2010-05  255        35.3
2010-06  267        38.2
2010-07  185        44.3
2010-08  163        50.3
2010-09  239        42.3
2010-10  375        37.1
2010-11  326        25.2
2010-12  76         18.4
2011-01  72         40.3
2011-02  125        34.4
2011-03  179        43.6
2011-04  106        39.6
2011-05  111        42.3
2011-06  108        47.2
2011-07  101        50.5
2011-08  107        52.3
21 rows

The join in came_back repeats each customer once per active month, and GROUP BY f.cohort, f.customer_id brings it back to one row per customer. MAX(...) is 1 if any of those months is in the window. The last cohort is August 2011, because a later cohort has no three full months after it in the data.

The share of each cohort that bought again within three months, December 2009 to August 2011. The first cohort is the highest and by far the largest.

Text description of the chart

A line chart of 21 cohorts. The first cohort, December 2009, is the highest at 63.0%, with 951 customers. The line falls to 55.4% and 52.3% in January and February 2010, and then moves between 35% and 50% for most cohorts. It drops to 25.2% for November 2010 and to its lowest, 18.4%, for December 2010, with only 76 customers. In 2011 the cohorts have 72 to 179 customers each, and the line rises from 34.4% to 52.3% in August 2011.

What this tells us. At first sight, the dashboard is right: the first cohorts came back far more often than the later ones. But two things are suspicious. The first cohort is the best by far, and it is also by far the largest: 951 customers, against about 100 a month in 2011.

Think about what “first month” means here. It is the first month in the data, not the first order ever. The shop sold before December 2009. A loyal customer who has bought every month for years appears in the data for the first time in December 2009. The query calls that customer “new”. So the first cohorts are full of old, loyal customers. You can test this idea. If customers bought before the data starts, some of their returns are of goods that they never bought in the data. EXISTS (subquery) is 1 when the subquery finds at least one row, and 0 when it finds none, so sale_in_data says whether the customer bought that product before the return:

WITH return_lines AS (
    SELECT r.month, r.line_no,
           EXISTS (SELECT 1 FROM goods_lines AS s
                   WHERE s.customer_id = r.customer_id
                     AND s.stock_code = r.stock_code
                     AND s.kind = 'sale'
                     AND s.invoiced_at <= r.invoiced_at) AS sale_in_data
    FROM goods_lines AS r
    WHERE r.kind = 'return' AND r.customer_id IS NOT NULL
)
SELECT CASE WHEN month <= '2010-02' THEN '1: Dec 2009 to Feb 2010'
            WHEN month <= '2010-11' THEN '2: Mar to Nov 2010'
            ELSE                         '3: Dec 2010 to Dec 2011' END AS period,
       COUNT(*) AS return_lines,
       ROUND(100.0 * SUM(NOT sale_in_data) / COUNT(*), 1) AS sold_before_the_data_pct
FROM return_lines
GROUP BY period
ORDER BY period

period                   return_lines  sold_before_the_data_pct
-----------------------  ------------  ------------------------
1: Dec 2009 to Feb 2010  2096          42.8
2: Mar to Nov 2010       7007          7.0
3: Dec 2010 to Dec 2011  8539          1.7
3 rows

What this tells us. In the first three months, 42.8% of the returned goods were bought before the data starts. A year later, only 1.7% were. So the history before December 2009 is real and large, and it fills the first cohorts with old customers. Compare two December cohorts directly:

WITH active AS (
    SELECT DISTINCT customer_id, month
    FROM goods_lines
    WHERE kind = 'sale' AND customer_id IS NOT NULL
),
first_month AS (
    SELECT customer_id, MIN(month) AS cohort FROM active GROUP BY customer_id
),
came_back AS (
    SELECT f.cohort, f.customer_id,
           MAX(a.month >  f.cohort AND
               a.month <= strftime('%Y-%m', f.cohort || '-01', '+3 months')) AS back
    FROM first_month AS f
    JOIN active AS a ON a.customer_id = f.customer_id
    GROUP BY f.cohort, f.customer_id
)
SELECT CASE WHEN cohort = '2009-12' THEN 'Dec 2009: first month of the data'
            WHEN cohort = '2010-12' THEN 'Dec 2010: 12 months of history before' END AS cohort,
       COUNT(*) AS customers,
       ROUND(100.0 * AVG(back), 1) AS back_within_3_months_pct
FROM came_back
WHERE cohort IN ('2009-12', '2010-12')
GROUP BY cohort

cohort                                 customers  back_within_3_months_pct
-------------------------------------  ---------  ------------------------
Dec 2009: first month of the data      951        63.0
Dec 2010: 12 months of history before  76         18.4
2 rows

What this tells us. The same calendar month gives 951 “new” customers with 63.0% coming back, and 76 new customers with 18.4%. The second number is close to the truth, because a customer who bought in the 12 months before December 2010 is not in that cohort. The first number mostly describes old customers. So the dashboard’s “falling retention” is mostly the start of the data, not a change in the shop.

What does the honest picture say? From January to August 2011, between 34.4% and 52.3% of new customers bought again within three months. The cohorts of November 2010 (25.2%) and December 2010 (18.4%) are low. Customers who first buy in the Christmas rush come back less often in the next three months. Those months are also the quietest of the year. For Ines and Kwame, a Christmas customer needs a reason to come back in the spring.

In the notebook, you change one thing: the window from three to six months.

The top product in each country, with ties

Ines also wants, for each country, the product that most orders contained in the last season, September to November 2011. ROW_NUMBER() gives one row per country. But what happens when two products are in the same number of orders? Look at two countries, with ROW_NUMBER() and RANK() side by side:

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY country ORDER BY orders DESC) AS row_number,
           RANK()       OVER (PARTITION BY country ORDER BY orders DESC) AS rank
    FROM orders_per_product
)
SELECT country, stock_code, orders, row_number, rank
FROM ranked
WHERE country IN ('Spain', 'Switzerland') AND rank = 1
ORDER BY country, row_number

country      stock_code  orders  row_number  rank
-----------  ----------  ------  ----------  ----
Spain        22077       6       1           1
Spain        22423       6       2           1
Switzerland  22326       6       1           1
Switzerland  22551       6       2           1
Switzerland  22554       6       3           1
5 rows

What this tells us. In Spain, two products were in 6 orders each. In Switzerland, three were. RANK() gives all of them 1. ROW_NUMBER() gives one of them 1, but the ORDER BY does not say which, so SQLite chooses. On another computer or version, WHERE row_number = 1 can return another product, with no warning. How common is this?

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *, RANK() OVER (PARTITION BY country ORDER BY orders DESC) AS rank
    FROM orders_per_product
)
SELECT COUNT(DISTINCT country) AS countries,
       COUNT(DISTINCT CASE WHEN tied > 1 THEN country END) AS countries_with_a_tie_at_the_top
FROM (SELECT country, COUNT(*) AS tied FROM ranked WHERE rank = 1 GROUP BY country)

countries  countries_with_a_tie_at_the_top
---------  -------------------------------
30         16
1 row

What this tells us. 16 of the 30 countries have a tie at the top. Most of them are small markets with a few orders. So break ties with a rule that you can explain: more orders first, then more net sales, then the code. Also show how many products tied. COUNT(*) OVER (PARTITION BY country, orders) is a window function without ORDER BY: it counts the rows of the same country with the same number of orders, and keeps every row. The query shows only the countries whose top product is in at least 6 orders:

WITH orders_per_product AS (
    SELECT country, stock_code, COUNT(DISTINCT invoice) AS orders,
           ROUND(SUM(amount)) AS net_sales
    FROM goods_lines
    WHERE kind = 'sale' AND month BETWEEN '2011-09' AND '2011-11'
    GROUP BY country, stock_code
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY country
                              ORDER BY orders DESC, net_sales DESC, stock_code) AS place,
           COUNT(*) OVER (PARTITION BY country, orders) AS same_orders
    FROM orders_per_product
)
SELECT r.country, r.stock_code, p.description, r.orders, r.net_sales,
       r.same_orders AS products_with_as_many_orders
FROM ranked AS r
JOIN products AS p ON p.stock_code = r.stock_code
WHERE r.place = 1 AND r.orders >= 6
ORDER BY r.orders DESC

country         stock_code  description                          orders  net_sales  products_with_as_many_orders
--------------  ----------  -----------------------------------  ------  ---------  ----------------------------
United Kingdom  22086       PAPER CHAIN KIT 50'S CHRISTMAS       758     43602.0    1
France          23084       RABBIT NIGHT LIGHT                   40      4554.0     1
Germany         22326       ROUND SNACK BOXES SET OF4 WOODLAND   32      1061.0     1
EIRE            22423       REGENCY CAKESTAND 3 TIER             24      1947.0     1
Belgium         22326       ROUND SNACK BOXES SET OF4 WOODLAND   16      739.0      1
Norway          22720       SET OF 3 CAKE TINS PANTRY DESIGN     9       314.0      1
Netherlands     22629       SPACEBOY LUNCH BOX                   8       1525.0     1
Italy           22960       JAM MAKING SET WITH JARS             7       218.0      1
Portugal        22557       PLASTERS IN TIN VINTAGE PAISLEY      7       91.0       1
Finland         84997D      PINK 3 PIECE MINI DOTS CUTLERY SET   6       1009.0     1
Spain           22423       REGENCY CAKESTAND 3 TIER             6       666.0      2
Switzerland     22326       ROUND SNACK BOXES SET OF4 WOODLAND   6       351.0      3
12 rows

What this tells us. The result is now the same on every computer, and the last column shows where the choice was close. In the United Kingdom, a Christmas paper chain kit was in 758 orders: a clear winner for Ines’s buying plan. In the other countries, “the top product” is in 6 to 40 orders. In the 18 countries that the query does not show, it is in 1 to 4 orders. That is too few to plan stock for each country. The same few products (snack boxes, cake stands) win in several countries, so Ines should plan the small markets together.

Deliver a checked monthly table

Kwame needs one table that his reports can read every month, and that he can trust without redoing this work. The table monthly_sales has one row per month: the trading days, orders, registered customers, sales, returns, net sales, the return rate, the change against the same month last year, and a flag for an incomplete month.

DROP TABLE IF EXISTS monthly_sales;
CREATE TABLE monthly_sales AS
WITH monthly AS (
    SELECT month,
           COUNT(DISTINCT substr(invoiced_at, 1, 10)) AS trading_days,
           COUNT(DISTINCT CASE WHEN kind = 'sale' THEN invoice END) AS orders,
           COUNT(DISTINCT CASE WHEN kind = 'sale' THEN customer_id END) AS customers,
           ROUND(SUM(CASE WHEN kind = 'sale' THEN amount ELSE 0 END), 2) AS sales,
           ROUND(-SUM(CASE WHEN kind = 'return' THEN amount ELSE 0 END), 2) AS returns,
           ROUND(SUM(amount), 2) AS net_sales
    FROM goods_lines
    GROUP BY month
)
SELECT month, trading_days, orders, customers, sales, returns, net_sales,
       ROUND(returns / sales, 4) AS return_rate,
       LAG(net_sales, 12) OVER (ORDER BY month) AS net_sales_last_year,
       ROUND(net_sales / LAG(net_sales, 12) OVER (ORDER BY month) - 1, 4) AS change_vs_last_year,
       month < '2011-12' AS complete_month
FROM monthly
ORDER BY month;

The last four months of the table:

month    trading_days  orders  customers  sales       returns    net_sales   return_rate  net_sales_last_year  change_vs_last_year  complete_month
-------  ------------  ------  ---------  ----------  ---------  ----------  -----------  -------------------  -------------------  --------------
2011-09  26            1818    1259       1030475.36  17044.68   1013430.68  0.0165       844100.37            0.2006               1
2011-10  26            2005    1361       1106669.82  43992.16   1062677.66  0.0398       1072467.65           -0.0091              1
2011-11  26            2751    1660       1457745.53  25010.54   1432734.99  0.0172       1400559.24           0.023                1
2011-12  8             816     614        615493.27   174126.76  441366.51   0.2829       760323.91            -0.4195              0
4 rows

What this tells us. December 2011 has 8 trading days and complete_month = 0, so a report can leave it out of comparisons or show it with a warning. customers counts registered customers only; guest orders are in orders and in the money columns.

Validation rules

Now the rules that the table must always meet, as in Validation rules. Each rule is a query that returns the rows that break it. No rows means the rule passes:

rules = {
    "one row per month":
        "SELECT month FROM monthly_sales GROUP BY month HAVING COUNT(*) > 1",
    "no missing month":
        """SELECT month FROM (SELECT month, LAG(month) OVER (ORDER BY month) AS previous
                              FROM monthly_sales)
           WHERE previous IS NOT NULL
             AND month <> strftime('%Y-%m', previous || '-01', '+1 month')""",
    "net sales = sales - returns":
        "SELECT month FROM monthly_sales WHERE ABS(sales - returns - net_sales) > 0.02",
    "total equals goods_lines":
        """SELECT 1 FROM (SELECT SUM(net_sales) AS t FROM monthly_sales)
           WHERE ABS(t - (SELECT SUM(amount) FROM goods_lines)) > 0.5""",
    "return rate between 0 and 0.3":
        "SELECT month FROM monthly_sales WHERE return_rate < 0 OR return_rate > 0.3",
    "only the last month is incomplete":
        """SELECT month FROM monthly_sales
           WHERE complete_month = (month = (SELECT MAX(month) FROM monthly_sales))""",
}
failed = 0
for name, rule in rules.items():
    problems = con.execute(rule).fetchall()
    failed += bool(problems)
    print(f"{'FAIL' if problems else 'pass'}  {name}" + (f"  {problems[:3]}" if problems else ""))
print(f"{len(rules) - failed} of {len(rules)} rules pass")

pass  one row per month
pass  no missing month
pass  net sales = sales - returns
pass  total equals goods_lines
pass  return rate between 0 and 0.3
pass  only the last month is incomplete
6 of 6 rules pass

What this tells us. All six rules pass. Two of them protect the earlier findings. “No missing month” makes LAG(net_sales, 12) safe: with a gap, 12 rows back would no longer be 12 months back. “Total equals goods_lines” is the reconciliation as a rule, so a fan-out or a lost month fails loudly next time. The return-rate rule has a wide range (0 to 0.3) on purpose: the mistaken order of December 2011 gives 0.2829. If such an order happens again and the rate goes above 0.3, the rule stops the report, and a person must look.

The lineage note

Last, the notebook writes monthly_sales.csv and a short lineage note, LINEAGE.md: where the numbers come from and what each step changed, as in Track lineage. The code is in the notebook; this is what it writes:

# monthly_sales.csv: lineage

- Source: Online Retail II, Daqing Chen, UCI Machine Learning Repository,
  https://doi.org/10.24432/C5CG6D, CC BY 4.0. File online_retail_ii.csv.gz
  (SHA-256 c5ab45f73700a1411440c2cd6b7d85611d22cdc72b3594c2d29d37261e977e4f), the two Excel sheets joined in order.
- raw_lines: 1,067,371 lines, loaded unchanged, with line_no = position in the file.
- invoice_lines: 1,044,848 lines. Removed the 22,523 lines of sheet 2 dated
  1-9 Dec 2010 (line_no >= 525462): an exact copy of sheet 1. Kept 11,812 repeats inside one sheet.
- goods_lines: 1,033,062 lines. Goods only (no postage, fees, manual lines,
  vouchers or bad-debt adjustments), unit price above 0, sales and returns.
- monthly_sales: 25 months, Dec 2009 to Dec 2011. Net sales = sales - returns, in GBP.
  Dec 2011 has 9 days only (complete_month = 0). Guest orders (no customer ID) count in sales,
  not in customers.
- Checks: the six validation rules in the notebook pass; the total equals goods_lines.
- Output: monthly_sales.csv, SHA-256 e04eb68728bf33eeaabf0f30074445bc1946c72949e12afabf58301dde4fd03b

What this tells us. A reader of monthly_sales.csv can now answer “where does this number come from?” without asking you. The checksum of the input proves which file the build read. The checksum of the output lets Kwame see if the file changed after the checks.

Recommendations

You write the recommendations for Ines and Kwame in their words. The numbers are from 1 December 2009 to 9 December 2011.

For Kwame (finance):

1. Report net sales of goods: £18,981,261.52 for the two years, from monthly_sales. The other two totals included nine copied days, or postage, fees and bad debt. Keep the reconciliation table with the report.
2. Do not tighten the returns policy because of January 2011. Both return spikes were single mistaken orders, cancelled within 16 minutes. In half of the complete months, returns were 2.3% of sales or less. Report the return rate with and without mistaken orders.
3. Ask for a quantity check in the order system. An order of 74,215 or 80,995 units of one product should need a second confirmation.
4. Replace the retention chart. Its early cohorts are mostly customers who bought before the data starts. Show cohorts from December 2010 on, which have a year of history before them. In 2011, 34% to 52% of new customers bought again within three months.

For Ines (merchandising):

5. Have the Christmas stock in the warehouse by the end of August. September to November is 37.5% of the year’s net sales, and the peak is November, not December.
6. Plan for modest growth. The season grew 5.8% and the whole year 1.8%. The first nine days of December 2011 were 9.7% above 2010.
7. Plan by product for the UK, and by product group for the other markets. Outside the UK, the top product of a country was in 40 orders or fewer, in 18 of the 30 countries in fewer than 5, and 16 countries had a tie at the top.

What not to use these numbers for. They describe one shop from 2009 to 2011. They are not a forecast: they show what happened, not what will happen. The customer numbers count only registered customers.

Limits and next steps

The limits, in the order Kwame should know them:

• 11,812 repeats inside one sheet are kept. If they are double entries, net sales are at most £54,228 (0.3%) too high.
• The history before December 2009 is missing. “New customer” in the first year of the data is not reliable. Use cohorts from December 2010 on.
• Guests are 22.8% of the raw lines and 13.5% of net sales, but they have no ID. Customer questions describe registered customers only.
• Mistaken orders were found by size. The rule (a return above £10,000 within one hour of the same order) finds the two large ones. Smaller mistakes stay in the return rate.
• Non-goods codes are a list. A new fee code next year would count as goods until someone adds it to the list. The total rule does not catch that; a profile of new codes each month would.

Next steps: add a rule that lists codes not seen before, and run the build every month on the new export. Then, with clean monthly numbers, a forecast of the next season becomes possible. The machine learning course teaches the models.

Make it a portfolio piece

This case study can become a public project. It shows an employer that you can turn a messy export into numbers that finance can trust. It also shows that you can explain every difference.

What to put in the repository:

• README.md, with five short parts:
  1. Problem: “Make an online shop’s sales data trustworthy, and answer three planning questions: the Christmas season, return spikes, and customer retention.”
  2. Data and licence: Online Retail II, UCI, CC BY 4.0, with the citation and a link to the dataset card.
  3. Approach: the profile, the copied sheet found with LAG, four tables with keys and orphan checks, the goods_lines view, and the reconciliation table.
  4. Results: the three totals reconciled to £18,981,261.52; the season (37.5% of the year in September to November); the two mistaken orders behind the return spikes; the cohort trap (63.0% against 18.4%).
  5. Limits: the list above, in your words.
• The notebook, run from the top, with its outputs.
• monthly_sales.csv and LINEAGE.md. The notebook writes both into the folder explain-an-online-shops-sales.
• The SQL of the four tables and of monthly_sales as .sql files, so a reader can run them without the notebook.

Do not put shop.db or the data file in your repository: they are large, and the source is public. Link to the UCI page and to the course copy, and keep the attribution.

How to describe it in an interview, in two sentences: “I loaded a real online shop’s export of a million invoice lines into SQLite, found nine copied days with one window function, and reconciled three different revenue totals to one checked figure. With it, I showed that the return spikes were two mistaken orders, and that the falling retention came from where the data starts.”

Go further:

• Find all mistaken orders, not only the two large ones: an order line cancelled in full by the same customer within one hour. Count them, and handle a return that matches two orders without counting it twice.
• Build the same tables in PostgreSQL, with real date types and foreign keys that are always on, and compare the queries.
• Add a weekly table, and compare the weeks before Christmas in the two years.
• Use monthly_sales.csv as the input of a simple forecast of the next season, after the machine learning course.

Knowledge check

Check your understanding

Two thirds of the exact repeats were from 1 to 9 December 2010. How did you prove that they were a copy, and why did you keep the other 11,812 repeats?

Show the answer

The file joins two Excel sheets, one per year, in time order. LAG(invoice_date) OVER (ORDER BY line_no) showed that time goes backwards exactly once, at line 525,462: the start of the second sheet. The nine days then had 22,523 lines in each sheet, and not one line was different, so you removed the second copy. The other repeats are inside one sheet. They may be the same product entered twice on one invoice, which can be real, and nobody can prove that they are copies. They are £54,228, 0.3% of the amount, so you keep them and record the choice as a limit. SELECT DISTINCT * would have removed both kinds without a record.

Check your understanding

Kwame joins a table of invoice totals to the invoice lines and gets more than £1 billion for the UK. What happened, and which two habits catch this mistake?

Show the answer

The invoice totals have one row per invoice, and the lines have many rows per invoice. The join repeats each invoice’s total once for each of its lines. So SUM(total) adds the same total many times: invoice 489434 with 8 lines gives 8 × £505.30. The fix is to sum at the grain where the number lives: SUM(amount) on the lines. The two habits: count the rows before and after every join, and reconcile every total with a checked total (here £18,981,261.52). The rule “total equals goods_lines” makes the second habit automatic.

Check your understanding

The December 2009 cohort came back at 63.0%, the December 2010 cohort at 18.4%. Has the shop become worse at keeping new customers?

Show the answer

Mostly not. The data starts in December 2009, but the shop is older. “First month in the data” is not “first purchase”, so the December 2009 cohort is full of old, loyal customers. The evidence: 42.8% of returns in the first three months were of goods bought before the data starts, against 1.7% a year later. The December 2010 cohort has a year of history before it, so it holds new customers. Compare cohorts with the same history, from December 2010 on: in 2011, 34% to 52% of new customers came back within three months. Christmas cohorts come back less often in the quiet months after Christmas, which is a real and useful finding.

Recap

In this case study you turned 1,067,371 raw invoice lines into numbers that a finance team can trust:

• A profile before any change found guests (22.8% of the lines), returns, stock notes, non-goods codes and repeated lines. LAG over the file order proved that 22,523 lines were a copied sheet.
• Four tables with keys, filled with one row per key and checked with orphan queries. The business rules live in one view, goods_lines.
• A reconciliation explained three totals and settled on £18,981,261.52 of net sales of goods. A join of invoice totals to lines made a total 63 times too large without an error.
• Window functions and CTEs answered the questions: the season is September to November (37.5% of the year), the two return spikes were mistaken orders, and the best cohort was only the start of the data.
• A checked delivery: monthly_sales with six validation rules and a lineage note.

Next, a project folder: a leak-free table that flags the city repair requests that will be late.

Next: Prepare a leak-free table for city repair requests