Когортный анализ. Получаем данные

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

 

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

Конечно, есть риск, что компания обанкротится — тогда вы потеряете деньги. Но если ничего не вкладывать, ваши деньги точно обесценятся из-за инфляции.

 

К вам обратились две компании: «Рога, копыта и партнёры» и «Ёлки-палки интерпрайз». Обе открылись год назад, число клиентов растёт, покупают всё больше, выручка увеличивается. «Ёлки-палки интерпрайз» агрессивнее: тратит больше, но и растёт быстрее.

«Рога» и «Ёлки-палки» пока убыточны. Но оба основателя уверяют, что выйдут в прибыль, когда захватят долю рынка.

Наша задача — спрогнозировать прибыльность. Вспоминаем «принцип пирамиды» из второго урока. Обычно прибыль считают по формуле:

Прибыль = Доходы − Расходы

Это бесполезно, потому что неясно, почему растёт выручка — может быть, только из-за расходов на рекламу. Посчитаем по-другому:

Прибыль компании = Прибыль с каждого покупателя × Количество покупателей

 

Прибыль с покупателя = Суммарный доход с покупателя − Расходы на привлечение покупателя

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

 

Минутка терминологии

  • Суммарный доход с клиента называют LTV(от английского customer lifetime value).
  • Стоимость привлечения клиента называютCAC (от английского customer acquisition cost).
  • Расходы на привлечение окупаются, когда LTVстановится больше CAC. Окупаемость называют ROI (от английского return on investments).
  • ROI = LTV ÷ CAC, измеряется в процентах.

Если ROI равно 100%, значит расходы окупились. Дальше — прибыль.

 

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

В нашем случае когорта состоит из клиентов, сгруппированных по дате регистрации. Отслеживать будемзатраты, выручку, LTV и окупаемость когорт.

Разберём «Рога и копыта». У нас есть данные о пользователях и заказах.

Вспоминаем SQL. Первый шаг: считаем пользователей в каждой когорте и расходы на их привлечение:

 

SELECT

reg_month as cohort_id,

count(*) as cohort_size,

count(*) * 1000 as costs

— 1000 рублей — стоимость привлечения клиента

FROM

users

GROUP BY 1

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

 

SELECT

users.reg_month as cohort_id,

sum(orders.sum) as cohort_revenue

FROM

users INNER JOIN orders

ON users.uid = orders.uid

GROUP BY 1

Результат — суммарная выручка каждой когорты. Это почти то, что нам нужно. Осталось разбить выручку по месяцам. Для этого добавим в запрос ещё одну группировку: по месяцу заказа.

 

SELECT

users.reg_month as cohort_id,

orders.month as month,

sum(orders.sum) as cohort_revenue

FROM

users INNER JOIN orders

ON users.uid = orders.uid

GROUP BY 1, 2

Теперь мы видим, в какой месяц и сколько денег принесли клиенты из каждый когорты. Например, первая когорта принесла во втором месяце 225 339 рублей.

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

 

SELECT

rev.cohort_id AS cohort_id,

rev.month AS month,

round(rev.cohort_revenue  / size.cohort_size, 2) AS LTV,
— round() округляет значение до 2 знаков после запятой
round(rev.cohort_revenue / size.costs * 100 ,2) AS ROI
— умножаем на 100, чтобы привести к процентам

FROM

(

SELECT

reg_month as cohort_id,
count(*) AS cohort_size,
count(*) * 1000 as costs
— 1000 рублей — стоимость привлечения клиента

FROM

users

GROUP BY 1

) AS size
INNER JOIN
(

SELECT

users.reg_month as cohort_id,
orders.month as month,
sum(orders.sum) AS cohort_revenue

FROM

users INNER JOIN orders

ON users.uid = orders.uid

GROUP BY 1, 2

) AS rev

ON size.cohort_id = rev.cohort_id

 

Запрос выглядит громоздко, но по сути он простой. Сначала Бигквери считает первый вложенный селект и получает таблицу с размером когорт и расходами. Затем считаетвторой вложенный селект, получает выручку когорт. И, наконец, объединяет таблицы по общему полю cohort_id.

Результат: таблица из четырех столбцов:

  • cohort_id — месяц регистрации пользователей, то есть признак той или иной когорты;
  • month — месяц, за который смотрим метрики;
  • LTV — выручка с когорты за этот месяц, делённая на количество людей в когорте;
  • ROI — доля расходов когорты, которая окупилась в этот месяц.

 

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

 

SELECT

rev.cohort_id AS cohort_id,

rev.month AS month,

rev.month — rev.cohort_id AS cohort_age,

round(rev.cohort_revenue  / size.cohort_size, 2) AS LTV,
round(rev.cohort_revenue / size.costs * 100 ,2) AS ROI
— умножаем на 100, чтобы привести к процентам

FROM

(

SELECT

reg_month as cohort_id,
count(*) AS cohort_size,
count(*) * 1000 as costs
— 1000 рублей — стоимость привлечения клиента

FROM

users

GROUP BY 1

) AS size
INNER JOIN
(

SELECT

users.reg_month as cohort_id,
orders.month as month,
sum(orders.sum) AS cohort_revenue

FROM

users INNER JOIN orders

ON users.uid = orders.uid

GROUP BY 1, 2

) AS rev

ON size.cohort_id = rev.cohort_id

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