CRM-аналитика
SQL для конверсии во вторую покупку с одинаковым сроком наблюдения
Время чтения: 5 мин
Уровень материала: Для экспертов
Содержание
Майские клиенты успели купить повторно, июньские пока нет — и отчёт объявляет июнь худшим месяцем. Однако причина может быть в разном времени наблюдения. Для честного сравнения каждому клиенту нужно дать одинаковый срок после первой покупки.
Конверсия во вторую покупку за 30 дней — доля клиентов, совершивших второй подходящий заказ в течение установленного 30-дневного окна после первого. В этой статье считаем полные интервалы по 24 часа, а не календарные дни. Клиентов, чьё окно ещё не закончилось, в итоговую долю не включаем.
Ниже — учебный запрос для PostgreSQL. Оконная функция ROW_NUMBER нумерует заказы внутри каждого клиента, сохраняя строки. Документация PostgreSQL отдельно обращает внимание на порядок: при одинаковых значениях сортировки нумерация без дополнительного критерия не определена.
Договоритесь, что является покупкой
Запрос ожидает подготовленную таблицу orders_clean: одна строка на заказ, устойчивый customer_id, уникальный order_id, время покупки purchased_at типа timestamptz, статус status и сумма после возвратов net_paid на дату среза.
Считаем покупки со статусом completed и положительной суммой после возвратов. Полностью возвращённый заказ не является покупкой в этой конкретной версии метрики; частично возвращённый остаётся. Это редакционный выбор для примера, а не универсальное правило учёта.
В таблице уже должны быть устранены дубли, тестовые заказы и техническое дробление одной покупки на несколько записей, если бизнес считает её одним заказом. История должна начинаться до первой покупки анализируемых клиентов. Иначе первая видимая покупка будет ошибочно названа первой в жизни клиента.
Запрос с одинаковым окном
Для примера дата среза — 1 июля 2026 года, 00:00 UTC. Состояния заказов и возвратов также должны соответствовать этому срезу, а не текущему дню запуска запроса.
SET TIME ZONE 'UTC';
WITH params AS (
SELECT TIMESTAMPTZ '2026-07-01 00:00:00+00' AS cutoff
), ranked AS (
SELECT o.customer_id, o.order_id, o.purchased_at,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.purchased_at, o.order_id
) AS rn
FROM orders_clean o CROSS JOIN params p
WHERE o.status = 'completed'
AND o.net_paid > 0
AND o.customer_id IS NOT NULL
AND o.purchased_at \< p.cutoff
), per_customer AS (
SELECT customer_id,
MIN(purchased_at) FILTER (WHERE rn = 1) AS first_at,
MIN(purchased_at) FILTER (WHERE rn = 2) AS second_at
FROM ranked
GROUP BY customer_id
), mature AS (
SELECT c.*
FROM per_customer c CROSS JOIN params p
WHERE c.first_at + INTERVAL '30 days' \<= p.cutoff
)
SELECT DATE_TRUNC('month', first_at) AS cohort_month,
COUNT(*) AS customers,
COUNT(*) FILTER (
WHERE second_at \< first_at + INTERVAL '30 days'
) AS repeat_customers,
ROUND(100.0 * COUNT(*) FILTER (
WHERE second_at \< first_at + INTERVAL '30 days'
) / COUNT(*), 2) AS repeat_pct
FROM mature
GROUP BY 1
ORDER BY 1;
Интервал включает начало и исключает правую границу. Второй заказ ровно через 30 дней не попадает в числитель. Разные реальные заказы с одинаковым временем допускаются; order_id делает их порядок воспроизводимым. Если бизнес требует покупку в другой день, это отдельное условие, которого в запросе нет.
Как проверить результат на маленьком примере
Условный пример. Все указанные времена — 00:00 UTC; после очистки данные выглядят так.
| Клиент | Подходящие покупки | Результат на 1 июля |
|---|---|---|
| А | 1 и 10 мая | В знаменателе и числителе |
| Б | 1 и 31 мая | В знаменателе; вторая покупка на исключённой границе |
| В | 20 и 25 июня | Не включён: окно ещё не завершено |
| Г | Только 5 мая | В знаменателе без повторной покупки |
| Д | 10 и 20 мая | В знаменателе и числителе |
| Е | Только 15 мая | В знаменателе без повторной покупки |
В майской когорте пять клиентов, повторно купили в нужном окне двое: 40%. У клиента Д мог быть полностью возвращённый апрельский заказ: по выбранному правилу он удалён до нумерации. У клиента Е повторная техническая запись майского заказа должна исчезнуть на этапе подготовки данных.
Где запрос чаще всего портят
Первый риск — ограничить исходные заказы маем до нумерации. Тогда апрельская первая покупка исчезнет, а майская повторная станет «первой». Период нужной когорты фильтруют после определения действительной первой покупки.
Второй — начать расчёт со списка повторных заказов. Так теряются клиенты с единственной покупкой, и знаменатель становится слишком маленьким. Здесь строка остаётся у каждого подходящего клиента, даже если second_at пуст.
Третий — обновлять возвраты задним числом, сохраняя прежнее название отчёта. Состав когорты может измениться. Для воспроизводимости храните дату среза, версию правил и входные данные либо возможность восстановить их состояние.
Что этот отчёт позволяет заключить
Он сравнивает наблюдаемую долю повторных покупателей при одинаковом окне. Он не доказывает эффект welcome-цепочки: между месяцами могли измениться ассортимент, привлечение и сезонность. Для причинного вывода тот же показатель считают по корректно назначенным экспериментальным группам.
Неопознанные покупки не следует незаметно терять: рядом с конверсией показывайте долю заказов без клиентского ID. Иначе улучшение склейки профилей может выглядеть как рост повторных покупок.
Источники
PostgreSQL Global Development Group. Window Functions. PostgreSQL 18 Documentation, раздел 3.5. Проверено 22.09.2026.