Sunday, May 15, 2011

LifeTime Value Analysis

In this post, will try to present a broad framework for LifeTime Value analysis.
Benefits for a business owner, solutioning from an modeler perspective, useful analysis tools from an analyst perspective and finally some mining techniques that are ideal post initial analyses

Lifetime Value (LTV) analysis is a powerful tool in the analyst’s hands to:
• CRM Implementation
o Understand Customers: Identify the characteristics of customers who are profitable and who are not
o Design Targeted Campaigns: Use Customer understanding to develop targeted campaigns to:
* Move more customers to the high-value bucket
* Increase wallet share from high value customers
• Design Product Pricing
o Identify Break-even period & price based on lifetime value instead of initial value

Here is a list of steps and analytic tools to be used to do lifetime value analysis:

Step 1: Choose ‘point in time’

Fir First step to a LTV analysis is to choose a ‘time’ to follow customers through their lifecycle. We could do this in two ways:

  • Choose a time in the past and follow them to the present
  • Choose a time in the present and follow them to a time in the past

Telecom Example: Consider all customers that bought a pre-paid SIM Card in Jan 2008 from Airtel

Online Example: Consider all customers that made a purchase of a computer online in Jan 2008 from

It is important to have a common identifier to track the customer. It could be the customer phone #/customer # in the telecom example OR the cookie/customer # in case of the online example

Step 2: Get the Transaction History for the Customers in Question

Get a list of all transactions in the following format:

In terms of SQL/SAS:

Assume tbl_order_history is the transaction database table, tbl_customer_# is the table containing list of customers that made a purchase in Jan 2008.

Select customer_#, transaction_date, Offering_type, Revenue from tbl_order_history A

inner join (select distinct customer_# from tbl_customer_#) B

on A.customer_# = B.customer_#

Step 3: Analysis Tools (Images not relevant to the examples - only for illustration)

  • Identifying Spread of Customer Lifetime Value: A very good tool here is a box plot. Example: Below box plot shows that the lifetime value of the product X below ranges from $x to $y

  • How much of my margins come from my High LTV customers?: A good tool here is a Pareto Chart.
  • How many of my customers are net –ve even after x years?

Same as above – pareto Chart. Pareto shows that 29% of customers are still –ve margin after 4 years J

There are other questions and useful tools for the same. Examples of some questions:

  • What is my average customer break-even period? – Time taken to recover the cost
  • How is the customer lifetime value by Product? – Useful tool is a stacked column chart

Step 4: Summarize Learnings

Each of the above questions results in learnings





Customer Lifetime Value spreads from $x to $y

Median value of my customer is $x

Most of my customers have a LTV between $q1 and $q3 (where $q1 is the 25th percentile and $q3 is the 75th percentile)


X% of my margins come from my high LTV customers

It is attractive to go after the top quartile


M% of my customers are net –ve after 2 years

Offering to customers in this space is not sensible. We should understand their characteristics to ensure we do not sell to this bracket OR find ways to sell to them to move up


Offering X constitutes 70% of my customer lifetime value

Which offerings to focus on?

Step 5: Finalize projects to leverage opportunities

The key opportunity that always comes out is to

Identification of potential high LTV Customers and targeted campaigns for them

Potential Pilots & Mining Techniques:

Some Sample Pilots that could lead to a lot of $$ for a telecom firm
1. Identify Customers in the service provider's customer base that have characteristics of high LTV Customers BUT are not yet there
  • Run Campaigns targeting them with offers (Outbound/Contact Center campaigns OR email campaigns OR in-person campaigns). Campaign upside $ is a way to 'outcome' base revenue
  • From a data mining perspective, this is a classification problem where methods like logistic regression, decision tree come in handy
2. Identify transactional characteristics that differentiate the high LTV customers from the low LTV
  • Product Affinity analysis to identify product purchases/offerings purchases/usage characteristics that differentiate the high LTV from low LTV
  • Target customers in Low LTV quadrants with these offers
  • Discount these offerings to new customers to ensure they start off with transactional characteristics of high LTV customers
  • Useful tools from a data mining perspective are clustering, log linear models