Customer Segmentation and Customer Behaviour Analysis

End-to-end customer analytics on 1.07 million real e-commerce transactions: from rawinvoice lines to RFM segments, a four-page BI dashboard, and a prioritised set ofmarketing actions.

Stack: Python · Pandas · NumPy · SQL (DuckDB / ANSI) · Power BI · Jupyter

---

Business problem

A UK-based online giftware retailer sells to a mix of consumers and wholesale buyers.Management can see total revenue, but cannot see who is producing it. Marketing spend isallocated evenly across the customer base because there is no evidence for doing anythingelse.

Leadership asked six questions:

• Who are our most valuable customers?
• Which customers purchase frequently?
• Which customers have stopped purchasing?
• Which customers generate the highest revenue?
• Which customers are at risk of becoming inactive?
• Which customer groups should receive different marketing strategies?

Objectives

1. Turn raw transaction lines into a trustworthy, documented analytical dataset.
2. Establish the commercial baseline — customers, orders, revenue, order value, frequency.
3. Score every customer on Recency, Frequency and Monetary value.
4. Classify customers into segments using transparent, auditable rules.
5. Quantify revenue concentration and retention risk.
6. Translate the findings into recommendations a marketing team can act on.

---

Dataset

Attribute	Detail
Source	UCI Machine Learning Repository — Online Retail II (Chen, D., 2012)
Description	All transactions of a UK-based, non-store online giftware retailer
Period	01 Dec 2009 – 09 Dec 2011 (24 months)
Raw records	1,067,371 invoice lines
Raw columns	8
Analytical records	794,079 lines after cleaning
Customers	5,852 identified
Orders	36,597
Products	4,631
Countries	41
Net revenue	£16,360,751.72

Main variables: Invoice, StockCode, Description, Quantity, InvoiceDate,UnitPrice, CustomerID, Country. Revenue is derived as Quantity × UnitPrice.

Dataset limitations

• 22.6% of lines have no Customer ID (£2.57M of revenue). Real trade, but impossible toattribute to a customer — excluded from all customer-level analysis and reported openly.
• No demographics or firmographics — segments are behavioural only.
• No marketing, campaign or channel data — no action can be attributed, no ROI computed.
• No cost or margin data — all value figures are revenue, not profit.
• December 2011 is a partial month (data ends on the 9th) and must not be read as ademand collapse.
• Historic data (2009–2011). The methodology transfers; the specific numbers would needre-running on current data.

The raw .xlsx (45 MB) is not committed. scripts/build_pipeline.py downloadsinstructions are in Reproducing the analysis.

---

Repository structure

customer-segmentation/
├── data/
│   ├── raw/                          # source workbook (not committed)
│   └── processed/                    # cleaned outputs + kpi_summary.json
├── notebooks/
│   ├── 01_data_cleaning.ipynb
│   ├── 02_exploratory_analysis.ipynb
│   └── 03_rfm_analysis.ipynb
├── sql/
│   ├── 00_schema_setup.sql
│   ├── 01_data_quality.sql
│   ├── 02_customer_kpis.sql
│   ├── 03_rfm_analysis.sql
│   ├── 04_customer_segments.sql
│   ├── 05_customer_value.sql
│   └── 06_advanced_customer_analysis.sql
├── powerbi/
│   ├── dashboard-specification.md
│   ├── data-model.md
│   └── measures.md
├── images/                           # dashboard mockups + analysis charts
├── reports/
│   └── business-insights.md
├── scripts/
│   ├── build_pipeline.py             # full reproducible pipeline
│   ├── make_notebooks.py
│   ├── make_dashboard_mockups.py
│   └── validate_project.py           # end-to-end quality gate
├── README.md
├── requirements.txt
└── .gitignore

---

Tools

Tool	Use
Python 3 / Pandas / NumPy	Cleaning, aggregation, RFM scoring, segmentation
Matplotlib	Analytical charts and dashboard mockups
SQL (DuckDB, ANSI-portable)	Independent reimplementation of the metrics; validation
Power BI	Four-page management dashboard (specified in powerbi/)
Jupyter	Documented, executable analysis narrative
Excel	Source data format (.xlsx, two sheets)

---

Data cleaning

Raw → analytical dataset in eight logged steps. No record is removed silently — everystep records rows in, rows out and the monetary value affected todata/processed/cleaning_log.csv.

#	Step	Rule	Lines	Value affected
1	Duplicates	Identical on all business columns; keep first	−34,335	£431,717
2	Ledger adjustments	Invoice prefix A (bad-debt write-offs)	−6	−£147,614
3	Cancellations	Invoice prefix C → routed to a Return line type, not deleted	19,104 separated	−£1,462,051
4	Non-product lines	Postage, manual, bank charges, samples, tests, vouchers	−4,671	£821,167
5	Invalid quantity	Quantity ≤ 0 outside cancellations (write-offs, corrections)	−3,389	£0
6	Invalid price	UnitPrice ≤ 0 (free samples, entry errors)	−2,569	£0
7	Customer ID validation	Null CustomerID — cannot be segmented	−226,717	£2,574,732
8	Orphan returns	Returns from customers with no purchase in the window	−87	−£1,406

