Руководство к созданию собственного когортного отчёта по возвратности

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

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

Введение

Одна из моих разработок — приложение для корпоративной аналитики. И когортный отчёт по возвратности вероятно является важнейшей функцией. Мне не раз приходилось его переписывать, делая более настраиваемым, гибким.

Во время разработки я не нашёл примеров реализации такого отчёта, поэтому изложенный ниже алгоритм был создан мной из понимания сути когортного анализа. Если кто-то обнаружит косяки или знает варианты получше, пожалуйста, сообщите об этом в комментариях.

Условные определения:

• Когортный анализ (Cohort analysis) — метод оценки каких-либо метрик с разделением пользователей на независимые группы — когорты.
• Возвратность (Retention) — характеристика группы пользователей, вычисляемая соотношением активных пользователей в определённые временные промежутки. Пример: некоторое приложение установили 50 человек, через неделю активными пользователями остались лишь 5 человек; возвратность: 5 / 50 * 100% = 10%.

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

  
  

  На примере ниже видно, что из клиентов, пришедших в августе, на следующий месяц остались только 60% от их начального количества. А число активных клиентов в сентябре составляют: 100% от пришедших в сентябре + 60% от пришедших в августе + 30% от пришедших в июле. Аналогично, число активных клиентов в августе это 100% от новых клиентов в августе и 50% клиентов, пришедших в июле. То есть, сами когорты мы смотрим по строкам, а всех клиентов, активных в некоторый месяц, — по диагонали, по разным когортам.

  
  

• Life-Time Value (LTV) — характеристика группы клиентов, которая показывает, сколько дохода в среднем приносит клиент из этой группы.

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

Шаг 1. Постановка задачи, таблица Orders

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

// Дата заказа
date: DateTime,
// ID клиента
clientID: String,
// Стоимость заказа
price: Int

Шаг 2. Таблица Clients с датой прихода клиента

Нам нужно получить таблицу Clients с датой прихода клиента:

date: DateTime,
clientID: String

Возможно, у кого-то она уже есть (например, с датой регистрации пользователя или с датой установки приложения), но её также можно посчитать её как дату первого заказа.

Пример кода

Python:

Clients = pd.groupby(Orders, by=['clientID'], as_index=False)
Clients = Clients.agg({ 'dt' : {'date' : 'min' }})
Clients.columns = cli.columns.droplevel()
Clients.columns = ['clientID', 'date']

MySQL:

SELECT clientID, MIN(date) AS date
FROM Orders
GROUP BY clientID

Шаг 3. Объединение Orders и Clients в Mix

Производим объединение таблиц Orders и Clients по типу Left через общее поле clientID. Дабы избежать путаницы, поле date из первой таблицы называем dateOr, а у второй — dateCl.

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

Структура таблицы Mix:

// Дата прихода
dateCl: String,
// Дата заказа
dateOr: String,
// ID клиента
clientID: String,
// Стоимость заказа
price: Int

Пример кода

Python:

Mix = pd.merge(Orders, Clients, how='left', on=['clientID'])
Mix.columns = ['dateOr', 'clientID', 'price', 'dateCl']

def cutDate(txt):
    return txt[:7]

Mix['dateOr'] = Mix['dateOr'].apply(cutDate)
Mix['dateCl'] = Mix['dateCl'].apply(cutDate)

MySQL:

SELECT
    STRFTIME_UTC_USEC(Clients.date, "%Y-%m") AS dateCl,
    STRFTIME_UTC_USEC(Orders.date, "%Y-%m") AS dateOr,
    clientID, price
FROM Clients INNER JOIN Orders ON (Clients.date = Orders.date)

Шаг 4. Группируем раз, таблица Preresult

Наконец приближаемся к когортам! Произведём группировку сразу по трём полям: dateCl, dateOr и clientID.

Почему по клиенту тоже, а не только по датам?

Без этого мы не сможем найти число активных клиентов в какой-либо промежуток времени. Количество строк (функция Count()) дала бы нам лишь количество заказов в когорте в данный временной промежуток, а число активных клиентов найти было бы никак.

К этим полям добавляем следующие:

• Число заказов, которые сделал клиент в этот временной промежуток. Находим как количество сгруппированных строк:
  ordersCount = Count()
• Сумма, на которую этот клиент сделал заказы в этот временной промежуток. Находим как сумму стоимостей отдельных заказов:
  total = Sum(price)

Итоговая структура:

// Дата прихода
dateCl: String,
// Дата заказа
dateOr: String,
// ID клиента
clientID: String,
// Число заказов, которые сделал клиент в этот временной промежуток
ordersCount: Int,
// Сумма, на которую этот клиент сделал заказы в этот временной промежуток
total: Int,

Пример кода

Python:

