Когортный анализ в Google Analytics и Google Sheets — подробный гайд

2
551
Материалы для скачивания

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

Содержание

Что такое когортный анализ и где его применять

Когорта в маркетинге — это группа людей, которые совершили определенное действие в заданный отрезок времени. Например, впервые зашли на сайт в декабре или сделали первую покупку 1-7 ноября. Именно временной признак отличает когорту от обычного сегмента.

Суть когортного анализа заключается в том, что вы делите пользователей на группы по определенному признаку и исследуете, как меняется поведение этих групп со временем. Это помогает понять, как ваши маркетинговые усилия влияют на ключевые показатели эффективности: LTV, конверсии, ROI, Retention Rate, CAC и т.д.

Рассмотрим несколько примеров, что можно делать с помощью когортного анализа.

1. Точнее оценивать окупаемость рекламы

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

Чем длиннее период от первого визита до конверсии, тем выше шанс неправильно оценить эффективность рекламных каналов. Когортный анализ помогает решить эту проблему. Допустим, в ноябре вы запустили платную рекламную кампанию в Google Ads. Через месяц посмотрели на результаты и увидели, что ROI ниже 100%. Первая мысль — отключить показ объявлений. Однако не торопитесь с выводами. Если вы объедините в когорту пользователей, которых привлекла эта кампания в ноябре, и посмотрите на отчет в январе, то можете увидеть, что ROI значительно вырос. То есть люди «созревали» больше месяца, но в итоге совершили покупку.

2. Находить и удерживать лояльных клиентов

С помощью когортного анализа вы можете узнать, откуда к вам приходят самые лояльные пользователи. Например, создать когорту посетителей, которые впервые залогинились на вашем сайте в период с января по июнь. Затем сегментировать их по каналу привлечения и сравнить коэффициент удержания (Retention Rate) или коэффициент повторных покупок (Repeat Purchase Rates) помесячно в каждом канале. Очевидно, что каналы, у которых эти показатели выше, заслуживают больше вашего внимания и инвестиций.

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

3. Прогнозировать и увеличивать LTV

Пожизненная ценность клиента (Lifetime Value) — доход, который компания получила от клиента за все время сотрудничества с ним. Этот показатель сложно рассчитать, пока человек не перестанет быть вашим клиентом. Однако можно рассчитать LTV для отдельно взятой когорты за месяц, например, и сделать прогноз, сколько денег принесет эта когорта за больший период.

Кроме того, вы можете сегментировать когорты по рекламным каналам, чтобы сравнить LTV и расходы на привлечение пользователей (CAC). Это поможет понять период окупаемости каждого канала и перераспределить маркетинговый бюджет.

Формула Inbox placement rate

4. Проводить A/B тестирование

Также когортный анализ можно применять для оценки результатов A/B теста. Например, вы обновили дизайн кнопки на сайте. Чтобы узнать, повлияло ли это на конверсию, вы делаете 2 варианта страницы. Половине пользователей показываете старую кнопку, другой половине — новую. Далее считаете конверсию из просмотров страницы в клик по кнопке.

Однако обычный А/В тест не дает ответа на вопрос, как повлияет новый дизайн на изменение конверсии в долгосрочной перспективе. Для этого можно использовать когортный анализ. Через 3-4 недели после релиза постройте когортный отчет для пользователей, которые впервые попали на сайт через страницу с новой кнопкой. Затем сравните конверсию у этих пользователей и у когорты людей, не видевших новый дизайн. Разница — это и есть результат влияния нового дизайна.

Формула Inbox placement rate

5. Анализировать эффективность мобильных приложений

Отдельное место когортный анализ занимает в аналитике мобильных приложений, где он помогает оценить уровень возвратов (Retention Rate) пользователей в зависимости от версии приложения. Также с помощью анализа когорт можно определить какие источники приводят самых активных юзеров:

Что нужно для когортного анализа

