Fulfillment Method

{
  "digitalData": {
    "pageInstanceID": "prod-web-checkout-shipping",
    "page": {
      "pageInfo": {
        "pageID": "checkout-shipping",
        "pageName": "checkout:shipping",
        "destinationURL": "https://www.example.com/checkout/shipping",
        "sysEnv": "web"
      },
      "category": {
        "primaryCategory": "checkout",
        "subCategory1": "shipping"
      },
      "attributes": {
        "checkoutStep": 2,
        "transactionChannel": "web"
      }
    },
    "cart": {
      "cartID": "CART-88213",
      "price": {
        "basePrice": 174,
        "currency": "USD",
        "cartTotal": 174
      },
      "attributes": {
        "fulfillmentMethod": "split",
        "fulfillmentMethodCount": 2,
        "shippingTier": "Standard",
        "pickupLocationID": "STORE-4471"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-10293",
            "productName": "Trail Runner Jacket",
            "sku": "SKU-10293"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Outerwear"
          },
          "quantity": 1,
          "price": {
            "basePrice": 129,
            "currency": "USD"
          },
          "attributes": {
            "fulfillmentMethod": "bopis_in_store",
            "pickupLocationID": "STORE-4471"
          }
        },
        {
          "productInfo": {
            "productID": "SKU-55871",
            "productName": "Merino Hiking Socks",
            "sku": "SKU-55871"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Accessories"
          },
          "quantity": 2,
          "price": {
            "basePrice": 22.5,
            "currency": "USD"
          },
          "attributes": {
            "fulfillmentMethod": "ship_to_home"
          }
        }
      ]
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "add_shipping_info",
          "eventAction": "fulfillment_method_selected",
          "type": "checkout",
          "timeStamp": "2026-09-17T14:32:08.000Z"
        },
        "attributes": {
          "fulfillmentMethod": "split",
          "shippingTier": "Standard"
        }
      }
    ],
    "version": "1.0"
  }
}

{
  "event": "add_shipping_info",
  "fulfillment_method": "split",
  "pickup_location_id": "STORE-4471",
  "transaction_channel": "web",
  "checkout_step": 2,
  "ecommerce": {
    "currency": "USD",
    "value": 174,
    "shipping_tier": "Standard",
    "items": [
      {
        "item_id": "SKU-10293",
        "item_name": "Trail Runner Jacket",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Outerwear",
        "item_variant": "Slate / M",
        "price": 129,
        "quantity": 1,
        "fulfillment_method": "bopis_in_store",
        "pickup_location_id": "STORE-4471"
      },
      {
        "item_id": "SKU-55871",
        "item_name": "Merino Hiking Socks",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Accessories",
        "item_variant": "Charcoal / L",
        "price": 22.5,
        "quantity": 2,
        "fulfillment_method": "ship_to_home"
      }
    ]
  }
}

{
  "event": "add_shipping_info",
  "eventInfo": {
    "path": "commerce.shipping",
    "trigger": "Shipping Info Added"
  },
  "_experience": {
    "analytics": {
      "event1to100": {
        "event12": {
          "value": 1
        }
      }
    }
  },
  "commerce": {
    "shipping": {
      "fulfillmentMethod": "split",
      "shippingTier": "Standard",
      "pickupLocationId": "STORE-4471"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-10293",
      "name": "Trail Runner Jacket",
      "quantity": 1,
      "priceTotal": 129,
      "currencyCode": "USD",
      "fulfillmentMethod": "bopis_in_store",
      "pickupLocationId": "STORE-4471"
    },
    {
      "SKU": "SKU-55871",
      "name": "Merino Hiking Socks",
      "quantity": 2,
      "priceTotal": 45,
      "currencyCode": "USD",
      "fulfillmentMethod": "ship_to_home"
    }
  ],
  "cart": {
    "cartId": "CART-88213",
    "currencyCode": "USD",
    "priceTotal": 174
  },
  "channel": {
    "transactionChannel": "web",
    "typeAtSource": "web"
  }
}

