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

17 марта 2022 г.

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

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

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

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

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

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

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

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

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

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

Месяц**Количество загрузок**Январь30 000Февраль50 000Март40 000Апрель42 000Май44 000Зная, что компания вложила тысячи долларов в февральскую маркетинговую кампанию, можно сделать вывод, что увеличение количества рекламы является правильной стратегией. Прежде чем увеличивать маркетинговый бюджет, может быть полезно провести когортный анализ, который анализирует удержание пользователей на основе месяца, в который они загружают приложение. Если уровень удержания пользователей, скачавших приложение в феврале, низкий, то идеальное решение, скорее всего, связано с повышением вовлеченности, а не с вложением большего количества денег в маркетинговые кампании.

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

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

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

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

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

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

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

Обратите внимание, что «X» представляет собой ссылку на ячейку с датой в таблице. После того, как вы нажмете «Ввод», программа извлечет месяц и год из даты загрузки пользователя и заменит день на «1». Например, «05.01.2022» и «25.01.2022» станут «01.01.2022». Щелкните правой кнопкой мыши ячейку и выберите «Формат ячеек». Нажмите «Пользовательский» и удалите «дд/» из поля «Тип», чтобы он читался как «мм/гггг». Нажмите «ОК». Скопируйте эту формулу во все ячейки столбца, дважды щелкнув маркер заполнения в правом нижнем углу ячейки. Теперь «05.01.2022» и «25.01.2022» будут просто выводить «01/2022».

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

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

«ЕСЛИ (ISNUMBER (X), ОКРУГЛ ((XY)/30,0), «активный»)»

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

4. Создайте сводную таблицу

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

5. Создайте лист показателей удержания

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

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

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

7. Рассчитайте показатели удержания

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

Обратите внимание, что ни одна из компаний или продуктов, упомянутых в этой статье, не связана с компанией Indeed.