FOR YOUR ANALYST

The formulas

Return rate by product

-- value returned / value sold, by product, last 12 months
-- order_lines: order_id, line_id, product_id, quantity, price
-- refund_lines: refund_id, line_id, quantity, amount, created_at
WITH refunded AS (
  SELECT line_id, SUM(amount) AS amount FROM refund_lines GROUP BY line_id
)
SELECT ol.product_id,
       SUM(ol.quantity * ol.price)                      AS sold_value,
       COALESCE(SUM(rl.amount), 0)                      AS refunded_value,
       COALESCE(SUM(rl.amount), 0)
         / NULLIF(SUM(ol.quantity * ol.price), 0)       AS return_rate
FROM order_lines ol
JOIN orders o ON o.order_id = ol.order_id
LEFT JOIN refunded rl ON rl.line_id = ol.line_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY ol.product_id
ORDER BY refunded_value DESC;

Refunds miss exchanges; add them from your returns app’s export.

Repeat rate by how the first return ended

-- first-time customers 12 to 24 months ago, split by first-return outcome
-- returns: return_id, order_id, customer_id, requested_at, outcome
--          ('refund','exchange','credit','keep'), first_scan_at, refunded_at
WITH firsts AS (
  SELECT customer_id, MIN(created_at) AS first_at
  FROM orders GROUP BY customer_id
  HAVING MIN(created_at) BETWEEN CURRENT_DATE - INTERVAL '24 months'
                             AND CURRENT_DATE - INTERVAL '12 months'
),
first_return AS (
  SELECT DISTINCT ON (r.customer_id) r.customer_id, r.outcome
  FROM returns r JOIN firsts f ON f.customer_id = r.customer_id
  WHERE r.requested_at < f.first_at + INTERVAL '90 days'
  ORDER BY r.customer_id, r.requested_at
)
SELECT COALESCE(fr.outcome, 'no return')                          AS group_,
       COUNT(*)                                                   AS customers,
       AVG(CASE WHEN EXISTS (
             SELECT 1 FROM orders o2
             WHERE o2.customer_id = f.customer_id
               AND o2.created_at > f.first_at
               AND o2.created_at <= f.first_at + INTERVAL '12 months')
           THEN 1.0 ELSE 0 END)                                   AS repeat_12m
FROM firsts f
LEFT JOIN first_return fr ON fr.customer_id = f.customer_id
GROUP BY 1;

Some returns apps create an order for each exchange. Exclude those from the repeat count, or every exchanger looks like a repeat buyer.

How long the money takes

-- median days from request to first scan, and first scan to refund
SELECT date_trunc('month', requested_at)                               AS month,
       percentile_cont(0.5) WITHIN GROUP
         (ORDER BY EXTRACT(EPOCH FROM first_scan_at - requested_at)/86400) AS days_to_scan,
       percentile_cont(0.5) WITHIN GROUP
         (ORDER BY EXTRACT(EPOCH FROM refunded_at - first_scan_at)/86400)  AS days_scan_to_refund,
       AVG(CASE WHEN refunded_at <= first_scan_at + INTERVAL '1 day'
           THEN 1.0 ELSE 0 END)                                         AS share_refunded_at_scan
FROM returns
WHERE outcome = 'refund' AND first_scan_at IS NOT NULL
GROUP BY 1 ORDER BY 1;

Customers above the serial-returner line

-- each customer's share of orders returned, last 12 months
-- set :line from the chapter 11 tool, e.g. 0.66
SELECT o.customer_id,
       COUNT(DISTINCT o.order_id)                                  AS orders,
       COUNT(DISTINCT r.order_id)                                  AS orders_returned,
       COUNT(DISTINCT r.order_id)::numeric
         / COUNT(DISTINCT o.order_id)                              AS return_share
FROM orders o
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY o.customer_id
HAVING COUNT(DISTINCT o.order_id) >= 3
   AND COUNT(DISTINCT r.order_id)::numeric / COUNT(DISTINCT o.order_id) >= :line
ORDER BY return_share DESC;

The three-order minimum keeps one unlucky first order from flagging a new customer. The syntax throughout is Postgres.