Как посчитать повторные покупки в SQL

Зачем считать повторные покупки

Привлечение нового покупателя почти всегда стоит дороже удержания существующего — поэтому доля повторных покупок (repeat purchase rate) напрямую влияет на юнит-экономику. Продукты с сильным retention живут в основном на органике и возвратах, продукты со слабым — постоянно вынуждены перезакупаться в paid-каналах.

На собеседованиях в e-commerce эту метрику спрашивают регулярно: «посчитай долю повторных покупателей», «найди среднее время между заказами», «построй когортный repeat rate». Задачи решаются на оконных функциях — ROW_NUMBER, LAG, MIN / MAX.

Есть важные нюансы: считать долю повторных от всех или только от оплаченных заказов; считать ли два заказа за 5 минут как «один с разделённым платежом»; брать ли всех пользователей или ограничивать когортой, у которой уже был шанс вернуться.

В статье — готовые запросы:

• базовый repeat purchase rate;
• распределение по количеству заказов;
• доля клиентов с 2+, 3+, 5+ заказами;
• среднее время между покупками и время до второй;
• repeat rate по когортам;
• frequency (заказов на клиента в месяц);
• revenue от повторных vs новых.

Схема: orders(order_id, user_id, total, status, created_at).

1. Базовый repeat purchase rate

WITH buyer_orders AS (
    SELECT
        user_id,
        COUNT(*) AS orders_count
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_buyers,
    COUNT(*) FILTER (WHERE orders_count >= 2) AS repeat_buyers,
    100.0 * COUNT(*) FILTER (WHERE orders_count >= 2) / COUNT(*) AS repeat_rate_pct
FROM buyer_orders;

2. Распределение по количеству заказов

WITH buyer_orders AS (
    SELECT user_id, COUNT(*) AS orders_count
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    orders_count,
    COUNT(*) AS buyers,
    100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_buyers
FROM buyer_orders
GROUP BY orders_count
ORDER BY orders_count;

Обычно: 60% делают 1 покупку, 20% — 2, 10% — 3, 5% — 4, 5% — 5+.

3. Доля клиентов с 2+, 3+, 5+

WITH buyer_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
),
total AS (SELECT COUNT(*) AS n FROM buyer_orders)
SELECT
    100.0 * COUNT(*) FILTER (WHERE cnt >= 1) / n AS pct_1_plus,
    100.0 * COUNT(*) FILTER (WHERE cnt >= 2) / n AS pct_2_plus,
    100.0 * COUNT(*) FILTER (WHERE cnt >= 3) / n AS pct_3_plus,
    100.0 * COUNT(*) FILTER (WHERE cnt >= 5) / n AS pct_5_plus,
    100.0 * COUNT(*) FILTER (WHERE cnt >= 10) / n AS pct_10_plus
FROM buyer_orders, total;

4. Время между покупками

WITH ordered AS (
    SELECT
        user_id,
        created_at,
        LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order
    FROM orders
    WHERE status = 'paid'
)
SELECT
    user_id,
    AVG(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS avg_days_between,
    MIN(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS min_days,
    MAX(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS max_days
FROM ordered
WHERE prev_order IS NOT NULL
GROUP BY user_id;

5. Среднее время до второй покупки

Отдельный KPI — «time to second purchase». Ключевой индикатор retention.

WITH user_orders AS (
    SELECT
        user_id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
    WHERE status = 'paid'
)
SELECT
    AVG(EXTRACT(EPOCH FROM (o2.created_at - o1.created_at)) / 86400) AS avg_days_to_2nd,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (o2.created_at - o1.created_at)) / 86400
    ) AS median_days_to_2nd
FROM user_orders o1
JOIN user_orders o2 ON o2.user_id = o1.user_id AND o2.order_num = 2
WHERE o1.order_num = 1;

6. Repeat rate по когортам

Смотрим, улучшается ли retention новых когорт.

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
),
user_stats AS (
    SELECT user_id, COUNT(*) AS orders_count
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    c.cohort,
    COUNT(*) AS cohort_size,
    COUNT(*) FILTER (WHERE us.orders_count >= 2) AS repeat_buyers,
    100.0 * COUNT(*) FILTER (WHERE us.orders_count >= 2) / COUNT(*) AS repeat_rate
FROM cohorts c
JOIN user_stats us USING (user_id)
GROUP BY c.cohort
ORDER BY c.cohort;

7. Frequency — заказов на клиента в месяц

WITH user_activity AS (
    SELECT
        user_id,
        COUNT(*) AS total_orders,
        -- общее число месяцев между первой и последней покупкой
        (EXTRACT(YEAR  FROM AGE(MAX(created_at), MIN(created_at))) * 12
         + EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at))))::NUMERIC + 1
            AS active_months
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
    HAVING COUNT(*) >= 2   -- только пользователи с двумя и более заказами
)
SELECT
    AVG(total_orders / NULLIF(active_months, 0))         AS avg_frequency_per_month,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY total_orders / NULLIF(active_months, 0)
    )                                                    AS median_frequency
FROM user_activity;

В Postgres EXTRACT(MONTH FROM AGE(...)) даёт только компонент месяцев (0–11), поэтому прибавляем EXTRACT(YEAR FROM AGE(...)) * 12 — иначе данные за полтора года сожмутся в «6 месяцев».

8. Revenue от повторных vs новых

WITH user_order_num AS (
    SELECT
        user_id,
        total,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
    WHERE status = 'paid'
)
SELECT
    CASE WHEN order_num = 1 THEN 'first_purchase' ELSE 'repeat' END AS type,
    COUNT(*) AS orders,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value
FROM user_order_num
GROUP BY 1;

Обычно repeat покупатели имеют выше AOV.

Частые ошибки

1. Включать тестовые аккаунты

Очищайте от сотрудников, QA-аккаунтов и ботов — иначе repeat rate завышен.

2. Не различать «двойные» заказы

Два заказа за 5 минут — часто один заказ с разделением оплаты. Такие случаи лучше схлопывать в одну транзакцию.

3. «Все клиенты» без когорт

Свежие пользователи просто не успели вернуться, и их включение занижает метрику. В отчёте по repeat rate разбивайте когорты по месяцу первой покупки.

4. Repeat rate без окна

Repeat rate за всё время ≠ за 90 дней. Чётко определяйте горизонт — например, «купил ещё раз в течение 90 дней после первой покупки».

Связанные темы

• RFM-анализ
• LTV простыми словами
• Retention простыми словами
• Кейс: frequency упала

FAQ

Что считается «повторным»?

2+ заказа. Иногда 3+ для более строгой метрики.

Repeat rate 30% — хорошо?

Очень зависит от категории. Для товаров разовой покупки 30% — неплохо, для food delivery с высокой частотой заказов — скорее тревожный сигнал. Сравнивайте со своими историческими значениями и с бенчмарками внутри ниши.

Как связано с LTV?

LTV ≈ AOV × frequency × lifespan. Repeat rate прямо влияет на lifespan.

Time to 2nd purchase — что это даёт?

Прокси для product-market fit. Короткое время → клиенты ценят.