Refund Rate (RR)

Что такое Refund Rate (RR)?

Доля заказов с возвратом денег от общего числа заказов.

Формула

RR = #refunded / #total_orders × 100%

Как считать

RR = orders_refunded / orders_total в окне 30/60/90 дней от purchase. По категориям и SKU отдельно.

Зачем нужна

Прямые потери revenue. Высокий RR на SKU = качество товара / описание не соответствует. RR > 8% (для одежды — 15-25%) = срочный аудит.

SQL-запрос

SELECT
  c.category,
  COUNT(DISTINCT o.order_id) AS total_orders,
  COUNT(DISTINCT r.order_id) AS refunded_orders,
  ROUND(100.0 * COUNT(DISTINCT r.order_id) / NULLIF(COUNT(DISTINCT o.order_id), 0), 2) AS refund_rate_pct,
  SUM(r.refund_amount) AS refunded_value
FROM orders o
JOIN products p ON p.product_id = o.product_id
JOIN categories c USING (category_id)
LEFT JOIN refunds r ON r.order_id = o.order_id AND r.refunded_at <= o.created_at + INTERVAL '60 day'
WHERE o.created_at BETWEEN CURRENT_DATE - INTERVAL '90 day' AND CURRENT_DATE - INTERVAL '60 day'
GROUP BY c.category
ORDER BY refund_rate_pct DESC;