- Что такое когортный анализ в Google Таблицах и зачем он маркетологу
- Какие данные нужны для когортного анализа в Google Таблицах
- Как построить когортную таблицу пошагово
- Шаг 1. Определить когорту каждого клиента
- Шаг 2. Считаем номер периода для каждого заказа
- Шаг 3. Сводная таблица (Pivot table) по когортам
- Тепловая карта: визуализация удержания в Google Таблицах
- Как использовать когортный анализ для оптимизации Яндекс Директа
- Типичные ошибки когортного анализа в Google Таблицах
- Google Таблицы vs DataLens и Excel: что выбрать
- Часто задаваемые вопросы
- Заключение
Когда клиент спрашивает «а сколько людей, пришедших с Директа в прошлом месяце, купили повторно», Excel-отчёт с общими цифрами конверсии не отвечает на этот вопрос. Нужна таблица, где видно поведение каждой группы клиентов отдельно — по месяцам или неделям их появления. Это и есть когортная таблица, и собрать её проще, чем кажется, — без BI-инструментов, прямо в Google Таблицах.
Я работаю с контекстной рекламой и веду проекты, где бюджет клиента распределяется между кампаниями по факту повторных покупок, а не только по первой конверсии. Без когортной разбивки такие решения принимаются на глаз. В этой статье — рабочий алгоритм: какие данные выгружать, какими формулами считать удержание, как раскрасить таблицу тепловой картой и когда Google Таблиц уже не хватает.
Если нужен теоретический разбор метода — метрики удержания, LTV, типы когорт — я подробно разбирал это в статье про когортный анализ и расчёт удержания. Здесь фокус на другом: как физически построить такую таблицу руками, шаг за шагом.
Что такое когортный анализ в Google Таблицах и зачем он маркетологу
Когортный анализ в Google Таблицах — это способ сгруппировать клиентов по дате их первого касания (регистрации, первой покупки, первого клика по рекламе) и построить таблицу, где по строкам идут когорты, а по столбцам — периоды с момента старта. В ячейках — процент клиентов из когорты, которые совершили действие на этом периоде: вернулись на сайт, купили снова, оплатили подписку.
В отличие от обычного отчёта «конверсия за месяц», когортная таблица показывает динамику во времени по каждой группе отдельно. Например, конверсия в повторную покупку у клиентов, пришедших в январе, может быть 24%, а у февральской когорты — уже 31%, потому что в феврале поменялась цепочка ретаргетинга. Без разбивки по когортам эта разница просто растворилась бы в средней цифре.
Для рекламы в Яндекс Директе это особенно важно: когорта клиентов, пришедших по одной кампании, может стабильно возвращаться, а по другой — уходить после первой покупки. Именно на этих данных строится решение, куда доливать бюджет, а какую кампанию сокращать, даже если стоимость первого лида у неё ниже.
🔍 Когорта — это не сегмент
Сегмент делит клиентов по признаку («женщины 25–34», «пришли из РСЯ»). Когорта делит по времени появления. Один клиент может входить и в сегмент, и в когорту одновременно — это разные оси анализа, и путать их не стоит. Про деление базы по признакам я писал в статье про сегментацию клиентов и RFM-анализ.
Какие данные нужны для когортного анализа в Google Таблицах
Минимальный набор — три столбца: идентификатор клиента, дата первого события и дата каждого последующего события того же клиента. Дальше из них таблица сама вычисляет всё остальное. На практике данные приходят из двух источников.
- Яндекс Метрика — если считаем возврат посетителей на сайт без привязки к оплате. Экспортируется через отчёт «Посещения» с ClientID и датами визитов, либо через API — я разбирал выгрузку в статье про автоматизацию через API Яндекс Метрики.
- CRM или база заказов — если считаем повторные покупки, а не просто визиты. Нужны ID клиента, дата первого заказа, дата всех последующих заказов и их сумма — сумма понадобится для LTV.
Если аналитика построена на Google Analytics, а не на Метрике — это отдельный разговор: с 2024 года стандартный доступ к GA для российских проектов ограничен, и я писал, как перестроить трекинг, в статье про Google Analytics в России и переход на альтернативы.
💡 Считайте не визиты, а деньги
Возврат на сайт — слабый сигнал: человек мог зайти проверить цену и уйти. Для решений по бюджету Директа надёжнее когорта по оплатам из CRM, а визиты Метрики — вспомогательный срез, который показывает раннюю динамику до момента покупки.
Как построить когортную таблицу пошагово
Дальше — конкретный алгоритм на живом (но обобщённом, без привязки к реальному проекту) примере: интернет-магазин, 640 новых клиентов за квартал, данные о заказах выгружены из CRM в лист «Заказы» с колонками ClientID, ДатаЗаказа, Сумма.
Шаг 1. Определить когорту каждого клиента
В новом листе для каждого ClientID находим дату первого заказа функцией МИНЕСЛИ (или MINIFS в английской локали):
=МИНЕСЛИ(Заказы!B:B; Заказы!A:A; A2)
Далее месяц когорты: =ТЕКСТ(B2; «ММММ ГГГГ»)
Получаем таблицу «клиент → дата первого заказа → месяц когорты». Это основа, от которой строится всё остальное.
Шаг 2. Считаем номер периода для каждого заказа
Для каждой строки в листе «Заказы» вычисляем разницу между датой заказа и датой первого заказа этого клиента — в месяцах:
=РАЗНДАТ(ВПР(A2; Когорты!A:B; 2; 0); B2; «M»)
Здесь 0 — заказ в месяц первого касания (месяц 0), 1 — месяц спустя, и так далее. Эта колонка — ключ к сводной таблице на следующем шаге.
Шаг 3. Сводная таблица (Pivot table) по когортам
Выделяем данные и строим сводную таблицу: строки — «Месяц когорты», столбцы — «Номер периода», значения — количество уникальных ClientID. Google Таблицы считают это функцией СЧЁТЗ с уникальными значениями через дополнительный столбец-флаг «первое появление клиента в этом периоде», иначе повторные заказы одного клиента задвоят счётчик.
Результат — таблица вида:
| Когорта | Месяц 0 | Месяц 1 | Месяц 2 |
|---|---|---|---|
| Январь | 100% | 24% | 17% |
| Февраль | 100% | 31% | 19% |
| Март | 100% | 22% | — |
Проценты — это количество активных клиентов когорты в периоде, поделённое на исходный размер когорты. Пустая ячейка в марте за месяц 2 означает, что этот период ещё не наступил — заполнять её нулём было бы ошибкой, это исказит средние.
Тепловая карта: визуализация удержания в Google Таблицах
Сырые проценты в таблице читаются медленно — глаз не улавливает динамику по строкам. Решение — условное форматирование (Формат → Условное форматирование → Цветовая шкала) на диапазон с процентами: тёмно-зелёный для высоких значений, светло-жёлтый или белый для низких. Тогда падение удержания видно как визуальный градиент, без чтения цифр.
На такой карте сразу заметно, если какая-то когорта резко «проваливается» — например, из-за технической ошибки на сайте в конкретном месяце или из-за смены посадочной страницы для рекламных кампаний. Если проблема совпадает по времени с изменением на конкретной странице, полезно свериться с отчётом по посадочным страницам в Яндекс Директе — часто причина падения удержания лежит именно там, а не в качестве самого трафика. Пример когортной таблицы с тепловой картой — точные значения на скрине иллюстративны
Как использовать когортный анализ для оптимизации Яндекс Директа
Сама таблица — это только полдела. Ценность появляется, когда когорту разбивают дополнительно по источнику трафика: добавляем в исходные данные UTM-метку кампании и строим ту же таблицу отдельно для каждой кампании или группы объявлений.
На практике часто получается парадоксальная картина: кампания А даёт лид за 890 ₽, кампания Б — за 1 340 ₽, и по обычному отчёту А выглядит выгоднее. Но когортная разбивка показывает, что клиенты из А удерживаются на 9–11% на третьем месяце, а из Б — на 27–29%. С учётом повторных покупок кампания Б за квартал приносит больше выручки на каждый вложенный рубль, хотя стоимость первого лида у неё выше.
Это тот же принцип, что лежит в основе ассоциированных конверсий и сложных цепочек касаний — если вы ещё не смотрели полный путь клиента до покупки, полезно свериться с отчётом по ассоциированным конверсиям в Метрике и с разбором многоканальных последовательностей — они показывают путь до первой покупки, а когортная таблица — что происходит после неё.
Проставить UTM-метку кампании как отдельный столбец в исходных данных клиентов
Строить сводную таблицу когорт отдельно для каждой кампании, а не только суммарно
Сравнивать удержание, а не только CPL — дешёвый лид может быть невыгодным в перспективе трёх месяцев
Перепроверять выводы минимум через 2–3 месяца — на короткой выборке когорта может обмануть
Типичные ошибки когортного анализа в Google Таблицах
❌ Заполнять «未наступившие» периоды нулями
Мартовская когорта на третий месяц физически ещё не могла проявить активность. Ноль в этой ячейке искажает средние показатели по всей таблице — ставьте прочерк или оставляйте ячейку пустой.
❌ Считать визиты вместо оплат
Если бизнес-решение касается бюджета Директа, а когорта построена только по визитам Метрики без привязки к оплатам из CRM, выводы будут ошибочными — визит не равен деньгам.
❌ Делать выводы по когорте младше одного полного цикла продаж
Для товаров с циклом покупки в 2–3 месяца оценивать удержание на данных недельной свежести бессмысленно — когорта просто ещё не успела «дозреть».
❌ Не чистить дубликаты ClientID
Если один и тот же клиент попадает в выгрузку под разными идентификаторами (гость + зарегистрированный), когорта завышает базу и занижает процент удержания.
Google Таблицы vs DataLens и Excel: что выбрать
Google Таблицы — не единственный способ построить когортную таблицу. Разница между инструментами в основном в том, сколько ручной работы требуется на пересборку при поступлении новых данных.
| Инструмент | Плюсы | Минусы | Когда выбирать |
|---|---|---|---|
| Google Таблицы | Бесплатно, гибкие формулы, легко подключить импорт из CRM и Метрики, доступ для всей команды | Ручная пересборка сводной таблицы, лимиты на объём данных при десятках тысяч строк | Бюджет до 300–500 тыс. руб/мес, одна-две команды маркетинга, нет отдельного аналитика |
| Excel | Мощнее для больших объёмов, привычен бухгалтерии и финансовому отделу | Неудобная совместная работа, нет прямого импорта из веб-сервисов без коннекторов | Отчётность для финансового блока, разовые аудиты вне онлайн-среды |
| Yandex DataLens | Автообновление дашборда, обрабатывает большие объёмы, визуализация из коробки | Нужна настройка источников и хотя бы базовые навыки SQL, порог входа выше | Бюджет от 500 тыс. руб/мес, регулярная отчётность на несколько кампаний одновременно |
Логичный путь роста: начать с Google Таблиц, чтобы за один день получить рабочую когортную таблицу и проверить гипотезу, а после того как формат подтвердил пользу — переносить регулярный расчёт в DataLens или BI-систему, если объём данных и частота обновлений это оправдывают. Подробнее о связке рекламных данных с автоматизированными отчётами — в статье про сквозную аналитику для Яндекс Директа.
Часто задаваемые вопросы
Сколько данных нужно для первой когортной таблицы?
Минимум — 3 полных месяца лидов с привязкой к оплатам из CRM. На меньшем горизонте когорты первого и второго месяца ещё не «дозрели», и выводы об удержании будут случайными, а не закономерными.
Можно ли строить когортный анализ только по данным Метрики, без CRM?
Можно, но только для оценки удержания визитов и повторных обращений — не оплат. Если бизнес-решение касается бюджета на кампании, точность таблицы без данных о реальных платежах из CRM будет низкой.
Как часто нужно обновлять когортную таблицу?
Достаточно раз в месяц синхронизировать новые данные и пересчитывать сводную таблицу. Более частое обновление обычно не даёт новой информации, так как удержание меняется медленно.
Что делать, если в разных кампаниях слишком мало лидов для сравнения когорт?
Объединяйте похожие кампании в группы (например, по гео или типу объявлений) и стройте когорты по группам, а не по отдельным кампаниям — так выборка становится статистически значимой.
Подходит ли когортный анализ для e-commerce с разовыми покупками?
Да, но метрикой удержания в этом случае будет не повторная покупка, а, например, возврат товара, отзыв или переход в программу лояльности — под конкретную бизнес-модель нужно адаптировать целевое событие.
Как автоматизировать выгрузку данных в Google Таблицы, чтобы не собирать всё руками каждый месяц?
Используйте коннекторы API Метрики и CRM (например, через Google Apps Script или готовые надстройки) — они подтягивают свежие данные в отдельный лист по расписанию, а сводная таблица пересчитывается автоматически при обновлении источника.
Заключение
Когортный анализ в Google Таблицах — это не про сложные формулы, а про дисциплину сбора данных: фиксировать дату первого лида, привязывать к ней все последующие платежи и не путать визиты с деньгами. Сводная таблица с процентами удержания по месяцам занимает один лист, но меняет разговор о рекламном бюджете — вместо «эта кампания дала лиды по 400 рублей» появляется «эта кампания дала лиды, которые платят повторно в 22% случаев против 9% у другой».
Начните с малого: возьмите последние три-четыре месяца лидов, разбейте их по неделе или месяцу первого обращения, подтяните статусы оплат из CRM и посчитайте процент вернувшихся в каждой когорте. Даже такая базовая таблица уже покажет, куда стоит перераспределить бюджет Директа — не по цене клика, а по реальной отдаче во времени. А когда формат подтвердит свою пользу, его можно масштабировать на DataLens или BI-систему без потери логики расчёта.
Если нужна помощь с настройкой — оставьте заявку на консультацию.
Читайте также
→ Сквозная аналитика для Яндекс Директа: как связать рекламу и продажи
→ Атрибуция по ассоциированным конверсиям в Яндекс Метрике
→ Многоканальные последовательности в Яндекс Метрике: что это и как читать
Автор: Павел Комарков — специалист по контекстной рекламе с 2010 года, работал с бюджетами от 50 000 ₽ до 150 млн ₽ в месяц. Подробнее →
