Mathew K Analytics

Lesson 7 · Real-World Data Analytics

Customer Lifetime Value (CLV) in Python, Tested on Real Data | Data Analytics #7

How much is each customer worth? We calculate customer lifetime value in Python with a simple CLV formula, then do what most tutorials skip: check the…

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 7: Customer Lifetime Value Modeling#

  • Video seven of the hundred-video real-world data analytics series.
  • Predicting real customer value forward, then honestly checking the real prediction against real revenue that actually happened next.
  • Let's get into it.

This lesson is for analysts who want to put a forward-looking value on each customer, and then check honestly whether that prediction holds up. The business question is simple: which customers are likely to be worth the most over the coming months, and how far can you trust that estimate?

Customer lifetime value (CLV) is the revenue you expect a customer to bring in over a chosen period. You will use online_retail_clean.csv, the cleaned online retail transactions from earlier lessons, focusing on CustomerID, InvoiceNo, InvoiceDate, Revenue and Country. The data runs from December 2010 to December 2011.

In this lesson you will:

  • split the history into a calibration period for building the model and a holdout period for testing it
  • build a simple CLV as average order value times purchase frequency times an assumed lifespan
  • validate predictions against what customers really spent, using correlation and mean absolute error
  • link CLV to recency and frequency scores and flag high-value customers who went quiet

You should be comfortable with pandas groupby with named aggregation and with pd.qcut; the RFM lesson earlier in the series is a useful refresher.

Part 1: Why CLV Needs a Real Calibration and Real Holdout Split#

Any model that predicts the future needs to be tested on a future it has not seen, otherwise you are only measuring how well it remembers the past. That is why you split the data in time: a calibration period to build features and a holdout period to check predictions. This step loads the data, converts CustomerID to whole numbers with astype(int), and shows the date range. The output runs from 1 December 2010 to 9 December 2011, about a year of trading to divide.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
clean = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
clean['CustomerID'] = clean['CustomerID'].astype(int)
clean['InvoiceDate'].min(), clean['InvoiceDate'].max()
(Timestamp('2010-12-01 08:26:00'), Timestamp('2011-12-09 12:50:00'))

Part 2: Splitting Real Calibration and Real Holdout Periods#

This step picks the cutoff date. Everything before 1 September 2011 becomes the calibration set and everything on or after it becomes the holdout, using simple boolean comparisons on InvoiceDate. The output shows 223,127 calibration rows and 168,023 holdout rows. The holdout is only about three months long but holds a lot of rows, because autumn is this retailer's busy pre-Christmas season. Be aware that this seasonality can make the holdout look different from the calibration period for reasons that have nothing to do with the model.

cutoff = pd.Timestamp('2011-09-01')
calib = clean[clean['InvoiceDate'] < cutoff]
holdout = clean[clean['InvoiceDate'] >= cutoff]
calib.shape[0], holdout.shape[0]
(223127, 168023)

Part 3: Real Per-Customer Calibration Features#

CLV is built per customer, so the transaction rows need to be rolled up to one row per customer. The code groups the calibration data by CustomerID and uses named aggregation to compute total revenue, the number of distinct invoices with nunique, and the first and last purchase dates with min and max. The output shows 3,314 customers in the calibration period. Using only calibration rows here is essential: if holdout data leaked into these features, the later validation would be meaningless.

cust = calib.groupby('CustomerID').agg(TotalRevenue=('Revenue', 'sum'), Orders=('InvoiceNo', 'nunique'), FirstPurchase=('InvoiceDate', 'min'), LastPurchase=('InvoiceDate', 'max'))
cust.shape[0]
3314

Part 4: Real Average Order Value per Customer#

Average order value (AOV) is how much a customer spends per invoice, the first building block of the CLV formula. The code divides total revenue by the number of orders, then .describe() summarises the result. The output shows a median AOV of about 279 GBP but a mean of about 398, and a maximum of 77,183.60. When the mean sits well above the median like this, a few very large values are pulling it up. That maximum is likely a single huge order, so keep an eye on it.

