Delivery SLA Status

{
  "transaction": {
    "transactionID": "ORD1029384",
    "fulfillmentMethod": "ship_to_home",
    "item": [
      {
        "productInfo": {
          "productID": "SKU12345"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "transaction_id": "ORD1029384",
  "currency": "USD",
  "value": 149.98,
  "fulfillment_method": "ship_to_home",
  "items": [
    {
      "item_id": "SKU12345",
      "item_name": "Wireless Headphones",
      "quantity": 1,
      "price": 149.98
    }
  ]
}

{
  "event": "purchase",
  "order": {
    "orderId": "ORD1029384",
    "fulfillmentMethod": "ship_to_home"
  },
  "items": [
    {
      "sku": "SKU12345"
    }
  ]
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "order": {
      "purchaseID": "ORD1029384"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar_fulfillment_method": "ship_to_home"
        }
      }
    }
  }
}

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

-- Orders placed in the window with a carrier leg (BOPIS and curbside excluded)
WITH purchases AS (
  SELECT DISTINCT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'purchase'
    AND COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'fulfillment_method'), '') NOT IN ('bopis', 'curbside')
),
-- Packages of those orders from order management, excluding cancelled shipments
packages AS (
  SELECT s.shipment_id, s.order_id, s.tracking_number, s.destination_timezone,
         s.checkout_promise_start_date, s.checkout_promise_end_date
  FROM `your-project.oms.shipments` AS s
  JOIN purchases AS pu ON pu.transaction_id = s.order_id
  WHERE s.shipment_status != 'cancelled'
),
-- Latest customer-requested reschedule per package; carrier re-estimates never change the promise
reschedules AS (
  SELECT shipment_id, new_promise_start_date, new_promise_end_date
  FROM `your-project.oms.delivery_promise_changes`
  WHERE change_source = 'customer_reschedule'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY changed_at DESC) = 1
),
-- First carrier delivered scan per tracking number; failed attempts leave the package open
delivered AS (
  SELECT tracking_number, MIN(scan_timestamp) AS delivered_at
  FROM `your-project.oms.carrier_tracking_events`
  WHERE scan_type = 'delivered'
  GROUP BY tracking_number
),
-- Tracking numbers with a delivery exception still open
open_exceptions AS (
  SELECT DISTINCT tracking_number
  FROM `your-project.oms.delivery_exceptions`
  WHERE resolved_at IS NULL
),
-- Effective promise window (checkout promise unless rescheduled by the customer) and local delivery date per package
promised AS (
  SELECT p.shipment_id, p.order_id,
         COALESCE(r.new_promise_start_date, p.checkout_promise_start_date) AS promise_start,
         COALESCE(r.new_promise_end_date, p.checkout_promise_end_date) AS promise_end,
         DATE(d.delivered_at, p.destination_timezone) AS delivered_date,
         CURRENT_DATE(p.destination_timezone) AS local_today,
         x.tracking_number IS NOT NULL AS has_open_exception
  FROM packages AS p
  LEFT JOIN reschedules AS r USING (shipment_id)
  LEFT JOIN delivered AS d USING (tracking_number)
  LEFT JOIN open_exceptions AS x USING (tracking_number)
),
-- Per-package status: delivered packages are final; open packages turn Late the day after the latest promised date
classified AS (
  SELECT shipment_id, order_id,
         CASE
           WHEN delivered_date < promise_start THEN 'Early'
           WHEN delivered_date <= promise_end THEN 'On Time'
           WHEN delivered_date IS NOT NULL THEN 'Late'
           WHEN local_today > promise_end THEN 'Late'
           WHEN has_open_exception THEN 'At Risk'
           ELSE 'In Transit'
         END AS delivery_sla_status
  FROM promised
)
-- The order is On Time only when every package is Early or On Time
SELECT shipment_id, order_id, delivery_sla_status,
       LOGICAL_AND(delivery_sla_status IN ('Early', 'On Time')) OVER (PARTITION BY order_id) AS order_on_time
FROM classified;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 days
WITH params AS (
  SELECT CAST(CURRENT_DATE - INTERVAL '90' DAY AS DATE) AS start_date,
         CURRENT_DATE AS end_date
),
-- Orders placed in the window with a carrier leg (BOPIS and curbside excluded)
purchases AS (
  SELECT DISTINCT e.commerce.order.purchaseID AS transaction_id
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE e.eventType = 'commerce.purchases'
    AND CAST(e.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
    AND COALESCE(e._experience.analytics.customDimensions.eVars.eVar_fulfillment_method, '') NOT IN ('bopis', 'curbside')
),
-- Packages of those orders from order management, excluding cancelled shipments
packages AS (
  SELECT s.shipment_id, s.order_id, s.tracking_number, s.destination_timezone,
         s.checkout_promise_start_date, s.checkout_promise_end_date
  FROM oms_shipments AS s
  JOIN purchases AS pu ON pu.transaction_id = s.order_id
  WHERE s.shipment_status != 'cancelled'
),
-- Latest customer-requested reschedule per package; carrier re-estimates never change the promise
reschedules AS (
  SELECT shipment_id, new_promise_start_date, new_promise_end_date
  FROM (
    SELECT shipment_id, new_promise_start_date, new_promise_end_date,
           ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY changed_at DESC) AS rn
    FROM oms_delivery_promise_changes
    WHERE change_source = 'customer_reschedule'
  ) AS c
  WHERE rn = 1
),
-- First carrier delivered scan per tracking number; failed attempts leave the package open
delivered AS (
  SELECT tracking_number, MIN(scan_timestamp) AS delivered_at
  FROM carrier_tracking_events
  WHERE scan_type = 'delivered'
  GROUP BY tracking_number
),
-- Tracking numbers with a delivery exception still open
open_exceptions AS (
  SELECT DISTINCT tracking_number
  FROM delivery_exceptions
  WHERE resolved_at IS NULL
),
-- Effective promise window (checkout promise unless rescheduled by the customer) and local delivery date per package
promised AS (
  SELECT p.shipment_id, p.order_id,
         COALESCE(r.new_promise_start_date, p.checkout_promise_start_date) AS promise_start,
         COALESCE(r.new_promise_end_date, p.checkout_promise_end_date) AS promise_end,
         TO_DATE(FROM_UTC_TIMESTAMP(d.delivered_at, p.destination_timezone)) AS delivered_date,
         TO_DATE(FROM_UTC_TIMESTAMP(CURRENT_TIMESTAMP, p.destination_timezone)) AS local_today,
         x.tracking_number IS NOT NULL AS has_open_exception
  FROM packages AS p
  LEFT JOIN reschedules AS r ON r.shipment_id = p.shipment_id
  LEFT JOIN delivered AS d ON d.tracking_number = p.tracking_number
  LEFT JOIN open_exceptions AS x ON x.tracking_number = p.tracking_number
),
-- Per-package status: delivered packages are final; open packages turn Late the day after the latest promised date
classified AS (
  SELECT shipment_id, order_id,
         CASE
           WHEN delivered_date < promise_start THEN 'Early'
           WHEN delivered_date <= promise_end THEN 'On Time'
           WHEN delivered_date IS NOT NULL THEN 'Late'
           WHEN local_today > promise_end THEN 'Late'
           WHEN has_open_exception THEN 'At Risk'
           ELSE 'In Transit'
         END AS delivery_sla_status
  FROM promised
)
-- The order is On Time only when every package is Early or On Time
SELECT shipment_id, order_id, delivery_sla_status,
       BOOL_AND(delivery_sla_status IN ('Early', 'On Time')) OVER (PARTITION BY order_id) AS order_on_time
FROM classified;