Как часто вы натыкались на вкладку «Когортный анализ» в Google Analytics? Использовали его или проходили мимо. В любом случае вы понимаете, что в этом отчете можно увидеть возвращаемость пользователей по какому-либо временному признаку (дата первого посещения сайта, дата первой транзакции или любого другого события). Но что делать, если вы используете другие системы, в интерфейсе которых такого отчета нет? Я расскажу о том, как его можно собрать в MS Excel.
Основные понятия
Прежде, чем приступать к разбору когортного анализа, давайте разберем, что такое когорта.
Когорта — это группа лиц, объединенных общим признаком и датой совершения действия.
Соответственно, метод исследования этих когорт называют когортным анализом.
Постановка задачи
Изначально, я использую данные из системы, в которую падают оформленные заявки с нашего сайта. Иногда пользователи возвращаются и оставляют заявки снова спустя некоторое время. Основная задача — понять, сколько пользователей повторно оформили заявки через месяц, два и более после оформления первой.
Что для этого нужно
- Дата оформления заявок
- Дата первой оформленной заявки
- Уникальный номер пользователя (в данном случае это номер телефона или e-mail)
- Столбец с единицами (засчитывается факт отправленной заявки, который будет суммироваться для отображения результата)
Реализация
Итак, раз мы будем смотреть возвращаемость по месяцам, нам понадобится довольно большой период, я возьму 12 месяцев. Вот, что из себя представляет моя табличка с данными:
Система, из которой я сделала выгрузку заявок, не предоставляет данные по дате первой, поэтому, чтобы найти эту дату, в столбце B я использовала следующую формулу:
=МИН(ЕСЛИ($C$2:$C$66665=C2;$A$2:$A$66665))
Функция ЕСЛИ проверяет каждую ячейку массива из столбца Field phone на предмет равенства текущему телефону (Field phone). Если это так, то выдается соответствующее ему значение из столбца Date. В противном случае – логическое значение ЛОЖЬ (FALSE).
Таким образом внешняя функция МИН выбирает минимальное не из всех дат, а только из тех, где был использован текущий телефон, т.к. ЛОЖЬ функцией МИН игнорируется.
Не забудьте поставить курсор в формулу и нажать Ctrl+Shift+Enter вместо Enter! Это сочетание клавиш обозначит формулу, как формулу массива. Далее, ячейку можно растянуть по всем ячейкам вниз.
Составление сводной таблицы
Итак, все нужные столбцы мы получили, выделяем нашу таблицу и составляем сводную.
Раскидываем поля таким образом:
Поля Годы и Месяцы собрались автоматически.
После небольших разукрашиваний и доработок получаем итоговый вид:
Справа я отразила долю повторных заявок от всех заявок, которые были оформлены в соответствующем месяце.
Как читать отчет?
Очень просто. Смотрим по столбцу Первая заявка. Выбираем, например, апрель. В апреле 1432 пользователя первый раз оформили заявку, 19 из них вернулось и повторно оформило заявку в мае, 7 из них повторно в октябре и так далее.
Подходя к концу
Когортный анализ можно использовать для оценки окупаемости рекламных каналов, особенно для бизнеса с отложенной конверсией. В целом, он поможет оценить, какие из ваших решений в прошлом смогли повлиять на повышение конверсии, будь то подключение нового канала трафика или улучшение юзабилити сайта. Анализируйте прошлое, чтобы повышать конверсию в будущем. 🙂