cust['AvgOrderValue'] = cust['TotalRevenue'] / cust['Orders']
cust['AvgOrderValue'].describe().round(2)
count     3314.00
mean       397.76
std       1443.11
min          2.90
25%        167.16
50%        279.09
75%        409.35
max      77183.60
Name: AvgOrderValue, dtype: float64

Part 5: Real Purchase Frequency per Month#

The second building block is how often each customer buys. The code works out the length of the calibration period in days, converts it to months by dividing by 30, and divides each customer's order count by that. The output shows a median of 0.22 orders per month and a minimum of 0.110, which is what one order over about nine months gives. The maximum of 13.846 orders a month belongs to a very frequent buyer. Note that every customer is divided by the full period, even those who first bought only recently, which understates their true rate.

calib_period_days = (cutoff - calib['InvoiceDate'].min()).days
cust['PurchaseFreqPerMonth'] = cust['Orders'] / (calib_period_days / 30)
cust['PurchaseFreqPerMonth'].describe().round(3)
count    3314.000
mean        0.376
std         0.609
min         0.110
25%         0.110
50%         0.220
75%         0.440
max        13.846
Name: PurchaseFreqPerMonth, dtype: float64

Part 6: The Real Simple CLV Formula#

Now the pieces combine: predicted CLV equals AOV times purchases per month times an assumed lifespan, here set to 12 months. This is a deliberately simple model, which makes it easy to explain and to check. The output shows a median predicted 12-month value of about 716 GBP and a mean of about 2,048, with a maximum of 231,801.68. The big gap between mean and median tells you value is concentrated in a small group of customers, a common pattern in retail.

lifespan_months = 12
cust['PredictedCLV'] = cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * lifespan_months
cust['PredictedCLV'].describe().round(2)
count      3314.00
mean       2048.21
std        7807.41
min           3.82
25%         337.48
50%         715.82
75%        1767.17
max      231801.68
Name: PredictedCLV, dtype: float64

Part 7: Real Top Predicted-CLV Customers#

Ranking customers by predicted CLV shows who the model thinks matters most. The code sorts in descending order, keeps three columns, and shows the top 10. Customer 14646 leads with about 231,802 GBP, driven by both a high AOV and 4.40 orders a month. The list also shows two different routes to high value: customer 12346 has one enormous order (AOV 77,183.60 at 0.11 orders a month), while 14911 places small orders very often (11.32 a month). A single huge order is a much shakier basis for a forecast than a steady buying habit.

cust.sort_values('PredictedCLV', ascending=False)[['AvgOrderValue', 'PurchaseFreqPerMonth', 'PredictedCLV']].head(10).round(2)
AvgOrderValue PurchaseFreqPerMonth PredictedCLV
CustomerID
14646 4394.57 4.40 231801.68
18102 5020.66 2.86 172137.01
12415 8233.01 1.32 130280.65
17450 3891.38 2.42 112892.76
14156 2417.92 3.85 111596.51
12346 77183.60 0.11 101780.57
14911 666.33 11.32 90503.30
17511 2808.77 2.20 74077.54
13694 1177.22 4.29 60542.82
17949 1562.95 3.19 59770.02

Part 8: Real Total Predicted Portfolio Value#

Adding up every customer's prediction gives a headline figure for the whole customer base. The code sums PredictedCLV and prints it with the format :,.0f, which adds thousands separators and drops decimals. The output is about 6,787,762 GBP of predicted 12-month value. Treat this as a rough planning number rather than a forecast to bank on: it depends heavily on the 12-month lifespan assumption, which you will stress test later, and it assumes every customer keeps buying at their past rate.

total_predicted_clv = cust['PredictedCLV'].sum()
print(f'Total real predicted twelve-month CLV across the customer base: {total_predicted_clv:,.0f} GBP')
Total real predicted twelve-month CLV across the customer base: 6,787,762 GBP

Part 9: Real Actual Revenue During the Real Holdout Window#

