Transaction logs to customer-base analysis
Turn raw transaction logs into a customer table with DuckDB window functions, draw cohort retention curves, and see why churn is never observed when customers can leave without telling you.
Open the Python lab in Colab Python
The decision. Decide whether an ‘inactive for N days’ rule is good enough to stop retargeting customers, by counting how many still-active customers it would drop.
- Day
- Day 1 · Customers and churn (09:15–11:00)
- Time
- 105 minutes (45 briefing, 50 lab, 10 debrief)
- Languages
- Python with DuckDB. SQL is what analytics engineers already write, and DuckDB runs it inside the notebook on Colab with no database server; the same queries work on a warehouse.
- Labs
-
Python:
labs/python/01-customer-base-sql.ipynb - Data
- UCI Online Retail II, CDNOW transaction log, Synthetic retailer (generated by mktstats.synth.retailer)
What you will be able to do
- Clean a real transaction log in SQL: cancellations, missing customer IDs, returns and same-day purchases.
- Compute frequency, recency and customer age with window functions, using exactly the definitions the Module 2 models need.
- Build monthly cohort retention curves and read them without mixing up cohorts and calendar time.
- Explain why churn is unobserved in a non-contractual setting, and measure how often a ‘no purchase in 90 days’ rule misclassifies customers against known truth.
You leave with:
- A reusable SQL query that turns any transaction log into an RFM table (frequency, recency, age, mean spend)
- Cohort retention curves for CDNOW and UCI Online Retail II
- A confusion table of a recency-rule churn flag against the synthetic retailer’s true alive status
Before you start
- Module 0 · Pre-work: environment, warm-up and coding agents and its lab.
- A Google account for Colab. The default Python runtime (CPU) is enough.
- Or the workshop’s Docker image, which has every package installed.
Where we are. The pre-work turned the CDNOW log into one row per customer and fixed one definition: repeat purchases count purchase days, not rows. It did not say what a purchase is in a messier log (cancellations, returns, lines with no customer), and it did not say who is still a customer. This module asks: from a transaction log alone, can we tell who has left?
The lab
Python lab · 01-customer-base-sql
| Step | Time |
|---|---|
| Part A · From log to purchases | |
| Exercise 1 · Clean Online Retail II in SQL | 7 min |
| Part B · The RFM table | |
| Exercise 2 · Frequency, recency, age and spend with window functions | 12 min |
| Part C · Cohorts and unobserved churn | |
| Exercise 3 · Cohort retention curves | 8 min |
| Exercise 4 · How wrong is the recency rule? | 8 min |
| Decision · Should we stop retargeting by an inactivity rule? | — |
| Python subtotal: 4 exercises | 35 min |
Stretch (optional) · RFM segments in one pass. For anyone who finishes early.
4 exercises, 35 minutes between them, in a 50-minute lab slot. Each exercise has a stub to complete, a folded solution and a checkpoint.
1. The question: who is still a customer?
A subscription business knows when a customer leaves: they cancel. An online shop does not. Its customers buy when they want and stop without telling anyone. This is the non-contractual setting, and it is the one this week is about until Module 4’s short detour into subscriptions.
In a non-contractual business, churn is never observed. A customer who has not bought for four months may have left for good, or may be someone who buys twice a year. Every retention decision (whom to retarget, whom to count in the customer base, whom to send an offer) rests on a guess about which of the two each silent customer is.
This module builds the evidence for that guess from a transaction log, in SQL, and then measures how good the most common guess is. It uses three logs:
| Log | What it is | Used for |
|---|---|---|
| UCI Online Retail II | invoice lines of a UK online gift-ware retailer, December 2009 to December 2011, many of whose customers are wholesalers (real, CC BY 4.0) | cleaning, cohorts |
| CDNOW | purchases of 2,357 customers of an online CD store who first bought in early 1997, to mid-1998 (real) | the customer table, checked against a published count |
| The synthetic retailer | 5,000 customers generated by the workshop’s own code, with each customer’s true status known | measuring how wrong a churn rule is |
The synthetic retailer runs through the whole week. Because we generated it, we know for every customer whether they were still alive at the end of the observation period. Real data can never tell you that.
2. From a log to purchases
Objective for this section: clean a real transaction log in SQL: cancellations, missing customer IDs, returns and same-day purchases.
A row of a raw log is rarely a purchase. Online Retail II has one row per invoice line (one product on one invoice). The lab’s loader stacks the workbook’s two yearly sheets and drops invoices that appear in both, which leaves 1,044,848 lines from 5,942 customers (FULL run). Before anything is counted, four kinds of row have to be dealt with:
| Problem | How common (FULL run) | Rule | Why it matters later |
|---|---|---|---|
| No customer ID | 22.5% of lines | drop the line | a line that belongs to no one cannot enter a customer table |
Cancelled invoices (number starts with C) |
1.8% of lines | drop the invoice | a cancellation is not a purchase |
| Returns and corrections (quantity ≤ 0), free items (price ≤ 0) | — | drop the line | negative spend breaks the spend model of Module 4 |
| Several lines, sometimes two timestamps, per invoice | every invoice | sum the lines of an invoice | one invoice is one purchase |
The query is a WHERE clause applied line by line, then a GROUP BY customer_id, invoice that adds up quantity * price. Group by the invoice, not the timestamp: a few invoices carry two timestamps a minute apart, and grouping on time would split them. After cleaning, the log has 36,969 invoices from 5,878 customers.
A cleaning rule is a modelling decision. Dropping 22.5% of lines because they have no customer ID is right for a model of customers, and wrong for a report of total revenue. Write the rules down where the analysis is used, not only in the query.
Purchase days
The models from Module 2 on count purchase occasions: two orders by the same customer on the same day are one occasion (the pre-work’s rule). So the next step collapses the log to one row per customer per purchase day, summing the amount into day_amount.
The lab’s worked example then adds two columns with window functions. A window function computes a value for each row from other rows of the same group, without collapsing the group the way GROUP BY does:
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) AS purchase_index,
purchase_date - LAG(purchase_date)
OVER (PARTITION BY customer_id ORDER BY purchase_date) AS days_since_prevPARTITION BY customer_id makes each customer’s rows a group; ORDER BY purchase_date puts them in date order; LAG reads the previous row’s value. On CDNOW, 14.6% of the 4,339 gaps between purchase days are shorter than 7 days (FULL run). That number is why the workshop measures time as days divided by 7 rather than in calendar weeks: counting by week would merge those purchases with the one before and understate how often people buy.
3. The RFM table: three numbers per customer
Objective for this section: compute frequency, recency and customer age with window functions, using exactly the definitions the Module 2 models need.
Pick a cutoff date \tau, the end of the period the model is allowed to see (the calibration period). For customer i, let d_{i,1} < d_{i,2} < \dots < d_{i,n_i} be their distinct purchase days on or before \tau. Then
x_i = n_i - 1, \qquad t_{x,i} = \frac{d_{i,n_i} - d_{i,1}}{7}, \qquad T_i = \frac{\tau - d_{i,1}}{7}, \tag{1}
where differences of dates are in days, so t_{x,i} and T_i are in weeks.
| Symbol | Name in the lab | Meaning |
|---|---|---|
| x_i | frequency |
repeat purchases: purchase days after the first |
| t_{x,i} | recency |
time of the last purchase, measured from the first (weeks) |
| T_i | T |
the customer’s age at the cutoff: time from the first purchase to \tau (weeks) |
| \bar z_i | monetary_value |
mean day_amount over the repeat purchase days; 0 when x_i = 0 |
Two consequences of the definitions are worth saying aloud, because swapping them is the most common bug of Day 1.
- 0 \le t_{x,i} \le T_i. Recency equals T only for a customer whose last purchase falls on the cutoff day.
- “Recency” here is not “weeks since the last purchase”. That quantity is T_i - t_{x,i}, and it is what the churn rule in section 5 uses.
The first purchase is left out of x_i and of \bar z_i on purpose. It marks the moment a customer is “born” in the data; the models describe what happens after it.
Why three numbers are enough
The models of Module 2 assume that while a customer is alive they buy at random times at a constant rate \lambda per week: a Poisson process. Under that assumption, the probability of seeing a customer’s exact purchase times s_1 < \dots < s_x during a window of length t is
\lambda^{x} e^{-\lambda t}. \tag{2}
The individual gaps do not appear: only the number of purchases x and the length of the window t. What the model does not know is how long the customer was alive and buying. They were certainly alive until their last purchase, t_x; after that they may have left at any point up to T, or may still be alive. So the whole purchase history enters the model through x, t_x and T. Statisticians call such a summary a sufficient statistic: once you have it, the rest of the log adds nothing for this model. Module 2 writes out the full likelihood.
The cutoff filter must come before purchase days are counted. A query that counts all purchase days and then drops customers acquired after \tau still lets later purchases into x, and the forecast in Module 2 would be scored on data it had already seen.
The checkpoint: a published number
On CDNOW with the cutoff 1997-09-30 (39 weeks, the calibration period Fader, Hardie and Lee use in their BG/NBD note), the lab’s query gives 1,411 of 2,357 customers (59.9%) with no repeat purchase, the count the note reports, and it agrees to 10^{-9} with the workshop’s copy of PyMC-Marketing’s rfm_summary(..., time_unit="D", time_scaler=7). Real data have no ground truth, but they can have a published number. If your table reproduces it, the definitions are right.
Check yourself. A CDNOW customer first bought on 1997-01-10 and last bought on 1997-09-30, the cutoff day, with four purchase days in between. What are x, t_x and T? Which other customers have t_x = T?
Answer
Six purchase days in all (first, four in between, last), so x = 5. From 1997-01-10 to 1997-09-30 is 263 days, so t_x = T = 263/7 \approx 37.6 weeks. Recency equals T only for customers whose last purchase is on the cutoff day. A customer with no repeat purchase has x = 0 and t_x = 0, and T is their age.
4. Cohorts: reading retention curves
Objective for this section: build monthly cohort retention curves and read them without mixing up cohorts and calendar time.
A cohort is the group of customers whose first purchase fell in the same month c. Its retention curve gives, for k = 0, 1, 2, \dots months after the first,
the share of the cohort that bought in month k of its life. Every customer buys in their first month, so a_c(0) = 1. In SQL: one row per customer per month with a purchase (SELECT DISTINCT customer_id, date_trunc('month', date)), the first month per customer with MIN(month) OVER (PARTITION BY customer_id), then a count per cohort and k.
Three things to keep straight when you read the curves:
- k is the cohort’s age, not the calendar. Month k = 12 of the January cohort and of the June cohort are different calendar months. A seasonal spike shows up at different k for different cohorts; a drop in one calendar month shows up as a diagonal in a cohort-by-month table.
- The first cohort of a log is special. Online Retail II starts in December 2009, so its first “cohort” contains every customer who bought that month, including long-standing customers whose real first purchase is years earlier. The data cannot say when they first bought (left censoring). In the lab its month-12 share is 0.376, against 0.14 to 0.22 for the next five cohorts (FULL run). It is a mixture of loyal old customers and new ones, not a better cohort.
- “Active in month k” is not “alive”. The curves fall fast and then flatten. The level where a curve flattens is the share of a cohort that buys in any given month, not the share that is still a customer. A customer who buys every quarter is inactive in two months out of three while being a perfectly good customer.
The last point is the bridge to the rest of the day. A retention curve describes a cohort; it cannot say which individual customers are still with you.
Check yourself. A colleague reads the Online Retail II chart and says: “The December 2009 cohort retains customers almost twice as well as the cohorts after it, so whatever we did in December 2009 worked.” What is wrong with the reading?
Answer
The December 2009 group is not a cohort of new customers. The log starts that month, so it contains every customer who bought in December 2009, including established customers who first bought long before the data begin (left censoring). Established customers have survived for years and buy more regularly, so the group’s month-12 share (0.376) is higher for reasons that have nothing to do with December 2009. Compare cohorts only when their first month is a true first purchase.
5. Unobserved churn and the recency rule
Objective for this section: explain why churn is unobserved in a non-contractual setting, and measure how often a “no purchase in 90 days” rule misclassifies customers against known truth.
The most common working definition of churn is a recency rule: a customer with no purchase in the last N days has left. With the table of Equation 1, the rule is one comparison:
\text{flag}_i(N) = \mathbb{1}\big[\, 7\,(T_i - t_{x,i}) > N \,\big], \tag{4}
where 7\,(T_i - t_{x,i}) is the number of days since customer i’s last purchase and \mathbb{1}[\cdot] is 1 when the condition holds.
Why a fixed cutoff must misclassify
Take a customer who is alive and buys at rate \lambda per week. Under the Poisson assumption of section 3, the number of purchases in N days is Poisson with mean \lambda N / 7, so the probability that they make none, and are therefore flagged, is
P(\text{flagged} \mid \text{alive}, \lambda) = e^{-\lambda N / 7}. \tag{5}
For N = 90 days (12.9 weeks), a customer who buys about once every 4 weeks (\lambda = 0.25) is flagged with probability e^{-3.2} \approx 0.04; once every 10 weeks (\lambda = 0.1), e^{-1.3} \approx 0.28; once every 20 weeks (\lambda = 0.05), e^{-0.64} \approx 0.53. (These are calculations from Equation 5, not lab results.) A fixed N calls slow, loyal buyers lost, and keeps a fast buyer who stopped three weeks ago. A longer N trades one error for the other. No N removes both, because the rule ignores how often each customer used to buy.
What the lab measured
On the synthetic retailer at the end of its calibration period (2024-12-29), we know each customer’s true status. Call a customer positive if the rule flags them as churned, and compare with the truth (positive = not alive). With N = 90 days (FULL run):
| Truly left | Truly alive | Total | |
|---|---|---|---|
| Flagged as churned | 2,566 | 832 | 3,398 |
| Not flagged | 153 | 1,449 | 1,602 |
| Total | 2,719 | 2,281 | 5,000 |
The rule flags 3,398 customers, of whom 832 (24.5%) are still alive. Put the other way, it calls more than a third of the alive customers (832 of 2,281) lost. It misses few customers who have really left (153), because most of them have been silent for a long time.
That table exists only because we generated the data. On a real log you would see the 3,398 flagged customers and nothing else, which is exactly why a rule like this survives in practice: nobody can check it.
Check yourself. The synthetic retailer’s referral customers buy at about 0.21 purchases per week on average, its social customers at about 0.11 (true average rates, given in Module 3’s lab). Under Equation 5, which group’s alive customers does a 90-day rule flag more often, and by roughly how much for a customer at each group’s average rate?
Answer
Social. At \lambda = 0.11, e^{-0.11 \times 12.9} = e^{-1.42} \approx 0.24; at \lambda = 0.21, e^{-2.7} \approx 0.07. An alive social customer at the average rate is flagged about three times as often as an alive referral customer. A single cutoff applied to both treats the channels differently without anyone deciding to. Module 3 measures the channel differences properly, and Module 2 replaces the cutoff with a probability that uses each customer’s own rate.
6. The decision
The retailer pays to retarget every customer with ads. The proposal is to stop retargeting anyone inactive for N days. Dropping a customer who has left saves money; dropping one who is still alive may lose their next purchases.
The rule is stated before the numbers, with the economics that justify it. Let c be the retargeting cost per customer per quarter, Q the number of quarters, m the margin per purchase and y_i customer i’s purchase days in the holdout year after the cutoff. Then the net value of the rule is
\text{net}(N) = c\,Q \sum_i \text{flag}_i(N) \;-\; m \sum_{i\,:\,\text{flag}_i(N) = 1,\ \text{alive}_i} y_i . \tag{6}
The first term is the retargeting spend saved; the second is the margin of the alive customers it drops, under the pessimistic assumption that a dropped alive customer makes none of their next purchases. The lab uses the retailer’s assumed c = \$1.50 per quarter and m = \$6 per purchase over Q = 4 quarters.
Decision. Use an inactivity rule only if the margin from the alive customers it drops is less than the retargeting cost it saves (Equation 6). On the synthetic retailer (FULL run; the costs are assumptions, not measurements):
| N (days) | Customers dropped | Of which alive | Margin lost | Retargeting saved | Net |
|---|---|---|---|---|---|
| 30 | 4,066 | 1,369 | $27,750 | $24,396 | −$3,354 |
| 60 | 3,686 | 1,046 | $15,978 | $22,116 | +$6,138 |
| 90 | 3,398 | 832 | $9,708 | $20,388 | +$10,680 |
| 180 | 2,685 | 526 | $3,774 | $16,110 | +$12,336 |
A 30-day rule loses money; of the four rules tried, 180 days nets the most, and it still stops retargeting 526 customers who are alive. There is no probability here: the table is one realization under fixed assumptions. Your sentence names an N (or rejects the rule), the customers and dollars it concerns, and what would change it: a different margin or cost, evidence on whether retargeting causes purchases at all (Modules 6 and 11), or a model that tells alive from lapsed customers better than recency alone (Module 2).
Summary
- A raw log is not a list of purchases. Drop lines with no customer, cancellations, returns and free items; sum lines into invoices; collapse invoices into purchase days.
- Window functions (
PARTITION BY,ORDER BY,LAG,ROW_NUMBER,MIN() OVER) compute per-customer quantities without collapsing rows. - Frequency x, recency t_x and age T (Equation 1), measured at a cutoff in weeks of 7 days, are all a BTYD model needs from a purchase history (Equation 2). Filter by the cutoff before counting.
- Cohort retention curves (Equation 3) describe groups, by cohort age. The first cohort of a log is left-censored. “Active in month k” is not “alive”.
- Churn is unobserved in a non-contractual business. A fixed recency rule (Equation 4) must misclassify slow buyers (Equation 5); on the synthetic retailer a 90-day rule flagged 832 alive customers out of 3,398 flagged.
Equations and where you implement them
| Equation | What it is | In Lab 1 |
|---|---|---|
| Cleaning rules (section 2) | lines → invoices | Exercise 1: clean_online_retail(con); Checkpoint 1 runs a toy table with every case, then 1b checks 36,969 invoices on the real file |
Purchase days and LAG gaps |
one row per customer per day | Provided worked example; the gap histogram |
| Equation 1 | x, t_x, T, mean spend | Exercise 2: rfm_sql(con, table, cutoff); Checkpoint 2 compares with rfm_summary and the published 1,411 |
| Equation 2 | why (x, t_x, T) suffices | Not computed in the lab; Module 2 fits the models built on it |
| Equation 3 | cohort retention | Exercise 3: cohort_retention_sql(con, table); Checkpoint 3 checks a_c(0) = 1, cohort sizes and an independent pandas count |
| Equation 4 | the churn rule | Exercise 4: recency_rule(rfm, days) and confusion(flag, true_alive); Checkpoint 4 checks the 90-day counts |
| Equation 5 | why a fixed cutoff misclassifies | Not in the lab; it explains Exercise 4’s false positives |
| Equation 6 | value of the rule | Provided: the Decision cell |
Gotchas
- Timestamps are not days. Invoice dates carry a time of day; cast to
DATEbefore counting purchase days, or one day’s two orders become two occasions. - Weeks are days divided by 7.
rfm_summary(time_unit="W")merges purchases in the same calendar week and counts whole weeks; it gives more customers without a repeat purchase and different model estimates. Usetime_unit="D", time_scaler=7. - Cutoff first. Filter to purchases on or before the cutoff before counting anything, or the holdout leaks into the calibration table.
- Recency is measured from the first purchase. “Weeks since the last purchase” is T - t_x. Swapping the two makes lapsed customers look alive.
- Customers acquired after the cutoff do not exist yet at the cutoff. Leave them out of the calibration table and of any churn count.
- The first and last months of a log are partial. The first cohort is left-censored; Online Retail II’s last month has nine days of data, so every curve dips at its last point.
- Integer division. In DuckDB,
/on integers returns a decimal (7 / 2 = 3.5); some databases truncate it to 3. Check before porting the query to a warehouse.
Next steps
The recency rule needs an arbitrary cutoff, gives no probability, cannot forecast purchases, and treats a slow loyal buyer like a lost one. What we want instead is, for each customer, the probability that they are still alive given their own x, t_x and T, and a forecast of how many purchases they will make. Module 2 builds both from a model of how customers buy and how they silently leave, checks that the model recovers known parameters, and turns P(alive) into a retargeting threshold set by costs and margins rather than by habit.
Further reading
- Schmittlein, D. C., Morrison, D. G., & Colombo, R. (1987). Counting your customers: Who-are they and what will they do next? Management Science, 33(1), 1–24. The paper that posed this module’s question for non-contractual businesses.
- Wübben, M., & v. Wangenheim, F. (2008). Instant customer base analysis: Managerial heuristics often “get it right”. Journal of Marketing, 72(3), 82–93. Recency heuristics can rival the models at telling active from inactive customers; read it before assuming the model always wins.
- Fader, P. S., Hardie, B. G. S., & Lee, K. L. (2005). Implementing the BG/NBD model for customer base analysis in Excel. The source of the 1,411 count and of the CDNOW calibration period.
- Chen, D. (2012). Online Retail II. UCI Machine Learning Repository. The dataset page, with its column descriptions.
All entries, with the date each was checked, are on the References page.