Split Shipment Rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "purchase",
        "eventAction": "order_confirmation"
      }
    }
  ],
  "transaction": {
    "transactionID": "ORD-100245871",
    "total": {
      "basePrice": 184.97,
      "currency": "USD",
      "shipping": 0,
      "priceWithTax": 199.77
    },
    "attributes": {
      "plannedShipmentCount": 2,
      "splitShipmentPlanned": true,
      "splitReason": "inventory_node",
      "fulfillmentMethod": "ship_to_home",
      "shippingTier": "standard",
      "deliveryRegion": "US-TX"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-44821",
          "productName": "Trail Running Shoe"
        },
        "category": {
          "primaryCategory": "Footwear"
        },
        "quantity": 1,
        "price": {
          "basePrice": 129.99
        },
        "attributes": {
          "fulfillmentMethod": "ship_to_home",
          "fulfillmentSource": "owned_dc",
          "fulfillmentNode": "DC-DAL-01",
          "shipmentGroup": "1"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-90317",
          "productName": "Hydration Vest"
        },
        "category": {
          "primaryCategory": "Accessories"
        },
        "quantity": 1,
        "price": {
          "basePrice": 54.98
        },
        "attributes": {
          "fulfillmentMethod": "ship_to_home",
          "fulfillmentSource": "drop_ship",
          "fulfillmentNode": "VENDOR-2231",
          "shipmentGroup": "2"
        }
      }
    ]
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "ORD-100245871",
    "value": 184.97,
    "tax": 14.8,
    "shipping": 0,
    "currency": "USD",
    "shipping_tier": "standard",
    "items": [
      {
        "item_id": "SKU-44821",
        "item_name": "Trail Running Shoe",
        "item_category": "Footwear",
        "price": 129.99,
        "quantity": 1,
        "fulfillment_method": "ship_to_home",
        "fulfillment_source": "owned_dc",
        "fulfillment_node": "DC-DAL-01",
        "shipment_group": "1"
      },
      {
        "item_id": "SKU-90317",
        "item_name": "Hydration Vest",
        "item_category": "Accessories",
        "price": 54.98,
        "quantity": 1,
        "fulfillment_method": "ship_to_home",
        "fulfillment_source": "drop_ship",
        "fulfillment_node": "VENDOR-2231",
        "shipment_group": "2"
      }
    ]
  },
  "planned_shipment_count": 2,
  "split_shipment_planned": "true",
  "split_reason": "inventory_node",
  "fulfillment_method": "ship_to_home",
  "delivery_region": "US-TX"
}