To validate the model you need to know what customers really spent after the cutoff. The code sums holdout revenue per customer, then uses cust.index.map(...) to attach it to the calibration table, with .fillna(0) for customers who bought nothing. Taking .mean() of the condition > 0 gives the share who spent anything. The output 0.588 means about 59% of calibration customers came back in the holdout. Customers who appear only in the holdout are ignored, since the model never saw them.

actual_holdout_rev = holdout.groupby('CustomerID')['Revenue'].sum()
cust['ActualHoldoutRevenue'] = cust.index.map(actual_holdout_rev).fillna(0)
(cust['ActualHoldoutRevenue'] > 0).mean().round(3)
np.float64(0.588)

Part 10: Scaling the Real Prediction to the Real Holdout Horizon#

The CLV figure is for 12 months, but the holdout is shorter, so to compare like with like you need a prediction for the holdout length. The code measures the days from the cutoff to the last date in the data, divides by 30 to get months, and recomputes the prediction using that horizon. The output shows 3.3 months. Forgetting this step is a common mistake: comparing a 12-month prediction with three months of actual spend would make the model look wildly over-optimistic.

holdout_months = (clean['InvoiceDate'].max() - cutoff).days / 30
cust['PredictedHoldoutRevenue'] = cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * holdout_months
round(holdout_months, 2)
3.3

Part 11: Real Validation, Correlation#

Correlation measures how closely two columns move together, from -1 to 1, with 1 meaning a perfect straight-line relationship. The code builds a correlation matrix with .corr() and takes the single off-diagonal value with .iloc[0, 1]. The output is 0.68, a fairly strong positive relationship: customers predicted to spend more generally did spend more. Correlation only checks the ranking and direction, though. A model could be consistently too high or too low and still score well, which is why the next step checks the size of the errors.

correlation = cust[['PredictedHoldoutRevenue', 'ActualHoldoutRevenue']].corr().iloc[0, 1]
round(correlation, 3)
np.float64(0.68)

Part 12: Real Validation, Mean Absolute Error#

Mean absolute error (MAE) is the average size of the gap between prediction and reality, ignoring whether it is too high or too low. The code subtracts actual from predicted, takes .abs() and then .mean(). The output is about 624.80 GBP per customer. To judge that number, compare it with typical spend: the median AOV earlier was about 279 GBP, so an average miss of over 600 GBP is large for a typical customer. MAE is sensitive to the handful of very big customers, so a median absolute error would be a useful companion.

holdout_mae = (cust['PredictedHoldoutRevenue'] - cust['ActualHoldoutRevenue']).abs().mean()
round(holdout_mae, 2)
np.float64(624.8)

Part 13: Visualizing Real Predicted vs Real Actual#

A scatter plot of predicted against actual shows where the model works and where it fails. The code plots each customer as a semi-transparent point with alpha=0.3, draws a dashed red diagonal for perfect predictions, and limits both axes to the 99th percentile so a few extreme customers do not squash the view. Open clv_predicted_vs_actual.png and look for points along the diagonal, a band of points sitting on the x-axis (customers predicted to spend who spent nothing), and whether points sit mostly above or below the line.

plt.figure(figsize=(8, 8))
plt.scatter(cust['PredictedHoldoutRevenue'], cust['ActualHoldoutRevenue'], alpha=0.3, s=15, color='teal')
max_val = max(cust['PredictedHoldoutRevenue'].quantile(0.99), cust['ActualHoldoutRevenue'].quantile(0.99))
plt.plot([0, max_val], [0, max_val], color='red', linestyle='--', label='Real perfect prediction line')
plt.xlim(0, max_val)
plt.ylim(0, max_val)
plt.xlabel('Real Predicted Holdout Revenue')
plt.ylabel('Real Actual Holdout Revenue')
plt.title('Real Predicted vs Real Actual Customer Revenue')
plt.legend()
plt.tight_layout()
plt.savefig('clv_predicted_vs_actual.png', dpi=120)
plt.close()

