Повторная покупка за 30 дней в SQL: готовим список клиентов и метрику

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

Что считаем первой и повторной покупкой

Вход содержит только оплаченные действительные заказы. Для каждого клиента ищем первый и второй заказ по времени. При совпадении времени используем order_id как дополнительный ключ, чтобы порядок был определён. Если два заказа одновременно являются одной раздробленной покупкой, их следует объединить до нумерации по правилам бизнеса.

Окно повторной покупки включает день первой покупки и заканчивается через 30 дней включительно. Это календарный срок, а не следующий календарный месяц. В примере даты хранятся в ISO-формате без времени. Для timestamp границу следует задать на той же точности и заранее согласовать включение конца окна.

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

Почему нужен зрелый знаменатель

На дату 31 октября клиент с первой покупкой 25 октября ещё не прожил 30 дней. Его исключаем из расчёта доли целиком, даже если он уже успел вернуться. Это сохраняет одинаковое время наблюдения для всех клиентов. Молодые покупатели остаются отдельным списком, который войдёт в метрику после закрытия окна.

ROW_NUMBER нумерует заказы внутри клиента. Условная агрегация разворачивает первый и второй в столбцы. Финальная строка содержит признак repeat30, пригодный и для среднего по когорте, и для формирования аудитории. Не нужно соединять первый заказ со всеми последующими и потом исправлять размножение DISTINCT.

WITH orders(order_id,customer,paid_at) AS (
 VALUES (1,'A','2026-09-01'),(2,'A','2026-09-20'),
        (3,'A','2026-09-25'),(4,'B','2026-09-10'),
        (5,'B','2026-10-15'),(6,'C','2026-10-25')
), ranked AS (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY customer ORDER BY paid_at,order_id) AS rn
 FROM orders
), pairs AS (
 SELECT customer,MAX(CASE WHEN rn=1 THEN paid_at END) AS first_at,
 MAX(CASE WHEN rn=2 THEN paid_at END) AS second_at
 FROM ranked GROUP BY customer
)
SELECT customer,first_at,second_at,
 CASE WHEN second_at<=date(first_at,'+30 days') THEN 1 ELSE 0 END AS repeat30
FROM pairs
WHERE date(first_at,'+30 days')<='2026-10-31'
ORDER BY customer;

Ожидаемые строки результата в порядке столбцов SELECT:

[["A", "2026-09-01", "2026-09-20", 1], ["B", "2026-09-10", "2026-10-15", 0]]

Проверяем результат по клиентам

A вернулся через 19 дней и получает единицу. Его третий заказ никак не увеличивает вклад в долю. B вернулся позже окна и получает ноль. C пока исключён по зрелости. Следовательно, среди двух зрелых клиентов повторили покупку 50%. Для отчёта рядом с процентом нужно сохранить число зрелых клиентов.

Если бизнес хочет долю клиентов с любой повторной покупкой после первого месяца, требуется другой вопрос. Наш признак не станет единицей от позднего возвращения B: окно уже закрыто. Такая стабильность удобна для сравнения когорт приобретения на одинаковом сроке.

Что делать со списком

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

Не называйте всех клиентов с нулём ушедшими навсегда. Они лишь не сделали вторую покупку в выбранные 30 дней. Для операционной кампании также понадобятся согласие на коммуникацию, действующие исключения и дата снимка. Эффект предложения проверяют отдельно: сам список показывает состояние покупок, но не доказывает, что сообщение увеличит удержание.

Для регулярного когортного отчёта полезно отдельно проверить знаменатель и периоды: Retention по когортам в SQL: знаменатель, дубли и периоды.

Следующий уровень SQL-практики

В «Продвинутом SQL» вы рассчитываете повторные покупки, retention и последовательные события, а затем решаете цельные тестовые задания. Запросы выполняются в браузере на SQLite и проверяются на нескольких наборах данных. Итоговый проект объединяет приёмы в витрину интернет-магазина. Откройте программу «Продвинутого SQL».