Когортный анализ в продуктовой аналитике — Направления в аналитике

• Этапы когортного анализа
• Пример когортного анализа с использованием SQL

Когортный анализ — это метод исследования пользователей, основанный на группировке пользователей по определенным характеристикам (дате первого визита или первой покупки).

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

Этапы когортного анализа

Когортный анализ включает в себя следующие этапы:

• Определение метрик — выбор метрик, которые вы хотите изучить (например, количество продаж или уровень удержания)
• Формирование когорт — группировка пользователей на основе характеристик (дата первой покупки или регистрации)
• Сравнение когорт — анализ изменений метрик с течением времени в каждой когорте для выявления трендов и различий

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

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

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

Пример когортного анализа с использованием SQL

Представим, что нам нужно проанализировать удержание пользователей. Удержание (retention) это параметр рассчитываемый как отношение количества пользователей за промежуток времени. Например, если в января к нам пришло 10 пользователей, а к марту их стало 7, то удержание с января по март будет 7 / 10 = 70%.

Существует несколько подходов как учитывать вернувшихся пользователей для расчета удержания, но мы воспользуемся наиболее простым - Return ON or AFTER. Согласно ему, пользователь учитывается в промежуточные периоды, если он все же вернулся позже. В таком случае, нам достаточно посмотреть на первый и последний визит.

Допустим у нас есть данные посещений:

INSERT INTO clickstream (user_id, visit_date)
VALUES
(1, '2018-01-01'),
(1, '2018-02-01'),
(1, '2018-03-01'),
(1, '2018-04-01'),
(2, '2018-01-01'),
(2, '2018-04-01'),
(3, '2018-01-01'),
(3, '2018-02-01'),
(3, '2018-03-01'),
(4, '2018-01-01'),
(5, '2018-02-01'),
(5, '2018-03-01'),
(6, '2018-01-01'),
(6, '2018-02-01'),
(6, '2018-03-01'),
(6, '2018-04-01'),
(7, '2018-01-01'),
(7, '2018-02-01'),
(7, '2018-04-01');

Расчитаем удержание за 1, 2 и 3 месяца, для пользователей, которые впервые зашли в январе 2018.

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

first_last_visits AS (
    SELECT
        user_id,
        MIN(visit_date) as first_visit,
        MAX(visit_date) as last_visit
    FROM clickstream
    GROUP BY user_id
)

Затем определим нашу анализируемую когорту:

-- ...

SELECT
    DATE_TRUNC('month', first_visit) as cohort_month,
    user_id,
    first_visit,
    last_visit
FROM first_last_visits
WHERE EXTRACT(month FROM first_visit) = 1;

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

-- ...

SELECT
    cohort_month,
    COUNT(*) as cohort_size,
    COUNT(*) FILTER (WHERE last_visit >= first_visit + INTERVAL '1 month') as retained_1_month,
    COUNT(*) FILTER (WHERE last_visit >= first_visit + INTERVAL '2 months') as retained_2_months,
    COUNT(*) FILTER (WHERE last_visit >= first_visit + INTERVAL '3 months') as retained_3_months
FROM cohort_data
GROUP BY cohort_month;

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

Наконец, добавляем последний блок с расчетом процента удержания

-- ...

SELECT
    TO_CHAR(cohort_month, 'YYYY-MM') as cohort,
    cohort_size,
    ROUND(100.0 * retained_1_month / cohort_size, 1) as retained_1_month,
    ROUND(100.0 * retained_2_months / cohort_size, 1) as retained_2_months,
    ROUND(100.0 * retained_3_months / cohort_size, 1) as retained_3_months
FROM retention_data
ORDER BY cohort_month;

https://www.db-fiddle.com/f/g8U1J4pxSnLBo87jbynUxV/0

В итоге получим такую таблицу:

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