Transcript LTV
Business Intelligence/
Decision Models
Week 4
Lifetime Value
Review
Week 2: Data organization in RDBMS, SQL
Queries
Week 3: Importing data into SPSS and Data
Transformation in preparation for analytics
Week 4: Customers’ Lifetime value
CLV Spreadsheets
SPSS Life Tables and Means
Estimating CLV from SPSS and into Excel
How Lifetime Value is used
for acquisition and retention
We need to know the value (the equity) of our
customers, so as to properly target our sales
and retention efforts
We need to discriminate among our customers
to acquire and retain the best
More specifically, how much money should be
spent on
Acquisitions
Retention
What is lifetime value?
Net present value of the profit to be realized
on the average new customer during a given
number of years.
To compute it, you must be able to track
customers from year to year.
Customer Lifetime Value
n
CLV = [NPV
Σi=1(Pri X Inci)] – AC0
where Pr is the survival probability for period i
Pr X Inc. is the expected income for period I
n is the number of time periods
NPV is the net present value
AC is the acquisition cost
LTV
Spreadsheet
Life tables (SPSS)
Simple CLV Spreadsheet
Customers
Retention Rate
Orders per Year
Avg Order Size
Total Revenue
Acquisition
Year
100,000
60%
1.8
$90
$16,200,000
Second
Year
Third
Year
60,000
70%
2.5
$95
$14,250,000
42,000
80%
3
$100
$12,600,000
70%
$11,340,000
$55
$5,500,000
$16,840,000
65%
$9,262,500
$20
$1,200,000
$10,462,500
65%
$8,190,000
$20
$840,000
$9,030,000
Gross Profit
Discount Factor
Net Present Value
-$640,000
1
-$640,000
$3,787,500
1.16
$3,265,086
$3,570,000
1.35
$2,644,444
Cumulative NPV Profit
Customer LTV
-$640,000
-$6
$2,625,086
$26
$5,269,531
$53
Costs
Cost of Sales
Acquisition/Mkt. Cost
Marketing Costs
Total Costs
Discount Factor = (1 + (.08 x 2))2 or D = (1.16)2 = 1.35.
http://www.dbmarketing.com/articles/Art251a.htm
How much to invest in
retention? During Year 2
Pr. of cancelling = 30%
Replacement Cost: $35.00 * 30% = $10.50
Gross profit if surviving: $3,787,500/60,000 = $63.13
Opportunity Cost if cancelled: $63.13 x 30% = $18.94
Total Expected Cost: $18.94 + $10.50 = $29.44
If 100% sure to salvage, investment < $29.44
If only 10% probability of salvage, investment < $2.94
NPV (Corrected)
$1 @ 10% = $1.10 (after 1 yr)
$1.10 @ 10% = $1.21 (after 2 yrs)
$1 x (1.10)3 = $1.33 (after 3 yrs)
FV = PV x (1 + r) n
PV = FV/(1 + r) n
Discount Rate
First year (0): (1+.06)0
=
1.0
Second year : (1+.06)1
= 1.060
Third year : (1+.06)2
= 1.124
_________________________________
PV = FV in p0
$100/1
= $100
PV = FV in p1
$100/1.06 = $94
PV = FV in p2
$100/1.124 = $89
NPV over all three years
=$283
Excel for discounting factor
Discount Rate = (1 + r)^n
Discount Rate = POWER((1+r),n)
Discount Rate
http://en.wikipedia.org/wiki/Discounting
Discount Rate for period 1 (r):
Interest Rate (e.g. 10%)
Cost of Capital (e.g. 15%)
Hurdle Rate (e.g. ROI = 20%)
Simple CLV Spreadsheet
Starting Parametres
Period
Year
Acquisitions
Retention
Oders per year
Avg Oder Size
Margin
Accquisition Cost
Marketing Cost
Annual Discount Rate
0
1
2
1
2
3
100,000
60%
1.8
$90
70%
$35
$20
16%
70%
2.5
$95
65%
80%
3
$100
65%
$20
16%
$20
16%
Tutorial
1.
2.
Program a CLV Worksheet (See Excel
sheet)
Use SPSS to Estimate CLV
a)
b)
c)
Use Survival/Life table to estimate
cumulative survival rate by time period and
customer segment
Use Compare Means to estimate annual
purchases
Transfer data into your CLV spreadsheet