Key decisions and why:

• Cancellations are kept as negative revenue, not deleted. Returns are real customerbehaviour, so a customer's monetary value is gross sales − returns. Deleting themwould overstate the value of customers who return heavily.
• Unidentified-customer revenue is excluded but quantified. £2.57M of genuine tradecannot be attributed to a customer; hiding the exclusion would misrepresent coverage.
• Outliers are surfaced, not removed. Orders of 80,000 units are genuine wholesalepurchases, not errors.

Result: 794,079 lines retained (74.4% of raw), reconciling exactly to the customerdimension.

---

Exploratory data analysis

Area	Finding
Scale	5,852 customers · 36,597 orders · £16.36M net revenue
Order value	£466.41 average (gross, per invoice)
Customer value	£2,795.75 mean vs £840.25 median — a 3.3× skew
Frequency	72.4% repeat rate; median 3 orders per customer
Lifecycle	Median 96 days since last order; median gap between consecutive orders 25 days
Geography	UK = 83.5% of revenue from 91.2% of customers
Seasonality	Sep–Nov = 36.7% of full-month revenue in both years
Returns	4.2% of gross revenue; 41.4% of customers returned at least once

The mean/median gap and the Lorenz curve both point to the same conclusion: the customerbase is too unequal to manage as one group. That is what motivates segmentation.

---

RFM methodology

Dimension	Definition
Recency	Days between the reference date and the customer's most recent purchase. Lower is better.
Frequency	Count of distinct sales invoices. Returns do not add to frequency.
Monetary	Total net revenue (gross sales minus returns).

Reference date: 10 December 2011 — the day after the final transaction (09 Dec 2011).A fixed in-dataset anchor keeps recency reproducible over time, and guarantees everyrecency value is at least 1 day.

Scoring. Each dimension is split into quintiles scored 1–5. Recency is reversed so5 always means "best". Frequency and Monetary are ranked before cutting(rank(method="first")) because frequency has heavy ties at low values that wouldotherwise collapse the quintiles.

Why quintiles: cut-points come from the data's own distribution, so scoring adapts tothis customer base rather than imposing arbitrary thresholds. The trade-off — statedplainly — is that scores are relative: a 5 means "top 20% of these customers", not anabsolute standard.

Resulting score bands:

Score	Recency (days)	Frequency (orders)	Monetary (£)
5 (best)	1–20	8–373	2,829 – 578,409
4	22–59	4–8	1,179 – 2,820
3	60–187	2–4	591 – 1,178
2	188–409	1–2	272 – 590
1	410–739	1	−1,343 – 272

Note how uneven the value ranges are despite near-equal customer counts — the topmonetary quintile spans from £2.8K to over half a million.

---

Customer segments

Segments are assigned on the R × FM grid, where FM is the rounded average of theFrequency and Monetary scores. Rules are mutually exclusive, evaluated in order, and everycustomer receives exactly one segment.

Segment	Rule	Business meaning
Champions	R≥4 and F≥4 and M≥4	Recent, frequent, high spend — protect
Loyal Customers	R≥3 and FM≥4	Consistently valuable — grow share of wallet
Potential Loyalists	R≥4 and FM 2–3	Recent, moderate value — develop
New Customers	R=5 and FM=1	First small purchase — onboard
Promising Customers	R=4 and FM=1	Recent but unproven — nurture
Needs Attention	R=3 and FM 2–3	Mid value, cooling — intervene
About To Sleep	R=3 and FM=1	Low value, slipping — cheap nudge
At Risk	R≤2 and FM≥4	Was valuable, now inactive — win back
Hibernating	R≤2 and FM 2–3	Long inactive, low-mid value — low-cost test
Lost Customers	R≤2 and FM=1	Long inactive, never valuable — deprioritise

Why Champions requires all three scores rather than an averaged FM: an averaged rulelets a single very large order (high M, low F) qualify a customer as a Champion.Requiring F≥4 and M≥4 separately means a Champion has a demonstrated buying habit.

Segment results

Segment	Customers	% of base	Net revenue	% of revenue	Avg value	Avg recency
Champions	1,282	21.9%	£11.33M	69.3%	£8,839	20 d
Loyal Customers	858	14.7%	£2.24M	13.7%	£2,610	79 d
At Risk	442	7.6%	£1.24M	7.6%	£2,802	352 d
Hibernating	1,416	24.2%	£0.73M	4.5%	£517	429 d
Potential Loyalists	689	11.8%	£0.41M	2.5%	£598	27 d
Needs Attention	493	8.4%	£0.31M	1.9%	£623	109 d
Lost Customers	481	8.2%	£0.07M	0.4%	£144	483 d
About To Sleep	96	1.6%	£0.01M	0.1%	£152	110 d
Promising Customers	65	1.1%	£0.01M	0.1%	£156	38 d
New Customers	30	0.5%	£0.005M	0.0%	£166	11 d



