Delivery Attempt Number

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Backend source: OMS shipment tracking scans, not GA4 export tables
-- Relative reporting window: last 90 complete days
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Shipments with any scan in the window, every fulfillment method kept (including NULL)
WITH shipments AS (
  SELECT DISTINCT s.tracking_number, s.fulfillment_method
  FROM `your-project.oms.shipments` AS s
  JOIN `your-project.oms.shipment_tracking_scans` AS sc ON sc.tracking_number = s.tracking_number
  WHERE sc.scan_local_date BETWEEN start_date AND end_date
),

-- First terminal scan per shipment, where the value freezes
terminal AS (
  SELECT tracking_number, scan_timestamp AS terminal_ts, scan_id AS terminal_scan_id
  FROM (
    SELECT
      sc.tracking_number, sc.scan_timestamp, sc.scan_id,
      ROW_NUMBER() OVER (PARTITION BY sc.tracking_number ORDER BY sc.scan_timestamp, sc.scan_id) AS terminal_rank
    FROM `your-project.oms.shipment_tracking_scans` AS sc
    JOIN shipments USING (tracking_number)
    WHERE sc.scan_type IN ('DELIVERED', 'REDIRECTED_TO_PICKUP_POINT', 'RETURNED_TO_SENDER')
      AND sc.scan_local_date <= end_date
  )
  WHERE terminal_rank = 1
),

-- Relevant scans at or before the first terminal scan; repeated attempted scans on the same local day count once
scans AS (
  SELECT tracking_number, scan_type, scan_timestamp, scan_id
  FROM (
    SELECT
      sc.tracking_number, sc.scan_type, sc.scan_timestamp, sc.scan_id,
      ROW_NUMBER() OVER (PARTITION BY sc.tracking_number, sc.scan_type, sc.scan_local_date ORDER BY sc.scan_timestamp, sc.scan_id) AS same_day_rank
    FROM `your-project.oms.shipment_tracking_scans` AS sc
    JOIN shipments AS sh ON sh.tracking_number = sc.tracking_number
    LEFT JOIN terminal AS t ON t.tracking_number = sc.tracking_number
    WHERE sc.scan_type IN ('OUT_FOR_DELIVERY', 'DELIVERY_ATTEMPTED', 'DELIVERED')
      AND sc.scan_local_date <= end_date
      AND (t.terminal_ts IS NULL
        OR sc.scan_timestamp < t.terminal_ts
        OR (sc.scan_timestamp = t.terminal_ts AND sc.scan_id <= t.terminal_scan_id))
  )
  WHERE scan_type <> 'DELIVERY_ATTEMPTED' OR same_day_rank = 1
),

-- An attempt is an attempted or delivered scan that follows an out-for-delivery scan
ordered AS (
  SELECT
    tracking_number, scan_type,
    LAG(scan_type) OVER (PARTITION BY tracking_number ORDER BY scan_timestamp, scan_id) AS prev_scan_type
  FROM scans
),
attempts AS (
  SELECT tracking_number, COUNT(*) AS attempt_number
  FROM ordered
  WHERE scan_type IN ('DELIVERY_ATTEMPTED', 'DELIVERED')
    AND prev_scan_type = 'OUT_FOR_DELIVERY'
  GROUP BY tracking_number
)

-- Per-shipment value: 1, 2, 3 or 4+; store pickup, ship-to-store and no attempt yet are (not set)
SELECT
  s.tracking_number,
  CASE
    WHEN s.fulfillment_method IN ('store_pickup', 'ship_to_store') THEN '(not set)'
    WHEN a.attempt_number IS NULL THEN '(not set)'
    WHEN a.attempt_number >= 4 THEN '4+'
    ELSE CAST(a.attempt_number AS STRING)
  END AS delivery_attempt_number
FROM shipments AS s
LEFT JOIN attempts AS a ON a.tracking_number = s.tracking_number;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Backend source: OMS shipment tracking scans, not web event datasets
-- Relative reporting window: last 90 complete days
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date,
         date_sub(current_date(), 1) AS end_date
),

