Когортный анализ для GetCourse в Power BI

354
Когортный анализ для GetCourse в Power BI

Когортный анализ для GetCourse в Power BIПривет, на связи Александра Салих из mybi connect.

В ноябре 2023 года был анонсирован выход источника GetCourse в нашем сервисе. Далее мы разработали шаблонную модель и базовый отчет для Power BI, который отображал основные показатели продаж GetCourse, полученные с помощью базовой выгрузки mybi connect. В прошлой статье по GetCourse мы разобрали процесс создания модели данных в Power BI для данного источника и теперь можем двигаться дальше и продолжить разбираться в ее применении…

В данной статье мы решили рассмотреть применение когортного анализа на примере отчета, построенного в Power BI на основе данных из GetCourse.

Теоретические аспекты когортного анализа

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

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

Когорта

Когорта представляет собой группу пользователей, объединенных общими характеристиками или событиями в определенный временной период. Например, когорта может быть сформирована по дате регистрации пользователей, что позволит выделить группы, присоединившиеся к GetCourse в одно и то же время.

Временные интервалы

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

Ключевые метрики

Ключевые метрики играют важную роль в когортном анализе, поскольку они предоставляют информацию о том, как изменяется эффективность бизнес-процессов с течением времени.

В качестве основных метрик мы решили использовать следующие меры:

  1. [Покупатели] — количество уникальных пользователей, которые совершили покупки.
  2. [Удержание покупателей] — процент уникальных пользователей, которые продолжили совершать покупки в последующих после регистрации временных интервалах.
  3. [Отток покупателей] — процент уникальных пользователей, которые совершили всего лишь одну покупки после регистрации.
  4. [Доход от покупок] — сумма полученных средств от покупателей, зарегистрировавшихся в выбранном периоде времени.
  5. [Покупки] — количество покупок, совершенных покупателями, зарегистрированными в выбранном периоде времени.

Цель и методология когортного анализа в Power BI на примере GetCourse

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

При создании отчета на данных GetCourse мы хотели получить ответы на следующие вопросы:

  1. Какой процент пользователей совершает повторные заказы с течением времени?
  2. Какой процент пользователей уходит после первой покупки?
  3. В какой период после регистрации пользователь начинает делать первые покупки?
  4. Какова динамика покупок пользователей?
  5. Сколько постоянных и сколько потерянных покупателей?

В данный момент в сервисе mybi connect реализованы базовая и пользовательская выгрузки, с помощью которых пользователи могут получать данные из GetCourse в промежуточную аналитическую БД и далее строить свои собственные отчеты. Мы же решили построить шаблонный для разбора методологии и демонстрации наших данных.

Для реализации когортного анализа нам необходимо получить данные из GetCourse с помощью базовой выгрузки в сервисе mybi connect. Конкретно, нам необходимо получить данные, находящиеся в следующих пяти таблицах — ‘GC Пользователи’, ‘GC Параметры пользователей’, ‘GC Платежи’, ‘SHD Связь календарей’ и ‘SHD Параметры дат’. Данее необходимо произвести нужные преобразования и провести нужные связи. После объединения таблиц, у нас получилась схема, представленная на рисунке 1.
Когортный анализ для GetCourse в Power BI
Схема объединения таблиц базовой выгрузки из GetCourse для когортного анализа

Основные данные для когортного анализа в нашем случае берутся из таблицы ‘GC Платежи’. Она содержит необходимые сведения, такие как ‘GC Платежи'[Идентификатор пользователя], ‘GC Платежи'[Идентификатор платежа], ‘GC Платежи'[Дата оплаты], ‘GC Платежи'[Получено] и другие. 

В нашем случае нам были интересны две группы пользователей (когорты) — пользователи, объединенные:

  • по дате первой покупки;
  • по дате регистрации 

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

Когорты по дате регистрации формируются на основе времени регистрации пользователей и предоставляют информацию о долгосрочной активности и удержании клиентов с момента регистрации.

