Customer Lifetime Value (CLV)

Use this tool to assess the financial value of your customers. Available in Excel with the XLSTAT software.

What is Customer Lifetime Value?

This tool will help you to estimate the cash flows you will get from customers and estimate how long you can keep them after the acquisition. Using CLV, you will be able to streamline the marketing or advertising operation you could engage in order to increase your customers retention rate.

Customer Lifetime Value (CLV) can be defined as the present value of the future cash flows attributed to the relationship between a customer and the company. Several models for calculating CLV exist which vary according to multiple factors, specific to each organization. Here, the implemented model is the Simple Retention Model (SRM), suitable for customers in a contractual situation.

Probabilistic model for CLV:

  • Cancellation/churn time: Assume that all customers, being part of a certain segment, are retained each subscription period with probability r (retention rate) for all periods. Moreover, assume that a customer cancellation occurring at a certain period is independent of the cancellation during any other period. Let T be a random variable indicating the time of cancellation and t be a realization of T. Under these assumptions, T follows a geometric distribution.

  • CLV: When a customer cancels during period t, there will be t cash flows if it occurs at the beginning of the period and t−1 cash flows if it is at the end of the period. Let d be the discount rate.

  • Estimation of retention rates: In the previous section, we assume that the retention rate r was known, but in practice, it's not always the case. That's why XLSTAT proposes to estimate the later from the data. In any organization, some but not all customers will cancel. A customer who has not yet canceled is said to be censored. Thus, we can say that the organization will not have yet observed this customer cancellation time.

Options of Customer Lifetime Value function in XLSTAT

Segments labels: Select the segments labels. This field is only available when the segments option is enabled and at least one of the following options is enabled: discount rate, fixed costs or user-defined retention rate.

Discount rate: Activate this option if you want to take into account the discount rate applied to customers. This rate is considered fixed. If the segments option is enabled, select one value per segment and make sure that the lines are in the same order as segments labels.

Fixed costs: Activate this option if you want to deduct certain fixed operating costs from the revenues generated when calculating CLV. Select multiple columns if you have multiple costs to include. If the segments option is enabled, select one value per segment and make sure that the lines are in the same order as segments labels.

Retention rate:

  • Estimate: Activate this option to estimate the retention rate from the input data. If the segments option is enabled, the retention rate will be estimated for each segment.

  • User defined: Activate this option if you want to define the retention rate yourself. This rate is considered fixed. If the segments option is enabled, select one value per segment and make sure that the lines are in the same order as segments labels.

Payment:

  • Start of period: Select this option if payments occur at the beginning of each period.

  • End of period: Select this option if payments occur at the end of each period.

Results of Customer Lifetime Value function in XLSTAT

CLV: The average CLV is displayed. If the segment option is enabled, this value is displayed for each segment.

Estimated churn rate: In this table are displayed the churn and retention rate. If the segment option is enabled, these values are displayed for each segment.

Estimated defection time: Statistics on the time before defection are displayed in order to quickly visualize the dispersion of customers cancelation times. Thus, the 1st quartile, 3rd quartile, median and mean of customers departure times are displayed. If the segment option is enabled, these values are displayed for each segment.

Cashflow evolution: This table shows the evolution of cash flow. Each line in the table contains a cash flow forecast by period. The first line corresponds to the period following the most recent period contained in the input data.

Individual results: The CLV is displayed for each customer.

CLV forecast: A simulation on the average CLV value of customers remaining in the database after the last recorded churn date is performed over the period chosen by the user.

Sensitivity analysis: The impact of an increase in retention rate on CLV is displayed. The variations considered are increments of 5% from the estimated/defined retention rate. Each line in the table corresponds to a simulated retention rate. The CLV and the average time before defection are displayed in columns.

The CLV per segment chart is only available when the segment option is active. This graph displays the CLV for each segment as a bar chart.

Estimated defection time: This chart summarizes in a bar chart the information contained in the corresponding table described above.

Churn probabilities: Activate this option to display the chart showing the churn probabilities as a function of time.

Cashflow evolution: Activate this option to display the chart showing the cashflow evolution as a function of time.