-- Shipments with any scan in the window, every fulfillment method kept (including NULL)
shipments AS (
  SELECT DISTINCT s.tracking_number, s.fulfillment_method
  FROM oms_shipments s
  JOIN oms_shipment_tracking_scans sc ON sc.tracking_number = s.tracking_number
  CROSS JOIN params p
  WHERE sc.scan_local_date BETWEEN p.start_date AND p.end_date
),

-- First terminal scan per shipment, where the value freezes
terminal AS (
  SELECT tracking_number, scan_timestamp AS terminal_ts, scan_id AS terminal_scan_id
  FROM (
    SELECT
      sc.tracking_number, sc.scan_timestamp, sc.scan_id,
      ROW_NUMBER() OVER (PARTITION BY sc.tracking_number ORDER BY sc.scan_timestamp, sc.scan_id) AS terminal_rank
    FROM oms_shipment_tracking_scans sc
    JOIN shipments sh ON sh.tracking_number = sc.tracking_number
    CROSS JOIN params p
    WHERE sc.scan_type IN ('DELIVERED', 'REDIRECTED_TO_PICKUP_POINT', 'RETURNED_TO_SENDER')
      AND sc.scan_local_date <= p.end_date
  ) r
  WHERE terminal_rank = 1
),

-- Relevant scans at or before the first terminal scan; repeated attempted scans on the same local day count once
scans AS (
  SELECT tracking_number, scan_type, scan_timestamp, scan_id
  FROM (
    SELECT
      sc.tracking_number, sc.scan_type, sc.scan_timestamp, sc.scan_id,
      ROW_NUMBER() OVER (PARTITION BY sc.tracking_number, sc.scan_type, sc.scan_local_date ORDER BY sc.scan_timestamp, sc.scan_id) AS same_day_rank
    FROM oms_shipment_tracking_scans sc
    JOIN shipments sh ON sh.tracking_number = sc.tracking_number
    LEFT JOIN terminal t ON t.tracking_number = sc.tracking_number
    CROSS JOIN params p
    WHERE sc.scan_type IN ('OUT_FOR_DELIVERY', 'DELIVERY_ATTEMPTED', 'DELIVERED')
      AND sc.scan_local_date <= p.end_date
      AND (t.terminal_ts IS NULL
        OR sc.scan_timestamp < t.terminal_ts
        OR (sc.scan_timestamp = t.terminal_ts AND sc.scan_id <= t.terminal_scan_id))
  ) d
  WHERE scan_type <> 'DELIVERY_ATTEMPTED' OR same_day_rank = 1
),

-- An attempt is an attempted or delivered scan that follows an out-for-delivery scan
ordered AS (
  SELECT
    tracking_number, scan_type,
    LAG(scan_type) OVER (PARTITION BY tracking_number ORDER BY scan_timestamp, scan_id) AS prev_scan_type
  FROM scans
),
attempts AS (
  SELECT tracking_number, COUNT(*) AS attempt_number
  FROM ordered
  WHERE scan_type IN ('DELIVERY_ATTEMPTED', 'DELIVERED')
    AND prev_scan_type = 'OUT_FOR_DELIVERY'
  GROUP BY tracking_number
)

-- Per-shipment value: 1, 2, 3 or 4+; store pickup, ship-to-store and no attempt yet are (not set)
SELECT
  s.tracking_number,
  CASE
    WHEN s.fulfillment_method IN ('store_pickup', 'ship_to_store') THEN '(not set)'
    WHEN a.attempt_number IS NULL THEN '(not set)'
    WHEN a.attempt_number >= 4 THEN '4+'
    ELSE CAST(a.attempt_number AS STRING)
  END AS delivery_attempt_number
FROM shipments s
LEFT JOIN attempts a ON a.tracking_number = s.tracking_number;