CLV Case Study
1 Situation Analysis
Since i-basket is a US company. Let’s use Marks and Spencer as a UK example for conducting 5C analysis for a grocery retailer. You can follow similar templates to conduct situation analysis for the first assignment.
1. Company
Products: M&S provides a wide range of products, including groceries, clothing, cafe, and home products.
Value Proposition: Offers high-quality, sustainable products and is known for its strong ethical standards.
Market Position: Holds a steady market share, but faces challenges in some segments such as clothing and home segments.
2. Customers
Demographics: Appeals to a broad demographic but is notably popular among middle to upper-middle-class consumers.
Customer Needs: Customers seek quality groceries, fashionable clothing, and home goods.
Customer Journey: Engaging through both online and offline channels, M&S appeals to both in-store and e-commerce shoppers.
3. Competitors
Direct Competitors: Tesco, Sainsbury’s, and other UK retail giants.
Indirect Competitors: Online retailers and fast-fashion brands.
Unique Selling Propositions (USPs): Competitors may offer lower prices (Lidl, Aldi) or broader product ranges (Tesco).
4. Collaborators
Suppliers: Partnerships with farmers and manufacturers.
Distributors: Works through its own retail network and an online platform.
Joint Ventures: Various collaborations, like with Ocado for online grocery retailing.
5. Climate
Economic Factors: Influenced by economic health and consumer spending patterns.
Legal & Political Factors: Brexit and changing trade and labor laws impact operations.
Social & Cultural Trends: Growing preference for online shopping and sustainable practices.
Technological Innovations: The integration of technology in retail experience and e-commerce. Lack direct online channel, may need to develop it’s own grocery shopping online and mobile channels to keep up with competitors.
Environmental Factors: Adopting and promoting sustainable and ethical practices.
2 Compute Customer Lifetime Value
2.1 Step 1: Determine time unit of analysis
- Time unit of analysis
[…] Because the typical customer contract is renewed annually, all relevant variables will be measured on a yearly basis.
- When should we use monthly analysis? When there is strong within-year seasonality of customer purchases
2.2 Step 2: Determine number of years
- \(N\): the number of years over which the customer relationship is assessed
- […] i-basket typically used a five-year horizon for revenue and profitability calculations
2.3 Step 3: Compute profit margin 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
- 40 times each year; each time $100; with profit margin 7% (COGS 93%)
- profit margin 7% (COGS 93%)
- variable delivery costs each order
- the annual CF from customers CF
2.4 Step 4: Compute sequence of retention rate
- \(r\): retention rate
- […] i-basket examined the proportion of customers who renewed their membership from one year to the next (using an average across three years). This proportion was 0.7
2.5 Step 5: Compute sequence of discount factors
- \(k\): the interest rate
- […] A yearly discount rate of 10%
discount_rate <- 0.1
discount_factor <- 1/(1+discount_rate)
discount_factor_seq <- discount_factor ^ (seq(1,N))
discount_factor_seq[1] 0.9090909 0.8264463 0.7513148 0.6830135 0.6209213- […] 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.6 Step 6: Compute customer acquisition costs
CAC = total costs for customer ad clicks + total costs of $15 promo + total costs of free deliveries
CAC Part I: Costs of paid search ads to get 1 new member.
[…] 20% of those that signed up for the free trial ended up becoming members
[…] a fifth of those who clicked on an ad were willing to give the service a try
- How many customers need to click the ad to get 1 new customer?
n_clickers_for_1newmember <- (1/clicker_to_trier_rate) * (1/trier_to_member_rate)
n_clickers_for_1newmember[1] 25- Total costs for customer clicks
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
(1 - profit_margin) * # profit rate
(1/trier_to_member_rate) # num of triers
total_cost_promo[1] 69.75CAC Part III: total costs from selling groceries for each trier
- 2 visits for each trier, the profits from the 2 visits are
- The 2 visits are free of delivery charges, which are 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
deliverycost_each_trier[1] 17.525- Net costs for each trier from the 2 visits
- Total net profits from all 25 triers
totalcosts_from_all_triers <- netcost_each_trier * (1/trier_to_member_rate)
totalcosts_from_all_triers[1] 17.625- CAC = total costs for customer ad clicks + total costs of $15 promo + total costs of free deliveries
2.7 Step 7: Compute CLV
- Compute the CLV based on the CLV formula (Table A)
- Revenues, variables costs, and profit for the next 5 years
- Apply retention rate
[1] 39.0000 27.3000 19.1100 13.3770 9.3639- Apply discount factor
profit_seq_after_churn_discount<- profit_seq_after_churn * discount_factor_seq
profit_seq_after_churn_discount[1] 35.454545 22.561983 14.357626 9.136671 5.814245- Compute CLV by summing up future expected profits
3 Use CLV to Guide Marketing Decisions
We can use CLV to guide our marketing decisions. Remember that any marketing activity comes with a cost and a benefit.
3.1 User Defined Function
To facilitate the process, let’s first see how to use a user defined function to compute CLV for any new scenario with ease.
computeCLV <- function(N = 5,
membership = 99,
n_visit = 40,
revenue_each_visit = 100,
profit_margin = 0.07,
retention_rate = 0.7,
discount_rate = 0.1,
clicker_to_trier_rate = 0.2,
trier_to_member_rate = 0.2,
promo_first_order_each_trier = 15) {
# Step 3
## compute M
M <- membership + revenue_each_visit * n_visit * profit_margin
## compute c
deliverycost_each_visit <- 5 + revenue_each_visit * 0.035
c <- deliverycost_each_visit * n_visit
## compute CF = M - c
CF <- M - c
## compute profit sequence
profit_seq <- rep(CF,N)
# Step 4: compute sequence of retention
retention_seq <- retention_rate ^ (seq(1,N) - 1)
# Step 5: compute sequence of discount factors
discount_factor <- 1/(1+discount_rate)
discount_factor_seq <- discount_factor ^ (seq(1,N))
# Step 6: Compute CAC
## Part I
n_clickers_for_1newmember <- (1/clicker_to_trier_rate) * (1/trier_to_member_rate)
total_cost_clicks <- 0.4 * n_clickers_for_1newmember
## Part II
total_cost_promo <- promo_first_order_each_trier * # promotion amount
(1 - profit_margin) * # profit rate
(1/trier_to_member_rate) # num of triers
## Part III
profit_each_trier <- revenue_each_visit * profit_margin * 2
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
netcost_each_trier <- deliverycost_each_trier - profit_each_trier
totalcosts_from_all_triers <- netcost_each_trier * (1/trier_to_member_rate)
## Compute CAC
CAC <- total_cost_clicks + total_cost_promo + totalcosts_from_all_triers
# Step 7: Compute CLV
## apply churn rate to profit sequence
profit_seq_after_churn <- profit_seq * retention_seq
## apply discount factor to profit sequence
profit_seq_after_churn_discount<- profit_seq_after_churn * discount_factor_seq
## take sum and deduct the CAC
CLV <- sum(profit_seq_after_churn_discount) - CAC
return(CLV)
}3.2 Use CLV to 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%?