Class 3 Case Study: Customer Lifetime Value

Author

Affiliation

Dr Wei Miao

UCL School of Management

Published

October 11, 2023

Class objectives

How to apply CLV calculation in a real-life case study for i-basket grocery chain
Discuss and see examples of how CLV can be used by marketers to guide marketing decisions

1 Recap: Customer Lifetime Value

1.1 Customer Life Cycle

We learned campaign-centric break-even analysis tools, such as break-even quantity and net present value. BEA is also referred to as cost-benefit analysis.
- Costs of business activities
- Benefits of business activities
CLV is a customer-centric break-even analysis, which considers a customer as an asset to the company that generates future cashflows
- Costs: customer acquisition costs (CAC)
- Benefits: customer generates profits for the company in each period

1.2 Customer Acquisition Cost

A new Bubble Tea shop in Canary Wharf is contemplating whether or not to attract new customers by sending ads leaflets to nearby residents.
- randomly sending out leaflets: expected response rate of 1%
- using names purchased from a marketing agency: expected response rate of 4%
A intuitive way of computing CAC: think about to obtain 1 new customer, how many offers to make; then multiply it with cost per offer

1.3 CLV: Formula

\[ \mathrm{CLV} = - CAC + \sum_{t=1}^{N} \frac{CF_t * r^{(t-1)}}{(1+k)^{t}} \]

where $CF_t = M_t - c_t$

$r$ is the average per period retention rate; $r^{(t-1)}$ is the cumulative retention rate in period $t$
$N$ is the number of periods over which the relationship is calculated
$M_{t}$ is the profit margin the customer generates in period $t$, which is typically $M_t = revenue * (1 - COGS)$
$c_{t}$ is the marketing costs per period to maintain customer relationship in each period $t$
$k$ is the interest (discount) rate for discounting future cash flows; $d= \frac{1}{1+k}$ is the discount factor

2 Case Study: i-basket CLV

2.1 Situation Analyses for a Grocery Company

Let’s use a UK grocery retailer Marks and Spencer as an example

Company
Customer
Collaborators
Competitors
Context/Climate

2.2 Overview for Computing CLV

2.3 Step 1: Determine time unit of analysis

Time unit of analysis
- […] (find info in the case study)
  - When should we use monthly analysis or other units of time?

2.4 Step 2: Determine number of years

$N$: the number of years over which the customer relationship is assessed
- […] (find info in the case study)
- How can you do better here?

N <-

2.5 Step 3: Compute CF for each period

$CF = M - c$: gross profit each year, which is the profit from sales M minus marketing costs c

most customers paid the $99 annual membership fee

membership <-

2.6 Step 3: Compute CF for each period

40 times each year; each time $100; with profit margin 7% (COGS 93%)

n_visit <- 
revenue_each_visit <- 
profit_margin <- 
## think carefully about how M is calculated, it's tricky ~~~~
M <-

variable delivery costs each order:
- […] find info in the case study about delivery costs
- Why classified as variable marketing costs? Does it make a difference?

deliverycost_each_visit <- 
c <- deliverycost_each_visit * n_visit

2.7 Step 3: Compute CF for each period

The annual CF from customers CF

# CF is the cash flow for one year
CF <- 

# create a sequence of CF for N years 
profit_seq <- rep(CF,N)

2.8 Step 4: Compute sequence of retention rate

$r$: retention rate

[…] (find info in the case study)

# retention_rate is the probability of customer staying with us after 1 year
retention_rate <- 
  
# create a geometric sequence of accumulative retention rate for N years  
retention_seq <-

2.9 Step 5: Compute sequence of discount factors

$k$: the discount rate

[…] A yearly discount rate of 10%

discount_rate <- 0.1
discount_factor_seq <-

[…] The team decided to take a conservative approach whereby all profits are booked at the end of year.
- All profits earned per customer in year 1 need to be discounted once, the profits earned in year 2 need to be discounted twice, and so on

2.10 Step 6: Compute customer acquisition costs

What does the CAC include in the case study?

[…] (find info in the case study)

total costs for customer ad clicks
total costs of $15 promo
total costs of free deliveries

2.11 Paid Search Advertising (Search Engine Marketing, SEM)

Brands/companies (Uni)
- bid on keywords
Advertisers (Google)
- decides listings
Customers
- paid search (bids-based)
- organic (quality-based)

2.12 Marketing Funnel for Paid Search Ads

Marketing Funnels

A marketing funnel is a model that represents the customer’s journey from the initial awareness of a product or service to the ultimate action, often a purchase. This journey is depicted as a funnel to illustrate the decrease in the number of potential customers at each stage–Awareness, Interest, Consideration, Intent, Evaluation, and Purchase - each representing a different stage in the customer’s journey toward transaction.

