Retail · Advanced
Use real online-retail sales to build monthly cohorts, retention rates, RFM scores and a churn flag that show which customers to win back.
A UK online shop sells gifts and homeware, and many of its customers are small shops that buy in bulk. The commercial director thinks too many first-time buyers never come back and that good customers drift away unnoticed. She has asked you to measure retention properly, score every customer and size the revenue at risk before next quarter's marketing budget is set. You have two files: two years of invoice lines (December 2009 to December 2011) from a sample of customers plus some anonymous sales, and a product list that flags postage, fees and other lines that are not goods. The shop and its data are real but anonymised; the roles in this brief are fictional.
Your role
Customer Insights Analyst, online gift wholesaler
The shop tracks revenue but not retention. It cannot say what share of each month's new customers come back, which customers drive most of the revenue, or which valuable customers have gone quiet. The marketing budget for win-back campaigns depends on these answers.
Count total lines, cancellations, lines with no customer and non-product lines. Then write your rule for a clean sale and count how much each filter removes.
Using your clean-sale rule, report revenue, invoices and customers per month, cancellations per year, and the UK against international split.
Real data: Online Retail II by Daqing Chen, published on the UCI Machine Learning Repository (https://archive.ics.uci.edu/dataset/502/online+retail+ii), licence CC BY 4.0, downloaded on 24 October 2026. The original is an Excel file with 1,067,371 lines. To keep the download small we took a fixed sample: every line for about 1 in 12 customers (chosen by a hash of the customer id, so each customer's full history is kept), plus the lines of about 1 in 24 invoices that have no customer id. We also removed 22,523 lines that appear on both sheets of the original file (1 to 9 December 2010). online-retail-transactions.csv has 67,188 rows and 7 columns. One row is one line on an invoice: invoice number, stock code, quantity, invoice date and time, unit price in pounds sterling (GBP), customer id and country. It covers 1 December 2009 to 9 December 2011. online-retail-products.csv has 4,026 rows and 3 columns: stock code, product name and an is_product flag. We added the flag (a few codes such as POSTAGE, Manual, Discount, Bank Charges and gift vouchers are not goods) and picked the most common name for each code. Left for you to handle, on purpose: cancellations (invoice numbers starting with C, negative quantities), lines with no customer id, zero prices, non-product lines, and a few exact duplicate lines that exist in the source. Unit prices are rounded to 2 decimals. Stock codes are case-sensitive (85123A and 85123a are different codes in the source).
Open in SQL workspace
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionaryonline_retail_productsOne row per stock code that appears in the invoice lines, with its most common name and a flag for codes that are not goods. · 4,026 rows
15 exercises worth 265 points. Your query runs against this project’s dataset and is marked automatically.
Find each customer's first purchase month and count new customers per month. Then build the retention table: for each cohort, the share still buying 1, 2, 3 and more months later.
Measure the repeat-purchase rate and the average days to a second order. Then score every customer from 1 to 5 on recency, frequency and monetary value with NTILE, and group the scores into segments such as Champions (high on all three) and At Risk (low recency, high frequency).
Flag a customer as churned if their last clean sale is more than 90 days before the last date in the data. Size the revenue those customers used to bring in and write a one-page memo with three actions.
| Column | Type | Description | Example |
|---|---|---|---|
stock_code | text | Product code; matches online_retail_transactions.stock_code. | 85123A |
description | text | Most common product name for the code in the source. Empty for a few codes that never had a name. | WHITE HANGING HEART T-LIGHT HOLDER |
is_product | boolean | true for goods; false for postage, carriage, fees, discounts, manual adjustments, samples and gift vouchers. | true |
All graded SQL exercises passed in the SQL workspaceonline_retail_transactions67,188 invoice lines from a UK online gift shop, December 2009 to December 2011: a fixed sample of customers plus some anonymous sales. Prices in GBP. · 67,188 rows
| Column | Type | Description | Example |
|---|---|---|---|
invoice_no | text | Invoice number. Six digits for a sale; a leading C marks a cancellation. | 489523 |
stock_code | text | Product code; joins to online_retail_products.stock_code. Some codes are not goods (for example POST for postage). | 84879 |
quantity | integer | Units on the line. Negative for cancellations and some stock adjustments. | 800 |
invoice_date | timestamp | Date and time the invoice was raised. | 2009-12-01 11:46:00 |
unit_price | numeric | Price per unit in pounds sterling (GBP), rounded to 2 decimals. Zero for some free or adjusted lines. | 1.45 |
customer_id | integer | Five-digit customer number. Empty for anonymous sales. | 12931 |
country | text | Country of the customer on that invoice. | United Kingdom |
The exercises are marked automatically. The memo is marked on whether the sale rule is stated and applied the same way everywhere, whether cancellations and anonymous sales are handled on purpose with their impact stated, and whether each recommendation is backed by a number from your queries.
Recommended tools
Skills covered