• Веб-аналитика
• Аналитика мобильных приложений
• Анализ и проектирование систем
• Python
• MySQL

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

В статье будет описан относительно простой, но полезный алгоритм построения когортой таблицы, а также приведены наброски кода с 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

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

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

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

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)

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.

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

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

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

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

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']

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,

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'])

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

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

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

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

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

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.

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