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

Зачем Repeat Purchase Rate

В e-com покупатель, сделавший 2+ заказов, окупает себя в 3 раза быстрее single-purchase юзера. Repeat Purchase Rate (RPR) — индикатор loyalty и здоровья customer base.

Формула

Repeat Purchase Rate = customers_with_2plus_orders / total_customers × 100%

Окно — обычно 90 или 365 дней.

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

WITH user_orders AS (
    SELECT
        user_id,
        COUNT(*) AS orders_count
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_customers,
    COUNT(*) FILTER (WHERE orders_count >= 2) AS repeat_customers,
    COUNT(*) FILTER (WHERE orders_count >= 2)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS rpr_pct
FROM user_orders;

По cohort

WITH first_orders AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS first_order_month
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
),
repeat_users AS (
    SELECT
        f.first_order_month,
        f.user_id,
        COUNT(o.order_id) AS total_orders
    FROM first_orders f
    JOIN orders o ON o.user_id = f.user_id
        AND o.status = 'paid'
    GROUP BY f.first_order_month, f.user_id
)
SELECT
    first_order_month,
    COUNT(*) AS new_customers,
    COUNT(*) FILTER (WHERE total_orders >= 2) AS repeated,
    COUNT(*) FILTER (WHERE total_orders >= 2)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS rpr_pct
FROM repeat_users
GROUP BY first_order_month
ORDER BY first_order_month;

По сегментам

По acquisition channel:

WITH user_data AS (
    SELECT
        u.user_id,
        u.acquisition_channel,
        COUNT(o.order_id) AS orders
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
    WHERE u.signup_date >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY u.user_id, u.acquisition_channel
)
SELECT
    acquisition_channel,
    COUNT(*) AS customers,
    COUNT(*) FILTER (WHERE orders >= 2) AS repeat_customers,
    COUNT(*) FILTER (WHERE orders >= 2)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS rpr_pct
FROM user_data
WHERE orders >= 1
GROUP BY acquisition_channel
ORDER BY rpr_pct DESC;

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

Ошибка 1. Считать в неполном году. Юзер купил в марте, ещё не сделал второй заказ. На графике RPR за апрель — занижено. Используйте matured cohorts.

Ошибка 2. Включать pending / cancelled. Только status = 'paid'.

Ошибка 3. 2+ orders в один день. Два заказа в одну дату — counts как repeat? В классической логике да, но интуитивно «repeat» — это return через time. Фильтруйте time_between_orders > N days.

Ошибка 4. RPR vs frequency. RPR — binary (yes/no repeat). Frequency — count. Разные метрики.

Ошибка 5. Игнорировать subscription auto-renewal. В subscription auto-renew генерирует «repeats» автоматически. RPR в e-com и SaaS — разные понятия.

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

• Как посчитать frequency в SQL
• Как посчитать retention в SQL
• Как посчитать AOV в SQL
• Как посчитать LTV в SQL

FAQ

Какой RPR считается хорошим?

E-com: 20-40%. Premium-сегмент: 30-50%. FMCG-доставка: 60%+.

RPR vs Retention?

Retention — про активность. RPR — про покупки. Можно retention 70% при RPR 20% (юзеры заходят, но мало покупают).

Окно 90 или 365 дней?

Зависит от категории. FMCG — 30. Одежда — 90. Электроника — 365.

RPR падает — что делать?

1. Cohort analysis: какие когорты упали. 2) Cross-sell email / push кампании. 3) Loyalty program review. 4) Categories анализ.

Single-purchase business?

В edu / vehicles — RPR низкий по природе. Не сравнивайте с FMCG.