Как посчитать Coupon Redemption Rate в SQL

Зачем Coupon Redemption Rate

Маркетолог отправляет 100K купонов «-20% next order». Использовано 5К → Redemption Rate 5%. Это низко (стандарт 10-25%). Нужно: лучший targeting / лучшее offer / лучшее timing.

Формула

Coupon Redemption Rate = coupons_used / coupons_issued × 100%

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

Данные: coupons(coupon_id, user_id, issued_at, used_at).

SELECT
    DATE_TRUNC('month', issued_at) AS month,
    COUNT(*) AS coupons_issued,
    COUNT(*) FILTER (WHERE used_at IS NOT NULL) AS coupons_used,
    COUNT(*) FILTER (WHERE used_at IS NOT NULL)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS redemption_rate_pct
FROM coupons
WHERE issued_at >= '2026-01-01'
GROUP BY 1
ORDER BY 1;

По кампаниям

SELECT
    campaign_name,
    discount_pct,
    COUNT(*) AS issued,
    COUNT(*) FILTER (WHERE used_at IS NOT NULL) AS used,
    COUNT(*) FILTER (WHERE used_at IS NOT NULL)::NUMERIC * 100
        / NULLIF(COUNT(*), 0) AS redemption_rate
FROM coupons
WHERE issued_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY campaign_name, discount_pct
ORDER BY redemption_rate DESC;

Higher discount обычно = higher redemption. Но economics могут быть негативные.

Incremental Revenue

WITH redeemed AS (
    SELECT
        c.user_id,
        c.discount_pct,
        c.used_at,
        o.total AS order_total
    FROM coupons c
    JOIN orders o ON o.user_id = c.user_id AND o.created_at = c.used_at
    WHERE c.used_at IS NOT NULL
),
control_baseline AS (
    SELECT AVG(total) AS avg_order_no_coupon
    FROM orders
    WHERE status = 'paid'
      AND order_id NOT IN (SELECT order_id FROM coupons WHERE used_at IS NOT NULL)
)
SELECT
    discount_pct,
    COUNT(*) AS redeemed_count,
    AVG(order_total) AS avg_order_with_coupon,
    (SELECT avg_order_no_coupon FROM control_baseline) AS avg_order_no_coupon,
    AVG(order_total) - (SELECT avg_order_no_coupon FROM control_baseline) AS uplift
FROM redeemed
GROUP BY discount_pct;

Не все redemptions = incremental — часть юзеров купила бы и без купона.

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

Ошибка 1. Считать как incremental все redemptions. Часть юзеров купила бы и без купона. Real incremental — uplift vs control.

Ошибка 2. Coupon в shopping cart, но не used. Юзер положил, удалил, или не закончил purchase. Должен быть только actual purchase.

Ошибка 3. Expired coupons. Не использованы потому что expired vs действительно ignored — разные сигналы.

Ошибка 4. Один user, много coupons. Limit per user (например, 1 coupon = 1 use).

Ошибка 5. ROI без учёта margin. Coupon -30% на product с margin 25% — продаёте в минус. Считайте ROI с margin.

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

• Как посчитать discount rate в SQL
• Как посчитать AOV в SQL
• Как посчитать ROAS в SQL
• Как посчитать gross margin в SQL

FAQ

Какой Redemption Rate ok?

Email coupons: 5-15%. Push notifications: 2-10%. Welcome offers: 20-40%.

Higher discount = better?

Higher redemption, но lower margin. Sweet spot — measure ROI.

Targeted vs mass coupons?

Targeted higher redemption (relevance). Mass — больший reach.

Coupons и churn?

Coupons могут retain churned. Win-back campaigns обычно 5-15% redemption.

Как improve?

1. Better targeting (segment-specific). 2) Timing (right moment). 3) Mobile-first UX. 4) Clear value prop.