Rails 24:00 Orvane Home & Outdoor

← Definitions

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

Test results

meaningtestpassedobservedexpected
trustplanted_weekpassed34planted return_abuse incidents between 10.0 and 120.0
trustcount_betweenpassed5between 0.0 and 120.0
trustuniquepassed{'duplicates': 0}account_id unique
trustdetector_shapepassed{'missing_columns': [], 'bad_rows': 0, 'rows': 5}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

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.

Changed 2026-10-12: Agreed with the owner.

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

Tests: planted_week count_between unique