Part 14: Rebuilding Real RFM Segments for This Same Population#

Recency, how recently a customer last bought, is a classic signal of future value. It is the R in RFM (recency, frequency, monetary). The code sets a snapshot date one day after the last calibration purchase, counts days since each customer's last order, and uses pd.qcut to split them into four equal-sized groups. The labels run [4, 3, 2, 1] so the most recent quarter gets a 4. The output shows four groups of 807 to 850 customers, roughly equal as expected from quartiles.

snapshot_date = calib['InvoiceDate'].max() + pd.Timedelta(days=1)
recency = (snapshot_date - cust['LastPurchase']).dt.days
cust['R_score'] = pd.qcut(recency, 4, labels=[4, 3, 2, 1], duplicates='drop').astype(int)
cust['R_score'].value_counts().sort_index()
R_score
1    816
2    841
3    807
4    850
Name: count, dtype: int64

Part 15: Real Average Predicted CLV by Recency Score#

This step checks whether the simple CLV formula agrees with recency, even though recency is not part of the formula. The code groups by R_score and averages PredictedCLV. The output rises steadily, from about 692 GBP for the least recent group to about 4,645 for the most recent. That makes sense: frequent buyers are more likely to have bought recently. A useful extension would be to check whether recency also predicts actual holdout revenue, since a customer who stopped buying months ago may be overvalued by a formula that ignores recency.

cust.groupby('R_score')['PredictedCLV'].mean().round(2).sort_index(ascending=False)
R_score
4    4644.50
3    1768.44
2    1008.67
1     691.81
Name: PredictedCLV, dtype: float64

Part 16: Real Average Predicted CLV by Order Frequency Tier#

Frequency is the F in RFM. Because many customers share the same order count, pd.qcut on raw counts would fail with duplicate bin edges, so the code first applies .rank(method='first'), which gives every customer a unique rank, then splits into quartiles. The output shows the top frequency group averaging about 5,609 GBP of predicted CLV, while groups 1 and 2 are almost identical at about 618 and 623. That is expected, since frequency is built into the formula, and it shows most of the value sits with the most frequent quarter.

cust['F_score'] = pd.qcut(cust['Orders'].rank(method='first'), 4, labels=[1, 2, 3, 4]).astype(int)
cust.groupby('F_score')['PredictedCLV'].mean().round(2).sort_index(ascending=False)
F_score
4    5608.89
3    1340.94
2     622.85
1     617.58
Name: PredictedCLV, dtype: float64

Part 17: Real High-Predicted, Zero-Actual Churn Risk Flags#

The most actionable output of a CLV model is often the list of valuable customers who have gone quiet. The code keeps customers above the 90th percentile of predicted CLV with .quantile(0.9), then filters those with zero holdout revenue. The output shows 33 of the 332 high-value customers, about 10%, spent nothing in the holdout. These are strong candidates for a win-back call or offer. A three-month gap is not proof they have churned, though, since some good customers simply order less often.

high_predicted = cust[cust['PredictedCLV'] > cust['PredictedCLV'].quantile(0.9)]
churn_risk = high_predicted[high_predicted['ActualHoldoutRevenue'] == 0]
churn_risk.shape[0], high_predicted.shape[0]
(33, 332)

Part 18: Real Lost Value from Those Churn-Risk Customers#

Putting a money figure on the quiet customers helps a team decide how much effort a win-back campaign deserves. The code sums the predicted holdout revenue for the 33 churn-risk customers and prints it. The output is about 107,562 GBP that the model expected but did not see. Present this carefully: it is what the model predicted, not money that was definitely lost, and the model itself has an average error of over 600 GBP per customer.

lost_predicted_value = churn_risk['PredictedHoldoutRevenue'].sum()
print(f'Real predicted revenue left on the table from likely-churned high-value customers: {lost_predicted_value:,.0f} GBP')
Real predicted revenue left on the table from likely-churned high-value customers: 107,562 GBP

Part 19: Real Sensitivity to the Lifespan Assumption#