Независимо от того, в какой системе вы собираетесь строить когортный отчет, нужно определиться с четырьмя основными параметрами:

  • Признак, по которому формируется когорта — действие, которое объединяет людей в когорте: первый визит на сайт, первая покупка, регистрация и т.п.
  • Размер когорты — временной интервал для когорты: день, неделя, месяц.
  • Отчетный период — время, в течение которого вы будете исследовать поведение когорт.
  • Ключевой показатель, который вы будете анализировать: ROI, Retention Rate, LTV и т.д.

По какому признаку создавать когорты зависит от того, какой показатель вы хотите проанализировать и улучшить. Нужно рассчитать Retention Rate и остановить отток пользователей? Исследуйте поведение людей, которых объединяет время первого визита, регистрации или установки приложения. Хотите определить и увеличить LTV? Формируйте когорты по дате первой покупки.

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

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

Чтобы создать отчет по когортам в Google Analytics, зайдите в меню «Аудитория» — «Когортный анализ». В самом верху вы увидите настройки:

Создание отчета по когортному анализу в Google Analytics

Здесь можно задать 4 основных параметра, о которых мы писали выше: тип когорты, он же признак, по которому вы ее собираете; размер когорты; анализируемый показатель и отчетный период.

Поскольку «Когортный анализ» в GA находится в режиме бета-тестирования, настройки имеют определенные ограничения:

  • Вы можете создавать когорты только по дате первого посещения.
  • В одном когортном отчете можно анализировать только один показатель. Всего же в настройках доступно 14 показателей.
  • В поле «Размер когорты» доступны три варианта: день, неделя, месяц.
  • Задать собственный отчетный период не получится, так как диапазон дат ограничен и зависит от размера когорты. К примеру, максимальный отчетный период для когорт, созданных по дням — 30 дней, по неделям — 12 недель, по месяцам — 3 месяца.
  • В стандартных настройках невозможно отфильтровать данные, например, по типу трафика, устройству или другому параметру. Для этого нужно использовать сегменты.

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

Динамика анализируемого показателя для когорт

К примеру, на скриншоте выше мы видим, что на третью неделю коэффициент удержания клиентов для всех пользователей составляет 1,64%, а для когорты людей, впервые посетивших сайт 14-20 октября — 2,56%. Под графиком находится таблица с данными по каждой когорте за весь отчетный период. Давайте разберемся, как ее интерпретировать. Для примера мы выбрали:

  • Показатель — коэффициент удержания клиентов.
  • Размер когорты — по неделям.
  • Диапазон дат — последние 6 недель.

В итоге у нас получилась такая таблица:

Таблица когорт в google analytics

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

Из скриншота выше видно, что с 30 сентября по 6 октября на сайт зашли 11 529 новых пользователей. Через неделю только 5,35% из них вернулись на сайт, через две недели — 2,66% и так далее.

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

Когортные отчеты в других сервисах

Кроме Google Analytics, встроенные когортные отчеты есть во многих рекламных и аналитических сервисах. Например, они встречаются почти во всех системах аналитики для мобильных приложений.

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

Когортные отчеты в системах AppMetrica (Яндекс.Метрика для мобильных приложений) и Adjust используются в основном, чтобы отслеживать возврат пользователей. В Adjust можно добавить для анализа второй показатель, например, количество сессий на пользователя:

Пример отчета по когортам в AppMetrica:

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

Также когортный анализ есть в Mixpanel и Kissmetrics. Это системы аналитики для сайтов и мобильных приложений, которые работают с данными в разрезе пользователей, а не посещений и просмотров страниц. Настройки отчетов в этих сервисах во многом схожи с Google Analytics, но есть и некоторые различия.

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

Пример отчета по когортам в Kissmetrics:

Когортный отчет в OWOX BI Smart Data

Недавно когортный отчет появился и в нашем сервисе для визуализации данных OWOX BI Smart Data. В качестве источника данных сервис использует ваш Google BigQuery проект, в который вы можете выгружать информацию из Google Analytics, рекламных сервисов, CRM и ERP-систем.

Чтобы получить отчет, достаточно написать в поисковой строке слово «когорта», и система выдаст вариант ответа, в котором вы можете выбрать нужную метрику, размер когорт и отчетный период:

