Online Retail II dataset

A Brightfield dashboard of Online Retail II. The main panel counts orders per month from December 2009 to December 2011, coloured by whether the customer was returning, new that month or had no customer ID, under the title "The regulars carry this shop: in every full month of 2011, returning customers placed 77% to 87% of orders, November's peak included". Beside it: orders by customer type, orders by country (top eight), and how sure the new and returning labels are. — Data: Chen (2012), UCI Machine Learning Repository, CC BY 4.0

The invoices of a UK-based online retailer of giftware, many of whose customers are wholesalers, from 1 December 2009 to 9 December 2011, as the UCI Machine Learning Repository publishes them. The recipe makes one row per purchase invoice, 40,076 orders in all: its month, country, number of lines and value, and whether its customer was new that month or returning. Cancellations, bad-debt adjustments and invoices of no positive value are left out, and the Customer ID is read only to work out each customer's first month and order numbers; no table the recipe makes holds it.

Load it

These commands install arc, Meridian’s open-source Protocol runner, clone the open-analytics repository and run this Protocol, which writes build/retail_orders.parquet in open-analytics/examples/online-retail-ii.

shell

curl -fsSL https://install.meridian.online/arc | bash
git clone https://github.com/meridian-online/open-analytics
cd open-analytics/examples/online-retail-ii && arc run

arc runs the Protocol’s SQL on DuckDB 2.x, which its installer puts in place when no DuckDB is on your PATH, saying what to add to PATH; the last step, which describes the Parquet, needs finetype 0.6.54 or later on your PATH.

Columns

Name	Type	Description
invoice	integer	The six-digit invoice number.
invoice_time	datetime	When the invoice was made, from the workbook's Excel serial date, to the second.
month	date	The first day of the invoice's month.
month_number	integer	Months since December 2009, the first month in the data, which is 0.
country	string	The customer's country.
lines	integer	The number of lines on the invoice.
order_value	number	The sum of quantity times unit price over the invoice's lines, in pounds sterling, to the penny.
customer	string	new this month when the invoice falls in the month of the customer's first order, returning when it is later, and no customer ID where none was recorded.
customer_order_number	integer	The invoice's place in its customer's sequence of orders, from 1; empty where no customer ID was recorded.
customer_record	string	How much of the customer's history the data can see when the invoice was made: under a year, a year or more, or no customer ID. In the first year a returning customer can be labelled new, because the data does not reach back to their earlier orders.

Source and licence

Licence

Creative Commons Attribution 4.0 International, as the UCI Machine Learning Repository publishes the dataset

Sources

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

Meridian hosts none of this data: the Protocol downloads it, from the address its fetch step names, when you run it.

The steps

6 stepsduckdb >=1.2

Every step runs in order. op: steps are typed, versioned operators; sql: steps are DuckDB models.

01fetch_archiveop: http_fetch@1

Fetch the dataset's zip from the UCI Machine Learning Repository, pinned by sha256.

producesbuild/online_retail_ii.zip

with

url: https://archive.ics.uci.edu/static/public/502/online+retail+ii.zip
out: build/online_retail_ii.zip
sha256: 572e36277c2390fbfde10664750731e0a86f55e33470d91919085f0408e67bfb
headers:
  User-Agent: Meridian Protocol (open-analytics demo; research@meridian.online)

02extract_workbookop: archive_extract@1
Extract online_retail_II.xlsx, the one workbook in the zip.
readsbuild/online_retail_ii.zipproducesbuild/uci/online_retail_ii.xlsx
with
```
archive: build/online_retail_ii.zip
members: [online_retail_II.xlsx]
dest: build/uci
```
03retail_linessql: models/01_retail_lines.sql
Read both of the workbook's sheets, counting the days they share once. Keep sales and their cancellations, leave out the six bad-debt adjustments, and replace the Customer ID with each customer's first month and order number; the ID itself is not kept.
readsbuild/uci/online_retail_II.xlsxproducesretail_lines
model
```
models/01_retail_lines.sql
```
reads the workbook with DuckDB's excel extension, which DuckDB downloads from extensions.duckdb.org on the first run when it is not installed.
04retail_orderssql: models/02_retail_orders.sql
One row per invoice that is not a cancellation and has a positive value: its month, country, lines and value, and whether its customer was new that month or returning.
producesretail_orders
model
```
models/02_retail_orders.sql
```
05export_retail_ordersop: parquet_export@1
Write build/retail_orders.parquet, the file this Protocol ends in, on your own disk. ORDER BY ALL makes the bytes reproducible.
readsretail_ordersproducesbuild/retail_orders.parquet
with
```
input: retail_orders
dest: build/retail_orders.parquet
compression: zstd
order_by: ALL
```
06describeop: datapackage_describe@1
Emit datapackage.json from the built Parquet: finetype types every column, and descriptor.overrides.json adds the curated title, licence, sources and per-field prose.
readsbuild/retail_orders.parquetdescriptor.overrides.jsonproducesdatapackage.json
with
```
parquet: build/retail_orders.parquet
overrides: descriptor.overrides.json
out: datapackage.json
```