Pickup Completion Rate

{
  "transaction": {
    "transactionID": "ORD-10458821",
    "total": {
      "basePrice": 142.5,
      "currency": "USD",
      "shipping": 0,
      "taxRate": 0.0825,
      "priceWithTax": 154.26
    },
    "attributes": {
      "fulfillmentMethod": "pickup_in_store",
      "storeID": "STR-0412",
      "pickupWindow": "same_day_2h",
      "holdPeriodDays": 7
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-778120",
          "productName": "Cordless Drill Kit 20V"
        },
        "category": {
          "primaryCategory": "Tools"
        },
        "quantity": 1,
        "price": {
          "basePrice": 142.5,
          "currency": "USD"
        },
        "attributes": {
          "fulfillmentMethod": "pickup_in_store",
          "storeID": "STR-0412"
        }
      }
    ]
  },
  "page": {
    "pageInfo": {
      "pageName": "checkout:order-confirmation",
      "pageID": "order-confirmation"
    },
    "category": {
      "primaryCategory": "checkout"
    }
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "ORD-10458821",
    "value": 142.5,
    "tax": 11.76,
    "shipping": 0,
    "currency": "USD",
    "items": [
      {
        "item_id": "SKU-778120",
        "item_name": "Cordless Drill Kit 20V",
        "item_category": "Tools",
        "price": 142.5,
        "quantity": 1,
        "location_id": "STR-0412"
      }
    ]
  },
  "fulfillment_method": "pickup_in_store",
  "store_id": "STR-0412",
  "pickup_window": "same_day_2h",
  "hold_period_days": 7
}

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "purchase"
  },
  "transaction": {
    "purchaseID": "ORD-10458821",
    "revenue": 142.5,
    "currency": "USD",
    "fulfillmentMethod": "pickup_in_store",
    "storeID": "STR-0412",
    "pickupWindow": "same_day_2h",
    "holdPeriodDays": 7
  },
  "product": [
    {
      "productID": "SKU-778120",
      "productName": "Cordless Drill Kit 20V",
      "category": "Tools",
      "quantity": 1,
      "price": 142.5,
      "fulfillmentMethod": "pickup_in_store"
    }
  ]
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-10458821",
      "priceTotal": 142.5,
      "currencyCode": "USD"
    },
    "shipping": {
      "shippingMethod": "pickup_in_store"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-778120",
      "name": "Cordless Drill Kit 20V",
      "quantity": 1,
      "priceTotal": 142.5
    }
  ],
  "placeContext": {
    "POIinteraction": {
      "POIDetail": {
        "POIID": "STR-0412"
      }
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar30": "pickup_in_store",
          "eVar31": "STR-0412",
          "eVar32": "same_day_2h",
          "eVar33": "7"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Pickup Completion Rate: pickup orders collected within the hold window / all pickup orders placed (same placement-date cohort)
-- Unit of count: the distinct pickup order (order_id), attributed to the store holding it
-- Denominator: the FULL pickup order base from the order management platform (app, web, call centre, in-store endless aisle)
-- GA4 purchase events are joined on transaction_id ONLY for device / traffic-source attribution and the data-layer hold-period fallback
WITH pickup_orders AS (
  SELECT
    o.order_id AS transaction_id,
    o.placed_ts AS placed_ts,
    o.store_id AS store_id,
    o.fulfillment_method AS fulfillment_method
  FROM `your-project.oms.orders` AS o
  WHERE o.fulfillment_method IN ('pickup_in_store', 'curbside', 'locker')
    AND DATE(o.placed_ts) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
    -- only test, employee and pre-release fraud-voided orders leave the base; retailer-side pick failures stay in
    AND COALESCE(o.is_test_order, FALSE) = FALSE
    AND COALESCE(o.is_employee_order, FALSE) = FALSE
    AND COALESCE(o.fraud_voided_before_release, FALSE) = FALSE
),
ga4_purchases AS (
  SELECT
    ecommerce.transaction_id AS transaction_id,
    device.category AS device_category,
    traffic_source.source AS traffic_source,
    MAX(IF(ep.key = 'hold_period_days', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS hold_period_days_param
  FROM `your-project.analytics_123456789.events_*`,
    UNNEST(event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND event_name = 'purchase'
    AND ecommerce.transaction_id IS NOT NULL
    AND ecommerce.transaction_id NOT IN ('', '(not set)')
  GROUP BY transaction_id, device_category, traffic_source, event_timestamp, user_pseudo_id
),
digital_attribution AS (
  -- attribution only: this CTE never filters or builds the denominator
  SELECT
    transaction_id,
    ANY_VALUE(device_category) AS device_category,
    ANY_VALUE(traffic_source) AS traffic_source,
    MAX(hold_period_days_param) AS hold_period_days_param
  FROM ga4_purchases
  GROUP BY transaction_id
),
store_config AS (
  SELECT store_id, time_zone, hold_period_days
  FROM `your-project.oms.store_pickup_config`
),
collections AS (
  -- roll every pickup fulfillment group of an order up to order level
  SELECT
    transaction_id,
    MIN(ready_for_pickup_ts) AS ready_for_pickup_ts,
    MAX(collected_ts) AS collected_ts,
    LOGICAL_AND(pickup_status = 'collected') AS all_groups_collected
  FROM `your-project.oms.order_pickups`
  GROUP BY transaction_id
),
cohort AS (
  SELECT
    o.transaction_id,
    o.store_id,
    o.fulfillment_method,
    COALESCE(d.device_category, 'offline_unattributed') AS device_category,
    COALESCE(d.traffic_source, 'offline_unattributed') AS traffic_source,
    DATE(o.placed_ts, COALESCE(s.time_zone, 'UTC')) AS placement_date,
    COALESCE(s.hold_period_days, d.hold_period_days_param, 7) AS hold_days,
    CURRENT_DATE(COALESCE(s.time_zone, 'UTC')) AS store_today,
    CASE
      WHEN c.all_groups_collected
       AND c.collected_ts IS NOT NULL
       AND c.collected_ts <= TIMESTAMP_ADD(COALESCE(c.ready_for_pickup_ts, o.placed_ts),
                                           INTERVAL COALESCE(s.hold_period_days, d.hold_period_days_param, 7) DAY)
      THEN 1 ELSE 0
    END AS is_collected
  FROM pickup_orders AS o
  LEFT JOIN collections AS c
    ON c.transaction_id = o.transaction_id
  LEFT JOIN store_config AS s
    ON s.store_id = o.store_id
  LEFT JOIN digital_attribution AS d
    ON d.transaction_id = o.transaction_id
)
SELECT
  placement_date,
  store_id,
  fulfillment_method,
  device_category,
  traffic_source,
  COUNT(DISTINCT transaction_id) AS pickup_orders_placed,
  COUNT(DISTINCT IF(is_collected = 1, transaction_id, NULL)) AS pickup_orders_collected,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT IF(is_collected = 1, transaction_id, NULL)),
                    COUNT(DISTINCT transaction_id)) * 100, 2) AS pickup_completion_rate_pct,
  -- cohort locks once hold period plus a 1-day late-scan buffer has elapsed
  LOGICAL_OR(DATE_ADD(placement_date, INTERVAL hold_days + 1 DAY) > store_today) AS is_provisional
FROM cohort
GROUP BY placement_date, store_id, fulfillment_method, device_category, traffic_source
ORDER BY placement_date DESC, store_id, fulfillment_method;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Pickup Completion Rate: pickup orders collected within the hold window / all pickup orders placed (same placement-date cohort)
-- Unit of count: the distinct pickup order (purchaseID / order_id), attributed to the store holding it
-- Denominator: the FULL pickup order base from the order management dataset (app, web, call centre, in-store endless aisle)
-- XDM commerce.purchases events are joined on purchaseID ONLY for device / traffic-source attribution and the data-layer hold-period fallback
WITH pickup_orders AS (
  SELECT
    order_id AS transaction_id,
    placed_ts AS placed_ts,
    store_id AS store_id,
    fulfillment_method AS fulfillment_method
  FROM aep_oms_orders_dataset
  WHERE fulfillment_method IN ('pickup_in_store', 'curbside', 'locker')
    AND placed_ts >= date_sub(current_date(), 90)
    -- only test, employee and pre-release fraud-voided orders leave the base; retailer-side pick failures stay in
    AND COALESCE(is_test_order, FALSE) = FALSE
    AND COALESCE(is_employee_order, FALSE) = FALSE
    AND COALESCE(fraud_voided_before_release, FALSE) = FALSE
),
xdm_purchases AS (
  SELECT
    commerce.order.purchaseID AS transaction_id,
    device.type AS device_category,
    marketing.trackingCode AS traffic_source,
    CAST(_experience.analytics.customDimensions.eVars.eVar33 AS INTEGER) AS hold_period_days_param
  FROM aep_web_events_dataset
  WHERE eventType = 'commerce.purchases'
    AND timestamp >= date_sub(current_date(), 90)
    AND commerce.order.purchaseID IS NOT NULL
    AND commerce.order.purchaseID <> ''
),
digital_attribution AS (
  -- attribution only: this CTE never filters or builds the denominator
  SELECT
    transaction_id,
    FIRST(device_category, TRUE) AS device_category,
    FIRST(traffic_source, TRUE) AS traffic_source,
    MAX(hold_period_days_param) AS hold_period_days_param
  FROM xdm_purchases
  GROUP BY transaction_id
),
store_config AS (
  SELECT store_id, time_zone, hold_period_days
  FROM aep_store_pickup_config_dataset
),
collections AS (
  -- roll every pickup fulfillment group of an order up to order level
  SELECT
    transaction_id,
    MIN(ready_for_pickup_ts) AS ready_for_pickup_ts,
    MAX(collected_ts) AS collected_ts,
    MIN(CASE WHEN pickup_status = 'collected' THEN 1 ELSE 0 END) AS all_groups_collected
  FROM aep_oms_order_pickups_dataset
  GROUP BY transaction_id
),
cohort AS (
  SELECT
    o.transaction_id,
    o.store_id,
    o.fulfillment_method,
    COALESCE(d.device_category, 'offline_unattributed') AS device_category,
    COALESCE(d.traffic_source, 'offline_unattributed') AS traffic_source,
    to_date(from_utc_timestamp(o.placed_ts, COALESCE(s.time_zone, 'UTC'))) AS placement_date,
    COALESCE(s.hold_period_days, d.hold_period_days_param, 7) AS hold_days,
    to_date(from_utc_timestamp(current_timestamp(), COALESCE(s.time_zone, 'UTC'))) AS store_today,
    CASE
      WHEN c.all_groups_collected = 1
       AND c.collected_ts IS NOT NULL
       AND unix_timestamp(c.collected_ts)
           <= unix_timestamp(COALESCE(c.ready_for_pickup_ts, o.placed_ts))
              + COALESCE(s.hold_period_days, d.hold_period_days_param, 7) * 86400
      THEN 1 ELSE 0
    END AS is_collected
  FROM pickup_orders AS o
  LEFT JOIN collections AS c
    ON c.transaction_id = o.transaction_id
  LEFT JOIN store_config AS s
    ON s.store_id = o.store_id
  LEFT JOIN digital_attribution AS d
    ON d.transaction_id = o.transaction_id
)
SELECT
  placement_date,
  store_id,
  fulfillment_method,
  device_category,
  traffic_source,
  COUNT(DISTINCT transaction_id) AS pickup_orders_placed,
  COUNT(DISTINCT CASE WHEN is_collected = 1 THEN transaction_id END) AS pickup_orders_collected,
  ROUND(COUNT(DISTINCT CASE WHEN is_collected = 1 THEN transaction_id END) * 100.0
        / NULLIF(COUNT(DISTINCT transaction_id), 0), 2) AS pickup_completion_rate_pct,
  -- cohort locks once hold period plus a 1-day late-scan buffer has elapsed
  MAX(CASE WHEN date_add(placement_date, hold_days + 1) > store_today THEN 1 ELSE 0 END) = 1 AS is_provisional
FROM cohort
GROUP BY placement_date, store_id, fulfillment_method, device_category, traffic_source
ORDER BY placement_date DESC, store_id, fulfillment_method;