The 12-month lifespan was an assumption, so you should show how much the answer depends on it. The loop recomputes total predicted CLV for 6, 12, 18 and 24 months and prints each. The output scales exactly in proportion, from about 3.39 million GBP at 6 months to about 13.58 million at 24. That is because lifespan is a simple multiplier in the formula. The takeaway: the total is driven as much by your assumption as by the data, so always state the lifespan when you quote a CLV figure.

for months in [6, 12, 18, 24]:
    total = (cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * months).sum()
    print(f'{months}-month lifespan assumption: {total:,.0f} GBP total real predicted CLV')
6-month lifespan assumption: 3,393,881 GBP total real predicted CLV
12-month lifespan assumption: 6,787,762 GBP total real predicted CLV
18-month lifespan assumption: 10,181,643 GBP total real predicted CLV
24-month lifespan assumption: 13,575,524 GBP total real predicted CLV

Part 20: Real One-Time Buyers vs Real Predicted CLV#

One-time buyers are a large and uncertain group, so it is worth seeing how the model treats them. The code splits customers into those with exactly one order and those with more, and averages predicted CLV for each. The output shows about 531 GBP for one-timers against about 3,129 GBP for repeat buyers, roughly six times more. The formula gives one-timers a small but positive value because it assumes they keep buying at one order per nine months, which may be optimistic for someone who never returned.

one_timers = cust[cust['Orders'] == 1]
one_timers['PredictedCLV'].mean().round(2), cust[cust['Orders'] > 1]['PredictedCLV'].mean().round(2)
(np.float64(531.39), np.float64(3129.19))

Part 21: Real Sanity Check on the Formula#

A quick manual recalculation confirms the vectorised column was built as intended. The code takes the top predicted customer, recomputes AOV times frequency times lifespan by hand with .loc, and checks the difference from the stored value is below 0.01. The output True confirms they match. Comparing with a small tolerance rather than == is good practice with floating-point numbers, which can differ in the last decimal places after arithmetic.

sample_customer = cust.sort_values('PredictedCLV', ascending=False).index[0]
manual_clv = cust.loc[sample_customer, 'AvgOrderValue'] * cust.loc[sample_customer, 'PurchaseFreqPerMonth'] * lifespan_months
abs(manual_clv - cust.loc[sample_customer, 'PredictedCLV']) < 0.01
np.True_

Part 22: Saving the Real CLV Table#

Saving the customer table lets marketing or a later lesson use it without rerunning the analysis. The code picks the key columns, rounds to two decimals, and writes customer_lifetime_value.csv. Here the index, CustomerID, is saved because to_csv keeps the index by default, which is what you want. Reloading and comparing row counts gives True, so all 3,314 customers made it into the file.

output_cols = ['AvgOrderValue', 'PurchaseFreqPerMonth', 'PredictedCLV', 'ActualHoldoutRevenue', 'R_score', 'F_score']
cust[output_cols].round(2).to_csv('customer_lifetime_value.csv')
reloaded = pd.read_csv('customer_lifetime_value.csv')
reloaded.shape[0] == cust.shape[0]
True

Part 23: Real Final Business Summary#

A one-line summary is what most stakeholders will read, so it should carry both the scope and the trust level. The code prints the number of customers and the holdout correlation. The output reads 3,314 customers, with a correlation of 0.68 between predicted and actual holdout spend. Quoting a validation figure alongside the prediction is a good habit: it tells the reader how far to trust the numbers.

print(f'Predicted CLV for {len(cust)} real customers; model correlation with real actual holdout spend was {round(correlation, 2)}.')
Predicted CLV for 3314 real customers; model correlation with real actual holdout spend was 0.68.

Part 24: Visualizing the Real Predicted CLV Distribution#

A histogram shows how predicted value is spread across customers. Because a few customers have enormous values, the code uses .clip(upper=...) at the 95th percentile so that everything above it is set to that cap, keeping the main shape visible. Open clv_distribution.png and expect a strongly right-skewed shape: most customers near the low end and a long tail to the right, plus a small spike at the far right where the clipped values pile up. That spike is an artefact of clipping, not a real cluster.

