Cohorts, Retention and LTV

In the Real World · Brief · Data science · Advanced · 3 to 5 days

Marketing wants to spend more on acquisition. Finance will not approve it without a ceiling. Work out what a customer is worth and therefore what one may cost, before reading the walkthrough.

The situation

Fellgate Outdoor sells outdoor gear into the UK, Ireland and the EU. You have twelve months of orders, 2025-08-01 to 2026-07-31, covering 1,671 customers and 2,674 orders.

The finance director has seen a cohort report showing customer value falling steadily across the year and wants to know whether acquisition should be cut rather than increased.

The data

These are the same three files as the profit leak audit. If you have done that project you already have the contribution model this one needs.

Cost assumptions from finance

What the room believes

1. Customer value has fallen across the year, so acquisition quality is deteriorating.
2. Some channels buy better customers than others.
3. Retention decays over time.
4. A large first order predicts a customer who comes back.

Definition of done

1. A verdict on each of the four beliefs, with the evidence.
2. A stated definition of the value metric, including whether it is revenue or something else, and why.
3. A defensible figure for what a customer is worth, with whatever qualification that figure needs.
4. A maximum acquisition cost, and a clear statement of what it depends on.
5. An explicit line between what you observed and what you extrapolated.

If you want to go further

• Put a confidence interval on every group mean before you rank anything, then count how many of your differences survive.
• Work out which single extra column in the cohort report would have prevented the misreading.
• Decide whether a parametric lifetime value model is appropriate here, and be able to say why or why not.
• Check whether the ceiling should be the same for every destination.

In the Real World · Data science · Advanced · 3 to 5 days

The cohort table showed customer value falling 51.4 per cent across the year and the room had already decided acquisition was getting worse. It was not. The decline correlates 0.952 with how long each cohort has been watched, and there is no single number called lifetime value to find.

Section 1Problem Definition

No code yet. Lifetime value is the metric most often quoted and least often defined, and almost every argument about it is really an argument about a definition nobody wrote down.

Business objective

Set a maximum acquisition cost. Marketing wants to raise spend, finance wants a number it can defend, and the only honest way to produce one is to say what a customer contributes and over what period.

Three decisions to make before any code

Hypotheses

1. H1. Customer value has fallen across the year, so acquisition quality is deteriorating.
2. H2. Some channels buy better customers than others.
3. H3. Retention decays over time, as it does in most subscription businesses.
4. H4. A large first order predicts a customer who comes back.

Section 2Data Collection

The same three files as the profit leak audit, because the contribution model is the input here rather than the output.

import numpy as np
import pandas as pd
from scipy import stats

lines = pd.read_csv('data/fellgate-order-lines.csv').drop_duplicates()
prod  = pd.read_csv('data/fellgate-products.csv')
rates = pd.read_csv('data/fellgate-shipping-rates.csv')

print('order lines :', len(lines))
print('orders      :', lines['order_id'].nunique())
print('customers   :', lines['customer_id'].nunique())

order lines : 4,516
orders      : 2,674
customers   : 1,671

12 months, 2025-08-01 to 2026-07-31. That window is short for a lifetime value question and section 7d is honest about what it can and cannot support.

Section 3Data Preprocessing

3aDuplicates and schema checks

The order lines carry a small re-export duplication, removed on load above, and the catalogue join is validated the same way the profit leak audit validated it. The check that matters here is different: a customer must have exactly one first order.

# rebuild the order-level contribution, the model from the profit leak audit
FEE_PCT, FEE_FIXED, PICKPACK = 0.014, 0.20, 1.10
RET_UK, RET_INTL, RET_HANDLING, WRITE_OFF = 4.20, 7.90, 2.50, 0.12
ZONE = {'UK': 'UK', 'Ireland': 'Ireland', 'Germany': 'EU',
        'France': 'EU', 'Netherlands': 'EU'}

