Как посчитать Payment Success Rate в SQL

Зачем Payment Success Rate

В e-com / SaaS payments fail 5-15% времени. Если ваш decline rate 20% — 20% lost revenue. Optimization payment gateways = direct money.

Формула

Payment Success Rate = successful / total_attempts × 100%
Decline Rate = declined / total_attempts × 100%

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

Данные: payment_attempts(payment_id, user_id, status, decline_reason, amount, attempted_at).

SELECT
    DATE_TRUNC('week', attempted_at) AS week,
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'success') AS successes,
    COUNT(*) FILTER (WHERE status = 'declined') AS declines,
    COUNT(*) FILTER (WHERE status = 'success')::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS success_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Decline reasons

SELECT
    decline_reason,
    COUNT(*) AS declines,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct_of_declines,
    SUM(amount) AS lost_revenue
FROM payment_attempts
WHERE status = 'declined'
  AND attempted_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY decline_reason
ORDER BY declines DESC;

Топ-причины: insufficient_funds, expired_card, 3D-secure_failed, fraud_suspected.

По типам карт

SELECT
    card_brand,
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'success')::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS success_rate
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY card_brand
HAVING COUNT(*) >= 500
ORDER BY success_rate DESC;

Visa / Mastercard в РФ обычно 90-95% success. American Express ниже (international issues).

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

Ошибка 1. Single attempt = lost. Юзер retried 5 раз, успешно last. Counts as 1 success + 4 declines или 1 final attempt success?

Ошибка 2. Auto-recovery. Stripe / другие retry automatically. Track «final» status, не initial.

Ошибка 3. 3DS confusing. 3DS challenge — not «decline», а «requires action». Categorise separately.

Ошибка 4. Fraud false positives. Legitimate transactions flagged as fraud → declined. Lost revenue + customer frustration.

Ошибка 5. International cards. В РФ international cards — issue с 2022. Filter or separate analysis.

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

• Как посчитать конверсию в SQL
• Как посчитать revenue в SQL
• Как посчитать cart abandonment в SQL
• Как посчитать funnel в SQL

FAQ

Какой Payment Success Rate ok?

В РФ: 90-95% — норма. <85% — много issues. <80% — критично.

Decline Rate растёт — что делать?

1. Decline reasons analysis. 2) Better fraud rules. 3) Smart retry logic. 4) Multi-gateway routing.

3DS effect on conversion?

3DS challenge может drop CR на 5-15%. Use risk-based authentication.

Subscription failed payments?

Used dunning emails + smart retries (3-7 attempts over week).

Payment Success vs Authorization Rate?

Synonyms. Authorization — банк approve. Capture — money moved.