Дату первой покупки вычислить несложно, для этого просто нашли минимальную ‘GC Платежи'[Дата оплаты] для каждого пользователя. Но данных по регистрации пользователей в таблице ‘GC Платежи’ не хватало. Способ соединения таблиц не давал получить ‘GC Пользователи'[Дата регистрации] с помощью функции RELATED. В результате мы решили использовать функцию LOOKUPVALUE, так как таблица ‘GC Платежи’ содержит уникальные ‘GC Платежи'[Идентификатор пользователя], благодаря которым мы сможем найти соответствующую ‘GC Пользователи'[Дата регистрации]. Был создан следующий вычисляемый столбец:

Дата регистрации пользователя =
LOOKUPVALUE (
    'GC Пользователи'[Дата регистрации],
    'GC Параметры пользователей'[Идентификатор пользователя],
__'GC Платежи'[Идентификатор пользователя]
)

В результате для каждого пользователя в таблице ‘GC Платежи’ мы получили ‘GC Платежи'[Дата регистрации пользователя] и ‘GC Платежи'[Дата первой покупки]. Будем принимать их за тип когорты, который пользователь сможет выбрать с помощью переключателя из таблицы ‘Параметры строк матрицы когорты'[Тип когорты]. Это своеобразные точки отсчета, начиная с которых мы будем отслеживать покупки пользователей по временным интервалам.

Таблица ‘GC Платежи’ связана с таблицей ‘SHD Параметры дат’ активной связью через ‘GC Платежи'[Идентификатор даты]. Поэтому столбец ‘GC Платежи'[Дата оплаты] связан с нашим календарем. Но так как нас интересует еще и ‘GC Платежи'[Дата регистрации пользователя], то нам необходимо соединить ее с таблицей ‘SHD Параметры дат'[Дата], и в этом случае связь будет неактивной. Но мы можем активировать ее в наших вычислениях.

Далее в статье мы не будем рассматривать вариант объединения пользователей по ‘GC Платежи'[Дата первой покупки], а остановимся подробнее только на пользователях по ‘GC Платежи'[Дата регистрации пользователя], так как принцип построения отчета для этих двух групп схож. Отметим только, что необходимо отладить выбор той или иной меры в зависимости от выбранного ‘Параметры строк матрицы когорты'[Тип когорты]. Для этого мы отдельно рассчитали все меры по ‘GC Платежи'[Дата первой покупки] и ‘GC Платежи'[Дата регистрации пользователя].

Следующим действием нам было необходимо определить наши временные интервалы, точнее задать параметр ‘Параметры строк матрицы когорты'[Размер когорты]. В нашем примере мы решили брать такие размеры как день, неделя и месяц. То есть соответственно количество дней/недель/месяцев от выбранной точки отсчета. Для этого мы вычислили разницу между ‘GC Платежи'[Дата регистрации пользователя] и ‘GC Платежи'[Дата оплаты], и рассчитали ее соответственно в днях/неделях/месяцах. Для большей наглядности мы оформили результат вычисления в формате “День 0”, “Неделя 0”, “Месяц 0”. Приведем пример расчета в вычисляемом столбце:

Дней между регистрацией и оплатой =
FORMAT (
    DATEDIFF (
        'GC Платежи'[Дата регистрации пользователя],
        'GC Платежи'[Дата оплаты],
        DAY
    ),
    "День 0"
)

Мы также добавили переключатель для параметра ‘Параметры строк матрицы когорты'[Размер когорты], чтобы пользователь мог выбирать нужный ему интервал. Исходя из ‘Параметры строк матрицы когорты'[Тип когорты] и ‘Параметры строк матрицы когорты'[Размер когорты], у нас сформировался столбец ‘Параметры столбца матрицы когорты'[Дата оплаты], который отображает необходимый временной интервал в выбранной размерности.

Теперь у нас появились нужные нам строки (‘Параметры строк матрицы когорты'[Тип когорты]) и столбцы (‘Параметры столбца матрицы когорты'[Дата оплаты]), чтобы построить матрицу для когортного анализа. Не хватало только метрик, по которым можно проводить анализ. 

Приведем примеры вычислений описанных выше мер:

Покупатели по дате регистрации =
CALCULATE (
    DISTINCTCOUNT ( 'GC Платежи'[Идентификатор пользователя] ),
    FILTER (
        'GC Платежи',
        'GC Платежи'[Дата регистрации пользователя] >= 
    MIN ( 'SHD Параметры дат'[Дата] )
            && 'GC Платежи'[Дата регистрации пользователя] <= 
    MAX ( 'SHD Параметры дат'[Дата] )
    )
)

В данной мере нам необходимо было учитывать выбранным интервал нашего календаря как в строках матрицы (‘Параметры строк матрицы когорты'[Тип когорты]), так и в столбцах матрицы (‘Параметры столбца матрицы когорты'[Дата оплаты]).

Удержание покупателей по дате регистрации =
VAR CurrentMonth =
    SELECTEDVALUE ( 'GC Платежи'[Месяц регистрации] )
VAR CurrentWeek =
    SELECTEDVALUE ( 'GC Платежи'[Неделя года регистрации] )
VAR CurrentDay =
    SELECTEDVALUE ( 'GC Платежи'[Дата регистрации пользователя] )
VAR CohortSize =
    SELECTEDVALUE ( 'Параметры строк матрицы когорты'[Размер когорты] )
VAR NewCustomersPerCohort =
    CALCULATE (
        COUNTROWS ( VALUES ( 'GC Платежи'[Идентификатор пользователя] ) ),
        ALL ( 'GC Платежи' ),
        SWITCH (
            CohortSize,
            "Неделя", 'GC Платежи'[Неделя года регистрации] = CurrentWeek,
            "День", 'GC Платежи'[Дата регистрации пользователя] = CurrentDay,
            "Месяц", 'GC Платежи'[Месяц регистрации] = CurrentMonth
        )
    )

VAR RetentionRate =
    DIVIDE (
        'Меры когортного анализа'[Покупатели по дате регистрации],
        NewCustomersPerCohort
    )
RETURN
    RetentionRate

[Удержание покупателей] рассчитывается как процент пользователей, совершивших покупки от общего числа зарегистрированных пользователей. При этом нам необходимо учитывать ‘Параметры строк матрицы когорты'[Размер когорты] и период оплаты, выбранный в нашем календаре. Необходимо настроить процентный формат для данной меры.

Отток покупателей по дате регистрации :=
IF (
    ISBLANK ( 'Меры'[Удержание покупателей по дате регистрации] ),
    BLANK (),
    1 - 'Меры'[Удержание покупателей по дате регистрации]
)

[Отток покупателей] рассчитывается как обратная величина от [Удержание покупателей].

Расчет [Дохода от покупок] и [Покупки] довольно прост и строится по тому же принципу, по которому мы считали [Покупатели].

Доход от покупок по дате регистрации =
CALCULATE (
    'GC Платежи'[Платежи: Получено],
    FILTER (
        'GC Платежи',
        'GC Платежи'[Дата регистрации пользователя] >= 
    MIN ( 'SHD Параметры дат'[Дата] )
            && 'GC Платежи'[Дата регистрации пользователя] <= 
    MAX ( 'SHD Параметры дат'[Дата] )
    )
)

[Покупки]:

Покупки по дате регистрации =
CALCULATE (
    DISTINCTCOUNT ( 'GC Платежи'[Идентификатор платежа] ),
    FILTER (
        'GC Платежи',
        'GC Платежи'[Дата регистрации пользователя] >= 
    MIN ( 'SHD Параметры дат'[Дата] )
            && 'GC Платежи'[Дата регистрации пользователя] <= 
    MAX ( 'SHD Параметры дат'[Дата] )
    )
)

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

Когортный анализ для GetCourse в Power BI
Добавление метрик в переключатель с помощью вкладки Моделирование

Обзор визуализаций и графиков

В результате у нас получилась матрица, которая позволяет отобразить наши метрики по ‘Параметры строк матрицы когорты'[Тип когорты] и ‘Параметры строк матрицы когорты'[Размер когорты]. Мы также добавили условное форматирование, чтобы было нагляднее отличить большие и маленькие числа.

