Скоро наш журнал обновит свой дизайн
Close
Будь в курсе
Подпишитесь на рассылку, чтобы не пропускать новые выпуски журнала
лекция
Аналитика и маркетинг
Прогнозирование. Практика
Прогноз на основе одной переменной редко бывает оптимальным. Добавив в модель больше признаков, можно получить более точный результат. Сегодня продолжим прогнозировать ROI, но в этот раз используем больше данных.

Во‑первых, у нас в распоряжении не 10 когорт, а 1450.

Во‑вторых, помимо недельной и годовой выручки данные содержат тип рекламного канала, количество пользователей в когорте и затраты на привлечение пользователей.
Каждая строка — это когорта людей, пришедших из определённого рекламного канала за сутки:

  • Channel — тип рекламного канала;
  • Costs — затраты на привлечение;
  • Regs — количество зарегистрированных пользователей;
  • Revenue_week — выручка когорты за первую неделю после регистрации;
  • Revenue_year — выручка когорты за первый год после регистрации.

Калькулятор окупаемости рекламного канала
Мы не просто составим модель, но и сделаем на её основе калькулятор, который смогут использовать маркетологи, не заморачиваясь с тем, как именно работает модель:
Маркетолог выбирает в калькуляторе рекламный канал, вводит количество регистраций, расходы и выручку когорты за первую неделю. Программа подставляет данные в модель и выдаёт прогноз выручки и ROI за год.

В прошлом уроке мы обсуждали, что работа с моделью состоит из трёх шагов: подготовки, оценки точности и использования для прогнозирования новых данных. Добавим ещё один шаг — подготовку данных.

Готовим данные

Нам нужно спрогнозировать ROI, но в исходных данных такой метрики нет. Первым желанием может быть добавить её, но в нашем случае это излишне. Вспоминаем, что ROI = LTV ÷ CAC:
Расходы на привлечение клиентов не меняются, ROI растёт только за счёт изменения выручки. Если спрогнозируем LTV, получим и ROI.

Показателей CAC и LTV в исходных данных тоже нет. Но их стоит добавить, потому что мы используем эти метрики в модели. Поделим недельную, годовую выручку и расходы на количество пользователей и получим CAC, LTV за неделю и год:

  • CAC = Costs ÷ Regs

  • LTV_week = Revenue_week ÷ Regs

  • LTV_year = Revenue_year ÷ Regs
Следующий шаг — нужно что‑то сделать с полем Channel. Модель — это уравнение. Уравнения не умеют работать с текстом. Сами подумайте: на что нужно умножить слово Social, чтобы получить прогноз?

Channel может принимать ограниченное количество значений: Media Social или Search. Такие переменные называются качественными, или категорийными. Чтобы привести их к числовым, вводят фиктивные переменные.

Фиктивные переменные принимают значения 1 или 0 и обозначают наличие или отсутствие какого‑либо признака.

Например, в базе данных погоды есть столбец «осадки», в котором записано «дождь» или «без осадков».

Фиктивная переменная для этого поля может быть «шёл ли дождь?». Если значение равно 1, значит, шёл, если 0 — погода была ясная.
Фиктивных переменных вводят на одну меньше, чем возможно значений. Если бывают только «дождь» и «без осадков» — достаточно одной переменной, если «дождь», «снег» и «без осадков» — две.

Чтобы закодировать Сhannel, введём две переменные: is_search и is_media:
=IF() проверяет условие на истинность и возвращает одно из заданных значений.
В итоге получится вот такой набор данных (я поставил LTV_year в самый правый столбец):
Данные готовы, можно переходить к построению модели.

Строим модель

В прошлом уроке мы строили модель с помощью графика и линии тренда. Это наглядно, но долго и неудобно, я предпочитаю использовать формулу =TREND().

=TREND() строит линейную регрессию в памяти компьютера и сразу возвращает прогноз. Не нужно заморачиваться с коэффициентами и подстановкой формул.

Пример.

В столбце А — LTV за неделю. В столбце B — LTV за год.
В шестой строке — новые данные, годовой LTV неизвестен. Чтобы получить прогноз, в формулу нужно ввести известные данные (B2:B5, A2:A5) и новую информацию (A6):