2.13 Step 6: Compute customer acquisition costs

CAC Part I: Costs of paid search ads to get 1 new member.

[…] The click-through rates varied across these digital platforms, but hovered around 1-2%.
- How to incorporate this information?

[…] a fifth of those who clicked on an ad were willing to give the service a try
- How many clickers do we need for 1 new “trier”?

# clicker_to_trier_rate is the % of trier customers from clickers
clicker_to_trier_rate <-

[…] 20% of those that signed up for the free trial ended up becoming members
- How many triers do we need for1 new member?

# trier_to_member_rate is the % of a new member from triers
trier_to_member_rate <-

How many clickers do we need for 1 new member?

n_clickers_for_1newmember <- (1/clicker_to_trier_rate) * (1/trier_to_member_rate) # 5 * 5 = 25

Finally, based on the the cost per click information, we can compute the total click costs to get 1 member.

total_cost_clicks <- 0.4 * n_clickers_for_1newmember # 0.4 * 25

2.14 Step 6: Compute customer acquisition costs

CAC Part II: total costs of $15 promo for first order each trier customer

What is the total promo cost for these “trier” customers’ first order?

promo_first_order_each_trier <- 15

total_cost_promo <- promo_first_order_each_trier * # promotion amount = $15
  (1 - profit_margin) *    # 7% profit margin
  (1/trier_to_member_rate) # num of triers = 5

2.15 Step 6: Compute customer acquisition costs

CAC Part III: total costs from selling groceries

For each trier, 2 visits , the profits from the 2 visits are:

profit_each_trier <-  revenue_each_visit * #$100 per visit
  profit_margin * # 7% profit margin
  2 # a trier shops twice

For each trier, the 2 visits are free of delivery charges, which are marketing costs to i-basket

deliverycost_1st <- 5 + (revenue_each_visit + promo_first_order_each_trier) * 0.035
deliverycost_2nd <- 5 + revenue_each_visit * 0.035
deliverycost_each_trier <- deliverycost_1st + deliverycost_2nd

For each trier, compute net marketing costs from the 2 visits (marketing costs - earned profits)

netcost_each_trier <- deliverycost_each_trier - profit_each_trier

Total net profits from all 25 triers

totalcosts_from_all_triers <- netcost_each_trier * (1/trier_to_member_rate)

2.16 Step 6: Compute customer acquisition costs

CAC = total costs for customer ad clicks (for all clickers) + total costs of $15 promo (for all triers) + total costs of selling groceries (for all triers)

CAC <- total_cost_clicks + total_cost_promo + totalcosts_from_all_triers

2.17 Step 7: Compute CLV

Compute the CLV based on the CLV formula (Table A)

7.1 Revenues, variables costs, and profit for the next 5 years

profit_seq

7.2 Apply retention rate

profit_seq_after_churn <- profit_seq *

7.3 Apply discount factor

profit_seq_after_churn_discount<- profit_seq_after_churn *

7.4 Compute CLV by summing up future expected profits

CLV <- sum(profit_seq_after_churn_discount) - CAC

3 CLV for Marketing Decisions

3.1 CLV as a Key Management Tool

We can use CLV as the key managerial tool for evaluating different marketing initiatives!

3.2 User Defined Functions in R

A User Defined Function (UDF) in R refers to a function created by the user, as opposed to built-in functions, to perform specific operations or actions. UDFs are useful for automating and coding specific tasks that aren’t covered by R’s rich set of built-in functions, allowing the user to specify exactly what the function should do.

Function Name: The user decides the name of the function.
Function Body: Contains the code that performs the operation.
Arguments: (Optional) Values that can be passed into the function to influence its behavior.
Return Value: What the function outputs after it is called and executed.

3.3 Syntax of UDF

function_name <- function(arg1, arg2, ...){
    # Code to perform operations
    
    return(output)
}

3.4 Use UDF to Compute CLV and Guide Marketing Decisions

(To guide customer acquisition) What if the company only offers $5 for first time purchase? This will save some CAC but the cliker-to-trier rate will decrease to 10%. Please compute the new CLV. Should you go ahead with the proposed change?
(To guide customer retention) What if the company increases the annual membership fee to $119? This will increase revenue from memberships but will also make some customers unhappy so their retention rate reduce to 60%. Please compute the new CLV. Should you go ahead with the proposed change? What if the retention rate reduces to 50%?
(To conduct sensitivity analysis; after-class) Due to post-COVID economic recession, new members’ spending is uncertain: there is a 60% chance of $30 revenue per visit, 30% chance of $40 revenue per visit, and 10% chance of $50 revenue per visit. What would be the expected CLV for a new member customer?