Shipment Status

{
  "page": {
    "pageInfo": {
      "pageName": "Shipment Tracking",
      "pageType": "order_tracking"
    }
  },
  "order": {
    "transactionID": "ORD-100293",
    "shipmentStatus": "Out for Delivery",
    "trackingNumber": "1Z999AA10123456784"
  }
}

{
  "event": "page_view",
  "page_type": "order_tracking",
  "transaction_id": "ORD-100293",
  "tracking_number": "1Z999AA10123456784",
  "shipment_status": "Out for Delivery"
}

{
  "event": "pageView",
  "page": {
    "pageType": "order_tracking"
  },
  "order": {
    "transactionId": "ORD-100293",
    "trackingNumber": "1Z999AA10123456784",
    "shipmentStatus": "Out for Delivery"
  }
}

{
  "commerce": {
    "order": {
      "purchaseID": "ORD-100293"
    }
  },
  "_tenant": {
    "shipmentStatus": "Out for Delivery",
    "trackingNumber": "1Z999AA10123456784"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar12": "shipmentStatus"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH
-- Carrier scans normalised to the governed status list (unmapped codes become Unknown); return shipments excluded
scans AS (
  SELECT
    e.tracking_number,
    e.carrier_event_ts,
    COALESCE(m.shipment_status, 'Unknown') AS shipment_status
  FROM `your-project.oms_backend.carrier_tracking_events` AS e
  LEFT JOIN `your-project.oms_backend.carrier_status_map` AS m
    ON m.carrier = e.carrier
   AND m.carrier_status_code = e.carrier_status_code
  WHERE DATE(e.carrier_event_ts) BETWEEN start_date AND end_date
    AND COALESCE(e.shipment_direction, '') != 'return'
),
-- One status per tracking number: terminal statuses win, otherwise the newest carrier event timestamp (not webhook receipt)
latest AS (
  SELECT tracking_number, shipment_status
  FROM scans
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY tracking_number
    ORDER BY IF(shipment_status IN ('Delivered', 'Returned to Sender'), 1, 0) DESC, carrier_event_ts DESC
  ) = 1
)
-- Shipment Status: the latest normalised status per tracking number
SELECT tracking_number, shipment_status
FROM latest;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH
-- Relative reporting window: last 90 complete days
params AS (
  SELECT date_sub(current_date(), 90) AS start_date,
         date_sub(current_date(), 1) AS end_date
),
-- Carrier scans normalised to the governed status list (unmapped codes become Unknown); return shipments excluded
scans AS (
  SELECT
    e.tracking_number,
    e.carrier_event_ts,
    COALESCE(m.shipment_status, 'Unknown') AS shipment_status
  FROM oms_backend_carrier_tracking_events AS e
  LEFT JOIN oms_backend_carrier_status_map AS m
    ON m.carrier = e.carrier
   AND m.carrier_status_code = e.carrier_status_code
  CROSS JOIN params AS p
  WHERE CAST(e.carrier_event_ts AS DATE) BETWEEN p.start_date AND p.end_date
    AND COALESCE(e.shipment_direction, '') <> 'return'
),
-- One status per tracking number: terminal statuses win, otherwise the newest carrier event timestamp (not webhook receipt)
latest AS (
  SELECT tracking_number, shipment_status
  FROM (
    SELECT
      tracking_number,
      shipment_status,
      ROW_NUMBER() OVER (
        PARTITION BY tracking_number
        ORDER BY CASE WHEN shipment_status IN ('Delivered', 'Returned to Sender') THEN 1 ELSE 0 END DESC, carrier_event_ts DESC
      ) AS rn
    FROM scans
  ) AS ranked
  WHERE rn = 1
)
-- Shipment Status: the latest normalised status per tracking number
SELECT tracking_number, shipment_status
FROM latest;