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

Зачем Chargeback Rate

Chargeback = customer disputes transaction через issuer. Card networks (Visa, Mastercard) threshold 1% → fines, и при 2% → loss of merchant account. Critical compliance метрика.

Формула

Chargeback Rate = chargebacks / total_transactions × 100%
Chargeback Rate (by value) = chargeback_amount / total_transaction_amount × 100%

Track both — count и value matter.

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

SELECT
    DATE_TRUNC('month', transaction_date) AS month,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_volume,
    COUNT(*) FILTER (WHERE chargedback) AS chargebacks,
    SUM(amount) FILTER (WHERE chargedback) AS chargeback_volume,
    COUNT(*) FILTER (WHERE chargedback)::NUMERIC * 100 / COUNT(*) AS cb_rate_count_pct,
    SUM(amount) FILTER (WHERE chargedback) * 100.0 / SUM(amount) AS cb_rate_value_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

По reason code

SELECT
    chargeback_reason_code,
    COUNT(*) AS chargebacks,
    SUM(amount) AS chargeback_value,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS share_pct
FROM chargebacks
WHERE chargeback_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY chargeback_reason_code
ORDER BY chargebacks DESC;

Common codes:

• 4837 / 30: Fraud
• 4855 / 53: Goods not received
• 4863 / 41: Cancelled recurring
• 4853 / 13: Refund not processed

Fraud vs Friendly

SELECT
    CASE
        WHEN chargeback_reason_code IN ('4837', '4849', '30', '31') THEN 'Fraud'
        WHEN chargeback_reason_code IN ('4855', '4853', '4863', '53', '41', '13') THEN 'Friendly / Process'
        ELSE 'Other'
    END AS category,
    COUNT(*) AS chargebacks,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS share_pct
FROM chargebacks
WHERE chargeback_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1;

Fraud = real fraud (stolen card). Friendly = customer disputes legit purchase. Friendly often more frequent.

Time to chargeback

SELECT
    AVG(EXTRACT(EPOCH FROM (chargeback_date - transaction_date)) / 86400) AS avg_days_to_cb,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (chargeback_date - transaction_date)) / 86400) AS median_days
FROM chargebacks cb
JOIN transactions t ON cb.transaction_id = t.transaction_id
WHERE cb.chargeback_date >= CURRENT_DATE - INTERVAL '90 days';

Most chargebacks within 30-60 days. Some up to 120 days (card network max).

Trend monitoring

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS cohort_month,
        COUNT(*) AS transactions,
        COUNT(*) FILTER (WHERE chargedback) AS chargebacks,
        COUNT(*) FILTER (WHERE chargedback)::NUMERIC * 100 / COUNT(*) AS cb_rate_pct
    FROM transactions
    WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    cohort_month,
    cb_rate_pct,
    CASE
        WHEN cb_rate_pct < 0.5 THEN 'GOOD'
        WHEN cb_rate_pct < 1 THEN 'WATCH'
        WHEN cb_rate_pct < 1.5 THEN 'WARNING (Visa monitoring)'
        ELSE 'CRITICAL (account at risk)'
    END AS status
FROM monthly;

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

Ошибка 1. Track cohort of transaction, not chargeback date. Chargeback today для transaction 60 days ago — attribute to original transaction month.

Ошибка 2. Count vs value. Visa thresholds на count. Internal tracking — both.

Ошибка 3. Pending chargebacks ignored. Open disputes — likely to chargeback. Include в forecast.

Ошибка 4. Won disputes как chargeback. Won dispute = money returned. Still counted by Visa.

Ошибка 5. No 3DS coverage. Without 3DS, all fraud chargeback liability on merchant. With 3DS — liability shifts.

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

• Как посчитать approval rate в SQL
• Как посчитать fraud rate в SQL
• Кейс: refund rate вырос — что делать
• Как посчитать return rate в SQL

FAQ

Какой Chargeback Rate ok?

< 0.5% — excellent. 0.5-1% — acceptable. > 1% — Visa flags. > 2% — account terminated.

Reduce chargebacks как?

1. Clearer descriptors. 2) Email confirmations. 3) Easy refund flow. 4) 3DS enable. 5) Fraud rules.

Friendly fraud — что это?

Legit customer disputes (forgot subscription, dispute instead of refund). 50%+ of disputes.

Win rate disputes?

Industry: 35-40% win rate. Document well (delivery, communication).

Chargeback fee?

Each chargeback ~$15-25 fee + transaction loss. Cost matters даже beyond rate.