Coefficients for extrapolating 5-year CLTV component forecasts

This article describes how to derive customer value over a 5-year horizon from predictions from a number of ML models. Let us recall that the CLTV indicator is a composition of forecasts of its components (more details in the article). In our implementation, the maximum model forecasting period is 24 months. It is important to note that the higher the forecast horizon, the less accurate the model can make a forecast. And the CLTV indicator is interesting for business over a longer horizon, in our case – five years. How can you get a five-year forecast from two-year forecasts? The answer is simple: extrapolate forecasts.

The main idea of ​​extending (extrapolating) forecasts is to divide users into several groups, and in each group to uniformly extend the forecast series.

Next we will discuss:

• approaches to series extrapolation, their advantages and problems

• how to select groups and prepare data for extrapolation

• advantages of the chosen approach to extending forecasts for 5 years, difficulties and ways to solve them.

1. Approaches to extrapolation

So, let's figure out what we need to extrapolate and how we can do it. We have access to monthly forecasts for each of the CLTV components for 24 months. The final goal is to derive some coefficients with the help of which, having a forecast for 24 months, we can obtain a forecast for 25, 26, 27, … 60 months. To do this, it is necessary to extrapolate or extend a number of forecasts.

To construct coefficients, we have access to historical and forecast data.

In our opinion, calculating coefficients based on historical data (facts) has many disadvantages, since their changes are influenced by many external factors (changes in the cost of tariff plans, crises, actions of competitors), the influence of which we want to avoid and study separately through experiments and tests. In addition, for 5-year extrapolation coefficients it would be necessary to use very old historical data, which would lead to out-of-date coefficient values. For example, subscriber behavior (in particular, survival rate) in the last year is different from their behavior 3 years ago. For the same reason, ML models do not predict revenue and costs over a 5-year horizon. The advantages of this approach include the availability of information about actual fluctuations in the service margin over long horizons. As a result of this approach, if the basis improves every year, we will obtain underestimated coefficient estimates. We used historical data only for validation.

For the reasons described above, predicted values ​​were used to construct the coefficients. For extrapolation, we built regressions with more or less monotonically decreasing/increasing series of forecasts. However, it is not always possible to see such a dependence in a series of data; in this case, you can look at a series of forecasts calculated with a cumulative total and thus arrive at monotonicity.

Example: there are a number of observations over 2 years for a user base active in December 2021 (a group of users was recorded). Observation = the amount of revenue we forecast for 1, 2, 3… 24 months ahead. We will depict the described series with a blue line on the graph, where the x-axis is the month of the forecast, and the y-axis is revenue. We see that the blue line first decreases and then fluctuates around zero. Orange shows the series calculated as a cumulative total – it shows a clear logarithmic relationship between the accumulated revenue forecast and the month of the forecast.

2. Data preparation

If we look at the series of forecasts of the same metric for different subscribers, we saw that for some subscribers these series grow stronger, for others – weaker, for others – decrease. Then if we extrapolate all series with the same coefficients, we will get overestimated indicators for some subscribers, underestimated indicators for others, but on average good indicators for all. At the same time, if we build separate coefficients for each subscriber, we will not be able to guarantee such a correct shape of the series curve as we saw for the totality of subscribers. The middle ground between two extremes is to divide the base into large segments. We divide the subscriber base into segments, the behavior of subscribers (survival, revenue, costs) in which differ significantly from each other, but have similar dynamics of changes within the segment. With this approach, as one or another segment increases, the overall indicator better adapts to the changes. Segments can be selected expertly or based on cluster analysis.

Beeline used the following combinations to define segments:

1. customer experience in the company (lifetime). We allocate 0-3 months, 4-12 months, 13+ months

2. social demographic characteristics of the client

3. geographical location of the client

4. client activity over the past year/six months/month, etc.

Let us designate each group of characteristics with a number as in the list. Let feature 1 take K1 = 5 values, feature 2 K2 = 6, K3 = 8. The result is K1*K2*K3 = 5*6*8*10 = 240 segments. An example of such a segment is subscribers with a lifetime of at least a year from the northern regions and active at the end of the period.

Next, the question arises over what period to take a sample to calculate the coefficients. We tried 2 approaches: a 10% sample of the database over several years and the entire database over the last six months. Option 2 won, since shortening the period for collecting predictions allows us to capture the current behavior of subscribers.

So, we formed a certain sample of clients over the last 6 months, pulled up their characteristics, allowing us to understand which segment each subscriber belongs to. Next, we add to this table the currently available monthly forecasts of some CLTV component. In the case of the SM Mobile component, we get the following table:

Next, within the reporting date and segment, we verticalize the row with a forecast for 1-24 months for convenience, we get 24 rows instead of 24 columns with forecasts earlier with rows y1, y2, y3, y4, … y24 for each segment.

3. Extrapolation

