Как спрогнозировать годовую выручку от группы пользователей при помощи Excel

Международный маркетинговый аналитик Gett Алексей Куличевский опубликовал в своем блоге на Medium статью о том, как спрогнозировать годовую выручку когорты по первому месяцу ее существования. Редакция публикует материал с разрешения автора.

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

Главная проблема LTV в том, что на сбор данных требуется время, которого обычно нет. Например, вы в течение месяца тестировали новый рекламный канал, и теперь вам нужно решить, продолжать или нет. За месяц покупок было немного, канал еще далек от точки безубыточности, но вы готовы вкладывать, если будете знать, что за год клиенты окупятся.

Сегодня я расскажу, как прогнозировать годовую выручку когорты по первому месяцу ее существования. Для прогноза будем использовать линейную регрессию.

Вам понадобятся:

• Excel (Google-таблицы тоже подойдут, но там немного другой интерфейс);
• исторические данные о продажах (на них будем обучать модель).

Если вдруг ваш бизнес молодой, и данных за год пока нет,? - ничего страшного. Постройте прогноз на тот период, за который данные есть.

Итак, начнем.

Шаг 1: достаньте данные

Для этого упражнения я подготовил специальный файл, на котором вы можете потренироваться. Скачайте его и откройте в Excel.

Позже попросите программиста выгрузить ваши данные в таком же формате. Покажите этот файл как пример.

Шаг 2: посчитайте выручку на каждого пользователя

Когорты, в которых больше людей, скорее всего, принесут больше денег просто потому, что там больше людей. Единственный вывод, который вы сделаете из модели: «нужно больше покупателей». Не очень полезно. Вместо этого спрогнозируем среднюю выручку с покупателя. Для этого поделим итоговую выручку с когорты на количество людей.

Шаг 3: постройте график

Начинается самое интересное. Чтобы построить модель, нужно понять, как взаимосвязаны данные. Для этого построим scatter plot (такие графики мы все в школе строили).

Каждая точка на графике? - одна когорта. По оси X?-? выручка за месяц. По оси Y -? выручка за год.

Вопрос в том, насколько больше?

Шаг 4: строим модель

Мы всего в паре шагов от победы. Добавьте на график линию тренда. И не забудьте вывести на экран формулу графика.

Получившаяся формула? -? и есть нужная нам модель. Напомню, что Y? -?LTV за год, а X -?LTV за первый месяц. То есть:

LTV за год = 4.67 * LTV за месяц - 0,72

«Леша, ты хочешь сказать, что можно вот так вот спрогнозировать выручку за год с помощью сложения и умножения? Не может быть!»

Именно так. Но мы еще не закончили. Остался последний шаг.

Шаг 5: оцениваем точность модели

Под формулой есть показатель R^2. Он показывает, насколько хорошо модель описывает имеющиеся данные. 0,93 означает «чертовски хорошо описывает».

Но нам гораздо интересней знать, с какой точностью модель прогнозирует будущее.

Чтобы оценить точность модели, разделим имеющиеся данные на 2 группы: обучающую и тестовую.

Обучающую группу пометим нулем, тестовую? -? единицей.

На основе обучающей группы построим модель: добавим график с линей тренда и получим формулу.

Формула немного изменилась. Это нормально.

LTV за год = 5 * LTV за месяц? -?1.72

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

На графике голубая линия? -? реальный LTV каждой когорты, а оранжевая? -? прогноз, результат работы модели. Смотрите, как они близко.

В статистике используют специальный показатель MSE, сводящий точность модели к одной цифре. Но чтобы не перегружать вас в одной статье, предлагаю почитать про него на «Википедии».

Вернемся к тому, зачем мы все это делали.

Напомню, что вам нужно принять решение, продолжать ли рекламную кампанию.

Допустим, что за месяц вы потратили $10 тысяч и привлекли 600 новых пользователей. Эти пользователи за месяц принесли $2400 выручки.

Стоимость одного пользователя = $10000/600 = $16,7 LTV за первый месяц = $2400/600 = $4

Подставим значения в модель:

LTV за год = 5 * $4? -? 1,72 = $18,28 Прогнозный ROMI = $18,28/$16,17 = 113%.

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

P. S. Естественно, периоды 30 и 365 дней можно менять на любые другие. Я, например, часто прогнозирую первый месяц по первому дню.