Привет, на связи Александра Салих из mybi connect.
В ноябре 2023 года был анонсирован выход источника GetCourse в нашем сервисе. Далее мы разработали шаблонную модель и базовый отчет для Power BI, который отображал основные показатели продаж GetCourse, полученные с помощью базовой выгрузки mybi connect. В прошлой статье по GetCourse мы разобрали процесс создания модели данных в Power BI для данного источника и теперь можем двигаться дальше и продолжить разбираться в ее применении…
В данной статье мы решили рассмотреть применение когортного анализа на примере отчета, построенного в Power BI на основе данных из GetCourse.
Теоретические аспекты когортного анализа
Когортный анализ – метод исследования данных, где группы (когорты) объединяются в соответствии с общими характеристиками или временем события для анализа изменений в поведении в течение временных интервалов. Основная цель – оценка эффективности маркетинговых мероприятий, позволяющая выявить источники, обеспечивающие наивысший уровень конверсий и продаж.
Прежде чем мы погрузимся в детали отчета, давайте рассмотрим ключевые термины.
Когорта
Когорта представляет собой группу пользователей, объединенных общими характеристиками или событиями в определенный временной период. Например, когорта может быть сформирована по дате регистрации пользователей, что позволит выделить группы, присоединившиеся к GetCourse в одно и то же время.
Временные интервалы
Одной из ключевых особенностей когортного анализа являются временные интервалы. Они позволяют отслеживать изменения в поведении пользователей на протяжении определенных периодов после их вступления в когорту. Например, мы можем посмотреть сколько пользователей совершили покупки в месяц регистрации и в течение следующих месяцев после регистрации.
Ключевые метрики
Ключевые метрики играют важную роль в когортном анализе, поскольку они предоставляют информацию о том, как изменяется эффективность бизнес-процессов с течением времени.
В качестве основных метрик мы решили использовать следующие меры:
- [Покупатели] – количество уникальных пользователей, которые совершили покупки.
- [Удержание покупателей] – процент уникальных пользователей, которые продолжили совершать покупки в последующих после регистрации временных интервалах.
- [Отток покупателей] – процент уникальных пользователей, которые совершили всего лишь одну покупки после регистрации.
- [Доход от покупок] – сумма полученных средств от покупателей, зарегистрировавшихся в выбранном периоде времени.
- [Покупки] – количество покупок, совершенных покупателями, зарегистрированными в выбранном периоде времени.
Цель и методология когортного анализа в Power BI на примере GetCourse
Как уже сказано выше, источником данных для нашего отчета является GetCourse. Это многофункциональная образовательная система, предназначенная для создания, продажи и управления онлайн-курсами.
При создании отчета на данных GetCourse мы хотели получить ответы на следующие вопросы:
- Какой процент пользователей совершает повторные заказы с течением времени?
- Какой процент пользователей уходит после первой покупки?
- В какой период после регистрации пользователь начинает делать первые покупки?
- Какова динамика покупок пользователей?
- Сколько постоянных и сколько потерянных покупателей?
В данный момент в сервисе mybi connect реализованы базовая и пользовательская выгрузки, с помощью которых пользователи могут получать данные из GetCourse в промежуточную аналитическую БД и далее строить свои собственные отчеты. Мы же решили построить шаблонный для разбора методологии и демонстрации наших данных.
Для реализации когортного анализа нам необходимо получить данные из GetCourse с помощью базовой выгрузки в сервисе mybi connect. Конкретно, нам необходимо получить данные, находящиеся в следующих пяти таблицах – ‘GC Пользователи’, ‘GC Параметры пользователей’, ‘GC Платежи’, ‘SHD Связь календарей’ и ‘SHD Параметры дат’. Данее необходимо произвести нужные преобразования и провести нужные связи. После объединения таблиц, у нас получилась схема, представленная на рисунке 1.
Основные данные для когортного анализа в нашем случае берутся из таблицы ‘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 Параметры дат'[Дата] ) ) )
С помощью создания параметров во вкладке Моделирование, помещаем все нужные метрики в переключатель и получившуюся на основании этих параметрах таблицу заносим в качестве значений в матрицу когортного анализа.
Обзор визуализаций и графиков
В результате у нас получилась матрица, которая позволяет отобразить наши метрики по ‘Параметры строк матрицы когорты'[Тип когорты] и ‘Параметры строк матрицы когорты'[Размер когорты]. Мы также добавили условное форматирование, чтобы было нагляднее отличить большие и маленькие числа.
![[Покупатели], по дате регистрации, по месяцам](https://powerbirussia.ru/wp-content/uploads/2024/02/3.png)
Так же мы видим, что из 1377 зарегистрированных в сентябре, 1367 совершили покупки в нулевом месяце (то есть в месяце регистрации). В следующем месяце (октябре) только 28 из тех пользователей совершили покупки, и в последнем месяце только 3 пользователя что-то купили. Для октября показатели отличаются – покупателей в нулевом месяце меньше (видимо и рекламы было меньше), а вот в “Месяце 1” покупателей больше… Статистически это значит, что регистрации привлеченные в октябре имеют большую “склонность” к отложенным первым покупкам.
Рассмотрим ‘Меры'[Удержание пользователей] за этот же период.
![[Удержание пользователей], по дате регистрации, по месяцам](https://powerbirussia.ru/wp-content/uploads/2024/02/4.png)
Если пользователю отчета необходимо проанализировать данные с более детальной разбивкой, можно просто выбрать “Неделя” в ‘Параметры строк матрицы когорты'[Размер когорты]:
![[Удержание пользователей], по дате регистрации, по неделям](https://powerbirussia.ru/wp-content/uploads/2024/02/5.png)
Для этого мы создали отдельную страницу отчета и ввели следующие меры:
[Новые покупатели]:
Новые покупатели по дате регистрации := 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. На ось Х внесем дату регистрации.
На приведенном выше графике можно проследить как меняется количество новых, постоянных и потерянных пользователей в зависимости от даты регистрации (можно выбрать месяц, неделю или день регистрации).
Заключение
Благодаря данному анализу возможно проследить как влияют маркетинговые мероприятия на поведение пользователей, такое как регистрации, первые покупки и повторные покупки. В нашем примере анализ когорт по дате регистрации помог нам предположить, что в сентябре была проведена какая-то рекламная акция, приведшая к увеличению количества регистраций пользователей. Однако в последующих месяцах мы видим спад регистраций. Соответственно, это вызывает необходмость о принятии решения о возвращении данной акции, либо ввода каких-то дополнительных мер привлечения клиентов.
Также, рассматривая временные интервалы, мы увидели, что большинство пользователей уходят после первой покупки. Вероятно здесь необходимо уже продумать стратегию удержания пользователей, чтобы коэффициент удержания с каждым временным интервалом оставался как можно больше.
Когортный анализ гибок. Нарпимер, вы можете экспериментировать с различными принципами формирования когорт, добавлять новые временные интервалы и рассчитывать метрики, соответствующие вашим уникальным целям и задачам. Это открывает возможности для дальнейших исследований и улучшения стратегий, делая когортный анализ мощным инструментом для принятия бизнес-решений.