d = lines.merge(prod, on='sku', validate='many_to_one')
d['zone']  = d['country'].map(ZONE)
d['dt']    = pd.to_datetime(d['order_date'])
d['month'] = d['dt'].dt.to_period('M')
d['line_revenue'] = d['quantity'] * d['unit_price'] - d['line_discount']
d['line_cogs']    = d['quantity'] * d['unit_cost']
d['chargeable_kg'] = np.maximum(d['weight_kg'], d['volumetric_kg']) * d['quantity']
d['returned'] = d['line_status'] == 'returned'
print('rows joined :', len(d), '  unmatched costs :', d['unit_cost'].isna().sum())

3bHandling categorical mess

Country maps to shipping zone exactly as before, and the acquisition channel is taken from a customer’s first order rather than their most recent one. That choice is not cosmetic: crediting a customer’s whole value to whatever channel they last arrived through is how email and direct end up looking like the best acquisition sources in every business.

o = d.groupby('order_id').agg(
    customer=('customer_id', 'first'), month=('month', 'first'), dt=('dt', 'first'),
    zone=('zone', 'first'), channel=('channel', 'first'),
    chargeable_kg=('chargeable_kg', 'sum'), subtotal=('line_revenue', 'sum'),
    cogs=('line_cogs', 'sum'), shipping_charged=('shipping_charged', 'first'),
    returned_lines=('returned', 'sum')).sort_values('dt')

back = (d[d['returned']].groupby('order_id')
        .agg(refunded=('line_revenue', 'sum'), ret_cogs=('line_cogs', 'sum')))
o = o.join(back).fillna({'refunded': 0.0, 'ret_cogs': 0.0})


def carrier(zone, kg):
    band = rates[(rates['zone'] == zone) &
                 (rates['weight_from_kg'] < kg) &
                 (rates['weight_to_kg'] >= kg)]
    if len(band):
        return float(band['carrier_cost'].iloc[0])
    return float(rates[rates['zone'] == zone]['carrier_cost'].max())


o['carrier'] = [carrier(z, k) for z, k in zip(o['zone'], o['chargeable_kg'])]
o['contribution'] = ((o['subtotal'] - o['refunded'] + o['shipping_charged'])
                     - (o['cogs'] - o['ret_cogs'] * (1 - WRITE_OFF))
                     - o['carrier']
                     - o['returned_lines'] * (np.where(o['zone'] == 'UK', RET_UK, RET_INTL)
                                              + RET_HANDLING)
                     - ((o['subtotal'] + o['shipping_charged']) * FEE_PCT + FEE_FIXED)
                     - PICKPACK)
print('total contribution :', round(o['contribution'].sum()))

# first order defines the cohort and the acquisition channel
cust = o.groupby('customer').agg(first_month=('month', 'min'),
                                 acq_channel=('channel', 'first'),
                                 acq_zone=('zone', 'first'),
                                 orders=('subtotal', 'size'))
print(cust['orders'].value_counts().sort_index().to_string())

total contribution : 92,228

1     972
2     464
3     182
4      38
5      14
6       1

41.8% of customers order more than once, and the mean is 1.6 orders. A business where most customers buy once is a business where the acquisition ceiling matters more than anywhere else, because there is no second purchase to rescue a bad one.

3cDealing with outliers

Contribution per order carries the same tail the profit leak audit found, and the same treatment applies. What is new here is a customer-level question: does one customer carry a cohort? With 1,671 customers and a maximum of 6 orders each, no.

3dHandling missing values

Nothing missing. The one thing that looks like missing data and is not is a cohort’s empty months: a customer who does not order in month four produces no row, and that absence is the signal. Any join that turns it into a null and then drops it will delete exactly the customers you are trying to measure.

3eHandling skewed data

Customer contribution is right-skewed, because most customers buy once and a few buy six times. The mean is still the right statistic for a ceiling, because a ceiling is about the total the business can afford across everyone acquired, not about a typical customer. Section 4c reports the median alongside so the shape stays visible.

3fData types and normalisation

Two derived columns carry the whole analysis.

o = o.join(cust[['first_month']], on='customer')

# months since the customer's first order. Month 0 is the first order itself.
o['k'] = (o['month'] - o['first_month']).apply(lambda x: x.n)

# how many complete months we have actually watched each cohort for
LAST = o['month'].max()
cust['exposure'] = [(LAST - p).n for p in cust['first_month']]

print(cust.groupby('first_month')['exposure'].first().to_string())

