Calcualting CLV

Ashwin Malshe

18 October 2016

Using 3 functions that I wrote, this post shows how to get customer lifetime value (CLV). The example relies on the method described in the book Marketing Engineering.

This method assumes that you have segmented your customers in distinct segments. In order to calculate CLV, we will need the following inputs:

Transition probability matrix
Margin per customer in each segment
Number of customers in each segment
Discount rate
Number of periods over which CLV has to be calculated

Please refer to the lecture slides if you want to know more about the method.

In this note I am going to use a simple example that is already on the slides. I am going to manually input the transition matrix and the rest of the inputs so that this note is self contained.

We start off by loading the functions from clv.R. Make sure that you have this file on your hard drive.

source("/Volumes/Transcend/Dropbox/Work/Teaching/DA 6813/Course Documents/Code/clv.R")

Next we get all the inputs required to give to the CLV functions.

# Input the transition matrix
# Note that I am using transpose. Not sure why? Print out the matrix with and without the transpose.
transmat <-  t(matrix(c(0.50,0.20,0.30,0.00,0.00,
                        0.10,0.50,0.40,0.00,0.00,
                        0.05,0.25,0.00,0.70,0.00,
                        0.01,0.09,0.00,0.00,0.90,
                        0.00,0.00,0.00,0.00,1.00), nrow = 5, ncol = 5))

# Let's name the columns of this matrix so that we know what they stand for
colnames(transmat) <- c("Active_20","Active_80","Warm","Cold","Lost")

# Input the margin per customer in each segment

grmargin <- c(250,37,0,0,0)

# Number of customers in each segment

numcust <- c(3600,14400,9500,6200,21900)

# Discount rate. For this example, I will take it as 15%

discount <- 0.15

# Number of periods over which we will calculate CLV.

periods <- 8

Once we have all the necessary information, let’s explore what the functions will do.

We will start off by getting a matrix for the transition of customers from one segment to another over the 8 periods. For this we will use clv_cust_dist function. The function will return a matrix of customer transition through time. The syntax of the function is as follows:

clv_cust_dist(tmat = , cust0 = ,numperiod= , round=TRUE)

Where, tmat is the transtion matrix, cust0 is the vector of number of curent customers, numperiod is the number of periods over which we want the transition, and round is an option for whether you want the output rounded to 0 decimals. By default it is TRUE.

cust.dist <- clv_cust_dist(tmat = transmat, cust0 = numcust, numperiod = 8, round = T)
print(cust.dist)

##           Period_0 Period_1 Period_2 Period_3 Period_4 Period_5 Period_6
## Active_20     3600     3777     3382     2862     2362     1927     1564
## Active_80    14400    10853     8490     6721     5381     4327     3488
## Warm          9500     6840     5474     4411     3547     2861     2309
## Cold          6200     6650     4788     3832     3088     2483     2003
## Lost         21900    27480    33465    37774    41223    44002    46236
##           Period_7 Period_8
## Active_20     1266     1024
## Active_80     2814     2272
## Warm          1864     1506
## Cold          1616     1305
## Lost         48039    49493

The function nicely lays down the matrix. As we see, the number of customers in the top segment has declined more than 3 times over the 8 years period. On the other hand the Lost customers balooned from around 22,000 to almost 50,000.

Next we will get the discounted margin for each segment and year. When we add the columns of this matrix, we will get the discounted revenue in each year, giving us an idea about how fast we are losing value.

For this we will use clv_dismargin function. The syntax is as follows:

clv_dismargin(tmat = , cust0 = , margin = , drate = , numperiod= )

In this function there is no option to round anything. This function takes two new arguments. margin is a vector of margins for each customer segment. drate is the discount rate. Let’s get the discounted margins.

dismargin <- clv_dismargin(tmat = transmat, cust0 = numcust, margin = grmargin, drate = discount, numperiod = periods)

print(dismargin)

##           Period_0 Period_1 Period_2 Period_3 Period_4  Period_5  Period_6
## Active_20   900000 821087.0 639376.2 470417.5 337601.8 239541.48 169061.89
## Active_80   532800 349183.5 237538.6 163512.9 113824.4  79602.48  55790.19
## Warm             0      0.0      0.0      0.0      0.0      0.00      0.00
## Cold             0      0.0      0.0      0.0      0.0      0.00      0.00
## Lost             0      0.0      0.0      0.0      0.0      0.00      0.00
##            Period_7 Period_8
## Active_20 119017.26 83685.06
## Active_80  39144.64 27479.85
## Warm           0.00     0.00
## Cold           0.00     0.00
## Lost           0.00     0.00

As Warm, Cold, and Lost customer have zero margins, all those rows are zero in future as well. Let’s get the total discount margin for each year and then plot that using qplot.

yrmargin <- colSums(dismargin)
print(yrmargin)

##  Period_0  Period_1  Period_2  Period_3  Period_4  Period_5  Period_6 
## 1432800.0 1170270.4  876914.8  633930.5  451426.2  319144.0  224852.1 
##  Period_7  Period_8 
##  158161.9  111164.9

# Convert this into a data frame to plot using qplot
yrmargin <- as.data.frame(matrix(yrmargin,nrow = 9,ncol=1))
yrmargin$Period <- seq(0,8)
print(yrmargin)

##          V1 Period
## 1 1432800.0      0
## 2 1170270.4      1
## 3  876914.8      2
## 4  633930.5      3
## 5  451426.2      4
## 6  319144.0      5
## 7  224852.1      6
## 8  158161.9      7
## 9  111164.9      8

library(ggplot2)

ggplot2::ggplot(yrmargin, aes(x = as.factor(Period), y = V1, fill = as.factor(Period))) +
  geom_bar(stat="identity",position = position_dodge(width=0.9)) +
  ylab("Total Discounted Margin") + xlab("Period") +
  theme_bw() + theme(legend.position="none")

Now that we have some idea about how the discounted margin has been decreasing over the period, let’s finally get the CLV for customers in each segment. This will tell us how valuable each segment is. The thord function, clv takes in the same inputs as the previous function clv_dismargin and it outputs a list with two elements. The first element is a vector named CLV, which consists of the CLV for each customer segments. Note that this is CLV per customer. The second element of the list is the sum of CLV of all the customers in our database. This is the customer equity.

clv(tmat = transmat, cust0 = numcust, margin = grmargin, drate = discount, numperiod = periods)

## $CLV
##           CLV/Customer
## Active_20    276.59934
## Active_80    148.63486
## Warm          73.03613
## Cold          18.69707
## Lost           0.00000
## 
## $Cust.Equity
## [1] 2465570

There, we have it. Note that the CLV doesn’t take into account the margin from the current period. Thus, it’s the sum of all the discounted margins from the future periods. The CLV of the top most segment is 276.6 while even Warm and Cold customers have positive CLV. This suggests that although the current margin from these segments is 0, they are still valuable. However, in order to retain them, we can’t sepnd significant amounts as their CLV is much lower than other two segments.

Customer equity is the value of the existing customer base. If you carry out this exercise for all the customers of a firm, it will have a reasonable correlation with the firm’s market value. Of course, you will have to correct for the growth in the customer base over the next several years in order to get a better estimate.

Finally, let’s plot the CLVs