Когортный анализ в Excel
Когортный анализ в Excel - это метод группировки пользователей по дате их первого действия (первый визит, первая покупка) с последующим расчётом метрик удержания с использованием сводных таблиц (Pivot Tables) и формул массива, выполняемый непосредственно в табличном процессоре без программирования.
В интернет-маркетинге Excel используется для когортного анализа, когда объёмы данных не превышают лимиты программы (до 1-2 миллионов строк), а у аналитика нет доступа к SQL или специализированным платформам (GA4, Roistat). Excel также удобен для разовых отчётов и прототипирования когортных расчётов перед их автоматизацией на SQL или Python.
Microsoft Excel существует с 1985 года. К 2026 году, несмотря на появление более мощных инструментов, Excel остаётся самым доступным инструментом для когортного анализа в малом и среднем бизнесе благодаря низкому порогу входа и наличию у большинства маркетологов.
Суть
[править]Когортный анализ в Excel - это способ построить таблицу удержания клиентов с помощью сводных таблиц. Необходимо выгрузить данные из CRM или аналитики в Excel, добавить столбцы с когортами и периодами, а затем сделать сводную таблицу, где строки - когорты, столбцы - периоды.
Что такое когортный анализ в Excel
[править]Когортный анализ в Excel - это реализация метода когортного анализа с использованием стандартных функций табличного процессора: сводных таблиц (Pivot Tables), формул массива, функций работы с датами (DATEDIF, YEAR, MONTH) и условного форматирования для визуализации.
В отличие от SQL или Python, Excel не требует навыков программирования, но имеет ограничения по объёму данных (около 1-2 миллионов строк) и производительности при сложных расчётах.
Когда Excel подходит, а когда - нет
[править]| Сценарий | Excel подходит? | Альтернатива |
|---|---|---|
| Разовый отчёт на 50 000 строк | Да | - |
| Ежемесячный отчёт на 200 000 строк | Да, но потребуется макрос | GA4, Roistat |
| Регулярный отчёт на 5 млн строк | Нет (Excel зависнет) | SQL, Python |
| Прототипирование перед автоматизацией | Да | - |
Как работает когортный анализ в Excel
[править]Пошаговый алгоритм
[править]Шаг 1: Подготовка исходных данных
Исходная таблица должна содержать минимум три столбца:
| Столбец | Пример | Описание |
|---|---|---|
| user_id | 12345 | Уникальный идентификатор пользователя |
| action_date | 01.03.2025 | Дата действия (покупка, визит) |
| revenue | 1500 | Сумма (опционально, для LTV) |
Шаг 2: Добавление столбца с датой первого действия пользователя
Для этого используется формула массива MINIFS (Excel 2019+) или комбинация INDEX + MATCH.
=MINIFS($B:$B, $A:$A, $A2)
где столбец A - user_id, столбец B - action_date.
Шаг 3: Добавление столбцов с когортой и периодом
=YEAR(C2) & "-" & TEXT(MONTH(C2), "00") ' когорта (год-месяц) =DATEDIF(C2, B2, "M") ' период (месяцы после первого действия)
Шаг 4: Создание сводной таблицы
- Строки (Rows) - cohort (когорта)
- Столбцы (Columns) - period (период)
- Значения (Values) - user_id (Количество)
Шаг 5: Нормализация retention
После создания сводной таблицы нужно добавить вычисляемый столбец: каждую ячейку (период N) разделить на значение периода 0 для той же когорты.
=D2 / INDEX($D2:$I2, 1, 1)
Шаг 6: Визуализация (условное форматирование)
Выделить таблицу retention - Условное форматирование - Цветовые шкалы - выбрать зелёную шкалу.
Пример итоговой таблицы (когорты по месяцам, retention)
[править]| Когорта | Период 0 | Период 1 | Период 2 | Период 3 | Период 4 |
|---|---|---|---|---|---|
| 2025-01 | 100% | 35% | 20% | 15% | 10% |
| 2025-02 | 100% | 34% | 19% | 14% | - |
| 2025-03 | 100% | 36% | 21% | - | - |
| 2025-04 | 100% | 33% | - | - | - |
Преимущества
[править]- Доступность - Excel есть практически на каждом компьютере маркетолога.
- Низкий порог входа - не требует навыков программирования (SQL, Python).
- Наглядность - сводные таблицы и условное форматирование понятны без дополнительного обучения.
- Гибкость прототипирования - можно быстро перестроить когорты (по дням, неделям, месяцам).
- Бесплатно (для компаний с лицензией) - нет дополнительных затрат на аналитические платформы.
Недостатки
[править]- Ограничение по объёму данных - при 500 000+ строк Excel начинает тормозить, при 1-2 миллионах - зависает.
- Ручное обновление - данные нужно выгружать и обрабатывать вручную (или писать макросы).
- Ошибки при обновлении - при добавлении новых строк сводную таблицу нужно обновлять вручную.
- Нет версионирования - трудно отследить, какой отчёт когда был сделан.
- Медленная работа с большими данными - формулы массива и сводные таблицы на 500 000 строках могут вычисляться минутами.
Где используется
[править]Когортный анализ в Excel применяется для:
- Разовых аналитических задач - требуется быстро посмотреть retention для небольшого набора данных.
- Прототипирования - прежде чем писать сложный SQL-запрос или скрипт на Python, логика расчёта отрабатывается в Excel.
- Малого бизнеса - где объёмы данных не превышают 100-200 тысяч строк в месяц, а бюджета на платные аналитические инструменты нет.
- Презентаций и согласований - Excel-таблицу легко отправить руководителю, который умеет с ней работать.
Часто задаваемые вопросы
[править]Как сделать когортный анализ в Excel без формул массива?
[править]Если версия Excel не поддерживает MINIFS, можно сначала сгруппировать данные по user_id (Удалить дубликаты), получить дату первого действия, а затем через ВПР (VLOOKUP) добавить её к исходной таблице.
Почему Excel зависает при когортном анализе?
[править]Причина - формулы массива или сводные таблицы на большом объёме данных (более 500 000 строк). Решение: перейти на Power Pivot (встроенный в Excel), или использовать SQL или Python.
Как автоматизировать когортный анализ в Excel?
[править]Написать макрос VBA, который выгружает данные из базы (через ODBC или Power Query), обрабатывает их, строит сводную таблицу и применяет условное форматирование. Но проще один раз настроить SQL-запрос или GA4.
Какие альтернативы Excel для когортного анализа?
[править]Google Sheets (бесплатно, но медленнее), LibreOffice Calc (бесплатно), Power BI Desktop (бесплатно, мощнее, но сложнее). Для регулярных отчётов лучше использовать GA4, Roistat, SQL или Python.
Можно ли в Excel считать LTV когорт?
[править]Да. В сводной таблице вместо Количество user_id поставить Сумма revenue. Получится таблица выручки по когортам и периодам. Затем выручку периода 0 (первые покупки) вычитать не нужно - это и есть LTV нарастающим итогом.
