How do I actually calculate basic CLV from a raw transaction table?

Most of the time, your data isn't going to come to you as a neat "CLV" column. You'll have a messy table of transactions. To get the basic historical CLV, you need to aggregate your data to find the average order value and the purchase frequency per customer. I usually handle this with dplyr because it makes the grouping logic clear.

Let's imagine we're analyzing a specialty coffee subscription service called "BeanStream." Here is how I'd approach the initial calculation:

library(dplyr)

# Sample transaction data
transactions <- data.frame(
  customer_id = c(1, 1, 1, 2, 2, 3, 3, 3, 3),
  order_value = c(25, 25, 30, 50, 45, 20, 20, 20, 25),
  date = as.Date('2023-01-01') + c(0, 30, 60, 0, 30, 0, 30, 60, 90)
)

# Aggregate to customer level
clv_data <- transactions %>%
  group_by(customer_id) %>%
  summarise(
    avg_order_value = mean(order_value),
    purchase_freq = n()
  ) %>%
  summarise(
    overall_avg_value = mean(avg_order_value),
    overall_avg_freq = mean(purchase_freq)
  )

# Simple CLV = Avg Value * Avg Frequency
basic_clv <- clv_data$overall_avg_value * clv_data$overall_avg_freq
print(basic_clv)

Now, this gives you the value of a customer over the period observed, but it doesn't tell you the lifetime value because it ignores how long they actually stay with you.

How do I factor in churn to project future value?

This is where the "Lifetime" part of CLV actually happens. In the real world, customers leave. If you know your monthly churn rate (the percentage of customers who cancel every month), you can estimate the average customer lifespan as 1 / churn_rate.

I've found that people often overcomplicate this, but for a baseline projection, you just multiply your periodic value by the expected lifespan. If BeanStream has a monthly churn rate of 5%, the average customer stays for 20 months.

# Assuming we know the monthly churn rate
churn_rate <- 0.05 
customer_lifespan_months <- 1 / churn_rate

# Monthly value per customer (Avg Value * Monthly Freq)
# Let's say our avg customer spends $22 per month
monthly_value <- 22 

projected_clv <- monthly_value * customer_lifespan_months
print(projected_clv) # $440

Just a heads-up: this is a simplified model. It assumes the churn rate is constant, which it rarely is. Newer customers usually churn at a higher rate than veterans, but for a first-pass analysis, this is the standard way to do it.

Is there a way to calculate "Net CLV" by including acquisition costs?

Calculating the gross value is great for reporting, but as an engineer or analyst, you need to provide the "Net" value to the business side. If it costs you $50 in marketing (Customer Acquisition Cost or CAC) to get a customer who only brings in $440 over two years, your profit isn't $440.

You simply subtract the CAC from your projected CLV. I like to put this into a small function so I can run it across different marketing channels (like Instagram vs. Google Ads) to see which one is actually profitable.

calculate_net_clv <- function(avg_monthly_val, churn, cac) {
  lifetime <- 1 / churn
  gross_clv <- avg_monthly_val * lifetime
  return(gross_clv - cac)
}

# Compare two channels
insta_net <- calculate_net_clv(avg_monthly_val = 22, churn = 0.05, cac = 30)
google_net <- calculate_net_clv(avg_monthly_val = 22, churn = 0.05, cac = 70)

cat("Instagram Net CLV:", insta_net, "\nGoogle Net CLV:", google_net)

If the Net CLV is negative, you're paying more to acquire the customer than they'll ever spend with you. That's the kind of insight that actually gets you noticed in a meeting.

📋 Practical Task

Exercise: Calculating Net CLV for the BeanStream Subscription Tier

You have been given a dataset of three different subscription tiers for BeanStream. Your goal is to determine which tier is the most profitable by calculating the Net CLV for each.

The Data:

• Basic Tier: Monthly Value = $15, Monthly Churn = 0.08, CAC = $20
• Premium Tier: Monthly Value = $30, Monthly Churn = 0.04, CAC = $50
• Elite Tier: Monthly Value = $60, Monthly Churn = 0.02, CAC = $120

Your Task:

1. Create a data frame in R containing these three tiers and their respective values.
2. Write a function (or use a vectorized approach) to calculate the Projected_Gross_CLV using the formula: Monthly Value * (1 / Monthly Churn).
3. Calculate the Net_CLV by subtracting the CAC from the Gross CLV.
4. Print the final data frame and identify which tier has the highest Net CLV.