Digital Marketing Academy · Урок

Запросы по когортам и воронкам

Отвечайте на вопросы об удержании

«Запросы по когортам и воронкам» — бесплатный урок Digital Marketing Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Digital Marketing Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Digital Marketing Academy содержит 4 уроков всего.

Что такое когорта?

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

Агрегированные метрики скрывают отток, а когорты показывают, действительно ли новые пользователи остаются с вами.

SELECT user_id,
       DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;

Определение когорты

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

Сохраните это как повторно используемый строительный блок для всех последующих действий.

SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
       COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;

Периоды активности

Удержание измеряет активность в месяцах после регистрации. Вычислите период как разницу между месяцем заказа и месяцем когорты.

Период 0 — это месяц регистрации, период 1 — следующий месяц и так далее.

SELECT o.user_id,
       (DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
       + (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;

Построение сетки удержания

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

Каждая строка — это когорта, а каждый столбец — число месяцев с момента регистрации.

SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
       DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
       COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;

Коэффициент удержания

Сырые количества трудно сравнивать между когортами разного размера. Разделите число активных пользователей в каждом периоде на исходный размер когорты.

Оконная функция переносит размер периода 0 в каждую строку для вычисления соотношения.

SELECT cohort, period,
       active * 1.0 / FIRST_VALUE(active) OVER (
         PARTITION BY cohort ORDER BY period
       ) AS retention
FROM cohort_activity;

Что такое воронка?

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

Анализ воронки превращает расплывчатые жалобы на «низкую конверсию» в указание на конкретный этап с наибольшей утечкой.

SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;

Подсчёт каждого этапа

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

Один запрос, одна строка — вся воронка как на ладони.

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;

Коэффициенты конверсии этапов

Самое интересное число — это соотношение между последовательными этапами. Деление каждого этапа на предыдущий показывает самое значительное падение.

Преобразуйте значение в десятичное число, чтобы целочисленное деление не обнулило ваш коэффициент.

SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
       buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;

Упорядоченные воронки с временными метками

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

Это предотвращает подсчёт покупки, которая произошла до соответствующей регистрации.

SELECT COUNT(DISTINCT v.user_id) AS visited,
       COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
  ON p.user_id = v.user_id
  AND p.step = 'purchase'
  AND p.event_time > v.event_time
WHERE v.step = 'visit';

Воронка по каналам

Разделение воронки по каналам привлечения показывает, какие источники приводят пользователей, действительно совершающих конверсию, а не просто кликающих.

Сгруппируйте условные подсчёты по каналам, чтобы сравнить качество разных источников.

SELECT channel,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;

От понимания к действию

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

Вместе они переводят маркетинг от отчётности о произошедшем к диагностике причин и выбору следующего направления для инвестиций.

Быстрая проверка

Ваша воронка считает пользователей на этапах посещения, регистрации и покупки. Что показывают соотношения последовательных этапов?

Итоги

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

Теперь у вас есть продвинутый набор инструментов для диагностики удержания и конверсии полностью в SQL.

Часто задаваемые вопросы

Урок «Запросы по когортам и воронкам» бесплатный?

Да — полный текст урока «Запросы по когортам и воронкам» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Digital Marketing Academy, подпишись на CoddyKit PRO. Курс Digital Marketing Academy содержит 4 уроков всего.

Чему я научусь в уроке «Запросы по когортам и воронкам»?

Отвечайте на вопросы об удержании Ты практикуешь Digital Marketing Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Digital Marketing Academy?

Предыдущий опыт не требуется. Digital Marketing Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Запросы по когортам и воронкам»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Digital Marketing Academy?

Да. Каждый урок Digital Marketing Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к Digital Marketing Academy