что такое когортный отчет
Что такое когортный анализ и зачем он нужен
Из анализа действий клиентов можно получить много полезных знаний. Это средний чек, пиковое время продаж и многое другое. Но у разных целевых аудиторий эти данные могут быть разными. Поэтому имеет смысл рассматривать их отдельно для каждой группы. Для этого есть достаточно эффективный инструмент маркетинговой аналитики – когортный анализ.
Что такое когортный анализ
Когортный анализ является методом исследования подобных групп. Суть метода – в наблюдении за группами людей с определенными общими признаками.
Клиент может относиться к нескольким когортам. Например, пользователь, который зашел на сайт в марте через Яндекс и купил там продукт X, входит в следующие когорты:
Где и когда применяется анализ когорт
Анализ с исследованием групп людей по одинаковому признаку будет полезен везде, где бизнес зависит от численности клиентов. К онлайн бизнесу это относится особенно. Для него отток пользователей – самое страшное, что может произойти. Под оттоком понимается не только уменьшение уникальных пользователей на сайте. Сюда включается и отказ от рассылок, и снижение проведенного на сайте времени.
Обязательное условие успешности любого интернет-магазина – постоянный приток новых клиентов. Магазины работают так, что текучка пользователей будет постоянной, и зацикливаться только на LTV существующих клиентов нельзя. Именно поэтому анализ поведения новых посетителей для деятельности в интернете является эффективным инструментом аналитики.
Чтобы понимать, в каком русле двигаться, не обойтись без когортного анализа. Мониторинг поведения клиентов может очень сильно изменить положение интернет-магазина.
Ключевые показатели когортного анализа
На показателях для отслеживания стоит остановиться подробнее, так как именно эта настройка отчета является ключевой. Тут выбираются конкретные показатели, по которым будет проводиться анализ:
Так же есть коэффициент удержания клиентов, который выражается в процентном соотношении людей из когорты, вернувшихся на сайт после первого посещения.
Все данные, полученные из когортного анализа, могут быть полезными, если научиться применять их с умом. По динамике изменения сеансов можно понять, в какие дни клиенты чаще всего заходят на сайт. По времени посещения на сайте можно понять, случайно они туда попадают или вполне осознанно.
Пример проведенного когортного анализа
Простейший пример: исследование эффективности подписки на e-mail рассылку. Допустим, на гипотетическую рассылку интернет-магазина можно подписаться тремя разными способами. Это всплывающее окно на сайте самого магазина, ссылка из статьи на стороннем сайте партнёра и конкурс в ВК, для участия в котором нужно подписаться. В феврале через окно на сайте подписалась 1000 человек, конкурс привёл 700 подписчиков, а блог партнёра – 150. Эти три группы – наши когорты.
Проанализируем, какая из групп дольше остаётся подписанной на рассылку. Для этого нам нужны данные открываемости писем за следующие месяцы.
Как видно из таблицы, самые лояльные читатели рассылки – подписавшиеся с сайта партнёра. Они дольше всех читают рассылку, за полгода отписалась только половина. А конкурс в ВК не принёс ничего – все отписались сразу после проведения.
Отсюда можно сделать вывод – ресурсы лучше не тратить на конкурсы в соцсетях. Выгоднее сосредоточиться на продвижении у партнёров.
Заключение
Выбор конкретных когорт зависит от конкретного бизнеса, а также от проблем, которые наблюдаются в нем. Может появиться соблазн применить сразу все параметры, но это сделает воспринимаемость самого анализа невозможной. Кроме этого, некоторые параметры могут быть просто бесполезными в отдельном случае.
Следует с умом расставить приоритеты. Наиболее важными когортами являются те, которые позволяют выполнить оценку каналов привлечения. То есть, параметры, по которым можно определить эффективность способов увеличения посетителей сайта.
Можно перед началом анализа поставить перед собой вопросы, на которые нужно найти ответы. Например, приносят ли доход те клиенты, которые впервые появились на сайте после распродажи. На этом и нужно концентрироваться.
Что такое когортный анализ, когда и как его используют
Специалисты компании AppsFlyer рассказали, как когортный анализ помогает отсеивать лишние данные, чтобы точнее оценить эффективность рекламной кампании или провести А/Б-тест.
Существует заблуждение, что только глобальный анализ с максимальным охватом данных дает полный результат. На самом деле иногда слишком большие объемы данных и выборка могут стать причиной неверных выводов. Чтобы понять — что работает, а что нет — нужно правильно выбрать масштабы, разделить группы данных и сортировать их.
Данные нужно рассматривать в правильном контексте.
Аналитик данных
Почему при работе с данными важно отсеивать лишнее
Одно из первых правил статистики звучит так. Данные определяют не причинно-следственные связи, а только корреляцию между вещами и событиями — то, как они связаны в статистике. Чтобы понять эту корреляцию, нужно отсечь как можно больше «шума», лишних данных. Естественно, отсечь все лишнее невозможно. Но чтобы провести анализ правильно, нужно подобрать фильтры.
Рассмотрим простой пример — задачку о яблоках.
Дано: две семьи из четырех человек каждая, которые покупают одинаковое количество яблок — дюжину. Семья А покупает в воскресенье, семья Б — в среду.
Цель: сравнить привычки потребления двух семей.
Если рассматривать только графики, не группируя данные, выходит, что в субботу потребление яблок было максимальным. Это единственный факт, который можно понять из несгруппированных данных. Сделать другие полезные выводы сложно. Но, если сгруппировать данные по дате покупки яблок, можно увидеть определенные тенденции.
Теперь вернемся от яблок к рекламным технологиям. Суть эффективного анализа в том, что недостаточно просто сравнить ARPU (средний доход на одного пользователя) одного дня с другим. Нужно учитывать «шум» и отфильтровывать его:
При анализе любых KPI нужно сравнивать данные одного порядка, то есть условные яблоки с такими же яблоками, а не с лимонами.
Что такое когортный анализ и когда он нужен
Когда нужно сравнить KPI и оценить успешность стратегии, стоит воспользоваться когортным анализом. Его смысл в том, чтобы анализировать группы пользователей (когорты), которые объединены по общим признакам и времени выполнения действий.
Метод применяют, чтобы:
Как применять когортный анализ
Метод может показаться сложным, но освоить его легко — достаточно разбить процесс анализа на шаги.
Выбор запроса
Сначала определитесь, что хотите узнать. В зависимости от искомой информации, выберите KPI. Например, задачи могут быть такими:
Здесь же нужно решить, как вы будете определять, что кампания успешная. Для эффективного анализа важно понимать: какой вопрос будете задавать и какие показатели помогут найти на него ответ.
Вот простая формула для запросов в когортном анализе: группировать нужно пользователей с общими характеристиками, чтобы сравнить их поведение и показатели в определенное время.
Когортный анализ
Анализируя действия клиентов на сайте или любой другой площадке в Интернете можно выудить много полезных знаний о целевой аудитории. Например, узнать самое популярное время продаж, средний чек, показатель отказов и другое. Однако ключевые метрики у каждой ЦА в определенный период времени могут быть разными. Если попытаться рассмотреть их все вместе, можно легко запутаться или получить ошибочную картину происходящего. Поэтому маркетинговые показатели изучают отдельно для каждой группы методом анализа когорт. Рассмотрим подробнее этот инструмент.
Что такое когортный анализ
Начнем с определения.
Когорта – это сегмент целевой аудитории или группа людей, которых объединяют общие характеристики, опыт, признаки в конкретном временном отрезке.
Опыт, характеристики и прочие атрибуты ЦА могут изменяться довольно существенно с течением времени. Например, пользователь в июне покупает одну продукцию, в июле – другую, а в августе – третью. Точно так же обстоит дело и с услугами или каналами трафика: посетитель в будние дни заходит на сайт с компьютера, а по выходным с мобильного гаджета. С помощью когортного анализа можно исследовать группы с общими признаками и эффективно отслеживать их активность.
Когорты в маркетинге – это довольно неоднородная целевая аудитория: новички сайта или сервиса, постоянные пользователи, временные посетители и другие. Они все разные на первый взгляд, но, используя метод, маркетологи объединяют их в несколько когорт. Например, клиент купивший продукт на сайте компании в ноябре через контекстную рекламу Яндекса, входит в следующие группы:
Таким образом, идея когортного анализа заключается в том, чтобы разделить пользователей на группы по схожим характеристикам или атрибутам для отслеживания их поведения во времени.
Сфера применения
Анализировать группы представителей целевой аудитории по схожим признакам полезно там, где бизнес сильно привязан к количеству клиентов. Например, в онлайне отток пользователей достаточно существенно бьет по показателям доходности бизнеса. Здесь и снижение уникальных посетителей на сайте, отписка от рассылок, рост отказов и спад вовлеченности.
С другой стороны приток новых клиентов говорит об успешности маркетинговой стратегии, даже несмотря на большой пул постоянных покупателей. Поэтому в онлайн-бизнесе анализ когорт, мониторинг поведения пользователей помогает держать руку на пульсе.
Когортный анализ активно используется в следующих сферах:
Ключевые показатели
В любой аналитике есть соблазн начать изучать все метрики ради статистики. В качестве обучения – занятие полезное, но на практике нужно расставлять приоритеты. Какие когорты отнести к ключевым метрикам на конкретный момент времени, а каким уделить меньше внимания? Всё зависит от специфики вашего бизнеса.
Можно выделить ряд универсальных показателей когортного анализа, которые используются практически всегда:
Примеры когортного анализа
Как любая аналитическая система распределяет пользователей по когортам? Рассмотрим простой пример, житель Москвы посетил сайт 25 июля из органической выдачи Яндекса и приобрел продукт «Х» через форму онлайн-заявки. Он распределяется по следующим группам:
Ещё один пример. Нужно исследовать эффективность подписки пользователей на email-рассылку сайта агентства. Допустим, используем три площадки, где размещаем подписную онлайн-форму: конкурс в Инстаграм, партнерский ресурс и подписка на собственном сайте. После активного набора подписчиков в июле, получили следующий результат: с конкурса в Инстаграм получили 850 подписок, с сайта партнера – 120, а с самого ресурса – 1100. Далее мы отслеживаем процент открываемости писем и количество отписок. Например, за три месяца – август, сентябрь, октябрь – пришедшие с Инстаграм отписались сразу после окончания конкурса, больше всего постоянных читателей пришло с партнерского сайта.
Таким образом, мы определили, что тратить бюджет на привлечение подписчиков Инстаграм невыгодно. Лучше сосредоточиться на аудитории сайта партнера.
Резюме
Мы узнали, что когортный анализ – это более точный инструмент для изучения групп ЦА, чем A\B тестирование. Также мы изучили ключевые показатели этого метода аналитики, посмотрели несколько примеров из практики. В любом методе, главное, правильно расставлять приоритеты, ориентируясь на специфику бизнеса. Когортный анализ – не исключение их правил.
Что такое когортный анализ, что нужно для когортного анализа и где его применять
Соберите все данные для сквозной аналитики вместе с OWOX BI
Соберите все данные для сквозной аналитики вместе с OWOX BI
Очевидно, что новые пользователи и постоянные посетители сайта ведут себя по-разному и приносят разный доход. При грамотной маркетинговой стратегии эти различия могут принести большую пользу вашему бизнесу. Но как выявить закономерность и понять, что отличает людей, который впервые посетили ваш сайт месяц назад, от тех, кто сделал это год назад? Без когортного анализа здесь не обойтись.
В этой статье мы рассмотрим возможности когортного анализа и подробно опишем, как его проводить.
бонус для читателей
Скрипт для отчета по когортам в Google Sheets
Содержание
Что такое когортный анализ и где его применять
Когортный анализ — это метод маркетингового анализа, в котором пользователей рассматриваются не как единое множество, а делят на когорты. Чтобы лучше понимать, что такое когортный анализ, разберемся для начала с понятием «когорта».
Когорта в маркетинге — это группа людей, которые совершили определенное действие в заданный отрезок времени. Например, впервые зашли на сайт в декабре или сделали первую покупку 1-7 ноября. Именно временной признак отличает когорту от обычного сегмента.
Суть когортного анализа заключается в том, что вы делите пользователей на группы по определенному признаку и исследуете, как меняется поведение этих групп со временем. Это помогает понять, как ваши маркетинговые усилия влияют на ключевые показатели эффективности: LTV, конверсии, ROI, Retention Rate, CAC и т. д.
OWOX BI поможет объединить данные из разных систем: действия пользователей на сайте, звонки и заказы из CRM, email-рассылки и расходы на рекламу. Вы сможете настроить сквозную аналитику и автоматизировать отчеты любой сложности, в том числе и по когортному анализу.
Рассмотрим несколько примеров, что можно делать с помощью когортного анализа.
1. Точнее оценивать окупаемость рекламы
Далеко не все пользователи, которые впервые посещают ваш сайт, тут же совершают конверсионное действие. Некоторым нужно время, чтобы изучить ваше предложение, взвесить все за и против, сравнить с конкурентами и решиться на покупку, подписку или регистрацию. Это особенно актуально для бизнесов с отложенной конверсией, длинным циклом продаж и дорогими товарами.
Чем длиннее период от первого визита до конверсии, тем выше шанс неправильно оценить эффективность рекламных каналов. Когортный анализ помогает решить эту проблему. Допустим, в ноябре вы запустили платную рекламную кампанию в Google Ads. Через месяц посмотрели на результаты и увидели, что ROI ниже 100%. Первая мысль — отключить показ объявлений.
Однако не торопитесь с выводами. Если вы объедините в когорту пользователей, которых привлекла эта кампания в ноябре, и посмотрите на отчет в январе, то можете увидеть, что ROI значительно вырос. То есть люди «созревали» больше месяца, но в итоге совершили покупку.
2. Находить и удерживать лояльных клиентов
С помощью когортного анализа вы можете узнать, откуда к вам приходят самые лояльные пользователи. Например, создать когорту посетителей, которые впервые залогинились на вашем сайте в период с января по июнь. Затем сегментировать их по каналу привлечения и сравнить коэффициент удержания (Retention Rate) или коэффициент повторных покупок (Repeat Purchase Rates) помесячно в каждом канале.
Очевидно, что каналы, у которых эти показатели выше, заслуживают больше вашего внимания и инвестиций.
Анализируя поведение когорт, вы сможете лучше планировать свои маркетинговые кампании. Например, вы заметили, что на пятом месяце активность пользователей в когорте резко снижается. Можно отправить им для разогрева письмо с персональной скидкой.
3. Прогнозировать и увеличивать LTV
Пожизненная ценность клиента (Lifetime Value) — доход, который компания получила от клиента за все время сотрудничества с ним. Этот показатель сложно рассчитать, пока человек не перестанет быть вашим клиентом. Однако можно рассчитать LTV для отдельно взятой когорты за месяц, например, и сделать прогноз, сколько денег принесет эта когорта за больший период.
Кроме того, вы можете сегментировать когорты по рекламным каналам, чтобы сравнить LTV и расходы на привлечение пользователей (CAC). Это поможет понять период окупаемости каждого канала и перераспределить маркетинговый бюджет.
4. Проводить A/B тестирование
Также когортный анализ можно применять для оценки результатов A/B теста. Например, вы обновили дизайн кнопки на сайте. Чтобы узнать, повлияло ли это на конверсию, вы делаете 2 варианта страницы. Половине пользователей показываете старую кнопку, другой половине — новую. Далее считаете конверсию из просмотров страницы в клик по кнопке.
Однако обычный А/В тест не дает ответа на вопрос, как повлияет новый дизайн на изменение конверсии в долгосрочной перспективе. Для этого можно использовать когортный анализ. Через 3-4 недели после релиза постройте когортный отчет для пользователей, которые впервые попали на сайт через страницу с новой кнопкой. Затем сравните конверсию у этих пользователей и у когорты людей, не видевших новый дизайн. Разница — это и есть результат влияния нового дизайна.
5. Анализировать эффективность мобильных приложений
Отдельное место когортный анализ занимает в аналитике мобильных приложений, где он помогает оценить уровень возвратов (Retention Rate) пользователей в зависимости от версии приложения. Также с помощью анализа когорт можно определить какие источники приводят самых активных юзеров:
Что нужно для когортного анализа
Независимо от того, в какой системе вы собираетесь строить когортный отчет, нужно определиться с четырьмя основными параметрами:
По какому признаку создавать когорты зависит от того, какой показатель вы хотите проанализировать и улучшить. Нужно рассчитать Retention Rate и остановить отток пользователей? Исследуйте поведение людей, которых объединяет время первого визита, регистрации или установки приложения. Хотите определить и увеличить LTV? Формируйте когорты по дате первой покупки.
Это не значит, что признак когорты должен быть один. Вы можете использовать любые условия для создания когорт, в зависимости от ваших потребностей. Например, в когорту можно определить пользователей, которые зарегистрировались в июле и сделали первую покупку в этом же месяце.
Читайте также: как узнать, во сколько обходится привлечение новых клиентов или какие рекламные кампании лучше удерживают старых.
Когортный анализ в Google Analytics
Чтобы создать отчет по когортам в Google Analytics, зайдите в меню «Аудитория» — «Когортный анализ». В самом верху вы увидите настройки:
Здесь можно задать 4 основных параметра, о которых мы писали выше: тип когорты, он же признак, по которому вы ее собираете; размер когорты; анализируемый показатель и отчетный период.
Поскольку «Когортный анализ» в GA находится в режиме бета-тестирования, настройки имеют определенные ограничения:
Под настройками расположен график, который показывает динамику анализируемого показателя для всех пользователей и трех когорт на выбор:
К примеру, на скриншоте выше мы видим, что на третью неделю коэффициент удержания клиентов для всех пользователей составляет 1,64%, а для когорты людей, впервые посетивших сайт 14-20 октября — 2,56%. Под графиком находится таблица с данными по каждой когорте за весь отчетный период. Давайте разберемся, как ее интерпретировать. Для примера мы выбрали:
В итоге у нас получилась такая таблица:
В первом столбце мы видим, сколько всего человек посетили сайт за отчетный период, а также количество пользователей в каждой недельной когорте. Остальные столбцы показывают, как менялся Retention Rate для всех пользователей и для каждой когорты от недели к неделе. Чем выше показатель, тем темнее цвет ячейки.
Из скриншота выше видно, что с 30 сентября по 6 октября на сайт зашли 11 529 новых пользователей. Через неделю только 5,35% из них вернулись на сайт, через две недели — 2,66% и так далее.
В целом когортный анализ в Google Analytics помогает детальнее разобраться в данных. Например, вы видите, что продажи за квартал растут благодаря притоку новых клиентов. Однако, если копнуть глубже и посмотреть на когортный отчет по неделям, можно заметить, что на шестой неделе продажи по всем когортам резко падают. Теперь вы знаете, когда лучше запускать ремаркетинг.
Кстати, с помощью OWOX BI вы можете автоматически загружать в Google Analytics расходы из разных рекламных сервисов, чтобы сравнить ROAS по когортам пользователей.
Узнайте, какие кампании приносят прибыль, а какие не окупаются
Автоматически импортируйте расходы из рекламных сервисов в Google Analytics. Сравнивайте затраты, CPC и ROAS разных кампаний в одном отчете.
Когортный анализ в OWOX BI Smart Data
Недавно отчет по когортному анализу появился и в нашем сервисе для визуализации данных OWOX BI Smart Data. В качестве источника данных сервис использует ваш Google BigQuery проект, в который вы можете выгружать информацию из Google Analytics, рекламных сервисов, CRM и ERP-систем.
Чтобы получить отчет, достаточно написать в поисковой строке слово «когорта», и система выдаст вариант ответа, в котором вы можете выбрать нужную метрику, размер когорт и отчетный период:
Пока что в OWOX BI Smart Data можно создавать когорты только по дате привлечения пользователей, но в ближайшем будущем мы добавим и другие признаки.
Пример когортного отчета в Smart Data:
У OWOX BI есть trial-период, во время которого вы можете бесплатно попробовать когортный анализ и другие возможности сервиса.
Когортные отчеты в других сервисах
Кроме Google Analytics, встроенные когортные отчеты есть во многих рекламных и аналитических сервисах. Например, они встречаются почти во всех системах аналитики для мобильных приложений.
В AppsFlyer более гибкие настройки, чем в GA — в когортный отчет можно добавить сразу несколько фильтров. Также можно задать минимальный размер когорты, чтобы исключить те, в которых слишком мало пользователей.
Когортные отчеты в системах AppMetrica (Яндекс. Метрика для мобильных приложений) и Adjust используются в основном, чтобы отслеживать возврат пользователей. В Adjust можно добавить для анализа второй показатель, например, количество сессий на пользователя:
Пример отчета по когортам в AppMetrica:
Также когортный анализ есть в Mixpanel и Kissmetrics. Это системы аналитики для сайтов и мобильных приложений, которые работают с данными в разрезе пользователей, а не посещений и просмотров страниц. Настройки отчетов в этих сервисах во многом схожи с Google Analytics, но есть и некоторые различия.
В Kissmetrics вы можете сформировать когорту сразу по двум признакам. Например: пользователи, которые посетили сайт и конвертировались в подписку. Еще здесь можно группировать людей не только по времени, как это делают в обычных когортных отчетах, но и по любому другому признаку. К примеру, по сумме покупки, месту проживания, источникам трафика.
Пример отчета по когортам в Kissmetrics:
Когортный анализ в Google Sheets
Если вам не хватает возможностей когортного анализа в Google Analytics, а другие системы аналитики вы не используете, можно самостоятельно построить отчет по когортам в Google Sheets. Это можно сделать четырьмя способами, о которым мы расскажем подробнее чуть ниже: с помощью сводных таблиц, формул, формул с фильтрами или Apps Script.
бонус для читателей
Скрипт для отчета по когортам в Google Sheets
Для построения когортного отчета первыми тремя способами необходимо подготовить и выгрузить в Google Sheets исходные данные. Они должны быть в такой структуре:
Главное, чтобы в первом столбце у вас был месяц/неделя/день формирования когорты, во втором столбце — последующие месяц/неделя/день, а дальше — столбцы с анализируемыми метриками.
Выгрузить данные в Google Sheets вы можете любым удобным способом. В нашем примере мы использовали сырые данные, которые собрали из Google Analytics в Google BigQuery с помощью OWOX BI Pipeline. Затем с помощью SQL-запроса мы выбрали необходимую информацию:
В первой части запроса мы выбрали пользователей в зависимости от того, в какую неделю они зарегистрировались. То есть признак формирования когорты — регистрация, а размер когорты — неделя.
Во второй части выбрали количество покупок пользователя в неделю — это и есть метрика, которую хотим анализировать. Все части запроса мы склеили по User ID пользователя.
Затем мы выгрузили данные в Google Sheets с помощью OWOX BI BigQuery Reports Add-on. Один из плюсов аддона в том, что можно настроить запуск расчетов с нужной вам периодичностью, и отчет будет обновляться автоматически.
1. Когортный анализ с помощью сводной таблицы
Первый и самый простой способ построить когортный отчет — это сводная таблица. Напомним, что у нас в Google Sheets уже есть данные в нужной структуре: столбец А — неделя регистрации, столбец B — недели транзакций, столбец C — покупки. Теперь выделяем нужный диапазон ячеек, открываем вкладку «Данные» и выбираем «Сводная таблица». Справа появится панель с настройками:
В блоке «Строки» выбираем неделю регистрации, в блоке «Столбцы» — неделю транзакции (последующие недели), а в блоке «Значения» — количество покупок. Затем применяем условное форматирование, чтобы сделать отчет более удобным, и получаем такую таблицу:
Если у вас несколько анализируемых метрик, то отчет может выглядеть так:
В сводной таблице можно использовать фильтры, правда, это не очень удобно. Каждый раз, когда вы захотите отфильтровать данные, вам придется открывать настройки таблицы и искать там нужно значение.
2. Когортный анализ с помощью формул
Такой же отчет по когортам можно построить и с помощью 3 простых формул. Для этого создаем в Google Sheets новый лист и применяем формулы:
Обратите внимание, что Example в формулах — это название нашего листа в Google Sheets с исходными данными. Далее нам остается протянуть формулы на весь лист и применить условное форматирование. Отчет готов.
Читайте также: как компания boodmo построила эффективную модель отношений с клиентами, оптимизировала рекламные затраты и увеличила LTV с помощью когортного анализа.
3. Когортный анализ с помощью формул + фильтры
Если вы хотите удобно фильтровать данные в отчете по любому параметру, третью формулу придется немного усложнить. Давайте разберемся, как это сделать.
Для начала нужно дополнить таблицу с исходными данными параметрами, по которым будем фильтровать когортный отчет. В нашем примере это источник и канал трафика, а также название кампании. Вы же можете добавить любые нужные вам параметры, например, геоданные (страна, континент, город), версию мобильного приложения, категорию товаров и т. д.
Для наглядности мы добавили в отчет больше анализируемых метрик, а также изменили размер когорты с недели на месяц. В результате получилась такая таблица с исходными данными:
Затем нужно создать дополнительный лист и вынести на него из таблицы с исходными данными все месяцы регистрации (формирования когорты) с помощью первой формулы =UNIQUE (Example! A: A). Так будет проще обращаться к данным. Назовем этот лист, к примеру, Month.
После добавляем новый лист, на котором будет наш отчет, и создаем два фильтра по Source/Medium и Campaign. Для этого нужно открыть вкладку «Данные» — «Проверка данных». В поле «Диапазон ячеек» указываем адрес ячейки, в которой будет выпадающий список фильтра, в поле «Правила» оставляем условие «Значения из диапазона» и указываем адрес столбца с нужными параметрами из исходной таблицы с данными. Нажимаем «Сохранить».
В результате получается такой фильтр:
Дальше с помощью формулы =Month! A$2 мы подтягиваем в столбец А месяцы регистрации из листа Month. Одно значение должно повторяться столько раз, сколько у вас анализируемых метрик (в нашем примере их три). Именно поэтому мы вынесли месяцы формирования когорты на отдельный лист.
Поскольку параметров в нашем примере всего три (Users, Revenue, Cost) и они одинаковы для всех когорт, прописываем названия вручную, чтобы не забирать их формулами с другого листа.
В ячейке С2 используем формулу =ТРАНСП (UNIQUE (Example2! B2: B)), которая вытягивает месяца транзакций из таблицы с исходными данными и преобразует их в строку:
И наконец на пересечении месяца регистрации, последующего месяца и конкретной метрики вставляем нашу третью формулу:
=ЕСЛИ (И ($B$1=»»,$D$1=»»), СУММЕСЛИМН (Example2!$E:$E, Example2!$A:$A,$A3, Example2!$B:$B, C$2), ЕСЛИ (И ($B$1=»»), СУММЕСЛИМН (Example2!$E:$E, Example2!$A:$A,$A3, Example2!$B:$B, C$2, Example2!$D:$D,$D$1), ЕСЛИ (И ($D$1=»»), СУММЕСЛИМН (Example2!$E:$E, Example2!$A:$A,$A3, Example2!$B:$B, C$2, Example2!$C:$C,$B$1), СУММЕСЛИМН (Example2!$E:$E, Example2!$A:$A,$A3, Example2!$B:$B, C$2, Example2!$C:$C,$B$1, Example2!$D:$D,$D$1))))
Эта формула рассчитывает необходимую метрику в нужной ячейке с учетом фильтров.
Так как у нас 2 фильтра, то в формуле нужно прописать все варианты комбинаций этих фильтров, а именно:
Для каждой метрики формула в ячейке остается неизменной, меняется только столбец, по которому рассчитывается сумма конкретной метрики. Например для метрики Users делаем сумму столбца Example2!$E:$E, для метрики Revenue — столбца Example2!$H:$H, для метрики Cost — столбца Example2!$I:$I.
Если вы хотите, чтобы в отчете было больше двух фильтров, количество условий и размер формулы придется увеличить. Как это сделать, вы можете узнать из вебинара «Когортный анализ для чайников». На нем наш аналитик Оксана Шейдаева подробно рассказала, какая часть формулы за что отвечает, и показала в Google Sheets как строить все отчеты, описанные в этой статье. Заполните форму и мы пришлем вам на email запись вебинара, презентацию и примеры отчетов.
4. Когортный анализ с помощью Google Apps Script
Google Apps Script — язык программирования, который позволяет добавлять функции и обрабатывать данные в Sheets и других сервисах Google. Чтобы построить когортный отчет с помощью Apps Script, данные не нужно переносить в Google Sheets. Таблица с исходными данными создается в Google BigQuery, оттуда скрипт забирает их в отчет.
С помощью скрипта вы можете:
Чтобы подключить Apps Script к Google Sheets, открываем вкладку «Инструменты» — «Редактор скриптов». В открывшемся редакторе выбираем «Ресурсы» — «Дополнительные функции Google Services» и активируем BigQuery API, а также Google Sheets API:
Затем переходим в Панель управления Google API, чтобы активировать эти API и там. После этого в «Редакторе скриптов» вы можете запускать скрипты, которые будут брать данные из Google BigQuery и формировать когортные отчеты в Google Sheets.
Мы написали шаблон скрипта для отчета с одним параметром и двумя фильтрами. Вы можете создать в Google BigQuery таблицу с данными в необходимой структуре, которую мы описали выше, вставить в шаблон скрипта название своего проекта, а также таблицы в BigQuery и получить готовый отчет:
К тому же вы легко сможете модифицировать шаблон скрипта, чтобы построить отчет с нужными вам полями и необходимым количеством метрик. Например, как это сделала компания boodmo. Заполните форму, и мы пришлем шаблон скрипта для когортного анализа вам на email.
бонус для читателей
Скрипт для отчета по когортам в Google Sheets
Выводы
Если сравнивать способы когортного анализа по сложности, то самый простой вариант — это Google Analytics. Однако его возможности и настройки ограничены интерфейсом системы. Второй по легкости способ — это сводные таблицы в Google Sheets. Здесь вы можете формировать когорты по любым параметрам, а не только по дате первого посещения, как в GA. Варианты с формулами и Apps Script чуть сложнее, так как требуют дополнительных знаний.
Если вам нужно часто применять фильтры, то самый удобный вариант — создать отчет с помощью формул, тогда фильтры будут в выпадающем списке. Да, фильтры можно использовать во всех отчетах Google Sheets, однако в сводной таблице за ними нужно идти в настройки таблицы. В способе с Apps Script значение для фильтра нужно вписать в ячейку руками. Если вы при этом ошибетесь, то SQL-запрос вернет вам в качестве ответа ноль.
Мы подготовили сравнительную таблицу с плюсами и минусами всех способов когортного анализа, описанных в этой статье:
