Delivery Time Window

{
  "transaction": {
    "transactionID": "T1005843",
    "delivery": {
      "fulfillmentMethod": "ship_to_home",
      "deliveryTimeWindow": "Morning 08-12"
    }
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "T1005843",
    "value": 129.99,
    "currency": "USD",
    "fulfillment_method": "ship_to_home",
    "delivery_time_window": "Morning 08-12",
    "items": [
      {
        "item_id": "SKU_4821",
        "item_name": "Stand Mixer",
        "price": 129.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "purchase": {
    "transactionID": "T1005843",
    "fulfillmentMethod": "ship_to_home",
    "deliveryTimeWindow": "Morning 08-12"
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "order": {
      "purchaseID": "T1005843"
    },
    "purchases": {
      "value": 1
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar_fulfillment_method": "ship_to_home",
          "eVar_delivery_time_window": "Morning 08-12"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Governed-label mapping of carrier free text (note 4) happens at collection; delivery_time_window arrives already governed.
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 purchases AS (
  -- Slot confirmed on the purchase event only
  SELECT
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'fulfillment_method') AS fulfillment_method,
    NULLIF(TRIM((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'delivery_time_window')), '') AS delivery_time_window
  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'
),
ranked AS (
  -- Home delivery orders only; a duplicate purchase hit keeps the first one per order
  SELECT
    transaction_id,
    delivery_time_window,
    ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) AS rn
  FROM purchases
  WHERE fulfillment_method = 'ship_to_home'
    AND transaction_id IS NOT NULL
)
-- One window per order; delivery orders without a slot are labelled "No Slot Selected"
SELECT
  transaction_id,
  COALESCE(delivery_time_window, 'No Slot Selected') AS delivery_time_window
FROM ranked
WHERE rn = 1;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Governed-label mapping of carrier free text (note 4) happens at collection; eVar_delivery_time_window arrives already governed.
WITH params AS (
  -- Relative reporting window: last 90 complete days
  SELECT CAST(CURRENT_DATE - INTERVAL '90' DAY AS DATE) AS start_date,
         CAST(CURRENT_DATE - INTERVAL '1' DAY AS DATE) AS end_date
),
purchases AS (
  -- Slot confirmed on the purchase event only
  SELECT
    e.timestamp AS event_timestamp,
    e.commerce.order.purchaseID AS transaction_id,
    e._experience.analytics.customDimensions.eVars.eVar_fulfillment_method AS fulfillment_method,
    NULLIF(TRIM(e._experience.analytics.customDimensions.eVars.eVar_delivery_time_window), '') AS delivery_time_window
  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
),
ranked AS (
  -- Home delivery orders only; a duplicate purchase hit keeps the first one per order
  SELECT
    transaction_id,
    delivery_time_window,
    ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) AS rn
  FROM purchases
  WHERE fulfillment_method = 'ship_to_home'
    AND transaction_id IS NOT NULL
)
-- One window per order; delivery orders without a slot are labelled "No Slot Selected"
SELECT
  transaction_id,
  COALESCE(delivery_time_window, 'No Slot Selected') AS delivery_time_window
FROM ranked
WHERE rn = 1;