order_milestones (event_id, order_id, milestone, event_ts) logs each order's lifecycle events. milestone is 'placed', 'paid', 'shipped', 'delivered' or 'cancelled'. Integrations retry, so the same milestone can be logged more than once for an order, and events don't always happen in lifecycle order (some orders are paid after they ship).
Build the order lifecycle fact: one row per order with
- •
placed_at, paid_at, shipped_at, delivered_at — when the order first reached each milestone (NULL if it never did) - •
days_to_ship — calendar days from the date it was placed to the date it shipped (dates of the timestamps; NULL if not shipped) - •
status — the furthest stage it has reached in lifecycle order (placed → paid → shipped → delivered), whatever order the events happened in; except that an order with a cancelled event is 'cancelled' — unless it was delivered: a cancellation after delivery is a return and doesn't change the status
Columns: order_id, placed_at, paid_at, shipped_at, delivered_at, days_to_ship, status. Sort by order_id.