The graph below shows the dynamics of fact and forecast for a certain cohort of clients (for example, active in December 2019 with a lifetime of one year from the northern regions). We see that the average fact and margin forecast decrease with increasing period due to the survival factor. The increase as the period grows becomes less and less tends to a certain value, without crossing the 0 value, since most of the forecasted metrics cannot take negative values ​​(GB, minutes, income). You can also note the downward convexity of the curve. Let's ask our equation for these properties: monotonic decrease, convexity downwards, non-intersection of the x-axis (this condition is not true for all components; in some cases, the series tends to some negative value and intersects the x-axis. In this case, the constant will be negative). Satisfies the stated conditions

For each segment, we build a model that will describe the dependence of the CLTV component on the month of prediction by the equation

(you can do this in a loop, build a pairwise regression for each segment, or you can add a variable responsible for the segment to the equation and multiply it by x). To calculate the coefficients, we use the last 6 months of the forecast – 6 generations (where generation = a group of subscribers for whom a 24-month forecast is available for the month in question). Thus, the initial table is a list of subscribers for the last 6 months, their forecasts for each of the 6 months for 2 years in advance and a number of subscriber characteristics that allow us to determine which segment the subscriber belongs to.

We will build a pairwise regression for each segment in the cycle.

So, in each segment for each cohort we have a series of forecasts for 1,2,3,..12,…24 months ahead, we extrapolate it using the least squares method (we used statsmodels). We select the specification (formula of how y depends on x, logarithmically, linearly, exponentially…) of the equation based on the type of a number of predictors and a number of facts. To select the most suitable specification Ramsey test and other specification tests can be used.

The code below helps create a list of renewal coefficients for the selected segment of the segm segment, builds a summary and graph of the model for this segment, and you can conveniently view the main statistics of the model.

import numpy as np
import math
import pandas as pd
import statsmodels.api as sm
import statsmodels.formula.api as smf
 
# df - таблица с рядами суммарных прогнозов по сегментам
SEGM = '...' # выбранный сегмент для расчета
df_segm = df[df['segment'] == SEGM]
 
form_reg = '(service_margin) ~ 1 + np.log(period_key)'
model = smf.ols(formula=form_reg, data=df_segm)
result = model.fit()
# вынимаем a и b из y = a + b(ln(x))
a = result.params[0]
b = result.params[1]
# смотрим саммари
print(result.summary())
 
N_PRED_HORIZON = 24
y_fact = list(df_segm['service_margin'])
y_pred = []
for period in range(1,25):
    y_pred+=[a+b*np.log(period)]
for period in range(25,61):
    y_fact+=[a+b*np.log(period)]
    y_pred+=[a+b*np.log(period)]
 
segment_coeffs = y_pred[N_PRED_HORIZON:]/y_pred[N_PRED_HORIZON-1]
 
# строим прогноз и факт на 1 графике
print(segm)
plt.plot(y_fact, label="y_fact")
plt.plot(y_pred, label="y_pred")
plt.legend(loc="upper right")
plt.show()

At this stage, the coefficients of determination are controlled (

) and the adequacy of the coefficients of the constructed models. Next, we substitute for each segment and period from 25 to 60, we get the predicted

and divide it by

we get the coefficient for converting the forecast for the 24th month into a forecast for the selected period as in the code above.

Example: in the XXX segment, after applying the least squares method, we received the equation

substitute into the equation

we get

divide by the forecast for the 24th period

and we get the coefficient

Instead of building a model for each segment, you can add to the equation a variable responsible for each segment and multiply it by the period. With 240 segments the equation would look something like this

where s1, s2,… are the active variables of the segments, take values ​​0 and 1. For each segment, only one variable s will be equal to 1, the rest will be zero, thus the problem will be reduced to the same paired regression as we built in the previous version .

4. Problems and approaches to their solution

1. Important variables are not taken into account. (endogeneity) This problem can arise if our CLTV component is highly dependent on some parameter. In our case, this parameter is the number of days in a month, which determines the amount of the monthly payment in tariffs with daily payment. This gives us a problem: the random error is no longer random, but depends on the number of days in the forecast month, so the coefficient estimates are not valid. In this case, we can divide our target variable by the number of days in the month and build models for the daily average component.

2. The time series of speeds does not repeat the features of a number of facts. This problem can arise if the model performs well on average, but overpredicts in some segments and underestimates in others. For example, in the graph below, a number of forecasts are decreasing, while a number of facts are increasing. In such a case, we extended with a moving average.

3. The first 1-3 months of the forecast “jump” or have a different character of the curve. (convex upward). This problem occurs for fresh and inactive subscribers, because the models work worse for them. In this case, you can extrapolate the series starting from the 4-5th month of the forecast, or you can calculate a moving average with a window of 3 months and build statistical models on it.

So far we have discussed which extrapolation approaches can be used to extend forecasts out to a 5-year horizon. The guys from the Beeline CLTV team will talk about what to do next with these indicators, as well as about the features of forecasting CLTV components very soon in the following articles.