Как построить когортный анализ в Excel за 7 шагов (с преимуществами)

Традиционные показатели роста, такие как общее количество показов и количество продаж, могут быть полезны для анализа кампаний, но они являются несколько широкой статистикой. Когортный анализ - это инструмент, который оценивает эти показатели для определенных групп клиентов. Понимание того, как проводить когортный анализ, поможет вам лучше понять свою целевую аудиторию и адаптировать свои кампании к ее предпочтениям. В этой статье мы объясним, как построить когортный анализ в Excel, и обсудим преимущества этого процесса.

Что такое когортный анализ?

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

Виды когортного анализа

Различные группировки клиентов позволяют анализировать показатели удержания на основе различных факторов. Вот три типа когортного анализа, которые вы можете использовать:

• Анализ когорт по времени: Временные когорты разделяют клиентов на группы, основанные на определенном временном интервале. Например, каждая группа может состоять из клиентов, которые подписались на рассылку новостей в первом, втором, третьем и четвертом кварталах.

• Анализ когорты на основе размера: Размер относится к бюджету потенциальной клиентской базы. Сравнивая возможности фрилансеров и небольших компаний по сравнению с крупными компаниями, организации понимают, сколько нужно потратить на каждую кампанию, чтобы повысить коэффициент удержания.

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

Цель когортного анализа

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

Как построить когортный анализ в Excel

Вот как построить когортный анализ в Excel:

1. Импортируйте данные

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

2. Создать когорты

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

дата(ГОД(X), МЕСЯЦ(X), 1)

Обратите внимание, что X представляет собой ссылку на ячейку с датой в таблице. После нажатия кнопки Введите, программа извлекает месяц и год из даты загрузки пользователя и заменяет день на 1. Например, 01052022 и 1252022 обе таблицы станут 01012022 . Щелкните ячейку правой кнопкой мыши и выберите Формат Ячейки. Щелкните Пользовательский и удалите dd из Введите поле так, чтобы оно гласило mmyyyy . Нажмите OK. Скопируйте эту формулу во все ячейки столбца, дважды щелкнув по ручке заливки в правом нижнем углу ячейки. Теперь, 01052022 и 01252022 будет просто выведено 012022 .

3. Создайте Месяцы столбец

Создать Месяцы столбец справа от когорта столбец. В первой ячейке введите эту формулу:

IF(ISNUMBER(X), ROUND((X-Y)30,0), активный )

Первый аргумент оценивает, содержит ли дата загрузки в этой строке значение. Если этого не происходит, выводится значение активный чтобы указать, что клиент все еще активен в приложении. Если дата загрузки в строке содержит значение, то из него вычитается Y. Y - это дата, когда клиент удалил приложение. Затем это число делится на 30, чтобы вывести количество месяцев, в течение которых клиент был активен в приложении.

4. Создать разворотную таблицу

Щелкните по Вставьте вкладку в верхней части ленты и выберите PivotTable в Таблицы группа. Программа автоматически выбирает диапазон данных в вашей электронной таблице, но вы можете настроить диапазон вручную в диалоговом окне. Выберите, где должна появиться таблица - в существующем или новом рабочем листе, и нажмите кнопку OK. Перетащите Месяцы из списка полей в Столбцы область. Перетащите Когорта из списка полей в Строки область. Перетащить Клиент на Значения и обратите внимание, что число в каждом поле указывает на количество потерянных клиентов за период.

5. Создайте таблицу коэффициентов удержания

Выберите PivotTable, щелкните правой кнопкой мыши и выберите Копировать. Нажмите кнопку с плюсом (+) рядом с существующими листами в нижней части электронной таблицы. Дважды щелкните на имени листа, чтобы переименовать его Коэффициенты удержания. Вставьте данные из PivotTable в таблицу Коэффициенты удержания электронная таблица. Выберите Общий итог столбца справа, щелкните правой кнопкой мыши и выберите команду Вырезать. Щелкните правой кнопкой мыши столбец после Когорта столбец и нажмите Вставить. Вставить Общий итог в этот новый столбец. Выделите всю таблицу, щелкните правой кнопкой мыши и выберите Копировать. Щелкните правой кнопкой мыши в любом месте под таблицей и выберите Вставить.

6. Вычислите количество клиентов, которых вы удерживаете каждый месяц

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

7. Рассчитать коэффициент удержания

Выберите вторую таблицу, щелкните правой кнопкой мыши и выберите Копировать. Щелкните правой кнопкой мыши в любом месте над первой таблицей и нажмите кнопку Вставьте. В новой первой таблице выделите первую ячейку справа от ячейки Общий итог столбец. Тип = и сослаться на соответствующую ячейку во второй таблице. Введите символ деления и ссылку на ячейку слева от этой ячейки. Нажмите Введите, и скопируйте эту формулу во все остальные ячейки в ряду с помощью ручки перетаскивания и заливки. Преобразуйте значения в ячейках в проценты, выделив ячейки и нажав на символ процента в столбце Номер группа.