Delivery Promise Date

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "checkout:order confirmation",
        "pageType": "checkout"
      }
    },
    "transaction": {
      "transactionID": "T-100482",
      "total": {
        "currency": "USD",
        "basePrice": 129.98,
        "voucherCode": "SUMMER10",
        "voucherDiscount": 13,
        "shipping": 12.95,
        "shippingMethod": "Express (2-3 business days)",
        "transactionTotal": 129.93
      },
      "attributes": {
        "orderDate": "2026-09-14",
        "shippingTier": "Express (2-3 business days)",
        "deliveryPromiseDate": "2026-09-19",
        "deliveryPromiseEarliestDate": "2026-09-17",
        "deliveryPromiseLatestDate": "2026-09-19"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-20431",
            "productName": "Trail Running Shoe"
          },
          "category": {
            "primaryCategory": "Footwear"
          },
          "quantity": 1,
          "price": {
            "basePrice": 89.99,
            "currency": "USD"
          }
        },
        {
          "productInfo": {
            "productID": "SKU-11872",
            "productName": "Merino Running Socks"
          },
          "category": {
            "primaryCategory": "Accessories"
          },
          "quantity": 1,
          "price": {
            "basePrice": 39.99,
            "currency": "USD"
          }
        }
      ]
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "purchase",
          "eventAction": "order_placed",
          "type": "checkout"
        },
        "attributes": {
          "transactionID": "T-100482",
          "deliveryPromiseDate": "2026-09-19",
          "deliveryPromiseEarliestDate": "2026-09-17",
          "deliveryPromiseLatestDate": "2026-09-19"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "delivery_promise_date": "2026-09-19",
  "delivery_promise_earliest_date": "2026-09-17",
  "delivery_promise_latest_date": "2026-09-19",
  "ecommerce": {
    "transaction_id": "T-100482",
    "currency": "USD",
    "value": 129.93,
    "shipping": 12.95,
    "tax": 0,
    "coupon": "SUMMER10",
    "shipping_tier": "Express (2-3 business days)",
    "items": [
      {
        "item_id": "SKU-20431",
        "item_name": "Trail Running Shoe",
        "item_brand": "Northpeak",
        "item_category": "Footwear",
        "item_variant": "Blue / 10",
        "price": 89.99,
        "quantity": 1
      },
      {
        "item_id": "SKU-11872",
        "item_name": "Merino Running Socks",
        "item_brand": "Northpeak",
        "item_category": "Accessories",
        "price": 39.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "commerce:purchase",
  "eventInfo": {
    "eventName": "purchase",
    "checkoutStep": "confirmation"
  },
  "page": {
    "pageName": "checkout:order confirmation",
    "pageType": "checkout"
  },
  "commerce": {
    "order": {
      "purchaseID": "T-100482",
      "currency": "USD",
      "priceTotal": 129.93,
      "coupon": "SUMMER10"
    },
    "shipping": {
      "shippingTier": "Express (2-3 business days)",
      "shippingAmount": 12.95,
      "deliveryPromiseDate": "2026-09-19",
      "deliveryPromiseEarliestDate": "2026-09-17",
      "deliveryPromiseLatestDate": "2026-09-19"
    },
    "products": [
      {
        "SKU": "SKU-20431",
        "name": "Trail Running Shoe",
        "category": "Footwear",
        "priceTotal": 89.99,
        "quantity": 1
      },
      {
        "SKU": "SKU-11872",
        "name": "Merino Running Socks",
        "category": "Accessories",
        "priceTotal": 39.99,
        "quantity": 1
      }
    ]
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100482",
      "priceTotal": 129.93,
      "currencyCode": "USD"
    }
  },
  "shipping": {
    "shippingMethod": "Express (2-3 business days)",
    "shippingAmount": 12.95,
    "currencyCode": "USD"
  },
  "productListItems": [
    {
      "SKU": "SKU-20431",
      "name": "Trail Running Shoe",
      "priceTotal": 89.99,
      "quantity": 1
    },
    {
      "SKU": "SKU-11872",
      "name": "Merino Running Socks",
      "priceTotal": 39.99,
      "quantity": 1
    }
  ],
  "_yourtenant": {
    "delivery": {
      "promiseDate": "2026-09-19",
      "promiseEarliestDate": "2026-09-17",
      "promiseLatestDate": "2026-09-19"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar43": "2026-09-19",
          "eVar44": "2026-09-17",
          "eVar45": "2026-09-19"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Delivery Promise Date by order. Precedence: OMS promise stored on the order (canonical) > delivery_promise_date on purchase > last add_shipping_info in the same session before the purchase
-- A promise window resolves to its latest date; values are destination-local calendar dates parsed from ISO strings, never truncated from timestamps
WITH purchase_events AS (
  SELECT
    e.user_pseudo_id,
    e.event_timestamp,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
    COALESCE(
      NULLIF(MAX(e.ecommerce.transaction_id), '(not set)'),
      MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL))
    ) AS transaction_id,
    COALESCE(
      SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_latest_date', ep.value.string_value, NULL)))),
      SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_date', ep.value.string_value, NULL))))
    ) AS event_promise_date,
    SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_earliest_date', ep.value.string_value, NULL)))) AS event_promise_earliest_date,
    LOWER(COALESCE(MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL)), '')) AS shipping_tier_label,
    MAX(e.ecommerce.purchase_revenue) AS purchase_revenue
  FROM `your-project.analytics_123456789.events_*` AS e,
    UNNEST(e.event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'purchase'
  GROUP BY e.user_pseudo_id, e.event_timestamp, e.event_bundle_sequence_id
),
unique_purchases AS (
  SELECT *
  FROM purchase_events
  WHERE transaction_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),
checkout_selections AS (
  SELECT
    e.user_pseudo_id,
    e.event_timestamp,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
    COALESCE(
      SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_latest_date', ep.value.string_value, NULL)))),
      SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_date', ep.value.string_value, NULL))))
    ) AS promise_date,
    SAFE.PARSE_DATE('%Y-%m-%d', TRIM(MAX(IF(ep.key = 'delivery_promise_earliest_date', ep.value.string_value, NULL)))) AS promise_earliest_date
  FROM `your-project.analytics_123456789.events_*` AS e,
    UNNEST(e.event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'add_shipping_info'
  GROUP BY e.user_pseudo_id, e.event_timestamp, e.event_bundle_sequence_id
),
-- Re-selections supersede earlier ones; selections from other sessions (abandoned checkouts) are never carried into the order
last_selection_before_purchase AS (
  SELECT
    p.transaction_id,
    s.promise_date,
    s.promise_earliest_date
  FROM unique_purchases AS p
  JOIN checkout_selections AS s
    ON s.user_pseudo_id = p.user_pseudo_id
   AND s.ga_session_id = p.ga_session_id
   AND s.event_timestamp <= p.event_timestamp
  WHERE s.promise_date IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY p.transaction_id ORDER BY s.event_timestamp DESC) = 1
),
-- OMS delivery groups: order-level promise is the latest original promise across ship-to-address groups; revised promises live in separate columns and are not read
oms_order_promise AS (
  SELECT
    dg.order_id,
    COUNTIF(dg.fulfillment_type = 'ship_to_address') AS shipped_delivery_groups,
    MAX(IF(dg.fulfillment_type = 'ship_to_address', COALESCE(dg.promised_delivery_latest_date, dg.promised_delivery_date), NULL)) AS oms_promise_date,
    MAX(IF(dg.fulfillment_type = 'ship_to_address', dg.promised_delivery_earliest_date, NULL)) AS oms_promise_earliest_date
  FROM `your-project.oms.order_delivery_groups` AS dg
  WHERE dg.order_placed_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 32 DAY) AND CURRENT_DATE()
  GROUP BY dg.order_id
),
resolved AS (
  SELECT
    p.transaction_id,
    p.purchase_revenue,
    p.shipping_tier_label,
    COALESCE(o.shipped_delivery_groups = 0, FALSE) AS oms_not_applicable,
    CASE
      WHEN o.oms_promise_date IS NOT NULL THEN 'oms_order'
      WHEN p.event_promise_date IS NOT NULL THEN 'purchase_event'
      WHEN l.promise_date IS NOT NULL THEN 'session_add_shipping_info'
      ELSE 'unresolved'
    END AS promise_source,
    COALESCE(o.oms_promise_date, p.event_promise_date, l.promise_date) AS promise_date,
    CASE
      WHEN o.oms_promise_date IS NOT NULL THEN o.oms_promise_earliest_date
      WHEN p.event_promise_date IS NOT NULL THEN p.event_promise_earliest_date
      ELSE l.promise_earliest_date
    END AS promise_earliest_date,
    COALESCE(p.event_promise_date != l.promise_date, FALSE) AS selection_vs_purchase_mismatch,
    COALESCE(p.event_promise_date != o.oms_promise_date, FALSE) AS event_vs_oms_mismatch
  FROM unique_purchases AS p
  LEFT JOIN last_selection_before_purchase AS l
    ON l.transaction_id = p.transaction_id
  LEFT JOIN oms_order_promise AS o
    ON o.order_id = p.transaction_id
),
labelled AS (
  SELECT
    r.*,
    CASE
      WHEN r.oms_not_applicable THEN '(not applicable)'
      WHEN r.promise_date IS NULL AND REGEXP_CONTAINS(r.shipping_tier_label, r'pick ?up|in[ -]store|to store|digital|e-gift') THEN '(not applicable)'
      WHEN r.promise_date IS NULL THEN '(not set)'
      ELSE FORMAT_DATE('%Y-%m-%d', r.promise_date)
    END AS delivery_promise_date,
    CASE
      WHEN r.oms_not_applicable OR r.promise_date IS NULL THEN NULL
      WHEN r.promise_earliest_date IS NULL OR r.promise_earliest_date >= r.promise_date THEN FORMAT_DATE('%Y-%m-%d', r.promise_date)
      ELSE CONCAT(FORMAT_DATE('%Y-%m-%d', r.promise_earliest_date), ' to ', FORMAT_DATE('%Y-%m-%d', r.promise_date))
    END AS window_value
  FROM resolved AS r
),
totals AS (
  SELECT COUNT(DISTINCT transaction_id) AS all_orders
  FROM labelled
)
SELECT
  lb.delivery_promise_date,
  COALESCE(lb.window_value, lb.delivery_promise_date) AS delivery_promise_window,
  COUNT(DISTINCT lb.transaction_id) AS orders,
  ROUND(SUM(lb.purchase_revenue), 2) AS purchase_revenue,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT lb.transaction_id), MAX(t.all_orders)), 4) AS share_of_all_orders,
  COUNTIF(lb.promise_source = 'oms_order') AS orders_promise_from_oms,
  COUNTIF(lb.promise_source = 'purchase_event') AS orders_promise_from_purchase_event,
  COUNTIF(lb.promise_source = 'session_add_shipping_info') AS orders_promise_from_session_selection,
  COUNTIF(lb.selection_vs_purchase_mismatch) AS orders_selection_vs_purchase_mismatch,
  ROUND(SAFE_DIVIDE(COUNTIF(lb.selection_vs_purchase_mismatch), COUNT(DISTINCT lb.transaction_id)), 4) AS selection_vs_purchase_mismatch_rate,
  COUNTIF(lb.event_vs_oms_mismatch) AS orders_event_vs_oms_mismatch
