XLSTAT: Customer Lifetime Value (CLV) in Excel

 

Dataset for Example

The attached dataset represents a sample of monthly subscription data from a phone service provider between 2010 to 2014. Three groups of users from a value-based segmentation are represented: Young, Classic and Premium.

The goal here is to calculate the  CLV (customer lifetime value) of customers and also to estimate some KPI's such as the customers' retention rate or the  time before churn.

 

Setting Up

• Open XLSTAT.

• Click on the Marketing/ Marketing Tools menu and select the Customer Lifetime Value feature.

 

• In the Customer Lifetime Value dialog box, under the General tab:

  • Select the subscription price column in the ARPA field.

  • Choose the acquisition and churn date columns in the Time field.

  • Enable Segments and select the corresponding column.

  • Set the Subscription period to monthly.

  • Check Variable labels to use the column headers as variable names.

In the Options tab:

• Include parameters like the discount rate and fixed operating costs in the CLV calculation.

• Set the retention rate manually if known, or click Estimate to calculate it from the data.

• In the Payment section, select Start of period, as customers are charged at the beginning of the month.

 

In the Outputs tab:

• Select CLV forecast  to make simulations on the average CLV value of customers remaining in the database after the last recorded churn date over the user-defined period. For this example, choose a period of 6 months.

 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpret the Results

The first result displayed is the average CLV per segment . The lowest average is for the Young segment.

Then, the  estimated churn and retention rates are displayed for each segment.

Premium customers are the least loyal, while the Young segment is the most loyal, with a 97.15% monthly retention rate. This suggests that higher spenders are not necessarily the most loyal. The report also includes estimated time to churn and a corresponding chart.

 

The results show that half of the premium customers cancel within the first year. A targeted marketing campaign could help boost loyalty in this segment. The sensitivity analysis then evaluates how 5% increases in retention rate affect CLV.

A 5% retention rate increase in the Premium segment would raise average CLV from €804.55 to €3537.73, reinforcing the need for retention efforts. The final table provides a 6-month CLV forecast for remaining customers after the last churn date.