Transcript CLV

Business Intelligence/
Decision Models
Lifetime Value
Firm Worth

The selling price of a firm is based on discounted
future profits.

Future profits are generated by customers and
by customer acquisition

The firm equity aggregated from individual
customer equity (customer value)
How Lifetime Value is used
for acquisition and retention

We need to know the value (the equity) of our
customers, for sales targeting and retention
efforts

We need to discriminate among 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
Costs %
Cost of Sales
70%
$11,340,000
65%
$9,262,500
65%
$8,190,000
Unit Acquisition Cost
Unit Marketing Cost
Marketing Costs
Total Costs
$35
$20
$5,500,000
$16,840,000
$0
$20
$1,200,000
$10,462,500
$0
$20
$840,000
$9,030,000
-$640,000
(1+.16)0
1
-$640,000
-$640,000
-$6
$3,787,500
(1+.16)1
1.16
$3,265,086
$2,625,086
$26
$3,570,000
(1+.16)2
1.35
$2,644,444
$5,269,531
$53
Gross Profit
Discount Factor
Discount Factor
Net Present Value
Cumulative NPV Profit
Customer LTV
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

$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)

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
Costs %
Cost of Sales
70%
$11,340,000
65%
$9,262,500
65%
$8,190,000
Unit Acquisition Cost
Unit Marketing Cost
Marketing Costs
Total Costs
$35
$20
$5,500,000
$16,840,000
$0
$20
$1,200,000
$10,462,500
$0
$20
$840,000
$9,030,000
-$640,000
(1+.16)0
1
-$640,000
-$640,000
-$6
$3,787,500
(1+.16)1
1.16
$3,265,086
$2,625,086
$26
$3,570,000
(1+.16)2
1.35
$2,644,444
$5,269,531
$53
Gross Profit
Discount Factor
Discount Factor
Net Present Value
Cumulative NPV Profit
Customer LTV
Discount Factor = (1 + (.08 x 2))2 or D = (1.16)2 = 1.35.
http://www.dbmarketing.com/articles/Art251a.htm
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%
Getting Starting Values

Calculate tenure or LOF


Calculate Recency


Recode Recency if <> X into 0 or 1
Calculate annual spending by Tenure



(Current Dte – Dte Lst Purchase)
Define Status (Active/Lapsed)


(Dte Lst Purchase – Dte Frst Purchase)
Average annual spending or
Average orders X Average order size
Cost per acquisition

Total acquisition cost / Acquired numbers
Life Tables

Run Life Tables
By Tenure chunks (Months or Years)
 By Status (Terminal Event)
 By Customer Segment


Run compare means
DV Average Order, Average Spending
 IV Tenure chunks (Months or Years)

Tutorial
This week
 Program a CLV Worksheet (See Excel sheet)
Next week
 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