Как сделать когортный анализ в excel

Блог компании Oh My Stats. Разрабатываем одноименный продукт, промываем мозги маркетологам, делимся опытом

Как делать когортный анализ в Экселе

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

В апреле мы проводим курс «Практический », на котором разберем все возможности когортного анализа и 100500 других практических приемов привлечения, удержания и возвращения клиентов. Записывайтесь, цена скоро поднимется.

Когортный анализ. Сколько пользователей к вам вернулось?

Как часто вы натыкались на вкладку «Когортный анализ» в Google Analytics? Использовали его или проходили мимо. В любом случае вы понимаете, что в этом отчете можно увидеть возвращаемость пользователей по какому-либо временному признаку (дата первого посещения сайта, дата первой транзакции или любого другого события). Но что делать, если вы используете другие системы, в интерфейсе которых такого отчета нет? Я расскажу о том, как его можно собрать в MS Excel.

Основные понятия

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

Когорта — это группа лиц, объединенных общим признаком и датой совершения действия.

Соответственно, метод исследования этих когорт называют когортным анализом.

Постановка задачи

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

Что для этого нужно

  • Дата оформления заявок
  • Дата первой оформленной заявки
  • Уникальный номер пользователя (в данном случае это номер телефона или e-mail)
  • Столбец с единицами (засчитывается факт отправленной заявки, который будет суммироваться для отображения результата)

Реализация

Итак, раз мы будем смотреть возвращаемость по месяцам, нам понадобится довольно большой период, я возьму 12 месяцев. Вот, что из себя представляет моя табличка с данными:

Система, из которой я сделала выгрузку заявок, не предоставляет данные по дате первой, поэтому, чтобы найти эту дату, в столбце B я использовала следующую формулу:

Функция ЕСЛИ проверяет каждую ячейку массива из столбца Field phone на предмет равенства текущему телефону (Field phone). Если это так, то выдается соответствующее ему значение из столбца Date. В противном случае – логическое значение ЛОЖЬ (FALSE).

Таким образом внешняя функция МИН выбирает минимальное не из всех дат, а только из тех, где был использован текущий телефон, т.к. ЛОЖЬ функцией МИН игнорируется.

Не забудьте поставить курсор в формулу и нажать Ctrl + Shift + Enter вместо Enter! Это сочетание клавиш обозначит формулу, как формулу массива. Далее, ячейку можно растянуть по всем ячейкам вниз.

Составление сводной таблицы

Итак, все нужные столбцы мы получили, выделяем нашу таблицу и составляем сводную.

Раскидываем поля таким образом:

Сводная таблица

Поля Годы и Месяцы собрались автоматически.

После небольших разукрашиваний и доработок получаем итоговый вид:

Когортный анализ

Справа я отразила долю повторных заявок от всех заявок, которые были оформлены в соответствующем месяце.

Как читать отчет?

Очень просто. Смотрим по столбцу Первая заявка. Выбираем, например, апрель. В апреле 1432 пользователя первый раз оформили заявку, 19 из них вернулось и повторно оформило заявку в мае, 7 из них повторно в октябре и так далее.

Подходя к концу

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

Ссылка на основную публикацию