{
  "eventType": "commerce.checkouts",
  "timestamp": "2026-09-17T14:32:08.000Z",
  "commerce": {
    "shipping": {
      "shippingMethod": "split"
    }
  },
  "_tenant": {
    "shippingTier": "Standard"
  },
  "productListItems": [
    {
      "SKU": "SKU-10293",
      "name": "Trail Runner Jacket",
      "quantity": 1,
      "priceTotal": 129,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-55871",
      "name": "Merino Hiking Socks",
      "quantity": 2,
      "priceTotal": 45,
      "currencyCode": "USD"
    }
  ],
  "channel": {
    "_type": "https://ns.adobe.com/xdm/channels/web",
    "typeAtSource": "web"
  },
  "_experience": {
    "analytics": {
      "event1to100": {
        "event12": {
          "value": 1
        }
      },
      "customDimensions": {
        "eVars": {
          "eVar23": "split",
          "eVar24": "STORE-4471",
          "eVar25": "web"
        },
        "props": {
          "prop23": "split"
        },
        "lists": {
          "list1": {
            "list": [
              {
                "value": "SKU-10293|bopis_in_store"
              },
              {
                "value": "SKU-55871|ship_to_home"
              }
            ],
            "delimiter": ","
          }
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Fulfillment Method distribution across its carrier events (add_shipping_info, begin_checkout, purchase, refund),
-- reported at three grains: event grain (selection volume), order grain (purchase-time snapshot, 'split' when a
-- basket mixes methods) and item grain (line-level method for mixed baskets).
-- Event-scoped params are pivoted with UNNEST + MAX(IF(...)) inside scalar subqueries so no row fan-out occurs
-- and the items ARRAY stays intact for the line-level pass.
-- carrier_role labels each carrier event: begin_checkout fires BEFORE the shipping selection and is the
-- pre_selection_base where 'not_set' is expected; add_shipping_info is the selection; purchase is the system of record.
WITH base AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_day,
    e.event_name,
    e.user_pseudo_id,
    e.event_timestamp,
    e.items,
    (SELECT MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS ga_session_id,
    (SELECT MAX(IF(ep.key = 'fulfillment_method', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS fulfillment_method_raw,
    (SELECT MAX(IF(ep.key = 'pickup_location_id', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS pickup_location_id,
    (SELECT MAX(IF(ep.key = 'transaction_channel', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS transaction_channel,
    (SELECT MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS shipping_tier,
    (SELECT MAX(IF(ep.key = 'checkout_step', ep.value.int_value, NULL)) FROM UNNEST(e.event_params) AS ep) AS checkout_step,
    COALESCE(
      NULLIF(e.ecommerce.transaction_id, '(not set)'),
      (SELECT MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) FROM UNNEST(e.event_params) AS ep)
    ) AS transaction_id,
    e.ecommerce.purchase_revenue AS purchase_revenue
  FROM `your-project.analytics_123456789.events_*` AS e
  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 IN ('add_shipping_info', 'begin_checkout', 'purchase', 'refund')
),
-- Controlled vocabulary: anything outside the governed list is surfaced as '(unmapped)' data quality volume,
-- and sessions that never reached the shipping step stay 'not_set' instead of defaulting to ship_to_home.
events_typed AS (
  SELECT
    b.* EXCEPT (fulfillment_method_raw),
    CASE LOWER(TRIM(COALESCE(b.fulfillment_method_raw, '')))
      WHEN 'bopis_in_store' THEN 'bopis_in_store'
      WHEN 'curbside_pickup' THEN 'curbside_pickup'
      WHEN 'ship_to_store' THEN 'ship_to_store'
      WHEN 'ship_to_home' THEN 'ship_to_home'
      WHEN 'same_day_delivery' THEN 'same_day_delivery'
      WHEN 'split' THEN 'split'
      WHEN 'not_set' THEN 'not_set'
      WHEN '' THEN 'not_set'
      ELSE '(unmapped)'
    END AS fulfillment_method
  FROM base AS b
),
-- Grain 1: how often each value is carried, per carrier event. Denominator is every event of that carrier
-- event in the window, including not_set, so the shares of one carrier event sum to 1.
event_mix AS (
  SELECT
    event_name AS carrier_event,
    CASE event_name
      WHEN 'begin_checkout' THEN 'pre_selection_base'
      WHEN 'add_shipping_info' THEN 'selection'
      WHEN 'purchase' THEN 'system_of_record'
      ELSE 'inherited'
    END AS carrier_role,
    fulfillment_method,
    COUNT(*) AS events,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING))) AS sessions,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT pickup_location_id) AS pickup_locations,
    SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER (PARTITION BY event_name)) AS share_of_carrier_event
  FROM events_typed
  GROUP BY carrier_event, carrier_role, fulfillment_method
),
-- Purchase snapshot, deduplicated to one row per transaction_id.
purchases AS (
  SELECT *
  FROM events_typed
  WHERE event_name = 'purchase'
    AND transaction_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),
-- Grain 3 source: item-scoped fulfillment_method, pivoted out of item_params the same way.
-- LEFT JOIN keeps purchases with an empty items array so the order grain divides by every deduplicated purchase.
purchase_items AS (
  SELECT
    p.transaction_id,
    p.event_day,
    p.fulfillment_method AS event_fulfillment_method,
    it IS NOT NULL AS has_line,
    it.item_id,
    it.item_name,
    it.item_category,
    IFNULL(it.quantity, 0) AS quantity,
    IFNULL(it.item_revenue, IFNULL(it.price, 0) * IFNULL(it.quantity, 0)) AS item_revenue,
    CASE LOWER(TRIM(COALESCE(
      (SELECT MAX(IF(ip.key = 'fulfillment_method', ip.value.string_value, NULL)) FROM UNNEST(it.item_params) AS ip),
      '')))
      WHEN 'bopis_in_store' THEN 'bopis_in_store'
      WHEN 'curbside_pickup' THEN 'curbside_pickup'
      WHEN 'ship_to_store' THEN 'ship_to_store'
      WHEN 'ship_to_home' THEN 'ship_to_home'
      WHEN 'same_day_delivery' THEN 'same_day_delivery'
      WHEN '' THEN 'not_set'
      ELSE '(unmapped)'
    END AS item_fulfillment_method
  FROM purchases AS p
  LEFT JOIN UNNEST(p.items) AS it
),
-- Grain 2: order-level value. Two or more distinct line methods resolve to 'split' so pickup orders are never inflated.
-- When no line carries a governed value (or the order has no lines), fall back to the event-level value, then 'not_set'.
order_method AS (
  SELECT
    pi.transaction_id,
    IF(COUNT(DISTINCT IF(pi.item_fulfillment_method IN ('not_set', '(unmapped)'), NULL, pi.item_fulfillment_method)) > 1,
       'split',
       COALESCE(
         MAX(IF(pi.item_fulfillment_method IN ('not_set', '(unmapped)'), NULL, pi.item_fulfillment_method)),
         MAX(IF(pi.event_fulfillment_method IN ('not_set', '(unmapped)'), NULL, pi.event_fulfillment_method)),
         'not_set')
    ) AS order_fulfillment_method,
    SUM(pi.quantity) AS units,
    SUM(pi.item_revenue) AS item_revenue
  FROM purchase_items AS pi
  GROUP BY pi.transaction_id
),
order_mix AS (
  SELECT
    om.order_fulfillment_method AS fulfillment_method,
    COUNT(DISTINCT om.transaction_id) AS orders,
    SUM(om.units) AS units,
    ROUND(SUM(om.item_revenue), 2) AS revenue,
    SAFE_DIVIDE(COUNT(DISTINCT om.transaction_id), SUM(COUNT(DISTINCT om.transaction_id)) OVER ()) AS share_of_all_orders
  FROM order_method AS om
  GROUP BY fulfillment_method
),
item_mix AS (
  SELECT
    pi.item_fulfillment_method AS fulfillment_method,
    COUNT(DISTINCT pi.transaction_id) AS orders,
    SUM(pi.quantity) AS units,
    ROUND(SUM(pi.item_revenue), 2) AS revenue,
    SAFE_DIVIDE(SUM(pi.quantity), SUM(SUM(pi.quantity)) OVER ()) AS share_of_all_units
  FROM purchase_items AS pi
  WHERE pi.has_line
  GROUP BY fulfillment_method
)
SELECT
  'event' AS grain,
  em.carrier_event,
  em.carrier_role,
  em.fulfillment_method,
  em.events,
  em.sessions,
  em.users,
  em.pickup_locations,
  CAST(NULL AS INT64) AS orders,
  CAST(NULL AS INT64) AS units,
  CAST(NULL AS FLOAT64) AS revenue,
  ROUND(em.share_of_carrier_event, 4) AS share_of_base
FROM event_mix AS em
UNION ALL
SELECT
  'order',
  'purchase',
  'system_of_record',
  om.fulfillment_method,
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  om.orders,
  om.units,
  om.revenue,
  ROUND(om.share_of_all_orders, 4)
FROM order_mix AS om
UNION ALL
SELECT
  'item',
  'purchase',
  'system_of_record',
  im.fulfillment_method,
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  CAST(NULL AS INT64),
  im.orders,
  im.units,
  im.revenue,
  ROUND(im.share_of_all_units, 4)
FROM item_mix AS im
ORDER BY grain, carrier_event, share_of_base DESC, fulfillment_method;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Fulfillment Method distribution on the ExperienceEvent dataset, reading the XDM paths the mapping declares:
-- commerce.shipping.shippingMethod (holds the governed Fulfillment Method value), _tenant.shippingTier (tenant field,
-- not standard XDM), refunds on eventType commerce.backofficeCreditMemoIssued, _experience.analytics.customDimensions.eVars.eVar23
-- (fulfillment method), eVar24 (pickup location), eVar25 (transaction channel), props.prop23 and
-- customDimensions.lists.list1 (SKU|method pairs for mixed baskets).
-- Schema notes: the governed Fulfillment Method value lives in commerce.shipping.shippingMethod; refunds arrive as
-- eventType commerce.backofficeCreditMemoIssued. Checkout steps are told apart by their success events, never by the
-- fulfillment value: _experience.analytics.event1to100.event12 = Shipping Info Added (add_shipping_info) and
-- commerce.checkouts = begin_checkout, which fires BEFORE the selection and is the pre_selection_base where 'not_set' is expected.
WITH base AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp AS event_ts,
    eventType AS event_type,
    CASE
      WHEN eventType = 'commerce.purchases' THEN 'purchase'
      WHEN eventType = 'commerce.backofficeCreditMemoIssued' THEN 'refund'
      WHEN _experience.analytics.event1to100.event12.value > 0 THEN 'add_shipping_info'
      WHEN commerce.checkouts.value > 0 THEN 'begin_checkout'
    END AS carrier_event,
    LOWER(TRIM(COALESCE(
      commerce.shipping.shippingMethod,
      _experience.analytics.customDimensions.eVars.eVar23,
      _experience.analytics.customDimensions.props.prop23,
      ''
    ))) AS fulfillment_method_raw,
    _experience.analytics.customDimensions.eVars.eVar24 AS pickup_location_id,
    COALESCE(_experience.analytics.customDimensions.eVars.eVar25, channel.typeAtSource) AS transaction_channel,
    _tenant.shippingTier AS shipping_tier,
    commerce.order.purchaseID AS purchase_id,
    commerce.order.priceTotal AS order_total,
    productListItems AS product_list_items,
    _experience.analytics.customDimensions.lists.list1.list AS item_method_list
  FROM aep_web_events_dataset
  WHERE timestamp >= date_sub(current_date(), 30)
    AND timestamp < current_date()
    AND (
      eventType IN ('commerce.purchases', 'commerce.backofficeCreditMemoIssued')
      OR _experience.analytics.event1to100.event12.value > 0
      OR commerce.checkouts.value > 0
    )
),
-- Controlled vocabulary; unlisted values become '(unmapped)' data-quality volume, empty stays 'not_set'.
events_typed AS (
  SELECT
    b.*,
    CASE b.fulfillment_method_raw
      WHEN 'bopis_in_store' THEN 'bopis_in_store'
      WHEN 'curbside_pickup' THEN 'curbside_pickup'
      WHEN 'ship_to_store' THEN 'ship_to_store'
      WHEN 'ship_to_home' THEN 'ship_to_home'
      WHEN 'same_day_delivery' THEN 'same_day_delivery'
      WHEN 'split' THEN 'split'
      WHEN 'not_set' THEN 'not_set'
      WHEN '' THEN 'not_set'
      ELSE '(unmapped)'
    END AS fulfillment_method
  FROM base b
),
-- Grain 1: volume per carrier event. Denominator is every event of that carrier event, not_set included.
event_mix AS (
  SELECT
    carrier_event,
    CASE carrier_event
      WHEN 'begin_checkout' THEN 'pre_selection_base'
      WHEN 'add_shipping_info' THEN 'selection'
      WHEN 'purchase' THEN 'system_of_record'
      ELSE 'inherited'
    END AS carrier_role,
    fulfillment_method,
    COUNT(*) AS events,
    COUNT(DISTINCT ecid) AS visitors,
    COUNT(DISTINCT pickup_location_id) AS pickup_locations,
    CAST(COUNT(*) AS DOUBLE) / NULLIF(SUM(COUNT(*)) OVER (PARTITION BY carrier_event), 0) AS share_of_base
  FROM events_typed
  GROUP BY carrier_event, carrier_role, fulfillment_method
),
purchases AS (
  SELECT ecid, event_ts, purchase_id, order_total, fulfillment_method, product_list_items, item_method_list
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (PARTITION BY e.purchase_id ORDER BY e.event_ts) AS rn
    FROM events_typed e
    WHERE e.carrier_event = 'purchase'
      AND e.purchase_id IS NOT NULL
  ) p
  WHERE rn = 1
),
-- explode_outer keeps purchases with no productListItems so the order grain divides by every deduplicated purchase.
purchase_lines AS (
  SELECT purchase_id, order_total, fulfillment_method AS event_fulfillment_method, explode_outer(product_list_items) AS line
  FROM purchases
),
line_methods AS (
  SELECT purchase_id, explode(item_method_list) AS entry
  FROM purchases
),
line_method_pairs AS (
  SELECT
    purchase_id,
    split(entry.value, '\\|')[0] AS sku,
    LOWER(TRIM(COALESCE(split(entry.value, '\\|')[1], ''))) AS item_method_raw
  FROM line_methods
),
purchase_items AS (
  SELECT
    pl.purchase_id,
    pl.event_fulfillment_method,
    pl.line IS NOT NULL AS has_line,
    pl.line.SKU AS sku,
    COALESCE(pl.line.quantity, 0) AS quantity,
    COALESCE(pl.line.priceTotal, 0) AS line_revenue,
    CASE COALESCE(mp.item_method_raw, '')
      WHEN 'bopis_in_store' THEN 'bopis_in_store'
      WHEN 'curbside_pickup' THEN 'curbside_pickup'
      WHEN 'ship_to_store' THEN 'ship_to_store'
      WHEN 'ship_to_home' THEN 'ship_to_home'
      WHEN 'same_day_delivery' THEN 'same_day_delivery'
      WHEN '' THEN 'not_set'
      ELSE '(unmapped)'
    END AS item_fulfillment_method
  FROM purchase_lines pl
  LEFT JOIN line_method_pairs mp
    ON mp.purchase_id = pl.purchase_id
   AND mp.sku = pl.line.SKU
),
-- Grain 2: order value, 'split' when the basket mixes two or more governed methods.
-- When no line carries a governed value (or the order has no lines), fall back to the event-level value, then 'not_set'.
order_method AS (
  SELECT
    purchase_id,
    CASE
      WHEN COUNT(DISTINCT CASE WHEN item_fulfillment_method NOT IN ('not_set', '(unmapped)') THEN item_fulfillment_method END) > 1 THEN 'split'
      ELSE COALESCE(
        MAX(CASE WHEN item_fulfillment_method NOT IN ('not_set', '(unmapped)') THEN item_fulfillment_method END),
        MAX(CASE WHEN event_fulfillment_method NOT IN ('not_set', '(unmapped)') THEN event_fulfillment_method END),
        'not_set')
    END AS fulfillment_method,
    SUM(quantity) AS units,
    SUM(line_revenue) AS revenue
  FROM purchase_items
  GROUP BY purchase_id
),
order_mix AS (
  SELECT
    fulfillment_method,
    COUNT(DISTINCT purchase_id) AS orders,
    SUM(units) AS units,
    ROUND(SUM(revenue), 2) AS revenue,
    CAST(COUNT(DISTINCT purchase_id) AS DOUBLE) / NULLIF(SUM(COUNT(DISTINCT purchase_id)) OVER (), 0) AS share_of_base
  FROM order_method
  GROUP BY fulfillment_method
),
item_mix AS (
  SELECT
    item_fulfillment_method AS fulfillment_method,
    COUNT(DISTINCT purchase_id) AS orders,
    SUM(quantity) AS units,
    ROUND(SUM(line_revenue), 2) AS revenue,
    CAST(SUM(quantity) AS DOUBLE) / NULLIF(SUM(SUM(quantity)) OVER (), 0) AS share_of_base
  FROM purchase_items
  WHERE has_line
  GROUP BY item_fulfillment_method
)
SELECT
  'event' AS grain,
  carrier_event,
  carrier_role,
  fulfillment_method,
  events,
  visitors,
  pickup_locations,
  CAST(NULL AS BIGINT) AS orders,
  CAST(NULL AS BIGINT) AS units,
  CAST(NULL AS DOUBLE) AS revenue,
  ROUND(share_of_base, 4) AS share_of_base
FROM event_mix
UNION ALL
SELECT
  'order',
  'purchase',
  'system_of_record',
  fulfillment_method,
  CAST(NULL AS BIGINT),
  CAST(NULL AS BIGINT),
  CAST(NULL AS BIGINT),
  orders,
  units,
  revenue,
  ROUND(share_of_base, 4)
FROM order_mix
UNION ALL
SELECT
  'item',
  'purchase',
  'system_of_record',
  fulfillment_method,
  CAST(NULL AS BIGINT),
  CAST(NULL AS BIGINT),
  CAST(NULL AS BIGINT),
  orders,
  units,
  revenue,
  ROUND(share_of_base, 4)
FROM item_mix
ORDER BY grain, carrier_event, share_of_base DESC, fulfillment_method;