plt.figure(figsize=(9, 5))
plt.hist(cust['PredictedCLV'].clip(upper=cust['PredictedCLV'].quantile(0.95)), bins=40, color='indigo', edgecolor='white')
plt.xlabel('Real Predicted Twelve-Month CLV (GBP)')
plt.ylabel('Real Number of Customers')
plt.title('Real Distribution of Predicted Customer Lifetime Value')
plt.tight_layout()
plt.savefig('clv_distribution.png', dpi=120)
plt.close()

Part 25: Real Average Predicted CLV by Country#

Country can guide where to focus account management. Each customer is given their most frequent country with a mode() lookup, and the code averages predicted CLV per country and shows the top eight. The output is led by EIRE at about 69,253 GBP, then the Netherlands at about 29,557 and Australia at about 17,956. These averages are likely based on very few customers each, so one large wholesale buyer can dominate. Always check the customer count per country with .size() or .count() before drawing conclusions.

country_lookup = calib.groupby('CustomerID')['Country'].agg(lambda s: s.mode().iloc[0])
cust['Country'] = cust.index.map(country_lookup)
cust.groupby('Country')['PredictedCLV'].mean().sort_values(ascending=False).head(8).round(2)
Country
EIRE           69252.85
Netherlands    29556.66
Australia      17955.57
Singapore      10709.18
Japan           5911.25
Israel          4670.55
Sweden          4536.31
Iceland         3680.25
Name: PredictedCLV, dtype: float64

Part 26: Real Mean vs Real Median CLV#

Choosing between mean and median changes the story you tell. The output shows a mean predicted CLV of about 2,048 GBP and a median of about 716. The mean is almost three times the median, confirming the heavy right skew seen in the histogram. For a typical customer, quote the median. For budgeting total value across the base, the mean (or the total) is the relevant figure, because it includes the big accounts.

cust['PredictedCLV'].mean().round(2), cust['PredictedCLV'].median().round(2)
(np.float64(2048.21), np.float64(715.82))

Part 27: Does the Real Full Formula Beat a Real Naive Baseline#

A model is only worth using if it beats a simple alternative. The naive baseline here is AOV on its own, ignoring frequency. The code correlates AOV with actual holdout revenue and shows it next to the full model's correlation. The output is 0.091 for the baseline against 0.68 for the full formula. Order size alone tells you almost nothing about future spend, while adding frequency makes a big difference. Comparing with a baseline is a habit worth building for every model.

naive_correlation = cust[['AvgOrderValue', 'ActualHoldoutRevenue']].corr().iloc[0, 1]
round(naive_correlation, 3), round(correlation, 3)
(np.float64(0.091), np.float64(0.68))

Part 28: Real Total Actual Revenue Cross-Check#

Joins and map operations can quietly drop or duplicate values, so it is worth reconciling totals. The code sums actual holdout revenue from the customer table and compares it with holdout revenue for the same customers taken directly from the transactions, filtered with .isin(cust.index). The output True confirms the two totals agree within 1 GBP, so the mapping did not lose any spend.

total_actual_from_cust = cust['ActualHoldoutRevenue'].sum()
total_actual_from_holdout = holdout[holdout['CustomerID'].isin(cust.index)]['Revenue'].sum()
abs(total_actual_from_cust - total_actual_from_holdout) < 1
np.True_

Part 29: Real High-Value Customer Count by Threshold#

Thresholds help translate CLV into targeting decisions, such as who gets a dedicated account manager. The loop counts customers predicted above 500, 1,000, 2,000 and 5,000 GBP. The output shows 2,029 above 500 GBP, 1,316 above 1,000, 719 above 2,000 and 217 above 5,000. Seeing the counts drop like this helps you pick a threshold that matches the size of the team or budget available.

