An auditable cleaning pipeline and exploratory analysis of 541,909 real transaction lines from a UK online giftware retailer (Dec 2010 – Dec 2011).
The interesting part of this dataset is not the analysis, it is the mess: a quarter of the rows have no customer, 1.7% are cancellations that share a schema with sales, and postage and bank charges are booked as if they were products. This repository is about making defensible decisions on that mess — and being able to show the receipts for every one of them.
pip install -r requirements.txt
python -m src.pipeline # raw data in, clean data + reports + figures out| Metric | Value |
|---|---|
| Transaction lines analysed | 536,641 |
| Distinct invoices | 25,900 |
| Identified customers | 4,372 |
| Gross product revenue | £10,226,826 |
| Net product revenue (after returns) | £9,751,014 |
| Rows deleted during cleaning | 0.97% (exact duplicates only) |
| Rows flagged rather than deleted | 25.2% |
Full write-up: reports/03_findings.md
Net revenue nearly doubles into the Christmas peak — £1.42M in November 2011 against ~£700K in the spring. Then December 2011 shows a 28% return rate against a baseline of 1–3%.
That is not a finding, it is a trap. The dataset stops on 9 December 2011: the final month holds nine days of sales but a full month of returns against orders placed earlier. Every month-over-month chart that includes it is wrong, and every model trained on it learns the wrong seasonality. The figure marks the partial month rather than quietly plotting it.
RFM segmentation puts 26% of identified customers in Champions, and they produce 67% of revenue with an average of 9.9 orders each. Meanwhile 1,068 hibernating customers — a quarter of the base — contribute 5.8%. For a business shaped like this, retention and reactivation beat acquisition, and the top 20% of accounts are a concentration risk worth naming out loud.
The UK is 84.7% of net revenue. What matters more is the shape of the remainder: the Netherlands and EIRE reach the top three on single-digit customer counts. Those are wholesale relationships. Losing one of them is a material revenue event, which a country-level bar chart alone would never reveal.
Every rule is an isolated, tested function that returns the data and a record of what it did
and why. The audit trail is generated, not written by hand:
reports/02_cleaning_audit.md.
| Step | Removed | Flagged | Decision |
|---|---|---|---|
standardise_schema |
0 | 113,452 | Trim whitespace — otherwise one product aggregates as two |
drop_exact_duplicates |
5,268 | 0 | The only deletion: identical to the minute, worth £21,741 of phantom revenue |
flag_cancellations |
0 | 9,251 | Keep them, so gross and net revenue are both computable |
classify_line_type |
0 | 5,781 | Postage, fees, discounts, vouchers and staff notes are not products |
impute_descriptions |
0 | 1,454 | 1,342 recovered from the stock code; 112 labelled UNKNOWN |
flag_guest_customers |
0 | 135,037 | £1.7M of revenue that dropna() would have thrown away |
flag_price_anomalies |
0 | 2,512 | £0 giveaways stay in units, leave average selling price |
flag_quantity_outliers |
0 | 443 | Tukey fence in log space; the 80,995-unit order is real |
add_derived_columns |
0 | 521,160 | One shared definition of is_valid_sale |
Two principles drive this:
Flag, do not delete. Deleting the rows with no CustomerID — the standard tutorial move —
would remove about a sixth of the revenue, and those orders have a higher average line value
than identified ones. They are unusable for customer analysis and perfectly usable for revenue
analysis, so the analyst chooses, not the pipeline.
Delete only what provably is not a fact. Exactly one rule deletes anything: rows identical on all eight columns including the timestamp to the minute cannot be two separate events.
src/
config.py paths, source URL + checksum, domain constants
ingest.py checksum-verified download, CSV cache, typed reload
profiling.py pre-cleaning data quality profile
cleaning.py the nine cleaning rules + audit trail
features.py revenue, baskets, RFM, cohorts
visualize.py the eight report figures
pipeline.py CLI that runs the whole thing
tests/ 24 unit tests on rules and metric definitions
notebooks/ the narrative version, with outputs committed
reports/ generated profile, audit trail, findings, figures
data/ gitignored; reproduced by the pipeline
Reusable logic lives in src/ and is tested. The notebook asks questions and reads answers —
it is not where the logic hides.
python -m src.pipeline # ~25s using the cached raw CSV
python -m src.pipeline --force # re-download from source and re-parse the workbook
python -m pytest tests/ -q # 24 tests, ~1sThe download is checksum-pinned in src/config.py, so a clone either reproduces the
byte-identical input or fails loudly. There is no "download this file manually and put it in
a folder" step, and the raw data is never committed.
Why an audit trail instead of a clean notebook? A cleaning pipeline is an argument. Six months later, "why are there 536,641 rows and not 541,909?" needs an answer that is not archaeology through notebook cells.
Why is customer_id a nullable Int64? It arrives as float64 (17850.0) purely because
pandas needed somewhere to put the NaNs. An identifier is not a number you do arithmetic on.
Why judge outliers in log space? Quantity per line is log-normal with a long wholesale tail. A Tukey fence on raw values flags thousands of legitimate bulk orders; in log space it isolates 443 lines, and the largest one is a real order mirrored by a real cancellation.
Why did a test change a definition? The RFM test pinned recency to a fixed snapshot and exposed that the snapshot was anchored to the last identified sale — so it moved whenever the guest filter changed. It is now anchored to the end of the dataset. That is the argument for testing definitions rather than just code.
- Cancellations are counted as negative revenue but not matched line-by-line to their original invoices. Matching them would allow a true per-order return rate and a time-to-return distribution.
- The staff-note classifier is a keyword heuristic with a 30-character guard. It is measured (3,362 rows) but not validated against a labelled sample.
- Product categories do not exist in the source, so catalogue analysis stops at individual stock codes. Deriving categories from descriptions is the obvious next step.
- Next in this series: loading this cleaned table into a dimensional model
(
sql-data-modeling) and building a collection pipeline that produces a dataset from scratch (scraping-pipeline).
"Online Retail", D. Chen (2015), UCI Machine Learning Repository,
doi.org/10.24432/C5BW33, licensed CC BY 4.0.
Retrieved from a public mirror because the UCI host rate-limits; the exact file is pinned by
SHA-256 in src/config.py.