Прогнозирование оттока клиентов в Excel

В прошлом посте я показал как отток клиентов тихо убивает рост вашего бизнеса. Сегодня мы будем говорить о том, как сделать первые шаги к прогнозированию того, как будут размываться ваша когорта.

Начнем с того, что возьмем одну когорту и построим график ее размытия.

Сырые данные оттока одной когорты клиентов могут выглядеть вот так:

Cohort as a table.

 У нас есть две колонки:
  • колонка t - это шкала времени (это м.б. 1 неделя, 1 месяц или даже 1 год);
  • колонка S - это колонка описывает, какой % клиентов продолжил пользоваться вашим продуктом в соответствующий период времени t.
В нашем примере период t = 1 месяц. 

Например, в каком-то из месяцев мы привлекли X клиентов. Для нас это точка отсчета - наш период "0". В нем все 100% клиентов когорты с нами. 

В следующий период (месяц) перешло 63% (=0.63) клиентов. Это значит, что 37% клиентов, которые были в предыдущем периоде, не продолжили пользоваться нашим продуктом. Они ушли в отток.

Другое представление оттока когорты клиентов это график:

Cohort as a chart.

Суть графика та же: вы видите как размывается ваша когорта клиентов. В каждый следующий период переходит все меньше и меньше клиентов.

Еще один важный момент, на который я хочу обратить ваше внимание, это то, что многие путают кривую оттока и кривую удержания.

Survival curve vs Retention Curve.

В кривой оттока (S) мы оцениваем какой % клиентов от начального размера когорты продолжил пользоваться продуктом в соответствующем периоде.

В кривой удержания (R) мы оцениваем какой % клиентов предыдущего периода продолжил пользоваться продуктом в текущем периоде.

Теперь, когда мы сделали вводную в данные когорты клиентов, давайте переходить, собственно, к задаче прогнозирования.

Первое, с чего я бы хотел начать так это с того, что в "0" периоде любая когорта всегда полная (все 100% привлеченных клиентов с нами). Поэтому прогнозировать этот период нет смысла.

Далее, в Excel есть замечательный инструмент и называется он Trendline

Он позволяет вам, построив кривую оттока, подобрать к ней Trendline, который максимально описывает поведение вашей кривой оттока клиентов.

Trendline - Linear regression.

Наша первая попытка схватила направление тренда (тренд нисходящий) но, в целом, прямая линия плохо описывает поведение нашей кривой оттока клиентов. 

Это видно и визуально и через коэффициент R2 = 0.6986 (как правило, чем ближе R2 к 1.0, тем лучше Trendline описывает нашу кривую оттока).

Это происходит потому, что, по умолчанию, Excel добавляет Trendline на основе линейной регрессии (строит прямую линию вида y = a * x + b). 

Но, Excel позволяет вам также попробовать несколько других типов трендов:

Trendline options.

Давайте попробуем изменить Trendline c Linear на Logarithmic. Это должно выкривить нашу прямую линию тренда и улучшить fit.

Trendline with logarithmic transformation.

Как мы видим, в целом, результат стал заметно лучше. 

И визуально наша кривая Trendline лучше повторяет кривую оттока. И R2 вырос до 0.9336.

И хотя может показаться, что мы справились с задачей, но, на самом деле, работа по прогнозированию кривой оттока только начинается:
  1. одно дело нарисовать на графике линию тренда, другое дело научиться делать расчет точек кривой оттока для новых когорт;

  2. мы сделали расчет по историческим данным (11 месяцев жизни когорты), но, на самом деле, мы хотим по первым нескольким периодам когорты прогнозировать ее отток в будущем.
Тем не менее, мы уже понимаем - если мы будем применять преобразование к данным (например - логарифм), то мы сможем получить кривую, которая будет в достаточной степени похожа на нашу кривую оттока.

Давайте немного подробнее разберем преобразование логарифм.

Итак, любая прямая описывается уравнением y = a * x + b, где
  • a = это slope коэффициент, и 
  • b = это intercept коэффициент.
Применительно к оттоку клиентов прямую линию тренда можно описать вот так: S = a * t + b.

Чтобы преобразовать нашу линию тренда мы можем:
  • либо трансформировать t - выровнять кривую тренда;
  • либо трансформировать S - изогнуть прямую линию тренда.
Я предпочитаю выравнивать кривую тренда. 

Есть множество способов трансформирования, давайте начнем с LN(t)

Преобразование LN(t)

Применим функцию натурального логарифма к каждому значению в колонке t:

log transformation - LN(t).

После трансформирования колонки t наша кривая оттока клиентов должна была несколько выпрямиться и линейный тренд должен уже намного лучше описывать ее. Давайте это проверим:

Linear regression with transformed t: S = a * LN(t) + b.

