Changed to an address not on file address_change_fraud
An order whose delivery address was changed after payment to an address the account has never used, in another region, on a high-value basket from an established account: the shape of an account takeover.
Parameters: min_value = 200.0 min_account_age_days = 180
Test results
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| trust | count_between | passed | 0 | between 0.0 and 20.0 |
| trust | unique | passed | {'duplicates': 0} | order_id unique |
| trust | detector_shape | passed | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |
trust · v1 · trust and safety lead
The address changed (as of now) to one not on file and in a region other than the account's home region, the order is worth at least min_value, the account is at least min_account_age_days old, and the order is not already held, intercepted or released. Customers who move or send a gift look the same; whether they asked for the change in a message first is in the evidence, and the detector does not use it.
WITH changes AS (
SELECT order_id, max("at") AS changed_at, arg_max(payload, "at") AS payload
FROM order_events
WHERE type = 'address_changed' AND "at" <= {now} AND "at" >= {period_start} AND "at" < {period_end}
GROUP BY order_id
),
handled AS (
SELECT DISTINCT order_id FROM order_events
WHERE type IN ('held', 'intercept_requested', 'released') AND "at" <= {now}
),
asked AS (
SELECT m.order_id, max(m.at) AS asked_at FROM customer_messages m
WHERE m.topic = 'address_update_request' AND m.at <= {now} GROUP BY m.order_id
)
SELECT 'order' AS subject_type, o.id AS subject_id, o.id AS order_id, o.customer_id AS account_id,
o.marketplace_id, 'order:' || o.id AS scope, ch.changed_at AS started_at,
CASE WHEN o.total >= 500 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'order_no', o.order_no, 'total', o.total, 'marketplace', m.name,
'account_age_days', date_diff('day', c.created_at, {now}),
'minutes_after_payment', date_diff('minute', o.paid_at, ch.changed_at),
'home_region', hr.name, 'new_region', nr.name,
'new_address', json_extract_string(ch.payload, '$.to_line1'),
'asked_in_message', a.asked_at IS NOT NULL,
'stage', CASE WHEN o.delivered_at <= {now} THEN 'delivered' WHEN o.shipped_at <= {now} THEN 'shipped'
WHEN o.picked_at <= {now} THEN 'picked' ELSE 'paid' END
) AS evidence
FROM changes ch
JOIN orders o ON o.id = ch.order_id
JOIN customers c ON c.id = o.customer_id
JOIN marketplaces m ON m.id = o.marketplace_id
JOIN regions hr ON hr.id = c.region_id
JOIN regions nr ON nr.id = CAST(json_extract(ch.payload, '$.to_region_id') AS INTEGER)
LEFT JOIN handled h ON h.order_id = o.id
LEFT JOIN asked a ON a.order_id = o.id
WHERE NOT CAST(json_extract(ch.payload, '$.on_file') AS BOOLEAN)
AND nr.id <> c.region_id
AND o.total >= {min_value}
AND date_diff('day', c.created_at, {now}) >= {min_account_age_days}
AND h.order_id IS NULL
AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
ORDER BY started_at, o.id