The exposure column is the one that turns this project from a wrong answer into a right one. Every comparison below is filtered on it.

Section 4Exploratory Data Analysis

4aThe primary metric

Start with the table the business already has: total contribution per customer, by the month they were acquired.

Read down the contribution column and the story writes itself: 71.51 pounds from the 2025-08 cohort down to 34.72 from 2026-07, a fall of 51.4 per cent. Now read the column next to it.

Mean contribution per cohort against the number of months each cohort has been observed. The bars and the line are the same shape because they are the same fact.

4bNumerical variables

The fix is to compare cohorts only over a window every one of them has completed. Three windows, each restricted to the cohorts old enough to have finished it.

def cohort_values(K):
    """Contribution in the first K+1 months, per cohort, dropping any cohort
    that has not yet completed the window."""
    rows = []
    for p, grp in cust.groupby('first_month'):
        if (LAST - p).n < K:
            continue
        v = (o[(o['customer'].isin(grp.index)) & (o['k'] <= K)]
             .groupby('customer')['contribution'].sum().reindex(grp.index).fillna(0))
        rows.append((str(p), len(grp), v.mean()))
    return rows


for K in (0, 2, 5):
    rows = cohort_values(K)
    y = np.array([r[2] for r in rows])
    lr = stats.linregress(np.arange(len(rows)), y)
    print(f'first {K+1:2d} months, {len(rows):2d} cohorts  '
          f'spread {y.max()-y.min():5.2f}  trend {lr.slope:+.2f}/month  p {lr.pvalue:.3f}')

first  1 months, 12 cohorts  spread 12.80  trend -0.43/month  p 0.177
first  3 months, 10 cohorts  spread 10.78  trend -0.59/month  p 0.114
first  6 months,  7 cohorts  spread 16.43  trend +1.02/month  p 0.356

The same cohorts, compared at the same age. Each block drops the cohorts too young to have completed the window.

Not one of the three shows a significant trend. The smallest p-value across all three windows is 0.1135. Compared like for like, the 2026-07 customers are worth what the 2025-08 customers were worth.

H1 is dead. Acquisition quality has not deteriorated, and any budget decision taken on the strength of that first table would have been a reaction to arithmetic.

4cCategorical variables

# six months is the longest window that still leaves a usable number of cohorts
K = 5
elig = cust[cust['exposure'] >= K]
v6 = (o[(o['customer'].isin(elig.index)) & (o['k'] <= K)]
      .groupby('customer')['contribution'].sum().reindex(elig.index).fillna(0))

by_ch = v6.groupby(elig['acq_channel']).agg(['size', 'mean', 'sem'])
by_ch['lo'] = by_ch['mean'] - 1.96 * by_ch['sem']
by_ch['hi'] = by_ch['mean'] + 1.96 * by_ch['sem']
print(by_ch.round(2).sort_values('mean', ascending=False).to_string())

groups = [v6[elig['acq_channel'] == c] for c in elig['acq_channel'].unique()]
print('one-way ANOVA p =', round(stats.f_oneway(*groups).pvalue, 4))

             size   mean   sem     lo     hi
acq_channel
paid_search    224  57.74  3.91  50.08  65.39
organic        300  54.62  2.99  48.76  60.49
email          154  52.17  4.93  42.50  61.84
direct         136  51.93  4.38  43.35  60.50
social          97  46.07  4.26  37.73  54.41
one-way ANOVA p = 0.4743

Every interval overlaps every other interval, and a one-way test across the five gives p = 0.4743. H2 is dead too: no channel buys a better customer than any other, at least not by an amount this business can detect.

Geography does what channel does not. UK customers are worth 15.20 pounds more at six months than EU customers, p = 0.00552. The profit leak audit already explained why: carriage to the EU costs far more, and that difference compounds across every order a customer places.

4dRelationships between variables

first_cm  = o[o['k'] == 0].groupby('customer')['contribution'].sum()
came_back = (o[o['k'] > 0].groupby('customer').size()
             .reindex(first_cm.index).fillna(0) > 0)

