Shipping Tier

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "checkout:shipping",
        "pageType": "checkout"
      }
    },
    "cart": {
      "cartID": "CART-88412",
      "price": {
        "basePrice": 129.98,
        "voucherCode": "SUMMER10",
        "voucherDiscount": 13,
        "currency": "USD",
        "shipping": 12.95,
        "shippingMethod": "Express (2-3 business days)",
        "cartTotal": 129.93
      },
      "attributes": {
        "shippingTier": "Express (2-3 business days)",
        "checkoutStep": "shipping"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "add_shipping_info",
          "eventAction": "shipping_option_selected",
          "type": "checkout"
        },
        "attributes": {
          "shippingTier": "Express (2-3 business days)"
        }
      }
    ]
  }
}

{
  "event": "add_shipping_info",
  "ecommerce": {
    "currency": "USD",
    "value": 116.98,
    "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": "checkout:add_shipping_info",
  "eventInfo": {
    "eventName": "add_shipping_info",
    "checkoutStep": "shipping"
  },
  "page": {
    "pageName": "checkout:shipping",
    "pageType": "checkout"
  },
  "commerce": {
    "cartID": "CART-88412",
    "currency": "USD",
    "cartValue": 116.98,
    "coupon": "SUMMER10",
    "shipping": {
      "shippingTier": "Express (2-3 business days)",
      "shippingAmount": 12.95
    },
    "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.checkouts",
  "commerce": {
    "checkouts": {
      "value": 1
    },
    "cart": {
      "cartID": "CART-88412"
    }
  },
  "shipping": {
    "shippingMethod": "Express (2-3 business days)",
    "shippingAmount": 12.95,
    "currencyCode": "USD"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar42": "Express (2-3 business days)"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Shipping Tier by order: shipping_tier on the purchase if sent, otherwise the last add_shipping_info selection in the same session before the purchase
WITH shipping_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,
    MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL)) AS shipping_tier
  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
),
purchases AS (
  SELECT
    e.user_pseudo_id,
    e.event_date,
    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,
    MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL)) AS purchase_shipping_tier,
    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_date, e.event_timestamp, e.event_bundle_sequence_id
),
unique_purchases AS (
  SELECT *
  FROM purchases
  WHERE transaction_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),
last_selection_before_purchase AS (
  SELECT
    p.transaction_id,
    s.shipping_tier
  FROM unique_purchases AS p
  JOIN shipping_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.shipping_tier IS NOT NULL
    AND TRIM(s.shipping_tier) != ''
  QUALIFY ROW_NUMBER() OVER (PARTITION BY p.transaction_id ORDER BY s.event_timestamp DESC) = 1
),
orders AS (
  SELECT
    p.transaction_id,
    p.event_date,
    p.purchase_revenue,
    COALESCE(NULLIF(TRIM(p.purchase_shipping_tier), ''), TRIM(l.shipping_tier)) AS shipping_tier_raw,
    CASE
      WHEN NULLIF(TRIM(p.purchase_shipping_tier), '') IS NOT NULL THEN 'purchase_event'
      WHEN l.shipping_tier IS NOT NULL THEN 'session_last_add_shipping_info'
      ELSE 'unresolved'
    END AS tier_source
  FROM unique_purchases AS p
  LEFT JOIN last_selection_before_purchase AS l
    ON l.transaction_id = p.transaction_id
),
-- Canonical tier mapping (keyword baseline for the governed lookup); 'multiple' requires the OMS shipment-line join
tiered AS (
  SELECT
    o.*,
    CASE
      WHEN o.label IS NULL THEN '(not set)'
      WHEN o.label LIKE '%pickup%' OR o.label LIKE '%pick up%' OR o.label LIKE '%in store%' OR o.label LIKE '%in-store%' OR o.label LIKE '%to store%' OR o.label LIKE '%digital%' OR o.label LIKE '%e-gift%' THEN '(not applicable)'
      WHEN o.label LIKE '%same day%' OR o.label LIKE '%same-day%' THEN 'same_day'
      WHEN o.label LIKE '%next day%' OR o.label LIKE '%next-day%' OR o.label LIKE '%overnight%' THEN 'next_day'
      WHEN o.label LIKE '%express%' THEN 'express'
      WHEN o.label LIKE '%expedited%' OR o.label LIKE '%priority%' THEN 'expedited'
      WHEN o.label LIKE '%free%' THEN 'free'
      WHEN o.label LIKE '%standard%' OR o.label LIKE '%ground%' OR o.label LIKE '%economy%' THEN 'standard'
      ELSE '(other)'
    END AS shipping_tier
  FROM (SELECT *, LOWER(shipping_tier_raw) AS label FROM orders) AS o
)
SELECT
  shipping_tier,
  COUNT(DISTINCT transaction_id) AS orders,
  ROUND(SUM(purchase_revenue), 2) AS purchase_revenue,
  ROUND(SAFE_DIVIDE(SUM(purchase_revenue), COUNT(DISTINCT transaction_id)), 2) AS average_order_value,
  IF(shipping_tier = '(not applicable)', NULL,
     ROUND(SAFE_DIVIDE(COUNT(DISTINCT transaction_id), SUM(IF(shipping_tier = '(not applicable)', 0, COUNT(DISTINCT transaction_id))) OVER ()), 4)) AS share_of_shipped_orders,
  COUNTIF(tier_source = 'purchase_event') AS orders_tier_from_purchase_event,
  COUNTIF(tier_source = 'session_last_add_shipping_info') AS orders_tier_from_session_selection
FROM tiered
GROUP BY shipping_tier
ORDER BY orders DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Shipping Tier by order: shipping.shippingMethod on the purchase if sent, otherwise the last commerce.checkouts selection in the same 30-minute session before the purchase
WITH sessionized AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp AS event_ts,
    eventType AS event_type,
    TRIM(shipping.shippingMethod) AS shipping_method,
    commerce.order.purchaseID AS purchase_id,
    commerce.order.priceTotal AS order_total,
    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
),
shipping_selections AS (
  SELECT
    ecid,
    session.num AS session_num,
    event_ts,
    shipping_method
  FROM sessionized
  WHERE event_type = 'commerce.checkouts'
    AND shipping_method IS NOT NULL
    AND shipping_method <> ''
),
unique_purchases AS (
  SELECT ecid, session_num, event_ts, purchase_id, order_total, purchase_shipping_method
  FROM (
    SELECT
      ecid,
      session.num AS session_num,
      event_ts,
      purchase_id,
      order_total,
      NULLIF(shipping_method, '') AS purchase_shipping_method,
      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
),
last_selection_before_purchase AS (
  SELECT purchase_id, shipping_method
  FROM (
    SELECT
      p.purchase_id,
      s.shipping_method,
      ROW_NUMBER() OVER (PARTITION BY p.purchase_id ORDER BY s.event_ts DESC) AS rn
    FROM unique_purchases p
    JOIN shipping_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
),
orders AS (
  SELECT
    p.purchase_id,
    p.order_total,
    COALESCE(p.purchase_shipping_method, l.shipping_method) AS shipping_tier_raw,
    CASE
      WHEN p.purchase_shipping_method IS NOT NULL THEN 'purchase_event'
      WHEN l.shipping_method IS NOT NULL THEN 'session_last_checkout_selection'
      ELSE 'unresolved'
    END AS tier_source
  FROM unique_purchases p
  LEFT JOIN last_selection_before_purchase l
    ON l.purchase_id = p.purchase_id
),
-- Canonical tier mapping (keyword baseline for the governed lookup); 'multiple' requires the OMS shipment-line join
tiered AS (
  SELECT
    o.*,
    CASE
      WHEN o.label IS NULL THEN '(not set)'
      WHEN o.label LIKE '%pickup%' OR o.label LIKE '%pick up%' OR o.label LIKE '%in store%' OR o.label LIKE '%in-store%' OR o.label LIKE '%to store%' OR o.label LIKE '%digital%' OR o.label LIKE '%e-gift%' THEN '(not applicable)'
      WHEN o.label LIKE '%same day%' OR o.label LIKE '%same-day%' THEN 'same_day'
      WHEN o.label LIKE '%next day%' OR o.label LIKE '%next-day%' OR o.label LIKE '%overnight%' THEN 'next_day'
      WHEN o.label LIKE '%express%' THEN 'express'
      WHEN o.label LIKE '%expedited%' OR o.label LIKE '%priority%' THEN 'expedited'
      WHEN o.label LIKE '%free%' THEN 'free'
      WHEN o.label LIKE '%standard%' OR o.label LIKE '%ground%' OR o.label LIKE '%economy%' THEN 'standard'
      ELSE '(other)'
    END AS shipping_tier
  FROM (SELECT *, LOWER(shipping_tier_raw) AS label FROM orders) o
),
totals AS (
  SELECT COUNT(DISTINCT CASE WHEN shipping_tier <> '(not applicable)' THEN purchase_id END) AS shipped_orders
  FROM tiered
)
SELECT
  o.shipping_tier,
  COUNT(DISTINCT o.purchase_id) AS orders,
  ROUND(SUM(o.order_total), 2) AS order_revenue,
  ROUND(SUM(o.order_total) / NULLIF(COUNT(DISTINCT o.purchase_id), 0), 2) AS average_order_value,
  CASE WHEN o.shipping_tier = '(not applicable)' THEN NULL
       ELSE ROUND(CAST(COUNT(DISTINCT o.purchase_id) AS DOUBLE) / NULLIF(MAX(t.shipped_orders), 0), 4) END AS share_of_shipped_orders,
  SUM(CASE WHEN o.tier_source = 'purchase_event' THEN 1 ELSE 0 END) AS orders_tier_from_purchase_event,
  SUM(CASE WHEN o.tier_source = 'session_last_checkout_selection' THEN 1 ELSE 0 END) AS orders_tier_from_session_selection
FROM tiered o
CROSS JOIN totals t
GROUP BY o.shipping_tier
ORDER BY orders DESC;