for threshold in [500, 1000, 2000, 5000]:
    n_above = (cust['PredictedCLV'] > threshold).sum()
    print(f'{n_above} real customers predicted above {threshold} GBP twelve-month CLV')
2029 real customers predicted above 500 GBP twelve-month CLV
1316 real customers predicted above 1000 GBP twelve-month CLV
719 real customers predicted above 2000 GBP twelve-month CLV
217 real customers predicted above 5000 GBP twelve-month CLV

Part 30: Real Number of Customers With Any Predicted Value#

A final check confirms that every customer received a usable prediction. The code counts customers with predicted CLV above zero. The output is 3,314, the same as the total number of customers, so nobody ended up with a zero or negative value. That fits the formula: everyone in the calibration table has at least one order and positive revenue. If you later include returns or credit notes, rerun this check, since negative totals would break that assumption.

(cust['PredictedCLV'] > 0).sum()
np.int64(3314)

Wrap-Up: What You Learned#

  • A real CLV estimate is genuinely just average order value, times real purchase frequency, times an assumed real customer lifespan.
  • Any real CLV model needs a real calibration and real holdout split; predicting the real future and then checking it is the whole point.
  • A real positive correlation between predicted and real actual holdout revenue is what actually earns the model any trust.
  • Segmenting predicted CLV by real recency and real frequency shows exactly which customer types are genuinely worth investing in.
  • Customers with real high predicted value but real zero actual return are the real highest-value churn-risk flags a team can act on.
  • Next video: real product category performance and ABC analysis, ranking what this business actually sells best.

Practice on your own

  1. Move the cutoff to 2011-07-01, rebuild the calibration features and the holdout prediction, and compare the new correlation and MAE with 0.68 and 624.80.
  2. Compute each customer's purchase frequency from their own FirstPurchase date to the cutoff instead of the full calibration period, rebuild PredictedCLV, and check whether the holdout correlation improves.
  3. Group customers by R_score and compare average PredictedHoldoutRevenue with average ActualHoldoutRevenue for each group, to see whether the model over-predicts for customers who have not bought recently.

