Clean return clean_return
A return that is "clean". The warehouse means it can go back on the shelf; trust and safety means nothing about it points at abuse. Two definitions, no canonical one.
Parameters: abuse_line = 0.6
Test results
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| warehouse | count_between | passed | 10263 | between 100.0 and 20000.0 |
| trust | count_between | passed | 7790 | between 100.0 and 20000.0 |
warehouse · v1 · head of fulfilment
Received in the period in resaleable condition.
SELECT r.id AS return_id, r.order_id, r.customer_id, r.reason, r.condition, r.refund_amount
FROM returns r
WHERE r.received_at >= {period_start} AND r.received_at < {period_end} AND r.received_at <= {now}
AND r.condition = 'resaleable'
trust · v1 · trust and safety lead
Received in the period in resaleable condition, for a fit or preference reason (not a claim that something was missing or wrong), from an account whose returns are under abuse_line of its delivered orders.
WITH rate AS (
SELECT o.customer_id, count(DISTINCT r.id) / greatest(count(DISTINCT o.id), 1) AS return_rate
FROM orders o LEFT JOIN returns r ON r.order_id = o.id AND r.received_at <= {now}
WHERE o.delivered_at <= {now} AND o.delivered_at > {now} - INTERVAL 90 DAY
GROUP BY o.customer_id
)
SELECT r.id AS return_id, r.order_id, r.customer_id, r.reason, r.condition, r.refund_amount
FROM returns r JOIN rate t ON t.customer_id = r.customer_id
WHERE r.received_at >= {period_start} AND r.received_at < {period_end} AND r.received_at <= {now}
AND r.condition = 'resaleable' AND r.reason IN ('doesnt_fit', 'changed_mind', 'arrived_late', 'not_as_pictured')
AND t.return_rate < {abuse_line}