Case study 05 · SQL + Power BI · customer analytics
The 1%who pay the bills
Two years of transactions from a UK online gift retailer, one million rows, cleaned and segmented in SQL. The headline: revenue held flat only because existing customers spent more, while new customers fell by more than half.
DataUCI Online Retail II, 1 Dec 2009 to 9 Dec 2011. Real transactions from one retailer, with no cost or margin data.
£19.3MRevenue, 39,397 orders
5,850Known customers
31.2%Revenue from the top 1%
−54%New customers, 2011 vs 2010
Data flow · how a record moves
A million rows to a Power BI page
Crossed-out dots are rows removed while cleaning: duplicates and same-day reversals. Guest lines carry on into revenue but not into customer views. Click a stage to read what happens there.
01 · Cleaning
A million rows, honestly counted
Before any KPI, the raw file needed work. Exact duplicates went first. Then 1,382 same-day reversal lines (an order keyed in and cancelled the same day, £420k of noise) were paired up and removed. Left in, they made the return rate look like 3.6%; the real figure is 2.0%.
16,689 genuine return lines kept and analysed separately
Guest checkouts22.6% of lines have no customer id. They carry 13.3% of revenue and can't be segmented, so the customer views leave them out and say so.
02 · Concentration
58 customers, a third of the money
Rank customers by spend and the curve is steep. The grey tick shows what an even split would look like; the red bar is what each top slice actually brings in.
AlsoEIRE is the second biggest market by revenue (£615k) with only three customers. The UK is 85% of revenue.
03 · RFM segments
Who they are, what they bring
Recency, frequency and monetary value scored with ntile(5), then mapped to eight segments. Compare the grey bar (share of customers) with the red one (share of revenue). Champions are 23% of customers and 70% of revenue.
% of customers% of revenue
At risk, high value349 customers who used to order often (median 6 orders, £1,693) but have been quiet for a median of 334 days.
04 · Growth
Flat revenue hid a shrinking funnel
January to November, 2010 against 2011. Revenue rose a little. Underneath, the business was living off the customers it already had.
Revenue£0Mfrom £8.54M · +2.4%
New customers0from 3,284 · −54%
Returning share of customers0%from 79%
Seasonality0×November vs February
When do they come back?
72% of customers place a second order. Month-1 repeat rate is 21%, it sags to 13–14% by months 10–11, then bumps to 18% at month 12: people coming back for the same season a year later.
CohortsBuilt with first_value over each customer's orders and a range() month spine, so empty months show as zero instead of disappearing.
05 · Pipeline & actions
Four SQL scripts, five recommendations
01CleanDedup, pair and drop same-day reversals, split sales from returns, flag guests.
02KPIsRevenue, orders, AOV, country and month views; lag for growth.
03RFMntile(5) scores, segment rules, QUALIFY for top-N per group.
04CohortsMonthly retention matrix, time to second order, 90-day repeat.
→Power BIResult tables exported for a KPI page and a segment page, DAX measures written up.
R1ProtectAccount care for Champions and the top 1%.
R2Win backPersonal offer for the 349 high-value at-risk customers. 10% back at median spend ≈ £59k (illustrative).
R3AcquireFind what changed in 2011 and rebuild the top of the funnel.
R42nd orderReorder nudge 30–60 days after the first purchase.
R5PeakStock and campaigns in August for the Sep–Nov rush.