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

Zачем Approval Rate

Approval Rate = % payments authorized (vs declined). Low approval → revenue leak. 80% norm. > 90% — excellent. < 70% — major issue (3D secure friction, fraud rules too strict).

Формула

Approval Rate = approved_transactions / total_attempted × 100%

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

SELECT
    DATE_TRUNC('day', attempted_at) AS day,
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'approved') AS approved,
    COUNT(*) FILTER (WHERE status = 'declined') AS declined,
    COUNT(*) FILTER (WHERE status = 'approved')::NUMERIC * 100 / COUNT(*) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По issuer

SELECT
    issuer,  -- Visa, Mastercard, Amex, etc.
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'approved')::NUMERIC * 100 / COUNT(*) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY issuer
HAVING COUNT(*) >= 100
ORDER BY approval_rate_pct DESC;

Issuer-specific differences — investigate routing.

Decline reasons

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

Common reasons:

• Insufficient funds
• Card expired
• Do not honor (issuer policy)
• 3D Secure failed
• Fraud suspected

Retry logic

WITH attempts AS (
    SELECT
        user_id,
        order_id,
        attempted_at,
        status,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY attempted_at) AS attempt_num
    FROM payment_attempts
    WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    attempt_num,
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'approved') AS approved,
    COUNT(*) FILTER (WHERE status = 'approved')::NUMERIC * 100 / COUNT(*) AS approval_pct
FROM attempts
GROUP BY attempt_num
ORDER BY attempt_num;

Attempt 1: maybe 70% approval. Retry 2: capture 30% of failed. Total: ~80%+.

По country / amount

SELECT
    customer_country,
    NTILE(5) OVER (ORDER BY amount) AS amount_bucket,
    COUNT(*) AS attempts,
    COUNT(*) FILTER (WHERE status = 'approved')::NUMERIC * 100 / COUNT(*) AS approval_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY approval_pct;

High-amount transactions often higher decline rate.

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

Ошибка 1. Soft decline vs hard decline. Soft (temporary) — retry. Hard (final) — no retry.

Ошибка 2. Decline retries inflate denominator. Same order, 3 attempts — 1 success counted, 2 declines. Approval rate per order vs per attempt.

Ошибка 3. Fraud blocks как decline. Internal fraud rules → looks like decline. Track separately.

Ошибка 4. 3DS friction. 3DS challenge → users abandon. Track abandonment как decline indicator.

Ошибка 5. Country / currency mismatch. Foreign card on local merchant — high decline. By country segmentation.

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

• Как посчитать chargeback rate в SQL
• Как посчитать fraud rate в SQL
• Как посчитать revenue в SQL
• Как посчитать conversion uplift в SQL

FAQ

Какой Approval Rate ok?

Soft vs Hard decline?

Soft: try again (insufficient funds, network issue). Hard: don't retry (do not honor, stolen card).

3D Secure tradeoff?

3DS reduces fraud, но adds friction → some users abandon. Cart abandonment vs fraud loss trade-off.

Improve Approval Rate как?

1. Smart routing (multiple gateways). 2) Token recycling. 3) Reduce 3DS friction. 4) Fraud rule tuning. 5) Currency-matched merchant.

Approval Rate per gateway?

Different gateways = different approval. Multi-gateway routing optimizes.