Mobile App Analytics
MySQL
Python
System Analysis and Design
Web analytics
November 2017 11

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

Пример когортного отчёта со значениями LTV


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


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


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

+3
4.4k 34
Comments 4
Similar posts
Top of the day