r, p = stats.pointbiserialr(came_back.astype(int), first_cm)
print(f'came back    : {came_back.sum():5d} customers, first order {first_cm[came_back].mean():.2f}')
print(f'never again  : {(~came_back).sum():5d} customers, first order {first_cm[~came_back].mean():.2f}')
print(f'correlation r={r:.3f}  p={p:.4f}')

came back    : 654 customers, first order 37.78
never again  : 1,017 customers, first order 35.84
correlation r=0.023  p=0.3472

A difference of 1.94 pounds, correlation 0.023, p = 0.3472. H4 is dead. The size of a first order tells you nothing about whether the customer returns, which matters because chasing large first orders is a common and expensive acquisition strategy.

4eTesting our hypotheses

Four hypotheses, four refutations, and the fourth is the one that changes the shape of the answer. If retention does not decay, contribution does not converge on a ceiling, and lifetime value has no limit to find.

4fSubgroups

# a customer counts in month k only if their cohort has been watched that long
for k in range(1, 12):
    elig = cust[cust['exposure'] >= k]
    ordered = o[(o['k'] == k) & (o['customer'].isin(elig.index))]['customer'].nunique()
    print(f'month {k:2d}: {len(elig):5d} eligible, {ordered:3d} ordered, '
          f'{ordered/len(elig):.2%}')

month  1: 1,539 eligible, 126 ordered, 8.19%
month  2: 1,398 eligible, 133 ordered, 9.51%
month  3: 1,229 eligible, 111 ordered, 9.03%
month  4: 1,064 eligible, 103 ordered, 9.68%
month  5:   911 eligible,  74 ordered, 8.12%
month  6:   779 eligible,  72 ordered, 9.24%
month  7:   681 eligible,  66 ordered, 9.69%
month  8:   580 eligible,  71 ordered, 12.24%
month  9:   478 eligible,  52 ordered, 10.88%
month 10:   359 eligible,  50 ordered, 13.93%
month 11:   217 eligible,  20 ordered, 9.22%

Flat. The trend across the eleven months is 0.319 percentage points a month with p = 0.0489, and the direction is rising rather than falling.

Section 5Metric Construction

Everything above says the same thing: there is no single lifetime value here. There is a curve, and a number only appears once someone picks a point on it.

def value_at(K):
    """Mean contribution in the first K+1 months, over cohorts that have
    completed that window. The exposure filter is the whole point."""
    eligible = cust[cust['exposure'] >= K]
    v = (o[(o['customer'].isin(eligible.index)) & (o['k'] <= K)]
         .groupby('customer')['contribution'].sum()
         .reindex(eligible.index).fillna(0))
    return v


for k in range(0, 12):
    v = value_at(k)
    print(f'month {k:2d}: n={len(v):5d}  cumulative contribution {v.mean():6.2f}')

month  0: n=1,671  cumulative contribution  36.60
month  1: n=1,539  cumulative contribution  39.28
month  2: n=1,398  cumulative contribution  42.15
month  3: n=1,229  cumulative contribution  46.19
month  4: n=1,064  cumulative contribution  50.01
month  5: n=  911  cumulative contribution  53.66
month  6: n=  779  cumulative contribution  57.34
month  7: n=  681  cumulative contribution  60.79
month  8: n=  580  cumulative contribution  62.52
month  9: n=  478  cumulative contribution  65.79
month 10: n=  359  cumulative contribution  70.98
month 11: n=  217  cumulative contribution  71.51

Contribution accumulated per customer, by months since their first order. Observed to month 11. The shaded region is arithmetic, not evidence.

A first order contributes 36.60 pounds. Every month after that adds about 3.17 more, between 0.53 and 5.19. A straight line fits at r squared 0.9949.

Section 6Method Selection

Section 7Analysis and Validation

7aThe baseline the business believes

Two beliefs, and they point in opposite directions. Finance has seen the cohort table and believes customer value is collapsing, so the acquisition budget should be held. Marketing believes the business has a lifetime value figure somewhere north of 72 pounds and that spend should rise to meet it.

Finance is reading an artefact. Marketing is asking for a number that does not exist in the form they want it. Beating both means producing something better than a single figure, which is a curve and a choice.

7bThe analysis

The ceiling

With a value curve and a chosen horizon, the acquisition ceiling is arithmetic.

