Как посчитать Repeat Buyer Rate в SQL

Зачем Repeat Buyer Rate

Repeat Buyer = customer, кто сделал >= 2 purchases. Standard e-com metric. Indicates product-market fit. New customer acquisition expensive — repeat buyers «free» revenue.

Формула

Repeat Buyer Rate = customers_with_2+_orders / total_customers × 100%

Window обычно 12 months (или lifetime).

Базовый расчёт

WITH order_counts AS (
    SELECT
        user_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_customers,
    COUNT(*) FILTER (WHERE order_count >= 2) AS repeat_buyers,
    COUNT(*) FILTER (WHERE order_count >= 2)::NUMERIC * 100 / COUNT(*) AS repeat_buyer_rate_pct
FROM order_counts;

По cohort

Repeat buying rate by acquisition month:

WITH first_purchase AS (
    SELECT
        user_id,
        MIN(created_at) AS first_order_at
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
),
cohort_repeat AS (
    SELECT
        DATE_TRUNC('month', fp.first_order_at) AS cohort,
        fp.user_id,
        COUNT(o.order_id) AS total_orders
    FROM first_purchase fp
    LEFT JOIN orders o ON o.user_id = fp.user_id
      AND o.status = 'paid'
      AND o.created_at <= fp.first_order_at + INTERVAL '12 months'
    WHERE fp.first_order_at >= CURRENT_DATE - INTERVAL '18 months'
      AND fp.first_order_at < CURRENT_DATE - INTERVAL '6 months'
    GROUP BY 1, fp.user_id
)
SELECT
    cohort,
    COUNT(*) AS customers,
    COUNT(*) FILTER (WHERE total_orders >= 2) AS repeat_buyers,
    COUNT(*) FILTER (WHERE total_orders >= 2)::NUMERIC * 100 / COUNT(*) AS repeat_rate_pct
FROM cohort_repeat
GROUP BY cohort
ORDER BY cohort;

Time to second purchase

WITH ordered AS (
    SELECT
        user_id,
        created_at AS order_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
    WHERE status = 'paid'
),
first_two AS (
    SELECT
        user_id,
        MAX(order_at) FILTER (WHERE rn = 1) AS order1,
        MAX(order_at) FILTER (WHERE rn = 2) AS order2
    FROM ordered
    WHERE rn <= 2
    GROUP BY user_id
)
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (order2 - order1)) / 86400) AS median_days,
    AVG(EXTRACT(EPOCH FROM (order2 - order1)) / 86400) AS avg_days
FROM first_two
WHERE order2 IS NOT NULL;

По channels

SELECT
    u.acquisition_channel,
    COUNT(DISTINCT u.user_id) AS customers,
    COUNT(DISTINCT u.user_id) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM orders o
            WHERE o.user_id = u.user_id
              AND o.status = 'paid'
            GROUP BY o.user_id
            HAVING COUNT(*) >= 2
        )
    ) AS repeat_buyers,
    COUNT(DISTINCT u.user_id) FILTER (
        WHERE EXISTS (
            SELECT 1 FROM orders o
            WHERE o.user_id = u.user_id
              AND o.status = 'paid'
            GROUP BY o.user_id
            HAVING COUNT(*) >= 2
        )
    )::NUMERIC * 100 / COUNT(DISTINCT u.user_id) AS repeat_rate
FROM users u
WHERE u.acquired_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY u.acquisition_channel
ORDER BY repeat_rate DESC;

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

Ошибка 1. New customers скew. Cohort that acquired 1 month ago — низкий repeat rate (мало времени).

Ошибка 2. Refunds. Order refunded — counts? Exclude.

Ошибка 3. Window choice. 12 months vs lifetime — разные numbers. Compare apples-apples.

Ошибка 4. Definition of repeat. 2+ vs 3+ orders? Some define как 2nd within 30 days. Define.

Ошибка 5. Multi-account. Same person, two accounts — fragmented. ID resolution.

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

• Как посчитать days between orders в SQL
• Как посчитать repeat purchase rate в SQL
• Как посчитать order frequency в SQL
• Как посчитать LTV в SQL

FAQ

Какой Repeat Buyer Rate ok?

E-com average: 20-30%. Subscription / consumables: 40-60%. Best-in-class: 70%+.

Repeat vs Returning customer?

Synonyms. «Repeat buyer» — после первой purchase. «Returning» — после inactivity.

30 days vs 90 days vs lifetime?

90 days — стандарт. Lifetime — true loyalty. Pick one и stick.

Improve repeat rate как?

1. Email re-engagement. 2) Loyalty programs. 3) Subscriptions. 4) Reduce friction (saved cards).

Repeat buyer LTV?

LTV repeat buyer >> first-time. Focus retention spend on this segment.