{
  "event": "purchase",
  "eventInfo": {
    "eventName": "purchase"
  },
  "transaction": {
    "purchaseID": "ORD-100245871",
    "revenue": 184.97,
    "currency": "USD",
    "shippingTier": "standard",
    "plannedShipmentCount": 2,
    "splitShipmentPlanned": true,
    "splitReason": "inventory_node",
    "fulfillmentMethod": "ship_to_home",
    "deliveryRegion": "US-TX"
  },
  "product": [
    {
      "productID": "SKU-44821",
      "name": "Trail Running Shoe",
      "category": "Footwear",
      "quantity": 1,
      "price": 129.99,
      "fulfillmentMethod": "ship_to_home",
      "fulfillmentSource": "owned_dc",
      "fulfillmentNode": "DC-DAL-01",
      "shipmentGroup": "1"
    },
    {
      "productID": "SKU-90317",
      "name": "Hydration Vest",
      "category": "Accessories",
      "quantity": 1,
      "price": 54.98,
      "fulfillmentMethod": "ship_to_home",
      "fulfillmentSource": "drop_ship",
      "fulfillmentNode": "VENDOR-2231",
      "shipmentGroup": "2"
    }
  ]
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-100245871",
      "priceTotal": 184.97,
      "currencyCode": "USD"
    },
    "shipping": {
      "shippingMethod": "standard"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-44821",
      "name": "Trail Running Shoe",
      "productCategories": [
        {
          "categoryName": "Footwear"
        }
      ],
      "quantity": 1,
      "priceTotal": 129.99,
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar41": "owned_dc",
              "eVar42": "DC-DAL-01",
              "eVar43": "1"
            }
          }
        }
      }
    },
    {
      "SKU": "SKU-90317",
      "name": "Hydration Vest",
      "productCategories": [
        {
          "categoryName": "Accessories"
        }
      ],
      "quantity": 1,
      "priceTotal": 54.98,
      "_experience": {
        "analytics": {
          "customDimensions": {
            "eVars": {
              "eVar41": "drop_ship",
              "eVar42": "VENDOR-2231",
              "eVar43": "2"
            }
          }
        }
      }
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar38": "ship_to_home",
          "eVar39": "2",
          "eVar40": "inventory_node",
          "eVar44": "US-TX"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Split Shipment Rate = orders with >= 2 distinct original-fulfillment shipments / all orders with >= 1 ship-confirmed carrier shipment
-- Base and numerator come from the OMS shipment table; the GA4 purchase is joined on transaction_id for attribution only.
DECLARE end_date DATE DEFAULT CURRENT_DATE('America/New_York');
DECLARE start_date DATE DEFAULT DATE_SUB(end_date, INTERVAL 29 DAY);
DECLARE purchase_lookback_days INT64 DEFAULT 60;

-- Orders left after the OMS order-type exclusions: test, employee, fraud-cancelled and cancelled-before-ship
WITH eligible_orders AS (
  SELECT o.order_id
  FROM `your-project.oms.orders` AS o
  WHERE o.is_test_order = FALSE
    AND o.is_employee_order = FALSE
    AND o.order_status NOT IN ('fraud_cancelled', 'cancelled_before_ship')
),
-- Ship-confirmed carrier shipments that belong to original fulfillment: drops BOPIS, curbside, ship-to-store and digital, plus reships, exchanges, return labels and warranty sends
original_shipments AS (
  SELECT
    s.order_id,
    COALESCE(s.shipment_id, s.tracking_number) AS shipment_key,
    s.ship_confirm_ts,
    s.split_reason
  FROM `your-project.oms.shipments` AS s
  JOIN eligible_orders AS e
    ON e.order_id = s.order_id
  WHERE s.shipment_status = 'ship_confirmed'
    AND s.delivery_mode = 'carrier'
    AND s.shipment_reason_code = 'original_fulfillment'
),
-- One row per order: deduplicated shipment count, first ship-confirm timestamp and the customer-requested split flag
order_shipments AS (
  SELECT
    order_id,
    COUNT(DISTINCT shipment_key) AS shipment_count,
    MIN(ship_confirm_ts) AS first_ship_confirm_ts,
    LOGICAL_OR(split_reason = 'customer_requested') AS is_customer_requested_split
  FROM original_shipments
  GROUP BY order_id
),
-- Orders assigned to the reporting period by their first ship-confirm date in the store reporting time zone
shipped_order_cohort AS (
  SELECT
    order_id,
    shipment_count,
    is_customer_requested_split,
    DATE(first_ship_confirm_ts, 'America/New_York') AS first_ship_date
  FROM order_shipments
  WHERE DATE(first_ship_confirm_ts, 'America/New_York') BETWEEN start_date AND end_date
),
-- First purchase per transaction_id, scanned from before the cohort starts because an order can be placed well before it ships
ga4_purchases AS (
  SELECT
    transaction_id,
    device_category,
    traffic_source
  FROM (
    SELECT
      ecommerce.transaction_id AS transaction_id,
      device.category AS device_category,
      CONCAT(IFNULL(traffic_source.source, '(direct)'), ' / ', IFNULL(traffic_source.medium, '(none)')) AS traffic_source,
      ROW_NUMBER() OVER (PARTITION BY ecommerce.transaction_id ORDER BY event_timestamp) AS purchase_rank
    FROM `your-project.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL purchase_lookback_days DAY))
                            AND FORMAT_DATE('%Y%m%d', end_date)
      AND event_name = 'purchase'
      AND ecommerce.transaction_id IS NOT NULL
      AND ecommerce.transaction_id NOT IN ('', '(not set)')
  )
  WHERE purchase_rank = 1
)
-- Headline rate, the controllable rate that excludes customer-requested splits, and the GA4 match rate the digital cuts must be read against
SELECT
  c.first_ship_date,
  COALESCE(g.device_category, '(no digital match)') AS device_category,
  COALESCE(g.traffic_source, '(no digital match)') AS traffic_source,
  COUNT(*) AS shipped_orders,
  COUNTIF(c.shipment_count >= 2) AS split_orders,
  COUNTIF(c.shipment_count >= 2 AND NOT c.is_customer_requested_split) AS controllable_split_orders,
  ROUND(SAFE_DIVIDE(COUNTIF(c.shipment_count >= 2), COUNT(*)) * 100, 2) AS split_shipment_rate_pct,
  ROUND(SAFE_DIVIDE(COUNTIF(c.shipment_count >= 2 AND NOT c.is_customer_requested_split), COUNT(*)) * 100, 2) AS controllable_split_rate_pct,
  ROUND(SAFE_DIVIDE(SUM(COUNTIF(g.transaction_id IS NOT NULL)) OVER (PARTITION BY c.first_ship_date), SUM(COUNT(*)) OVER (PARTITION BY c.first_ship_date)) * 100, 2) AS ga4_match_rate_pct
FROM shipped_order_cohort AS c
LEFT JOIN ga4_purchases AS g
  ON g.transaction_id = c.order_id
GROUP BY 1, 2, 3
ORDER BY first_ship_date, shipped_orders DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Split Shipment Rate = orders with >= 2 distinct original-fulfillment shipments / all orders with >= 1 ship-confirmed carrier shipment
-- OMS order and shipment records are ingested as backend datasets; the web purchase (XDM commerce.purchases) is joined on purchaseID for attribution only.
-- Reporting window: the last 30 days of first ship-confirms; the purchase scan reaches 60 days further back because an order can be placed well before it ships.
-- Orders left after the OMS order-type exclusions: test, employee, fraud-cancelled and cancelled-before-ship
WITH eligible_orders AS (
  SELECT o.order_id
  FROM oms_orders_backend_dataset AS o
  WHERE o.is_test_order = false
    AND o.is_employee_order = false
    AND o.order_status NOT IN ('fraud_cancelled', 'cancelled_before_ship')
),
-- Ship-confirmed carrier shipments that belong to original fulfillment: drops BOPIS, curbside, ship-to-store and digital, plus reships, exchanges, return labels and warranty sends
original_shipments AS (
  SELECT
    s.order_id,
    COALESCE(s.shipment_id, s.tracking_number) AS shipment_key,
    s.ship_confirm_ts,
    s.split_reason
  FROM oms_shipments_backend_dataset AS s
  JOIN eligible_orders AS e
    ON e.order_id = s.order_id
  WHERE s.shipment_status = 'ship_confirmed'
    AND s.delivery_mode = 'carrier'
    AND s.shipment_reason_code = 'original_fulfillment'
),
-- One row per order: deduplicated shipment count, first ship-confirm timestamp and the customer-requested split flag
order_shipments AS (
  SELECT
    order_id,
    COUNT(DISTINCT shipment_key) AS shipment_count,
    MIN(ship_confirm_ts) AS first_ship_confirm_ts,
    MAX(CASE WHEN split_reason = 'customer_requested' THEN 1 ELSE 0 END) AS is_customer_requested_split
  FROM original_shipments
  GROUP BY order_id
),
-- Orders assigned to the reporting period by their first ship-confirm date in the store reporting time zone
shipped_order_cohort AS (
  SELECT
    order_id,
    shipment_count,
    is_customer_requested_split,
    to_date(from_utc_timestamp(first_ship_confirm_ts, 'America/New_York')) AS first_ship_date
  FROM order_shipments
  WHERE to_date(from_utc_timestamp(first_ship_confirm_ts, 'America/New_York'))
        BETWEEN date_sub(current_date(), 29) AND current_date()
),
-- First purchase per purchaseID, scanned from before the cohort starts because an order can be placed well before it ships
web_purchases AS (
  SELECT
    purchase_id,
    device_type,
    tracking_code
  FROM (
    SELECT
      commerce.order.purchaseID AS purchase_id,
      device.type AS device_type,
      COALESCE(marketing.trackingCode, '(direct)') AS tracking_code,
      ROW_NUMBER() OVER (PARTITION BY commerce.order.purchaseID ORDER BY timestamp) AS purchase_rank
    FROM web_events_dataset
    WHERE timestamp >= CAST(date_sub(current_date(), 89) AS timestamp)
      AND timestamp < CAST(date_add(current_date(), 1) AS timestamp)
      AND eventType = 'commerce.purchases'
      AND commerce.purchases.value = 1
      AND commerce.order.purchaseID IS NOT NULL
      AND commerce.order.purchaseID <> ''
  ) AS ranked
  WHERE purchase_rank = 1
)
-- Headline rate, the controllable rate that excludes customer-requested splits, and the digital match rate the digital cuts must be read against
SELECT
  c.first_ship_date,
  COALESCE(w.device_type, '(no digital match)') AS device_type,
  COALESCE(w.tracking_code, '(no digital match)') AS tracking_code,
  COUNT(*) AS shipped_orders,
  SUM(CASE WHEN c.shipment_count >= 2 THEN 1 ELSE 0 END) AS split_orders,
  SUM(CASE WHEN c.shipment_count >= 2 AND c.is_customer_requested_split = 0 THEN 1 ELSE 0 END) AS controllable_split_orders,
  ROUND(100.0 * SUM(CASE WHEN c.shipment_count >= 2 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS split_shipment_rate_pct,
  ROUND(100.0 * SUM(CASE WHEN c.shipment_count >= 2 AND c.is_customer_requested_split = 0 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS controllable_split_rate_pct,
  ROUND(100.0 * SUM(SUM(CASE WHEN w.purchase_id IS NOT NULL THEN 1 ELSE 0 END)) OVER (PARTITION BY c.first_ship_date) / NULLIF(SUM(COUNT(*)) OVER (PARTITION BY c.first_ship_date), 0), 2) AS digital_match_rate_pct
FROM shipped_order_cohort AS c
LEFT JOIN web_purchases AS w
  ON w.purchase_id = c.order_id
GROUP BY c.first_ship_date, COALESCE(w.device_type, '(no digital match)'), COALESCE(w.tracking_code, '(no digital match)')
ORDER BY c.first_ship_date, shipped_orders DESC;