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

Зачем Return Rate

Return Rate — % orders / items returned. Высокий return rate — индикатор UX issues (size charts wrong, product не описан, photo misleading). Прямой удар по margin (logistics + processing cost).

Формула

Return Rate (orders) = returned_orders / total_orders × 100%
Return Rate (items) = returned_items / total_items × 100%
Return Rate (value) = return_value / order_value × 100%

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

SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT order_id) FILTER (WHERE returned = TRUE) AS returned_orders,
    COUNT(DISTINCT order_id) FILTER (WHERE returned = TRUE)::NUMERIC * 100
    / COUNT(DISTINCT order_id) AS return_rate_pct,
    SUM(order_value) AS total_value,
    SUM(return_value) AS returned_value,
    SUM(return_value) * 100.0 / SUM(order_value) AS return_value_pct
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

По категориям

SELECT
    p.category,
    COUNT(*) AS items_ordered,
    COUNT(*) FILTER (WHERE oi.returned) AS items_returned,
    COUNT(*) FILTER (WHERE oi.returned)::NUMERIC * 100 / COUNT(*) AS return_rate_pct,
    SUM(oi.amount) AS revenue,
    SUM(oi.amount) FILTER (WHERE oi.returned) AS returned_revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.ordered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.category
HAVING COUNT(*) >= 100
ORDER BY return_rate_pct DESC;

Clothing — обычно 20-30%. Electronics — 10-15%. Books — 5-10%.

Причины возвратов

SELECT
    return_reason,
    COUNT(*) AS returns,
    COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS share_pct,
    AVG(time_to_return_days) AS avg_days_to_return
FROM returns
WHERE returned_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY return_reason
ORDER BY returns DESC;

Top reasons → actionable improvements:

• Size issue → size charts + recommendations
• Damaged → packaging review
• Not as described → photos / copy
• Quality → vendor review

Window (когда возврат counted)

Return window 30 days vs 90 days:

SELECT
    order_date,
    -- 30-day return rate
    COUNT(*) FILTER (WHERE returned_at <= order_date + INTERVAL '30 days') AS returned_30d,
    -- 90-day return rate
    COUNT(*) FILTER (WHERE returned_at <= order_date + INTERVAL '90 days') AS returned_90d,
    COUNT(*) AS total
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY order_date
ORDER BY order_date;

По customer cohort

WITH first_order AS (
    SELECT user_id, MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY user_id
)
SELECT
    DATE_TRUNC('month', f.first_order_date) AS cohort,
    COUNT(o.order_id) AS orders,
    COUNT(o.order_id) FILTER (WHERE o.returned) AS returns,
    COUNT(o.order_id) FILTER (WHERE o.returned)::NUMERIC * 100 / COUNT(o.order_id) AS return_rate_pct
FROM orders o
JOIN first_order f USING (user_id)
WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

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

Ошибка 1. Refunds vs returns. Refund (money back) ≠ return (item physically back). Different metrics.

Ошибка 2. Time lag. Returns lag orders. Current month return rate undercount (returns not yet processed).

Ошибка 3. Partial returns. Order с 5 items, returned 1. Order count or item count?

Ошибка 4. Exchange как return. Exchange counts? Depends. Business decision.

Ошибка 5. Fraud returns. Wardrobing (buy, use, return). Detect via repeat returners.

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

• Как посчитать fill rate в SQL
• Как посчитать perfect order rate в SQL
• Как посчитать gross margin в SQL
• Как посчитать CSAT в SQL

FAQ

Какой return rate ok?

Clothing: 20-30%. Electronics: 10-15%. Books: <5%. By category vastly different.

Return rate растёт — что делать?

Drill: by category, by reason, by SKU. Top SKU returns → fix size chart / photos / quality.

Time window?

Industry standard: 30 days. Customer-friendly: 90 days. Define per policy.

Wardrobing detection?

Track customer return rate. High-frequency returners — flag.

Refund vs return?

Refund — money. Return — item. Refund without return = customer service / damage adjustment.