Пока что в OWOX BI Smart Data можно создавать когорты только по дате привлечения пользователей, но в ближайшем будущем мы добавим и другие признаки.

Пример когортного отчета в Smart Data:

Кстати, у нас есть 14-дневный Trial-период, во время которого вы можете бесплатно попробовать когортный отчет и другие возможности OWOX BI.

Когортный отчет в Google Sheets

Если вам не хватает возможностей когортного анализа в Google Analytics, а другие системы аналитики вы не используете, можно самостоятельно построить отчет по когортам в Google Sheets. Это можно сделать четырьмя способами, о которым мы расскажем подробнее чуть ниже: с помощью сводных таблиц, формул, формул с фильтрами или Apps Script.

Для построения когортного отчета первыми тремя способами необходимо подготовить и выгрузить в Google Sheets исходные данные. Они должны быть в такой структуре:

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

Выгрузить данные в Google Sheets вы можете любым удобным способом. В нашем примере мы использовали сырые данные, которые собрали из Google Analytics в Google BigQuery с помощью OWOX BI Pipeline. Затем с помощью SQL-запроса мы выбрали необходимую информацию:

	
SELECT
  registration_week,
  transaction_week,
  SUM ( purchases ) AS purchases
FROM (
  SELECT
    IFNULL ( user.id, clientId ) AS userID,
    STRING ( YEAR ( date )) + '-' + RIGHT ( '0' + STRING ( WEEK ( date )), 2 ) AS registration_week
  FROM TABLE_DATE_RANGE ( [projectID:datasetID.session_streaming_], TIMESTAMP ( '2017-08-01' ), CURRENT_TIMESTAMP ())
  WHERE hits.eventInfo.eventCategory = 'registration' )  AS t1
LEFT JOIN EACH (
  SELECT
    IFNULL ( user.id, clientId ) AS userID,
    EXACT_COUNT_DISTINCT ( hits.transaction.transactionId ) AS purchases,
    STRING ( YEAR ( date )) + '-' + RIGHT ( '0' + STRING ( MONTH ( date )), 2 ) AS transaction_week
  FROM TABLE_DATE_RANGE ( [projectID:datasetID.session_streaming_], TIMESTAMP ( '2017-08-01' ), CURRENT_TIMESTAMP())
  WHERE  hits.eCommerceAction.action_type = 'purchase'
  GROUP BY userID, transaction_week) AS t2
ON  t1.userID = t2.userID
WHERE  REPLACE ( registration_week, '-', '' ) <= REPLACE ( transaction_week, '-', '' )
GROUP BY registration_week, transaction_week,
ORDER BY registration_week, transaction_week,
	

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

Во второй части выбрали количество покупок пользователя в неделю — это и есть метрика, которую хотим анализировать. Все части запроса мы склеили по User ID пользователя.

Затем мы выгрузили данные в Google Sheets с помощью OWOX BI BigQuery Reports Add-on. Один из плюсов аддона в том, что можно настроить запуск расчетов с нужной вам периодичностью, и отчет будет обновляться автоматически.

1. Когортный отчет с помощью сводной таблицы

Первый и самый простой способ построить когортный отчет — это сводная таблица. Напомним, что у нас в Google Sheets уже есть данные в нужной структуре: столбец А — неделя регистрации, столбец B — недели транзакций, столбец C — покупки. Теперь выделяем нужный диапазон ячеек, открываем вкладку «Данные» и выбираем «Сводная таблица». Справа появится панель с настройками:

В блоке «Строки» выбираем неделю регистрации, в блоке «Столбцы» — неделю транзакции (последующие недели), а в блоке «Значения» — количество покупок. Затем применяем условное форматирование, чтобы сделать отчет более удобным, и получаем такую таблицу:

Когортный отчет в Google Sheets с помощтю сводной таблице

Если у вас несколько анализируемых метрик, то отчет может выглядеть так:

Когортный анализ в Google Sheets по нескольким метрикам

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

2. Когортный отчет с помощью формул

