Late shipment late_shipment
An order that went out late. The marketplaces judge it by the ship-by time; customer service judges it by the delivery date the customer was shown. Both are right for their purpose, so the term has two definitions and no canonical one.
Parameters: grace_minutes = 0
marketplaces · v1 · head of marketplaces
Shipped in the period, after its ship-by time plus grace_minutes. This is the number the marketplaces use for the late-shipment rate on the seller scorecard.
SELECT o.id AS order_id, o.order_no, m.name AS marketplace, o.ship_by, o.shipped_at,
date_diff('minute', o.ship_by, o.shipped_at) AS minutes_late
FROM orders o JOIN marketplaces m ON m.id = o.marketplace_id
WHERE o.shipped_at >= {period_start} AND o.shipped_at < {period_end} AND o.shipped_at <= {now}
AND o.shipped_at > o.ship_by + INTERVAL ({grace_minutes}) MINUTE
ORDER BY o.shipped_at
customer_service · v1 · head of customer service
Delivered in the period after the promised date: the ship-by date plus the lane's normal transit days, at the end of that day. A parcel that left on time but sat in a carrier depot is late here and on time for the marketplace.
SELECT o.id AS order_id, o.order_no, m.name AS marketplace, o.ship_by, o.delivered_at,
CAST(o.ship_by AS DATE) + ln.transit_days + 1 AS promised_by
FROM orders o JOIN marketplaces m ON m.id = o.marketplace_id JOIN lanes ln ON ln.id = o.lane_id
WHERE o.delivered_at >= {period_start} AND o.delivered_at < {period_end} AND o.delivered_at <= {now}
AND o.delivered_at >= CAST(CAST(o.ship_by AS DATE) + ln.transit_days + 1 AS TIMESTAMP)
ORDER BY o.delivered_at