The next video in the series covers product category performance and ABC analysis, ranking what this business actually sells best.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. What is the simple CLV formula used in the lesson?

  2. Why does the lesson split the data into a calibration period and a holdout period?

  3. A customer has an average order value of 20, placed 3 orders in 6 months, and we assume a 12-month lifespan. What does this print?

    aov = 20
    freq_per_month = 3 / 6
    print(aov * freq_per_month * 12)
    
  4. What does the lesson call a customer with a high predicted CLV but zero actual holdout revenue?

  5. If you change the lifespan assumption from 12 to 24 months, what happens to the total predicted CLV in this formula?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Create a table of 8 customers with TotalRevenue and Orders from a 6-month calibration period. Compute AvgOrderValue, PurchaseFreqPerMonth (orders / 6), and PredictedCLV for a 12-month lifespan. Print the table and the total predicted CLV.

    💡 Hint

    AvgOrderValue = TotalRevenue / Orders.

    ✅ Show solution
    import pandas as pd
    
    cust = pd.DataFrame({
        'CustomerID': range(1, 9),
        'TotalRevenue': [600, 150, 900, 80, 420, 1200, 60, 300],
        'Orders': [6, 2, 5, 1, 3, 8, 1, 4],
    }).set_index('CustomerID')
    calib_months = 6
    lifespan_months = 12
    cust['AvgOrderValue'] = cust['TotalRevenue'] / cust['Orders']
    cust['PurchaseFreqPerMonth'] = cust['Orders'] / calib_months
    cust['PredictedCLV'] = cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * lifespan_months
    print(cust.round(2))
    print('Total predicted CLV:', round(cust['PredictedCLV'].sum(), 2))
    
  2. Build 20 transactions (CustomerID, InvoiceNo, InvoiceDate, Revenue) for 5 customers across 8 months. Split at a cutoff date (calibration before, holdout after), compute each customer's predicted holdout revenue (AOV x monthly frequency x holdout months) and actual holdout revenue, then print the correlation and mean absolute error.

    💡 Hint

    Use .map() to attach actual holdout revenue and fillna(0) for customers who did not buy.

    ✅ Show solution
    import pandas as pd
    
    tx = pd.DataFrame({
        'CustomerID': [1,1,1,1,2,2,2,3,3,3,3,3,4,4,5,5,5,5,5,5],
        'InvoiceNo': range(1, 21),
        'InvoiceDate': pd.to_datetime(['2024-01-10','2024-02-12','2024-05-03','2024-07-15','2024-01-20','2024-03-05','2024-04-10',
                                       '2024-01-05','2024-02-02','2024-03-15','2024-06-10','2024-07-20','2024-02-14','2024-03-01',
                                       '2024-01-25','2024-02-25','2024-04-25','2024-05-25','2024-06-25','2024-07-25']),
        'Revenue': [80,60,90,100,200,150,170,30,40,35,45,50,300,250,20,25,22,28,30,26],
    })
    cutoff = pd.Timestamp('2024-06-01')
    calib = tx[tx['InvoiceDate'] < cutoff]
    holdout = tx[tx['InvoiceDate'] >= cutoff]
    calib_months = (cutoff - calib['InvoiceDate'].min()).days / 30
    holdout_months = (tx['InvoiceDate'].max() - cutoff).days / 30
    
    cust = calib.groupby('CustomerID').agg(TotalRevenue=('Revenue', 'sum'), Orders=('InvoiceNo', 'nunique'))
    cust['AvgOrderValue'] = cust['TotalRevenue'] / cust['Orders']
    cust['FreqPerMonth'] = cust['Orders'] / calib_months
    cust['PredictedHoldout'] = cust['AvgOrderValue'] * cust['FreqPerMonth'] * holdout_months
    actual = holdout.groupby('CustomerID')['Revenue'].sum()
    cust['ActualHoldout'] = cust.index.map(actual)
    cust['ActualHoldout'] = cust['ActualHoldout'].fillna(0)
    
    print(cust[['PredictedHoldout', 'ActualHoldout']].round(1))
    print('Correlation:', round(cust['PredictedHoldout'].corr(cust['ActualHoldout']), 3))
    print('MAE:', round((cust['PredictedHoldout'] - cust['ActualHoldout']).abs().mean(), 2))
    
  3. Given a table of 10 customers with AvgOrderValue, PurchaseFreqPerMonth and ActualHoldoutRevenue (some zero), compute PredictedCLV, find the churn-risk customers (predicted CLV above the 70th percentile but zero actual holdout revenue), and print the lifespan sensitivity: total predicted CLV for 6, 12, 18 and 24 months.

    💡 Hint

    Use quantile(0.7) for the threshold and loop over lifespans.

    ✅ Show solution
    import pandas as pd
    
    cust = pd.DataFrame({
        'CustomerID': range(1, 11),
        'AvgOrderValue': [50, 120, 30, 200, 80, 45, 150, 25, 60, 90],
        'PurchaseFreqPerMonth': [0.5, 0.3, 1.0, 0.4, 0.2, 0.8, 0.5, 0.2, 0.6, 0.1],
        'ActualHoldoutRevenue': [100, 0, 90, 300, 0, 120, 0, 20, 150, 0],
    }).set_index('CustomerID')
    lifespan = 12
    cust['PredictedCLV'] = cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * lifespan
    
    threshold = cust['PredictedCLV'].quantile(0.7)
    high_predicted = cust[cust['PredictedCLV'] > threshold]
    churn_risk = high_predicted[high_predicted['ActualHoldoutRevenue'] == 0]
    print('High-predicted customers:', list(high_predicted.index))
    print('Churn-risk customers:', list(churn_risk.index))
    
    for months in [6, 12, 18, 24]:
        total = (cust['AvgOrderValue'] * cust['PurchaseFreqPerMonth'] * months).sum()
        print(f'{months}-month lifespan: {total:,.0f} total predicted CLV')
    

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.