Такой же отчет по когортам можно построить и с помощью 3 простых формул. Для этого создаем в Google Sheets новый лист и применяем формулы:

  • =UNIQUE(Example!A:A) — эту формулу вставляем в ячейку А2. Она вытягивает в столбец А все недели формирования когорты, которые есть в исходной таблице с данными.
  • =ТРАНСП(unique(Example!B2:B)) — эту формулу используем в ячейке В2. Она вытягивает все недели транзакций из столбца в таблице с исходными данными и преобразует их в строку.
    Пример когортного отчета в Google Sheets с помощью формул
  • =СУММЕСЛИМН(Example!$C:$C,Example!$A:$A,$A3,Example!$B:$B,B$2) — эту формулу вставляем в ячейку B3 на пересечении недели регистрации и недели транзакции. Эта формула суммирует все покупки из столбца С таблицы с исходными данными.

Обратите внимание, что Example в формулах — это название нашего листа в Google Sheets с исходными данными. Далее нам остается протянуть формулы на весь лист и применить условное форматирование. Отчет готов.

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, оттуда скрипт забирает их в отчет.

С помощью скрипта вы можете:

  • Создавать меню на панели инструментов в Google Sheets для самостоятельного запуска расчетов одним кликом.
  • Выполнять запросы к данным в BigQuery и обращаться к результатам этих запросов.
  • Создавать ячейки, которые будут использоваться как фильтры. Параметры в таких ячейках нужно прописывать вручную. То есть в фильтре не будет выпадающего списка, как в отчете, построенном с помощью формул. Вы сами указываете в ячейке значение, по которому хотите фильтровать данные, и это значение используется в SQL-запросе к данным в Google BigQuery.
  • Менять внешний вид отчета: применять условное форматирование, делать необходимые разрывы и отступы, менять местами элементы отчета, переворачивать таблицы и многое другое.

Чтоб подключить Apps Script к Google Sheets, открываем вкладку «Инструменты» — «Редактор скриптов». В открывшемся редакторе выбираем «Ресурсы» — «Дополнительные функции Google Services» и активируем BigQuery API, а также Google Sheets API:

Затем переходим в Панель управления Google API, чтобы активировать эти API и там. После этого в «Редакторе скриптов» вы можете запускать скрипты, которые будут брать данные из Google BigQuery и формировать когортные отчеты в Google Sheets.

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

Отчет по когортам, построенный с помощью Google Apps Script

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

Выводы

Если сравнивать способы когортного анализа по сложности, то самый простой вариант — это Google Analytics. Однако его возможности и настройки ограничены интерфейсом системы. Второй по легкости способ — это сводные таблицы в Google Sheets. Здесь вы можете формировать когорты по любым параметрам, а не только по дате первого посещения, как в GA. Варианты с формулами и Apps Script чуть сложнее, так как требуют дополнительных знаний.

Если вам нужно часто применять фильтры, то самый удобный вариант — создать отчет с помощью формул, тогда фильтры будут в выпадающем списке. Да, фильтры можно использовать во всех отчетах Google Sheets, однако в сводной таблице за ними нужно идти в настройки таблицы. В способе с Apps Script значение для фильтра нужно вписать в ячейку руками. Если вы при этом ошибетесь, то SQL-запрос вернет вам в качестве ответа ноль.

Мы подготовили сравнительную таблицу с плюсами и минусами всех способов когортного анализа, описанных в этой статье:

Google Analytics Сводная таблица в Google Sheets Формулы в Google Sheets Google Sheets + Apps Script
Простота
Возможность добавить фильтры
Удобность фильтров
Можно использовать сегменты

Выбирается в настройках сводной таблицы

Выбирается в выпадающем списке

Нужно писать значение в ячейке вручную
Возможность добавить в отчет несколько метрик
Возможность создать отчет без дополнительных знаний
Возможность менять внешний вид отчета
Условное форматирование отчета
Автоматическое обновление отчета
С помощью OWOX BI BigQuery Reports Add-on

С помощью OWOX BI BigQuery Reports Add-on

Если у вас остались вопросы, задавайте их в комментариях — с радостью ответим!

Вас также могут заинтересовать