maiven gateway · online··
manifest · 2d ago
Sign in
Back to catalog
MAIVENmodelmarts

mart_shipments_daily

certified
mart incremental

How quickly do parcels reach customers, by carrier.

Lineage Builds
Definition

Daily aggregate of shipments with SLA outcomes. `on_time_count` requires `delivered_at IS NOT NULL` AND `estimated_delivery_at IS NOT NULL` — in-flight or no-ETA shipments don't contribute to on-time rate. `avg_transit_hours` is the post-pickup time; `avg_ship_lag_hours` is order-to-label.

What it means
Watch out for
  • highTreating `on_time_count / shipment_count` as the on-time rate

    Denominator must be `delivered_count`, not `shipment_count` — in-flight shipments haven't been scored yet. Use the `on_time_delivery_rate` formula measure.

  • lowExcluding `label_created` from the surface

    Label-only rows have `shipped_day` coalesced to 1970-01-01 so they remain MergeTree-safe. Filter `shipped_day > '2000-01-01'` for hot-data analysis.

Questions this answers
  • “On-time delivery rate by carrier last month.”
  • “Average transit hours by service level over the last quarter.”
Related metrics
  • support_volume

    Returns and lost parcels drive support tickets.

  • revenue

    Fulfillment failures correlate with refunds.

References
13 of 13
Column
Type
Description
Tests · PII
shipped_day
Date
Date the carrier picked up. Coalesced to 1970-01-01 for label-only rows.
1 test
carrier
String
`ups` / `fedex` / `dhl` / `usps` / `royal_mail`.
1 test
service_level
String
`standard` / `express` / `overnight`. Coalesced to `unknown` for rows without a tier.
1 test
status
String
`label_created` / `in_transit` / `delivered` / `returned` / `lost`.
1 test
shipment_count
UInt64
Row count in the bucket.
2 tests
delivered_count
UInt64
Of `shipment_count`, how many are delivered. Denominator for on-time rate.
1 test
on_time_count
UInt64
Of `delivered_count`, how many met `estimated_delivery_at`.
1 test
late_count
UInt64
Of `delivered_count`, how many missed `estimated_delivery_at`.
1 test
returned_count
UInt64
Count of `status='returned'`.
1 test
lost_count
UInt64
Count of `status='lost'`.
1 test
avg_transit_hours
Nullable(Decimal(10, 2))
Mean transit hours (pickup → delivery) over delivered rows. Decimal(10,2).
avg_ship_lag_hours
Nullable(Decimal(10, 2))
Mean ship-lag hours (order → pickup). Decimal(10,2).
unique_orders
UInt64
Distinct order_id count.
1 test