CUSTOMER ANALYTICS · INTERACTIVE LAB
Choose one customer and read three pieces of evidence together: how recently they returned, how often they purchased, and how much value they generated. Then inspect multi-period slopes to separate fading engagement from a naturally long repurchase cycle.
ONLINE RETAIL · REAL DATA · 541,909 SOURCE ROWS
01 · DATA GRAIN
The lab uses the real 541,909-row Online Retail dataset from 1 December 2010 to 9 December 2011—the same source used by the original SQL, notebooks and slides. Missing IDs, cancellations, negative quantities and zero prices are removed before product lines are aggregated into invoice rows.
Customer IDs are hashed into R-xxxxxxxx identifiers and the raw file remains outside the public web directory. Preparation yields 18,532 invoices from 4,338 customers, with a twelve-customer teaching cohort selected for contrasting behaviour.
customer_id · invoice_id · occurred_at · amountOne row = one valid invoice after line aggregation; monetary values are GBP.R · F · M ARE THREE DIFFERENT QUESTIONS
RFM is attractive because its formulas are simple while its interpretation is not. We are not merely asking who spent the most; we measure distance, continuity and relationship size at a particular point in time.
Count days from the last transaction to the snapshot. Lower is often better, but daily goods and annual purchases cannot share one threshold. Read recency against the expected purchase cycle.
R = snapshot_date − max(purchase_date)Count distinct invoices, not product rows; otherwise one basket with ten items appears as ten purchases. Define the window explicitly because lifetime frequency automatically favours older customers.
F = count(distinct invoice_id)Gross sales, net sales, margin and contribution are different. This lab uses paid amount for formula clarity; a real system should use the value aligned with the decision and handle refunds and cancellations correctly.
M = sum(valid transaction value)RAW METRIC ≠ RFM SCORE
The raw metric describes the customer; a 1–4 score describes relative position within the population at the same snapshot. This lab uses quartiles: recency is reversed because lower is better, while frequency and monetary reward higher values.
When population or period changes, a score can change while the raw value stays constant. A feature store should retain raw values, binning logic and segmentation version.
04–05 · CUSTOMER MOVEMENT
Each point uses the same rolling window; a line is fitted to values normalized within that customer.
Positive means days since last purchase are rising—a negative signal.
Negative means rolling-window purchase frequency is falling.
Negative means rolling-window monetary value is falling.
CHURN SIGNAL · NOT A CALIBRATED PROBABILITY
HOW TO READ THREE SLOPES
Product, channel, service issues and alternatives.
Possible order consolidation rather than churn.
Product mix, discounts, unit price and economic context.
Compare category cadence and matched cohorts.
06 · FROM SIGNAL TO ACTION
The slope in this lab is a behavioural heuristic, not a trained and calibrated churn probability. A predictive model needs a churn horizon, future-derived labels, temporal validation, precision/recall and calibration checks, plus the product’s natural repurchase cycle.
FROM RFM HEURISTIC TO CHURN MODEL
RFM and slopes are useful, explainable features, but a model is credible only when every feature is built at prediction time without seeing the future.
For example, no purchase within 60 days after snapshot, aligned with product cadence.
Use RFM, slopes, tenure, interval variance, product diversity and refund rate from pre-snapshot data only.
Train on the past, validate on the next period and test on the latest period; do not leak future rows through random splitting.
Use precision, recall, PR-AUC, lift@k and calibration alongside contact cost and retention value.
STUDENT INVESTIGATION
FORMULA → SQL
SELECT customer_id,
DATEDIFF(:snapshot, MAX(occurred_at)) AS recency,
COUNT(DISTINCT invoice_id) AS frequency,
SUM(amount) AS monetary
FROM transactions
WHERE occurred_at > :snapshot - INTERVAL 120 DAY
AND occurred_at <= :snapshot
GROUP BY customer_id;In production, persist weekly or monthly snapshots and estimate slope by regression over multiple points rather than dividing two period scores once.
THE SAME RFM IDEA · TWO COMPUTATIONAL LANGUAGES
Read the question first, then translate the same formula between dataframe and relational operations in the slide sequence.
Remove missing IDs, duplicates, negative quantities and zero prices; otherwise the formula may be correct while its meaning is wrong.
df = df.dropna(subset=['CustomerID'])
df = df.drop_duplicates()
df = df[(df.Quantity > 0) & (df.UnitPrice > 0)]WHERE customer_id IS NOT NULL
AND invoice_no NOT LIKE 'C%'
AND quantity > 0
AND unit_price > 0The notebook creates an in-memory column; SQL creates a query-plan expression. The formula is identical.
df['TotalSum'] = df['UnitPrice'] * df['Quantity']unit_price * quantity AS line_valueTranslate groupby into GROUP BY, lambda into DATEDIFF, and count distinct invoices so basket lines do not become separate purchases.
rfm = df.groupby('CustomerID').agg({
'InvoiceDate': lambda x: (snapshot-x.max()).days,
'InvoiceNo': 'nunique',
'TotalSum': 'sum'
})SELECT customer_id,
DATEDIFF(:snapshot, MAX(invoice_date)) recency,
COUNT(DISTINCT invoice_no) frequency,
SUM(unit_price * quantity) monetary
FROM valid_transactions GROUP BY customer_id;Recency reverses because fewer days are better; ties may make the two methods form different boundaries.
r = pd.qcut(rfm.Recency, 4, labels=[4,3,2,1])
f = pd.qcut(rfm.Frequency, 4, labels=[1,2,3,4])
m = pd.qcut(rfm.Monetary, 4, labels=[1,2,3,4])5 - NTILE(4) OVER (ORDER BY recency) AS r,
NTILE(4) OVER (ORDER BY frequency) AS f,
NTILE(4) OVER (ORDER BY monetary) AS mThe original notebook introduces movement with two periods; this lab extends it across snapshots and separates R, F and M slopes.
movement['Slope'] = (
movement.RFM_ScoreQ2 /
movement.RFM_ScoreQ1
)REGR_SLOPE(recency_norm, snapshot_no) r_slope,
REGR_SLOPE(frequency_norm, snapshot_no) f_slope,
REGR_SLOPE(monetary_norm, snapshot_no) m_slopeMissingness and distribution exploration, visualisation, clustering and cell-by-cell explanation.
Computing near data, publishing reproducible views/snapshots and retaining governance.
SQL builds point-in-time feature tables; notebooks validate, visualise and prototype models.
The original SQL uses COUNT(*) and directly ranks last_order_date. This lesson first counts distinct invoices and derives elapsed days from a snapshot so Frequency and Recency match their definitions.
FROM THE ORIGINAL LECTURE · VISUAL READING
These are not a decorative gallery. They follow the reasoning sequence: definition → query → metrics → scoring → visualisation → churn signal.
BOUNDED PYTHON RUNNER · LIVE DASHBOARD
This dashboard exposes parameters, not arbitrary execution. The displayed code is the actual bounded operation, allowing students to read, run and inspect output without opening a server shell.
# Select an operation and run.{}Only four operations, twelve customer IDs and bounded snapshots/windows are accepted. There is no eval/exec, package installation, user filesystem access or public Python-service port.
ROLLING TIME WINDOW · DYNAMIC RFM
Move the snapshot or press Play and watch both boundaries: the right edge is “today,” which increases recency; the left edge expires evidence, reducing frequency and monetary value when transactions fall out.
Recency rises with time; F and M hold until the left boundary reaches a transaction.
Recency resets near zero, frequency increases and monetary value rises by the transaction amount.
Nothing is deleted from the database; the feature changes because the window no longer sees that evidence.
RFM SEGMENT MAP · CUSTOMER MOVEMENT
The map scores all 4,338 customers for the selected snapshot and window. The dashed outline marks the previous position; the solid outline marks the current one.
A segment is a business rule translating RFM scores into shared language, not a permanent truth. Changing the snapshot, window, or population quartile boundaries may move a customer.