=TREND(B2:B5, A2:A5, A6)

Вставьте формулу в ячейку B6 — и получите прогноз.
Отделим будущую модель от исторических данных, создадим новый лист prediction. Скопируем на лист названия столбцов из листа с данными и введём тестовые значения. Значение LTV_year пока оставим пустым. Его будем прогнозировать.
Теперь в LTV_year подставим формулу =TREND(data!F2:F1451,data!A2:E1451,A2:E2).

  • data!F2:F1451 — значения годового LTV на листе data;
  • data!A2:E1451 — признаки, на основе которых строим модель, тоже на листе data;
  • A2:E2 — новые данные, для которых нужно составить прогноз.
Получим готовый прогноз LTV. Попробуйте ввести новые данные, посмотрите, как меняется результат. Теперь оценим точность модели.

Оцениваем точность модели
Оценка точности всегда начинается с разделения данных на обучающую и тестовую выборки. Чтобы не ломать созданную модель, скопируем лист с данными, добавим ещё один столбец is_test, заполним его поочерёдно нулями и единицами и отсортируем по этому полю.

  • 0 — обучающая выборка,
  • 1 — тестовая.
Гугл‑таблицы автоматически спрогнозируют годовой LTV для строк 727...1451 (там, где is_test = 1) по данным из строк 2...726 (is_test = 0).

Раньше мы оценивали точность модели с помощью среднеквадратичной ошибки (MSE) — чем она меньше, тем модель точнее.

Недостаток MSE в том, что показатель сложноинтерпретируем. Непонятно, MSE = 10 — это много или мало? Чтобы ответить, нужно построить несколько моделей и выбрать наиболее точную.

В статистике также используют показатель RMSE (англ. root squared mean error), который проще объяснить.

Так как MSE — это среднее арифметическое от квадрата ошибок, то и измеряется он в единицах модели, возведённых в квадрат. Например, в $2. Чтобы вернуть нормальные измерения, от MSE берут квадратный корень:

RMSE = √ MSE — это средняя ошибка модели. Eсли её добавить в прогноз, получите интервал, внутри которого, скорее всего, окажется реальное значение.
Посчитаем MSE и RMSE для нашей модели.

Ошибка — это разница между прогнозом и реальным значением. Чтобы её посчитать, вычтем из реального годового LTV прогнозный. Введём в ячейку I727 формулу =F727‑H727 и скопируем её на весь столбец:
Теперь возведём ошибки в квадрат:
И посчитаем MSE и RMSE:
MSE модели равен 0,12 $2.

RMSE = √ 0,12 = 0,34 $.

Это значит, что в среднем модель ошибается на 0,34 $ в обе стороны, то есть можно ожидать, что реальное значение LTV будет в ±0,34 $ от предсказанного.

Чтобы увеличить точность, обычно прибавляют и убавляют не одну среднюю ошибку, а две, то есть ±0,68 $. Это называется 95%‑ный прогнозный интервал.

95%‑ный прогнозный интервал = прогноз ± 2 × RMSE.

В исходных данных средний годовой LTV = 5,09 $. Прогнозный интервал, соответственно, от 4,41 $ до 5,77 $. На мой взгляд, приемлемая точность.

Прогнозируем новые данные

Мы построили модель и оценили её точность. Теперь вернёмся к листу predictions и спрогнозируем новые данные. Например, подставим:
Добавим прогнозный интервал, LTV_year ± 2 × RMSE.

Нижняя граница = 6,78 $ − 2 × 0,34 $ = 6,09 $.

Верхняя граница = 6,78 $ + 2 × 0,34 $ = 7,46 $.
Это значит, что с 95%‑ной вероятностью годовой LTV этой когорты будет между 6,09 $ и 7,46 $.

Остался последний шаг: превратим таблицу в полноценный калькулятор LTV. Создадим новый лист, назовём его calculator. Добавим понятные заголовки и оформим калькулятор.
В исторических данных есть только три рекламных источника: Social, Search и Media. Если пользователь введёт что‑то другое, модель не будет знать, что делать, и всё сломается. Чтобы защититься, добавим валидацию данных с помощью Data ⟶ Data Validation ⟶ List of Items.