Shipment Volume

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Shipment Volume from the backend OMS shipment tables (no web events involved).
DECLARE window_start TIMESTAMP DEFAULT TIMESTAMP('2026-09-01 00:00:00');
DECLARE window_end   TIMESTAMP DEFAULT TIMESTAMP('2026-10-01 00:00:00');

WITH first_dispatch AS (
  -- earliest shipped / carrier-accepted status ever recorded per shipment
  SELECT
    e.shipment_id,
    MIN(e.status_timestamp) AS first_shipped_at
  FROM `project.oms.shipment_status_events` AS e
  WHERE e.canonical_status IN ('SHIPPED', 'CARRIER_ACCEPTED')
  GROUP BY e.shipment_id
),
customer_shipments AS (
  SELECT
    s.shipment_id,
    s.order_id,
    s.shipment_type,
    s.carrier,
    s.service_level,
    f.first_shipped_at
  FROM first_dispatch AS f
  JOIN `project.oms.shipments` AS s
    ON s.shipment_id = f.shipment_id
  WHERE COALESCE(s.is_test, FALSE) = FALSE
    AND COALESCE(s.fraud_status, '') <> 'REJECTED'
    AND COALESCE(s.voided_before_dispatch, FALSE) = FALSE
    AND s.shipment_type IN ('CUSTOMER_ORDER', 'REPLACEMENT', 'RESHIP')
)
SELECT
  DATE(first_shipped_at) AS shipment_date,
  carrier,
  service_level,
  COUNT(DISTINCT shipment_id) AS shipment_volume,
  COUNT(DISTINCT order_id) AS orders_shipped,
  SAFE_DIVIDE(COUNT(DISTINCT shipment_id), COUNT(DISTINCT order_id)) AS shipments_per_order
FROM customer_shipments
WHERE first_shipped_at >= window_start
  AND first_shipped_at < window_end
GROUP BY shipment_date, carrier, service_level
ORDER BY shipment_date, carrier, service_level;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Shipment Volume from the OMS shipment datasets ingested into AEP (backend records, not web events).
WITH first_dispatch AS (
  SELECT
    e.shipment_id,
    MIN(e.status_timestamp) AS first_shipped_at
  FROM oms_shipment_status_events e
  WHERE e.canonical_status IN ('SHIPPED', 'CARRIER_ACCEPTED')
  GROUP BY e.shipment_id
),
customer_shipments AS (
  SELECT
    s.shipment_id,
    s.order_id,
    s.shipment_type,
    s.carrier,
    s.service_level,
    f.first_shipped_at
  FROM first_dispatch f
  JOIN oms_shipments s
    ON s.shipment_id = f.shipment_id
  WHERE COALESCE(s.is_test, FALSE) = FALSE
    AND COALESCE(s.fraud_status, '') <> 'REJECTED'
    AND COALESCE(s.voided_before_dispatch, FALSE) = FALSE
    AND s.shipment_type IN ('CUSTOMER_ORDER', 'REPLACEMENT', 'RESHIP')
)
SELECT
  CAST(first_shipped_at AS DATE) AS shipment_date,
  carrier,
  service_level,
  COUNT(DISTINCT shipment_id) AS shipment_volume,
  COUNT(DISTINCT order_id) AS orders_shipped,
  CAST(COUNT(DISTINCT shipment_id) AS DOUBLE) / NULLIF(COUNT(DISTINCT order_id), 0) AS shipments_per_order
FROM customer_shipments
WHERE first_shipped_at >= TIMESTAMP '2026-09-01 00:00:00'
  AND first_shipped_at < TIMESTAMP '2026-10-01 00:00:00'
GROUP BY CAST(first_shipped_at AS DATE), carrier, service_level
ORDER BY shipment_date, carrier, service_level;