---

SQL analysis

Six scripts reimplement the analysis in ANSI-portable SQL (validated on DuckDB). They arenot a copy of the Python — they are an independent check on it, and they reconcileexactly.

Script	Purpose	Techniques
01_data_quality.sql	Row counts, nulls, duplicates, sign conventions, referential integrity, fact-vs-dimension reconciliation	CASE, aggregation, LEFT JOIN, subqueries
02_customer_kpis.sql	Headline KPIs, monthly trend, repeat vs one-time, order bands, geography, cohort entry	GROUP BY, HAVING, window functions
03_rfm_analysis.sql	RFM rebuilt in SQL with NTILE(5), score bands, reconciliation vs Python	CTEs, NTILE, FULL OUTER JOIN
04_customer_segments.sql	Segment rules in SQL, definition table, revenue vs headcount ranking, market composition	CASE, RANK(), conditional aggregation
05_customer_value.sql	Top customers, value deciles, Pareto, value tiers, net returners	NTILE(10), running totals, correlated subqueries
06_advanced_customer_analysis.sql	Recency bands, win-back lists, reorder cadence, cohorts, YoY change, efficiency index	LAG(), ROW_NUMBER(), self-comparison

Questions answered: top 10 by revenue · top by frequency · not purchased recently ·average revenue per customer · % of revenue from the top 10% · segments generating mostrevenue · segments with most customers · segments with highest AOV · valuable customersbecoming inactive · overall revenue concentration.

Sample — the concentration query:

WITH ranked AS (
    SELECT customer_id, monetary_net,
           ROW_NUMBER() OVER (ORDER BY monetary_net DESC) AS value_rank,
           COUNT(*)          OVER () AS total_customers,
           SUM(monetary_net) OVER () AS total_revenue
    FROM customers
)
SELECT
    ROUND(100.0 * SUM(CASE WHEN value_rank <= total_customers * 0.10
                           THEN monetary_net ELSE 0 END) / MAX(total_revenue), 2)
        AS pct_revenue_from_top_10pct
FROM ranked;
-- 63.24

---

Power BI dashboard

Four pages, specified in full in powerbi/dashboard-specification.md,with the star schema in data-model.md and ~40 DAX measures inmeasures.md. The mockups below are rendered from the realprocessed data.

Page 1 — Customer Overview. Six KPI cards (customers, revenue, orders, average customerrevenue, AOV, orders per customer) plus revenue/order trend, revenue distribution by valueband, customers by country, and monthly active customers.

Page 2 — RFM Analysis. Recency, frequency and monetary distributions; RFM scoredistribution; a 5×5 customer value matrix; and a frequency-vs-monetary scatter annotatedwith the correlation and an explicit "association, not causation" note.



Page 3 — Customer Segmentation. Customers, revenue, average revenue per customer,orders and AOV by segment — deliberately adjacent so the disagreement between headcountrank and revenue rank is visible at a glance.

Page 4 — Customer Value & Retention. Lorenz concentration curve, top 10 customers,revenue by recency band, a priority win-back table, and a segment performance bubble chart.



Slicers (synced across pages): Customer Segment · Country · Date · RFM Score.

Design principles: minimal layout, clear KPI cards, consistent typography, stronghierarchy, no 3D, no decoration, log axes relabelled in natural units, and every figurelabelled gross or net.

---

Key insights

All figures computed from the cleaned dataset; full detail inreports/business-insights.md.

1. Revenue is extremely concentrated. The top 10% of customers generate 63.2% ofnet revenue; the top 1% generate 31.1%; just 272 customers (4.6%) account for halfof all revenue. Gini coefficient 0.737.

2. Champions carry the business. 1,282 customers (21.9%) produce 69.3% of revenue,averaging £8,839 each across 17.0 orders. They are the only segment whose revenue sharematerially exceeds its headcount share (efficiency index 3.16).

3. Retention matters far more than acquisition. Repeat buyers are 72.4% of customersbut 96.7% of revenue. Customers with 13+ orders — 11% of the base — generate59.9% of revenue.

4. £1.24M is walking out the door. 442 At Risk customers previously averaged 6.7 ordersand £2,802 each, but have now been silent for ~352 days. Widening to all above-mediancustomers inactive 180+ days gives 637 customers holding £1.55M (9.5% of revenue).

5. 581 customers have broken their own buying rhythm — silent for more than 3× theirpersonal historic reorder gap — representing £1.52M. This is a sharper targetinglist than any flat recency cut-off.

