maiven gateway · online··
manifest · 1d ago
Sign in Back to catalog
Definition
Per-shipment SLA join. Computes `ship_lag_hours` (order → label/pickup), `transit_hours` (pickup → delivery), and `on_time` (delivered_at ≤ estimated_delivery_at). All three are Nullable until the relevant lifecycle event lands; in-flight and un-shipped rows carry NULL where the math doesn't apply.
21 of 21
Column
Description
Tests · PII
shipment_id
From staging. Primary key.
shipping_address_id
From staging. Nullable.
ship_to_country
From `stg_postgres__addresses.country` (LEFT JOIN). Coalesced to `'unknown'`.
carrier
From staging.
service_level
From staging. Coalesced to `'unknown'`.
status
From staging.
tracking_number
From staging. Nullable.
shipped_at
From staging. Nullable for `label_created`.
shipped_day
From staging. Nullable.
estimated_delivery_at
From staging. Nullable.
delivered_at
From staging. Nullable until delivery.
delivered_day
From staging. Nullable.
order_at
From `stg_postgres__orders.order_at`.
ship_lag_hours
Derived — `dateDiff('hour', order_at, shipped_at)`. Nullable for un-shipped or order_at-missing.
transit_hours
Derived — `dateDiff('hour', shipped_at, delivered_at)`. Nullable for in-flight.
on_time
Derived — `delivered_at <= estimated_delivery_at`. Nullable for in-flight or no-ETA.
is_delivered
Derived — `status = 'delivered'`.
is_returned
Derived — `status = 'returned'`.
is_lost
Derived — `status = 'lost'`.