Tools › Finance and Business › Marketing › Cohort Retention and CLV › Example
Fader and Hardie note 017: sBG fitted to five cohorts, with the DERL of note 018
| Tool | Cohort Retention and CLV |
| Source | Fader, P. S., and Hardie, B. G. S. (2007). "Fitting the sBG Model to Multi-Cohort Data." Research note 017, brucehardie.com; and (2007). "Computing DERL for the sBG Model Using Excel." Research note 018. |
| Level | Graduate |
Problem
Five annual cohorts of 10,000 customers (2002–2006) show 8,000, 6,480, 5,307 and 4,391 active after one to four years, the later cohorts observed for fewer years. Fit one shifted-beta-geometric model to all cohorts by full-information maximum likelihood and compute the discounted expected residual lifetime of a customer at a 10 % discount rate.
Inputs to Enter
- Retention table (loaded by default): cohort, size, active_1 … active_4 for 2002 (8000, 6480, 5307, 4391), 2003 (three values), 2004 (two), 2005 (one), 2006 (size only)
- Margin per period 1, discount rate 10 %, horizon 12
Answers
- α = 3.80, β = 15.19 (scipy 3.7981 / 15.1919, LL −40,255.837); 15,822 dropouts and 24,178 censored survivors; pooled Kaplan–Meier S(4) = 0.4391
- Note 018 at α 3.8, β 15.2, d = 10 %: DERL 3.31, 3.45, 3.59, 3.72, 3.84 for customers who have survived 1 … 5 periods; DEL = 1 + DERL(1)/1.1 = 4.0089; residual value of the base 120,543 at $1 per period
- sBG mean lifetime (α + β − 1)/(α − 1) = 18/2.8 periods; median life 4 periods
Notes
The tool's default cohort table is this example; press Submit. The DERL values are printed for the fitted parameters, which round to the note's 3.8 and 15.2.