def value_at_horizon(months):
    """Observed where we have the exposure, fitted where we do not."""
    k = months - 1
    if k <= 11:
        return value_at(k).mean(), 'observed'
    fit = stats.linregress([c for c in range(12)],
                           [value_at(c).mean() for c in range(12)])
    return fit.intercept + fit.slope * k, 'extrapolated'


for months in (6, 12, 24):
    ltv_raw, basis = value_at_horizon(months)
    ltv = round(ltv_raw, 2)          # report it once, then divide the reported figure
    for ratio, label in ((1.0, 'break even'), (2.0, '2 to 1'), (3.0, '3 to 1')):
        print(f'{months:2d} month payback, {label:10s}: '
              f'max CAC {round(ltv / ratio, 2):6.2f}  ({basis})')

 6 month payback, break even: max CAC  53.66  (observed)
 6 month payback, 2 to 1    : max CAC  26.83  (observed)
 6 month payback, 3 to 1    : max CAC  17.89  (observed)
12 month payback, break even: max CAC  71.51  (observed)
12 month payback, 2 to 1    : max CAC  35.76  (observed)
12 month payback, 3 to 1    : max CAC  23.84  (observed)
24 month payback, break even: max CAC 113.01  (extrapolated)
24 month payback, 2 to 1    : max CAC  56.51  (extrapolated)
24 month payback, 3 to 1    : max CAC  37.67  (extrapolated)

Maximum acquisition cost by payback horizon and target ratio. The faded bars rest on the extrapolation and should be treated as a direction, not a budget.

What the equal-age comparison changes about the budget

Because no cohort is worse than another, the ceiling is a single number rather than a schedule that has to tighten each quarter. Had H1 been true, the correct recommendation would have been to cut acquisition until the cause was found. It is not true, and the difference between those two recommendations is the entire value of this project.

7cSensitivity and robustness

The write-off rate barely moves it, which is expected: returns are an order-level cost and most customers place one order. The horizon moves it enormously, from 26.83 to 56.51 pounds.

7dWhat would change the answer

• A longer window. Twelve months of data cannot show whether repurchase eventually decays. If it does, every extrapolated figure is too high. This is the single biggest limitation and no method fixes it, only time.
• Seasonality. Fellgate Outdoor sells outdoor gear, and a customer acquired in the spring peak may behave differently over a year from one acquired in November. The cohorts here are too small to test that separately, and with two years of data it would be the first thing to check.
• The zone gap. EU customers are worth 15.20 pounds less. If acquisition cost is the same everywhere, the ceiling should not be. A single blended ceiling quietly overpays for the cheapest customers to serve and underpays for the most valuable.
• Anything that changes contribution per order. The ceiling inherits every assumption in the profit leak audit, including the shipping model. Fixing the free delivery band would raise this ceiling.

Section 8Decision and Handoff

What to do

What not to do

• Do not cut acquisition on the strength of the cohort table. The decline in it is not real.
• Do not quote a lifetime value without a horizon. The same data supports 53.66 and 113.01 pounds depending on where you stop, and both are defensible.
• Do not build the ceiling on revenue. Revenue per customer over twelve months is far higher than contribution and would license acquisition spend the business cannot fund.
• Do not fit a parametric lifetime value model to this window. There is no decay for it to estimate.

Reproducibility

What to take from this

• Put the exposure column next to every cohort metric. One extra column would have prevented the entire misreading, and it makes the mistake impossible to make silently.
• Compare cohorts at equal age or not at all. A lifetime total is not a comparable quantity across cohorts of different ages, ever.
• If retention does not decay, lifetime value has no limit. The metric becomes a rate plus a horizon, and the horizon belongs to finance.
• Build it on contribution. A revenue ceiling licenses spend on customers who cost more to serve than they bring.
• A null result is a finding. Channels not differing is what makes the media decision simple, and it is only sayable with intervals.
• Extrapolate visibly or not at all. Shade it, label it, and let the reader see exactly where the evidence stops.

Finance asked what a customer is worth. The honest answer is that the question is incomplete, and the useful answer is a curve plus a table that turns any horizon into a ceiling. The room came in believing customer value was collapsing and left with a number they could sign, which is a better outcome than either side expected.