Preresult = pd.groupby(Mix, by=['tel', 'dateOr', 'dateCl'], as_index=False)
Preresult = Preresult.agg({ 'price': { 'total': 'sum', 'ordersCount': 'count' } })
Preresult.columns = Preresult.columns.droplevel()
Preresult.columns = ['clientID', 'dateOr', 'dateCl', 'total', 'ordersCount']

MySQL:

SELECT
    clientID, dateCl, dateOr,
    COUNT(*) AS ordersCount,
    SUM(price) AS total,
FROM Mix
GROUP BY dateCl, dateOr, clientID

Шаг 5. Группируем два, таблица Result

Теперь уже можем обезличить наши данные, сгруппировав только по dateCl и dateOr. Добавляем другие поля:

• Число активных клиентов в данный временной промежуток. Находим как количество сгруппированных строк:
  clientsCount = Count()
• Число заказов, которые сделали все клиенты в этот временной промежуток. Находим как сумму числа заказов по отдельным клиентам:
  ordersCount = Sum(ordersCount)
• Сумма, на которую все клиенты сделали заказы в этот временной промежуток. Находим как сумму сумм по каждому клиенту:
  total = Sum(total)

Получается такая таблица:

// Дата прихода
dateCl: String,
// Дата заказа
dateOr: String,
// Число клиентов в когорте
clientsCount: Int,
// Число заказов, которые сделали все клиенты этой когорты в данный временной промежуток
ordersCount: Int,
// Сумма, на которую все клиенты этой когорты сделал заказы в данный временной промежуток
total: Int,

Пример кода

Python:

Result = pd.groupby(Preresult, by=['dateOr', 'dateCl'], as_index=False)
Result = Result.agg({ 'total': { 'total': 'sum' }, 'ordersCount': { 'ordersCount': 'sum', 'clientsCount': 'count' } })
Result.columns = Result.columns.droplevel()
Result.columns = ['dateOr', 'dateCl', 'total', 'ordersCount', 'clientsCount'])

MySQL:

SELECT
  dateCl, dateOr,
  COUNT(*) AS clientsCount,
  SUM(ordersCount) AS ordersCount,
  SUM(total) AS total
FROM Preresult
GROUP BY dateCl, dateOr

Шаг 6. Финальное преобразование, Pivot таблица Cohort

Теперь нам остаётся лишь преобразовать структуру таблицы: по строкам должны располагаться значения dateCl, по столбцам — dateOr, а в ячейках — желаемая величина (clientsCount, ordersCount, total или нечто иное).

Что это?

Эту операцию называют Pivot Table (на русском, кажется, нет нормального термина). Простой пример:

 x  y val    yx 1  2
 1  1  5     1  5  -
 1  3  8 ->  2  -  7
 2  2  7     3  8  - 

Из значений x получились столбцы, а из y – строки. Вот была у нас строка x=1, y=3, val=8, а стала ячейка в столбце (x) 1, в строке (y) 3 со значением (val) 8. Или строка x=2, y=2, val=7, стала ячейкой столбце (x) 2, в строке (y) 2 со значением (val) 7.

Ячейки, чья значения не описаны строками из изначальной таблицы, обычно заполняются значением NULL или чем-то логически эквивалентным.

Пример кода

Python:

# в качестве ячеек можно использовать clientsCount, ordersCount, total или другое поле
Cohort = Preresult.pivot(index='dateCl', columns='dateOr', values='Data')
# избавимся от null'ов для красоты
Cohort.fillna(0, inplace=True)

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

Возможные улучшения

1. Переименование колонок
   Сейчас dateOr и dateCl описывают дату независимо, а центр когортной таблицы направлен вправо-вверх. Но если на Шаге 3, 4 или 5 произвести операцию dateOr -= dateCl, то данное поле будет отображать дату с начала существования когорты, а центр таблицы будет направлен влево-вверх, что лучше воспринимается:
   

   *  09 10 11    *   0  1  2
   09  3  2  1    09  3  2  1
   10  -  3  2 -> 10  3  2  -
   11  -  -  3    11  3  -  -

2. Дополнительные параметры
   Что делать, если у Вас в таблице заказов есть другие интересные параметры? Например, тип оплаты или ID филиала? Довольно просто: эти параметры также будут присутствовать в таблицах на Шагах 3,4,5 (Mix, Preresult, Result), и они должны быть добавлены в поля обеих группировок. Затем, на Шаге 6 для каждой возможной комбинации параметров нужно построить свою таблицу Cohort. Например, у Вас 3 филиала и 2 типа оплаты, будет 3*2 = 6 когортных таблиц.
3. Нахождение LTV
   Имея продолжительную статистику, можно рассчитать Life-Time Value когорт пройдясь по строкам таблицы Cohort.

Заключение

Когортная таблица по возвратности — не единственное применение когортного анализа, есть и другие, более наглядные применения. Например, разделение пользователей на две группы по некоторому признаку (когорты) и отображение их характеристик на графике как двух независимых линий.

Полезное по теме: когортный анализ в Google Analytics.

Всем успеха ;)