Хотя наша кривая оттока не стала идеальной прямой после преобразования, однако теперь она намного точнее описывается с помощью прямой линии. Коэффициент R2 = 0.9336. 

Давайте начнем адресовать проблемы обозначенные ранее:

(1) одно дело нарисовать на графике линию тренда, другое дело научиться делать расчет точек кривой оттока для новых когорт

Во-первых, при настройке отображения линии тренда можно выбрать две дополнительные опции:
  • показывать уравнение линии тренда;
  • показывать коэффициент R2.
На последнем чарте четко видно, что y = -0.1445 * x + 0.5801. Можно конечно вначале построить график, затем добавить линию тренда, затем включить опцию показывать уравнение линии тренда. Но это совсем не удобно.

Удобнее воспользоваться функциями Excel:
  • SLOPE() - рассчитывает коэффициент a линейной регрессии;
  • INTERCEPT() - рассчитывает коэффициент b линейной регрессии;
  • RSQ() - рассчитывает коэффициент R2.
Применив эти три формулы мы получаем коэффициенты, точно такие же как на графике выше:

Excel funcs: SLOPE(), INTERCEPT() & RSQ().

Теперь, когда у нас есть коэффициенты a и b , мы можем по сырым данным делать расчет точек кривой оттока:

S curve forecasting.
  
И хотя R2 в некой степени показывает качество нашей модели, в дальнейшем мы будем оценивать качество через коэффициент ошибки MAPE (Mean Absolute Percentage Error).

Linear regression with transformed t = LN(t); MAPE calculated on full data coefficients.

Итак, первую проблему мы адресовали: мы научились делать преобразования, рассчитывать коэффициенты линии тренда, и, собственно, рассчитывать отток клиентов на период t.

Давайте адресовать вторую проблему:

(2) мы сделали расчет по историческим данным (11 месяцев жизни когорты), но, на самом деле, мы хотим по первым нескольким периодам когорты прогнозировать ее отток в будущем.

Это проблема общая - она касается всех статистических моделей (ML-моделей).

Обычно, для решения этой проблемы статистики делят свои данные на две непересекающихся части:
  • train dataset - данные на которых модель обучается (распознает тренд);
  • test dataset - данные на которых модель проверяется.
Мы с вами поступим точно также: мы будем тренировать модель на первых 2-х периодах (t = { 1; 2 }) и затем прогнозировать следующие точки кривой оттока клиентов на следующих периодах (t = { 3; 4; 5; 6; 7; 8; 9; 10; 11 }).

Для этого нам нужно пересчитать наши коэффициенты регрессии на первых двух периодах:

SLOPE() & INTERCEPT() calculated for train period (t = { 1; 2 }).

Как мы видим коэффициенты изменились. Данных сильно меньше и, возможно, новая линия тренда будет не такой точной.

Давайте по новым коэффициенты a и b на сырых данных рассчитаем точки кривой оттока:

Linear regression with transformed t = LN(t); MAPE calculated on train coefficients.

Как мы видим, на train-периоде ошибка (APE_train) нулевая, но дальше на test-периоде ошибка нарастает и уже в 11 периоде % ошибки = 69% от оригинального значения оттока.

Мы приходим к выводу, что наша модель не качественная и лучше ее не использовать.

Что же нам делать?

Ранее я уже упоминал, что существует много преобразований. Сегодня я предлагаю быстро протестировать еще несколько:
  • преобразовать наш х = 1 / t;
  • преобразовать наш x = LN(t) и наш y = LN(S);
  • преобразовать наш х = LN(t) и вместо S прогнозировать R.
Преобразование 1 / t

Linear regression with transformed t = 1 / t; MAPE calculated on train coefficients.

Как мы видим, преобразование 1 / t обученное на 2-х периодах дает более точную модель прогнозирования оттока клиентов.

Преобразование LN(s)LN(t)

Linear regression with transformed t = LN(t) and S = LN(S); MAPE calculated on train coefficients.

Как мы видим, преобразование LN(s)LN(t) обученное на 2-х периодах дает еще более точную модель прогнозирования оттока клиентов.

Преобразование RLN(t)

Linear regression with transformed t = LN(t) and S = R; MAPE calculated on train coefficients.

Как мы видим, преобразование RLN(t) обученное на 2-х периодах дает самую точную модель прогнозирования оттока клиентов.

Возможно, в итоге, вы пришли к следующей мысли:

Можем ли мы принять, что преобразование RLN(t) обученное на 2-х периодах всегда является нашей лучшей опцией для прогнозирования оттока клиентов?

Короткий ответ: нет.

Нужно ВСЕГДА тестировать модель на своих данных и подбирать наилучшую трансформацию.

Comments

Popular posts from this blog

IV/WOE - хороший способ понять какой информацией вы обладаете

A/B-тестирование: смотреть на конверсию vs смотреть на продажи

Продуктовая аналитика: влияние продуктовых фич на ретеншн