Return-abuse pattern return_abuse
One account returning most of what it orders, with goods coming back used, swapped or "missing an item". Run once a day.
Parameters: lookback_days = 60 min_returns = 4 min_return_rate = 0.6 min_not_resaleable = 3
trust · v1 · trust and safety lead
In the last lookback_days: at least min_returns returns received, returns at least min_return_rate of the account's delivered orders, and at least min_not_resaleable of them came back not resaleable (used, swapped, damaged or missing an item). Blocked accounts are not listed. Shoppers who order two sizes and send one back reach the rate; what separates them is the condition of what comes back, which is why a damaged parcel or two can still put an honest account on the list.
WITH r AS (
SELECT customer_id, received_at, condition, reason, refund_amount
FROM returns WHERE received_at <= {now} AND received_at > {now} - INTERVAL ({lookback_days}) DAY
),
agg AS (
SELECT customer_id, count(*) AS returns,
count(*) FILTER (WHERE condition <> 'resaleable') AS not_resaleable,
count(*) FILTER (WHERE reason IN ('item_missing', 'wrong_item', 'not_as_described')) AS claims,
round(sum(refund_amount), 2) AS refunded
FROM r GROUP BY customer_id
HAVING count(*) >= {min_returns} AND count(*) FILTER (WHERE condition <> 'resaleable') >= {min_not_resaleable}
),
delivered AS (
SELECT o.customer_id, count(*) AS delivered
FROM orders o JOIN agg a ON a.customer_id = o.customer_id
WHERE o.delivered_at <= {now} AND o.delivered_at > {now} - INTERVAL ({lookback_days}) DAY - INTERVAL 10 DAY
GROUP BY o.customer_id
),
marks AS ( -- when the pattern first showed: the account's min_not_resaleable-th bad return
SELECT customer_id, received_at,
row_number() OVER (PARTITION BY customer_id ORDER BY received_at) AS n
FROM returns
WHERE condition <> 'resaleable' AND received_at <= {now}
AND customer_id IN (SELECT customer_id FROM agg)
)
SELECT 'account' AS subject_type, c.id AS subject_id, c.id AS account_id, 'account:' || c.id AS scope,
mk.received_at AS started_at,
CASE WHEN a.not_resaleable >= 4 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'customer', c.name, 'customer_since', c.created_at, 'returns', a.returns,
'delivered_orders', d.delivered, 'return_rate', round(a.returns / d.delivered, 3),
'not_resaleable', a.not_resaleable, 'claims', a.claims, 'refunded', a.refunded,
'not_resaleable_share', round(a.not_resaleable / a.returns, 3), 'lookback_days', {lookback_days}
) AS evidence
FROM agg a
JOIN delivered d ON d.customer_id = a.customer_id
JOIN customers c ON c.id = a.customer_id
JOIN marks mk ON mk.customer_id = a.customer_id AND mk.n = {min_not_resaleable}
WHERE a.returns >= {min_return_rate} * d.delivered AND c.status <> 'blocked'
ORDER BY started_at, c.id