Customer lifetime value (CLV) is a prediction of the total revenue or profit that a business can generate from a customer over the entire duration of their relationship. CLV considers the customer’s average purchase value, the frequency of their purchases, and the length of their relationship with the business.
CLV is an important metric for any business because it can help you:
- Increase customer loyalty and retention by focusing on the customers who bring the most value to your business
- Reduce customer acquisition costs by targeting the customers who are most likely to stay with you for a long time
- Improve strategic decision-making by allocating your resources and efforts to the most profitable customer segments
- Enhance your products and services by understanding the needs and preferences of your customers
How to calculate CLV using Excel?
There are different ways to calculate CLV, depending on the data and assumptions you have. One of the simplest and most common methods is the historical CLV, which uses the past data of your customers to estimate their future value. The formula for historical CLV is:
To calculate CLV using Excel, you need to have the following data for each customer:
- Average order value (AOV): The average amount of money that a customer spends on each purchase
- Purchase frequency rate (PFR): The average number of purchases that a customer makes in a given period (e.g., month, year)
- Average customer lifespan (ACL): The average number of years that a customer stays with your business
You can calculate these values using the following formulas in Excel:
- AOV:
=SUM(revenue)/COUNT(orders) - PFR:
=COUNT(orders)/COUNT(customers) - ACL:
=AVERAGE(DATEDIF(first_order_date,last_order_date,"y"))
Once you have these values, you can calculate the CLV for each customer by multiplying them together. You can also calculate the average CLV for your entire customer base by taking the average of the individual CLVs.
A scenario to illustrate CLV calculation using Excel
To demonstrate how to calculate CLV using Excel, let’s use a hypothetical scenario of an online clothing store. Suppose you have the following data for 10 customers who have purchased from your store in the past year:
| Customer ID | Revenue | Orders | First Order Date | Last Order Date |
|---|---|---|---|---|
| 1 | $500 | 5 | 1/1/2023 | 12/1/2023 |
| 2 | $300 | 3 | 2/1/2023 | 10/1/2023 |
| 3 | $400 | 4 | 3/1/2023 | 11/1/2023 |
| 4 | $600 | 6 | 4/1/2023 | 12/1/2023 |
| 5 | $200 | 2 | 5/1/2023 | 9/1/2023 |
| 6 | $700 | 7 | 6/1/2023 | 12/1/2023 |
| 7 | $100 | 1 | 7/1/2023 | 7/1/2023 |
| 8 | $800 | 8 | 8/1/2023 | 12/1/2023 |
| 9 | $900 | 9 | 9/1/2023 | 12/1/2023 |
| 10 | $1000 | 10 | 10/1/2023 | 12/1/2023 |
Using the formulas mentioned above, you can calculate the AOV, PFR, and ACL for each customer in Excel. The results are shown in the table below:
| Customer ID | Revenue | Orders | First Order Date | Last Order Date | AOV | PFR | ACL |
|---|---|---|---|---|---|---|---|
| 1 | $500 | 5 | 1/1/2023 | 12/1/2023 | $100 | 0.42 | 1 |
| 2 | $300 | 3 | 2/1/2023 | 10/1/2023 | $100 | 0.25 | 0.75 |
| 3 | $400 | 4 | 3/1/2023 | 11/1/2023 | $100 | 0.33 | 0.75 |
| 4 | $600 | 6 | 4/1/2023 | 12/1/2023 | $100 | 0.5 | 0.75 |
| 5 | $200 | 2 | 5/1/2023 | 9/1/2023 | $100 | 0.17 | 0.5 |
| 6 | $700 | 7 | 6/1/2023 | 12/1/2023 | $100 | 0.58 | 0.5 |
| 7 | $100 | 1 | 7/1/2023 | 7/1/2023 | $100 | 0.08 | 0 |
| 8 | $800 | 8 | 8/1/2023 | 12/1/2023 | $100 | 0.67 | 0.5 |
| 9 | $900 | 9 | 9/1/2023 | 12/1/2023 | $100 | 0.75 | 0.5 |
| 10 | $1000 | 10 | 10/1/2023 | 12/1/2023 | $100 | 0.83 | 0.5 |
To calculate the CLV for each customer, you can simply multiply the AOV, PFR, and ACL together. The results are shown in the table below:
| Customer ID | Revenue | Orders | First Order Date | Last Order Date | AOV | PFR | ACL | CLV |
|---|---|---|---|---|---|---|---|---|
| 1 | $500 | 5 | 1/1/2023 | 12/1/2023 | $100 | 0.42 | 1 | $42 |
| 2 | $300 | 3 | 2/1/2023 | 10/1/2023 | $100 | 0.25 | 0.75 | $18.75 |
| 3 | $400 | 4 | 3/1/2023 | 11/1/2023 | $100 | 0.33 | 0.75 | $24.75 |
| 4 | $600 | 6 | 4/1/2023 | 12/1/2023 | $100 | 0.5 | 0.75 | $37.5 |
| 5 | $200 | 2 | 5/1/2023 | 9/1/2023 | $100 | 0.17 | 0.5 | $8.5 |
| 6 | $700 | 7 | 6/1/2023 | 12/1/2023 | $100 | 0.58 | 0.5 | $29 |
| 7 | $100 | 1 | 7/1/2023 | 7/1/2023 | $100 | 0.08 | 0 | $0 |
| 8 | $800 | 8 | 8/1/2023 | 12/1/2023 | $100 | 0.67 | 0.5 | $33.5 |
