XLSTAT: Customer Long-term Value (CLTV) in Excel

This tutorial shows how to compute and interpret a Customer Long-term Value analysis in Excel using the XLSTAT software.

 

Dataset for Example

The attached dataset samples monthly phone subscriptions from 2010 to 2014, covering three user segments: Young, Classic, and Premium. Using this example, we will calculate customer lifetime value (CLTV) and analyze the customer life cycle.

 

Setting Up

• Open XLSTAT.

• Click on the Marketing/ Marketing Tools menu and select the Customer Long-term Value (CLTV) feature.

  

• The Customer Long-term Value dialog appears.

  In the General tab:

  • Select the subscription price column for ARPA.

  • Select acquisition and churn date columns for Time (acquisition/churn).

  • Check Segments and select the segment data for segment-wise results.

  • Choose monthly as the Subscription period since data is monthly.

In the Options tab, include parameters like discount rate and fixed operating costs in the CLV calculation.

Since customers are charged at month start, select Start of period in Payment.

In the Outputs tab, choose desired results. Use the segment comparison option to compare retention functions across segments.

In the Charts tab, select from various curves—like comparison graphs showing retention, density, and hazard curves across segments—to overview customer behavior by segment.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

After the descriptive statistics table, the first result displayed is the average CLV per segment.

For each segment, the customer lifetime analysis table summarizes behavior: monthly cancellations, censored customers, retention, and churn rates.

The cumulative churn shows the total lost customers since the study began. For example, in the Classic segment at month 6, cumulative churn is 25.4%, meaning a quarter canceled within six months.

The probability density gives the chance a customer cancels exactly at month 6 (2.7% for Classic).

The hazard rate is the chance of canceling at month 6, given retention up to month 5 (3.7% for Classic).

The last columns show CLV estimates; a Classic segment customer canceling at month 6 has an average CLTV of €138,227.

The median retention time and the associated standard deviation are following. For the classic segment, half of the customers are lost after 15 months (14,719).

Several charts are generated next which summarize the information contained in the previous tables. 

The segment comparison output shows results of three Chi-square-based tests: Log-rank, Wilcoxon, and Tarone-Ware.

Lower p-values indicate more significant differences.

Here, all tests have p-values above 5%, so there’s no significant difference between the retention curves of the three segments.

Finally, a chart comparing the retention, density and hazard curves for the different segments is generated.