Delivery Exception Type

{
  "event": [
    {
      "page": {
        "pageInfo": {
          "pageName": "Order Status",
          "pageType": "order_status"
        }
      },
      "shipment": {
        "orderID": "",
        "trackingNumber": "",
        "carrier": "",
        "deliveryExceptionType": "",
        "exceptionTimestamp": "",
        "shipmentMode": "",
        "fulfillmentMethod": "",
        "deliveryRegion": "",
        "shippingTier": ""
      }
    }
  ]
}

{
  "event": "page_view",
  "page_type": "order_status",
  "order_id": "ORD-100234",
  "carrier": "UPS",
  "tracking_number": "1Z999AA10123456784",
  "delivery_exception_type": "address_issue",
  "exception_timestamp": "2026-09-23T14:12:00Z",
  "shipment_mode": "Standard",
  "fulfillment_method": "ship_to_home",
  "delivery_region": "US-West",
  "shipping_tier": "Standard"
}

{
  "event": "pageView",
  "page": {
    "pageType": "order_status"
  },
  "shipment": {
    "orderId": "ORD-100234",
    "carrier": "UPS",
    "trackingNumber": "1Z999AA10123456784",
    "deliveryExceptionType": "address_issue",
    "exceptionTimestamp": "2026-09-23T14:12:00Z",
    "shipmentMode": "Standard",
    "fulfillmentMethod": "ship_to_home",
    "deliveryRegion": "US-West",
    "shippingTier": "Standard"
  }
}

{
  "eventType": "pageView",
  "page": {
    "pageType": "order_status"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar12": "shipment.deliveryExceptionType"
        }
      }
    }
  },
  "shipment": {
    "orderId": "{{orderId}}",
    "trackingNumber": "{{trackingNumber}}",
    "carrier": "{{carrier}}",
    "shipmentMode": "{{shipmentMode}}",
    "fulfillmentMethod": "{{fulfillmentMethod}}",
    "deliveryExceptionType": "{{deliveryExceptionType}}",
    "exceptionTimestamp": "{{exceptionTimestamp}}",
    "deliveryRegion": "{{deliveryRegion}}",
    "shippingTier": "{{shippingTier}}"
  }
}

-- ==========================================
-- 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 scans AS (
  -- Exception scans seen on tracking pages, one row per package scan, UTC timestamp (source rank 2)
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'tracking_number') AS tracking_number,
    LOWER(TRIM((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'delivery_exception_type'))) AS raw_type,
    SAFE.TIMESTAMP((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'exception_timestamp')) AS scan_ts,
    2 AS source_rank
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL 1 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 1 DAY))
    AND event_name = 'page_view'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type') = 'order_status'
  UNION ALL
  -- OMS carrier exception events for the same packages (source rank 1, wins on conflict)
  SELECT tracking_number, LOWER(TRIM(exception_type)), scan_timestamp_utc, 1
  FROM `your-project.oms.carrier_events`
  WHERE event_category = 'exception'
),
mapped AS (
  -- Map raw codes to the fixed value list (attempted delivery = recipient_unavailable, unmapped = other); drop customer holds/reroutes and empty values
  SELECT
    tracking_number,
    scan_ts,
    source_rank,
    CASE
      WHEN raw_type IN ('weather', 'weather_delay', 'severe_weather') THEN 'weather'
      WHEN raw_type IN ('address_issue', 'incorrect_address', 'bad_address', 'incomplete_address') THEN 'address_issue'
      WHEN raw_type IN ('damaged', 'damage', 'package_damaged') THEN 'damaged'
      WHEN raw_type IN ('recipient_unavailable', 'attempted_delivery', 'delivery_attempted', 'notice_left') THEN 'recipient_unavailable'
      WHEN raw_type IN ('held_at_facility', 'facility_hold', 'held_at_terminal') THEN 'held_at_facility'
      ELSE 'other'
    END AS delivery_exception_type
  FROM scans
  WHERE tracking_number IS NOT NULL
    AND raw_type IS NOT NULL
    AND raw_type NOT IN ('', 'none', 'customer_hold', 'customer_requested_hold', 'customer_reroute', 'reroute_requested')
    AND DATE(scan_ts, 'UTC') BETWEEN start_date AND end_date
)
-- One value per package: OMS scans first, then the last exception scan, dated by the carrier exception scan (UTC)
SELECT
  tracking_number,
  DATE(scan_ts, 'UTC') AS exception_date,
  delivery_exception_type
FROM mapped
QUALIFY ROW_NUMBER() OVER (PARTITION BY tracking_number ORDER BY source_rank, scan_ts DESC) = 1;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Relative reporting window (UTC dates)
  SELECT CAST(CURRENT_DATE - INTERVAL '90' DAY AS DATE) AS start_date, CAST(CURRENT_DATE - INTERVAL '1' DAY AS DATE) AS end_date
),
scans AS (
  -- Exception scans seen on tracking pages, one row per package scan, UTC timestamp (source rank 2)
  SELECT
    e.shipment.trackingNumber AS tracking_number,
    LOWER(TRIM(e.shipment.deliveryExceptionType)) AS raw_type,
    to_timestamp(e.shipment.exceptionTimestamp) AS scan_ts,
    2 AS source_rank
  FROM web_events_dataset e
  WHERE e.eventType = 'pageView'
    AND e.page.pageType = 'order_status'
  UNION ALL
  -- OMS carrier exception events for the same packages (source rank 1, wins on conflict)
  SELECT tracking_number, LOWER(TRIM(exception_type)), scan_timestamp_utc, 1
  FROM oms_carrier_events
  WHERE event_category = 'exception'
),
mapped AS (
  -- Map raw codes to the fixed value list (attempted delivery = recipient_unavailable, unmapped = other); drop customer holds/reroutes and empty values
  SELECT
    s.tracking_number,
    s.scan_ts,
    s.source_rank,
    CASE
      WHEN s.raw_type IN ('weather', 'weather_delay', 'severe_weather') THEN 'weather'
      WHEN s.raw_type IN ('address_issue', 'incorrect_address', 'bad_address', 'incomplete_address') THEN 'address_issue'
      WHEN s.raw_type IN ('damaged', 'damage', 'package_damaged') THEN 'damaged'
      WHEN s.raw_type IN ('recipient_unavailable', 'attempted_delivery', 'delivery_attempted', 'notice_left') THEN 'recipient_unavailable'
      WHEN s.raw_type IN ('held_at_facility', 'facility_hold', 'held_at_terminal') THEN 'held_at_facility'
      ELSE 'other'
    END AS delivery_exception_type
  FROM scans s
  CROSS JOIN params p
  WHERE s.tracking_number IS NOT NULL
    AND s.raw_type IS NOT NULL
    AND s.raw_type NOT IN ('', 'none', 'customer_hold', 'customer_requested_hold', 'customer_reroute', 'reroute_requested')
    AND CAST(s.scan_ts AS DATE) BETWEEN p.start_date AND p.end_date
)
-- One value per package: OMS scans first, then the last exception scan, dated by the carrier exception scan (UTC)
SELECT tracking_number, CAST(scan_ts AS DATE) AS exception_date, delivery_exception_type
FROM (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY tracking_number ORDER BY source_rank, scan_ts DESC) AS rn
  FROM mapped m
) ranked
WHERE rn = 1;