Когортный анализ для GetCourse в Power BI
[Покупатели], по дате регистрации, по месяцам
Какие выводы мы можем сделать из этого отчета? Мы видим, что в сентябре зарегистрировалось гораздо большее количество пользователей (1377), чем в октябре (355) и ноябре (190). Если связать это с маркетинговыми усилиями, которые были приложены, можно проследить, что те акции, которые были проведены в сентябре, были эффективнее, чем в других месяцах.

Так же мы видим, что из 1377 зарегистрированных в сентябре, 1367 совершили покупки в нулевом месяце (то есть в месяце регистрации). В следующем месяце (октябре) только 28 из тех пользователей совершили покупки, и в последнем месяце только 3 пользователя что-то купили. Для октября показатели отличаются — покупателей в нулевом месяце меньше (видимо и рекламы было меньше), а вот в «Месяце 1» покупателей больше… Статистически это значит, что регистрации привлеченные в октябре имеют большую «склонность» к отложенным первым покупкам.

Рассмотрим ‘Меры'[Удержание пользователей] за этот же период.

Когортный анализ для GetCourse в Power BI
[Удержание пользователей], по дате регистрации, по месяцам
Если мы разделим количество покупателей в первом месяце (1367) на общее количество зарегистрированных в сентябре (1377), то получим как раз 99,3%. Соответственно, данная метрика показывает, что большинство покупок совершается именно в нулевом месяце, а далее очень малый процент пользователей совершает покупки.

Если пользователю отчета необходимо проанализировать данные с более детальной разбивкой, можно просто выбрать “Неделя” в ‘Параметры строк матрицы когорты'[Размер когорты]:

Когортный анализ для GetCourse в Power BI
[Удержание пользователей], по дате регистрации, по неделям
Также мы посчитали, что полезным будет сравнить, сколько приходит новых пользователей, сколько из них стали постоянными покупателями и сколько — потерянными. И все это по нашей когорте (в данном случае — по дате регистрации).

Для этого мы создали отдельную страницу отчета и ввели следующие меры:

[Новые покупатели]:

Новые покупатели по дате регистрации :=
CALCULATE (
    DISTINCTCOUNT ( 'GC Платежи'[Идентификатор пользователя] ),
    USERELATIONSHIP ( 'GC Платежи'[Дата регистрации пользователя],
__'SHD Параметры дат'[Дата] )
)

Как раз в случае этой меры мы активируем связь между ‘GC Платежи'[Дата регистрации пользователя] и таблицей ‘SHD Параметры дат’. В результате выводятся все уникальные пользователи по дате регистрации.

[Постоянные покупатели]:
Постоянные покупатели по дате регистрации =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'GC Платежи'[Идентификатор пользователя] ),
        'GC Платежи'[Количество платежей] > 1,
        FILTER (
            'GC Платежи',
            'GC Платежи'[Дата регистрации пользователя] >= 
        MIN ( 'SHD Параметры дат'[Дата] )
                && 'GC Платежи'[Дата регистрации пользователя] <= 
        MAX ( 'SHD Параметры дат'[Дата] )
        )
    )
)

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

Столбец ‘GC Платежи'[Количество платежей] рассчитывается следующим образом:

Количество платежей =
CALCULATE (
    COUNTROWS ( 'GC Платежи' ),
    ALLEXCEPT ( 'GC Платежи', 'GC Платежи'[Идентификатор пользователя] )
)

И последняя мера, [Потерянные покупатели]:

Потерянные покупатели по дате регистрации =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'GC Платежи'[Идентификатор пользователя] ),
        'GC Платежи'[Количество платежей] = 1,
        FILTER (
            'GC Платежи',
            'GC Платежи'[Дата регистрации пользователя] >= 
        MIN ( 'SHD Параметры дат'[Дата] )
                && 'GC Платежи'[Дата регистрации пользователя] <= 
        MAX ( 'SHD Параметры дат'[Дата] )
        )
    )
)

Условие отличается только тем, что мы рассматриваем пользователей с одним платежем.

Добавим полученные меры на столбчатый график на ось Y. На ось Х внесем дату регистрации.

Когортный анализ для GetCourse в Power BI
Новые, постоянные и потерянные пользователи по дате регистрации

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

Заключение

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

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

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