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

 

Вспоминаем прошлый урок. Итоговую прибыль мы представили как прибыль с покупателя, умноженную на количество покупателей.

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

В прошлом уроке мы получили данные о когортах «Рогов, копыт и партнёров» («Ёлки-палки» — в домашке). Сегодня оформим отчёт с помощью таблиц. Чтобы создать отчёт нужно загрузить и подготовить данные, создать сводные таблицы и оформить результаты. Разберём каждый этап подробнее. Отчёты делают в специальных программах — табличных процессорах, например, Экселе, Намберсе или Гугл Таблицах. Я не знаю, в какой системе вы работаете, поэтому на уроке используем Гугл Таблицы.

 

Загрузка и подготовка данных

 

Чтобы подготовить данные к анализу, нужно загрузить их в программу и проверить ошибки при загрузке. Загружаем данные. Самый быстрый способ — кликнуть в Бигквери по кнопке Save to Google Sheets. Гугл сам создаст новый файл и загрузит в него результаты запроса.

Более универсальный способ — скачать результаты в csv-файле. CSV — это как таблица в Экселе, только без форматирования. Поэтому csv-файлы маленькие и быстро открываются.

Чтобы скачать файл, нажмём по кнопке Download as CSV. Для загрузки в Гугл Таблицы, создадим новую таблицу и кликнем по File → Import.

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

Посмотрите на столбцы F и G в таблице. Числа по умолчанию выравниваются по правому краю, чтобы их было проще сравнивать (единицы к единицам, десятки к десяткам). В этих столбцах числа выровнены по левому краю, как текст.

Кликните по ячейке — увидите, что в строке формул перед числом стоит апостроф (‘). Это значит, что Гугл Таблицы решили, что в ячейке текст, а не число.

Чтобы это исправить, есть формула =VALUE().

=VALUE() превращает текст в числа.
Например, =VALUE(«100500») вернёт число 100500.

В ячейке H2 введём формулу =VALUE(F2). Мы говорим программе: «Cкажи, какое значение находится в ячейкеF2». Результат — число 298.17. Повторим для всей таблицы — скопируем ячейку H2 и вставим её в диапазон H2:I79.

Столбцы в табличных процессорах обозначаются буквами A, B, C, а строки — числами, как на шахматной доске. Адрес каждой ячейки складывается из буквы столбца и номера строки. Например, A1 или B7. Диапазоны ячеек называют по адресам левой верхней и правой нижней ячейки. Например,A1:B7.

 

Заменим текст на полученные числа. Скопируем H2:I79 и вставим в столбцы F и G. Чтобы вставить значения, а не формулы, используем специальную вставку (Edit → Paste special → paste values only). Формулы в H2:I79 больше не нужны. Удалим, чтобы не мешались.

Данные загружены, форматы проверены. Переходим к следующему этапу — созданию сводных таблиц.

 

Создаём сводные таблицы

 

Сводная таблица обобщает данные. Например, вы хотите посчитать, сколько «Рога и копыта» заработали за каждый месяц. Чтобы сделать вручную, нужно просуммировать столбец cohort_revenue для каждого order_month — это долго и неудобно. Сводная таблица решает эту задачу в два клика. Чтобы создать сводную таблицу, выделим данные и кликнем по Data → Pivot Table.

Чтобы посмотреть выручку за каждый месяц, добавим полеmonth в столбцы, а поле cohort_revenue в значения.

Теперь разобьём выручку по когортам. Добавим полеcohort_id в строки.

Узнаёте косынку из предыдущего урока? Мы построиликалендарный когортный отчёт. Каждая строка — когорта. В столбцах — календарные месяцы.

Читается отчёт так: «Первая когорта принесла в первый месяц 298 166 рублей. В следующем месяце эта когорта принесла ещё 225 339 рублей. Ещё через месяц — 180 957 рублей…»

Посчитаем доход с клиента (LTV). Напомню, что когорты выйдут в прибыль, когда клиенты принесут больше, чем бизнес потратил на их привлечение (1 000 рублей).

Отчёт автоматически перестроится, и мы увидим, что клиенты из первой когорты в первый месяц принесли по 298 рублей на человека, а на второй — ещё по 225.

Посчитаем, как накапливалась LTV когорт. Для этого в ячейку B16 вставим формулу =SUM($B2:B2) и скопируем её в диапазон B16:M27.

В верхнем правом углу косынки LTV когорт перевалил за 1 000 рублей. Годовой LTV самой старшей когорты составил 1 018 рублей. Это больше, чем стоимость привлечения клиента — значит, с этих когорт «Рога» получили прибыль.

 

Узнаем, сколько месяцев нужно когорте, чтобы выйти в плюс. Для этого сделаем когортный отчёт по возрасту: вместо календарных месяцев, поставим в столбцы сводной таблицы возраст когорты, а вместо LTV поставим ROI.

В нижней таблице накопительный LTV сменился на ROI. Сейчас это громоздкая таблица с кучей лишней информации.

Давайте её оформим и уберем лишнее:

1. Добавим подписи к когортам и месяцам;
2. Удалим лишнюю информацию: повторяющийся ROI и знаки после запятой;

Выделим месяцы с ROI > 100%.

Теперь видно, что все 4 когорты вышли в плюс через 8 месяцев после регистрации. Младшие когорты растут такими же темпами. Можем ожидать, что они окупятся тогда же и их LTV за год тоже составит 1 018 рублей. Отчёт готов. Давайте делать выводы.

 

Делаем выводы

 

Заполним пробелы в таблице из начала урока:

Это значит, что если прямо сейчас «Рога, копыта и партнёры» остановят продвижение и не получат новых клиентов, а просто продолжат обслуживать текущих, то за год они заработают 334 800 рублей прибыли.

Представьте, что вы вложите в компанию 100 миллионов рублей. Вся сумма пойдёт на привлечение клиентов. То есть компания привлечёт 100 000 дополнительных клиентови получит 1,8 млн рублей прибыли.