FROM labelled AS lb
CROSS JOIN totals AS t
GROUP BY 1, 2
ORDER BY 1, 2;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Delivery Promise Date by order. Precedence: OMS promise stored on the order (canonical) > _yourtenant.delivery on commerce.purchases > last commerce.checkouts selection in the same 30-minute session before the purchase
-- A promise window resolves to its latest date; values are destination-local calendar dates parsed from ISO strings, never truncated from timestamps
WITH sessionized AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp AS event_ts,
    eventType AS event_type,
    commerce.order.purchaseID AS purchase_id,
    commerce.order.priceTotal AS order_total,
    LOWER(COALESCE(TRIM(shipping.shippingMethod), '')) AS shipping_tier_label,
    to_date(TRIM(CAST(_yourtenant.delivery.promiseDate AS STRING)), 'yyyy-MM-dd') AS promise_date_value,
    to_date(TRIM(CAST(_yourtenant.delivery.promiseEarliestDate AS STRING)), 'yyyy-MM-dd') AS promise_earliest_value,
    to_date(TRIM(CAST(_yourtenant.delivery.promiseLatestDate AS STRING)), 'yyyy-MM-dd') AS promise_latest_value,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY endUserIDs._experience.ecid.id
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM aep_web_events_dataset
  WHERE timestamp >= date_sub(current_date(), 30)
    AND timestamp < current_date()
    AND endUserIDs._experience.ecid.id IS NOT NULL
),
unique_purchases AS (
  SELECT ecid, session_num, event_ts, purchase_id, order_total, shipping_tier_label, event_promise_date, event_promise_earliest_date
  FROM (
    SELECT
      ecid,
      session.num AS session_num,
      event_ts,
      purchase_id,
      order_total,
      shipping_tier_label,
      COALESCE(promise_latest_value, promise_date_value) AS event_promise_date,
      promise_earliest_value AS event_promise_earliest_date,
      ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY event_ts) AS rn
    FROM sessionized
    WHERE event_type = 'commerce.purchases'
      AND purchase_id IS NOT NULL
  ) p
  WHERE rn = 1
),
checkout_selections AS (
  SELECT
    ecid,
    session.num AS session_num,
    event_ts,
    COALESCE(promise_latest_value, promise_date_value) AS promise_date,
    promise_earliest_value AS promise_earliest_date
  FROM sessionized
  WHERE event_type = 'commerce.checkouts'
    AND COALESCE(promise_latest_value, promise_date_value) IS NOT NULL
),
-- Re-selections supersede earlier ones; selections from other sessions (abandoned checkouts) are never carried into the order
last_selection_before_purchase AS (
  SELECT purchase_id, promise_date, promise_earliest_date
  FROM (
    SELECT
      p.purchase_id,
      s.promise_date,
      s.promise_earliest_date,
      ROW_NUMBER() OVER (PARTITION BY p.purchase_id ORDER BY s.event_ts DESC) AS rn
    FROM unique_purchases p
    JOIN checkout_selections s
      ON s.ecid = p.ecid
     AND s.session_num = p.session_num
     AND s.event_ts <= p.event_ts
  ) ranked
  WHERE rn = 1
),
-- OMS delivery groups: order-level promise is the latest original promise across ship-to-address groups; revised promises live in separate columns and are not read
oms_order_promise AS (
  SELECT
    order_id,
    SUM(CASE WHEN fulfillment_type = 'ship_to_address' THEN 1 ELSE 0 END) AS shipped_delivery_groups,
    MAX(CASE WHEN fulfillment_type = 'ship_to_address' THEN COALESCE(promised_delivery_latest_date, promised_delivery_date) END) AS oms_promise_date,
    MAX(CASE WHEN fulfillment_type = 'ship_to_address' THEN promised_delivery_earliest_date END) AS oms_promise_earliest_date
  FROM aep_backend_oms_delivery_groups
  WHERE order_placed_date >= date_sub(current_date(), 32)
  GROUP BY order_id
),
resolved AS (
  SELECT
    p.purchase_id,
    p.order_total,
    p.shipping_tier_label,
    COALESCE(o.shipped_delivery_groups = 0, FALSE) AS oms_not_applicable,
    CASE
      WHEN o.oms_promise_date IS NOT NULL THEN 'oms_order'
      WHEN p.event_promise_date IS NOT NULL THEN 'purchase_event'
      WHEN l.promise_date IS NOT NULL THEN 'session_checkout_selection'
      ELSE 'unresolved'
    END AS promise_source,
    COALESCE(o.oms_promise_date, p.event_promise_date, l.promise_date) AS promise_date,
    CASE
      WHEN o.oms_promise_date IS NOT NULL THEN o.oms_promise_earliest_date
      WHEN p.event_promise_date IS NOT NULL THEN p.event_promise_earliest_date
      ELSE l.promise_earliest_date
    END AS promise_earliest_date,
    COALESCE(p.event_promise_date <> l.promise_date, FALSE) AS selection_vs_purchase_mismatch,
    COALESCE(p.event_promise_date <> o.oms_promise_date, FALSE) AS event_vs_oms_mismatch
  FROM unique_purchases p
  LEFT JOIN last_selection_before_purchase l
    ON l.purchase_id = p.purchase_id
  LEFT JOIN oms_order_promise o
    ON o.order_id = p.purchase_id
),
labelled AS (
  SELECT
    r.*,
    CASE
      WHEN r.oms_not_applicable THEN '(not applicable)'
      WHEN r.promise_date IS NULL AND r.shipping_tier_label RLIKE 'pick ?up|in[ -]store|to store|digital|e-gift' THEN '(not applicable)'
      WHEN r.promise_date IS NULL THEN '(not set)'
      ELSE date_format(r.promise_date, 'yyyy-MM-dd')
    END AS delivery_promise_date,
    CASE
      WHEN r.oms_not_applicable OR r.promise_date IS NULL THEN NULL
      WHEN r.promise_earliest_date IS NULL OR r.promise_earliest_date >= r.promise_date THEN date_format(r.promise_date, 'yyyy-MM-dd')
      ELSE CONCAT(date_format(r.promise_earliest_date, 'yyyy-MM-dd'), ' to ', date_format(r.promise_date, 'yyyy-MM-dd'))
    END AS window_value
  FROM resolved r
),
totals AS (
  SELECT COUNT(DISTINCT purchase_id) AS all_orders
  FROM labelled
)
SELECT
  lb.delivery_promise_date,
  COALESCE(lb.window_value, lb.delivery_promise_date) AS delivery_promise_window,
  COUNT(DISTINCT lb.purchase_id) AS orders,
  ROUND(SUM(lb.order_total), 2) AS order_revenue,
  ROUND(CAST(COUNT(DISTINCT lb.purchase_id) AS DOUBLE) / NULLIF(MAX(t.all_orders), 0), 4) AS share_of_all_orders,
  SUM(CASE WHEN lb.promise_source = 'oms_order' THEN 1 ELSE 0 END) AS orders_promise_from_oms,
  SUM(CASE WHEN lb.promise_source = 'purchase_event' THEN 1 ELSE 0 END) AS orders_promise_from_purchase_event,
  SUM(CASE WHEN lb.promise_source = 'session_checkout_selection' THEN 1 ELSE 0 END) AS orders_promise_from_session_selection,
  SUM(CASE WHEN lb.selection_vs_purchase_mismatch THEN 1 ELSE 0 END) AS orders_selection_vs_purchase_mismatch,
  ROUND(CAST(SUM(CASE WHEN lb.selection_vs_purchase_mismatch THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(DISTINCT lb.purchase_id), 0), 4) AS selection_vs_purchase_mismatch_rate,
  SUM(CASE WHEN lb.event_vs_oms_mismatch THEN 1 ELSE 0 END) AS orders_event_vs_oms_mismatch
FROM labelled lb
CROSS JOIN totals t
GROUP BY lb.delivery_promise_date, COALESCE(lb.window_value, lb.delivery_promise_date)
ORDER BY lb.delivery_promise_date, delivery_promise_window;