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

    Пример когортного отчёта со значениями 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. Финальное преобразование, таблица Cohort


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


    Пример кода

    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.


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

    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 4
    • 0
      Для тех кто в теме нового ничего нет. Для тех кто не в теме видимо ничего не понятно что и зачем, а жаль. Развивайте тему, полезная.
      • 0
        Спасибо, учту.
        • 0

          Всё очень даже понятно, и да — присоединяюсь: тема полезная и продолжение будет интересно.

          • 0
            Благодарю за приятный комментарий :)

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