6. The dormant tail is large but cheap to lose. Hibernating and Lost together are 32.4%of customers but only 4.9% of revenue.

7. Growth is treading water. Between 2010 and 2011, new and growing customers added~£2.81M while declining and churned customers removed ~£3.06M.

8. Demand is strongly seasonal. September–November produced 36.7% of full-monthrevenue across both years, peaking at £1.12M in November 2010.

9. The business is UK-concentrated. 83.5% of revenue from 91.2% of customers. Marketswith high revenue per customer (EIRE, Netherlands) rest on a handful of accounts andshould not be read as broadly attractive markets.

---

Business recommendations

Segment	Action	Evidence
Champions (1,282)	Named account management for the top ~250; churn alert if silent >60 days; early access before the autumn peak. Do not discount.	272 customers = 50% of revenue; already transacting above average AOV (£518 vs £466)
Loyal Customers (858)	Reorder prompting timed to each customer's own cadence; test category expansion with a holdout	Gap to Champions is driven by frequency (6.8 vs 17.0 orders), not basket size
Potential Loyalists / New / Promising (784)	Second-purchase incentives within the first 30 days; cap spend until conversion is measured	Median reorder gap 25 days; 55% of reorders occur within 30 days
At Risk (442)	Personalised win-back against the 581 rhythm-breakers, prioritised by historic value; lead with human contact, not vouchers	£1.52M of historic value; wholesale-scale buying (£419 AOV)
Hibernating / Lost (1,897)	Low-cost automated reactivation with a holdout; stop if uplift is not significant	32.4% of customers but 4.9% of revenue; Lost average £144 lifetime
Company-wide	Treat top-account retention as strategic risk; plan capacity for Sep–Nov; fix customer identification at point of sale	16.3% of revenue in 10 accounts; £2.57M of revenue currently unattributable

---

Limitations

• No causation can be claimed. Frequency and monetary value correlate strongly(Spearman ρ ≈ 0.86), but nothing here shows that more orders cause higher value.
• No marketing data — no campaigns, channels or contact history, so no attribution or ROI.
• No demographics or firmographics — segments are purely behavioural; "wholesale" isinferred from order sizes, not recorded.
• No cost or margin data — value is revenue, not profit.
• 22.6% of lines lack a Customer ID (£2.57M) and are invisible to customer-level analysis.
• Churn cannot be confirmed — a dormant customer may have left, be seasonal, or havemoved to another account.
• Dataset period ends December 2011, with a partial final month.
• RFM scores are relative to this base and this reference date, not an absolute standard.

---

Reproducing the analysis

git clone <repository-url>
cd customer-segmentation
pip install -r requirements.txt

# 1. Download the source data (45 MB) into data/raw/
curl -L -o data/raw/online_retail_ii.zip \
  "https://archive.ics.uci.edu/static/public/502/online+retail+ii.zip"
unzip data/raw/online_retail_ii.zip -d data/raw/

# 2. Run the full pipeline (cleaning → RFM → segments → aggregates)
python scripts/build_pipeline.py

# 3. Execute the notebooks
jupyter nbconvert --to notebook --execute --inplace notebooks/*.ipynb

# 4. Run the SQL layer (requires duckdb)
duckdb warehouse.duckdb < sql/00_schema_setup.sql
duckdb warehouse.duckdb < sql/05_customer_value.sql

# 5. Regenerate the dashboard mockups
python scripts/make_dashboard_mockups.py

# 6. Validate everything end to end
python scripts/validate_project.py

Quality control

scripts/validate_project.py is the project's quality gate. It checks that:

• revenue reconciles (Quantity × UnitPrice on every row; gross − returns = net);
• the fact table and customer dimension agree exactly on customers, orders and revenue;
• RFM values recomputed from transactions match the stored scores;
• every customer is classified into exactly one segment and shares sum to 100%;
• Python, SQL and the documented KPIs report identical figures;
• all 50 SQL statements execute;
• all notebooks execute without errors;
• every file path referenced in the documentation exists.

---

Skills demonstrated

Python · Pandas · NumPy · SQL (CTEs, window functions, NTILE, LAG,ranking, conditional aggregation) · Power BI (star schema, DAX, dashboard design) ·RFM Analysis · Customer Analytics · Data Cleaning · Exploratory DataAnalysis · Data Visualisation · Business Intelligence · Data Storytelling

Also demonstrated: reproducible pipeline design, cross-layer validation (Python ↔ SQL ↔documentation), explicit assumption documentation, and disciplined separation ofcorrelation from causation.

---

Licence & attribution

Dataset: Chen, D. (2012). Online Retail II. UCI Machine Learning Repository.https://doi.org/10.24432/C5CG6D — used here for non-commercial portfolio purposes.# customer-segmentation