Number of Shipments

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "order status",
        "pageType": "order_status"
      }
    },
    "transaction": {
      "transactionID": "ORD-100482",
      "attributes": {
        "shipmentCount": 2
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "page_view",
          "eventAction": "view",
          "type": "order_status"
        },
        "attributes": {
          "transactionID": "ORD-100482",
          "shipmentCount": 2
        }
      }
    ]
  }
}

{
  "event": "page_view",
  "page_type": "order_status",
  "transaction_id": "ORD-100482",
  "shipment_count": 2
}

{
  "event": "page:view",
  "eventInfo": {
    "eventName": "pageView"
  },
  "page": {
    "pageName": "order status",
    "pageType": "order_status"
  },
  "order": {
    "transactionId": "ORD-100482",
    "shipmentCount": 2
  }
}

{
  "eventType": "web.webpagedetails.pageViews",
  "timestamp": "2026-09-13T10:15:00Z",
  "web": {
    "webPageDetails": {
      "name": "order status",
      "siteSection": "order_status",
      "pageViews": {
        "value": 1
      }
    }
  },
  "commerce": {
    "order": {
      "purchaseID": "ORD-100482"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar40": "ORD-100482",
          "eVar45": "order_status",
          "eVar58": "2"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Number of Shipments per fully fulfilled order (bands 1 / 2 / 3+) from the OMS fulfillment tables, Split Shipment Rate over all shipped orders,
-- and order status page_view behaviour by band from the GA4 export (the OMS count is authoritative; the web shipment_count is only compared)
-- oms.* = Shopify fulfillments / Salesforce Order Management FulfillmentOrder + Shipment / Adobe Commerce sales_shipment + sales_shipment_track at order, line, fulfillment and tracking grain
WITH shippable_lines AS (
  SELECT
    l.order_id,
    l.line_id,
    l.quantity_ordered,
    COALESCE(l.quantity_cancelled, 0) AS quantity_cancelled,
    l.cancelled_at
  FROM `your-project.oms.order_lines` AS l
  WHERE COALESCE(l.requires_shipping, TRUE) = TRUE
    AND LOWER(COALESCE(l.fulfillment_method, 'ship')) NOT IN ('digital', 'gift_card', 'e_gift_card', 'warranty', 'service', 'pickup_in_store', 'ship_to_store')
),
original_orders AS (
  SELECT
    o.order_id,
    o.transaction_id,
    LOWER(COALESCE(o.fulfillment_channel, 'merchant')) AS fulfillment_channel,
    DATE(o.created_at) AS order_date
  FROM `your-project.oms.orders` AS o
  WHERE DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
    AND o.cancelled_at IS NULL
    AND LOWER(COALESCE(o.order_type, 'standard')) NOT IN ('exchange', 'replacement', 'reship')
),
-- Outbound fulfillments against original shippable lines in a shipped state only (Shopify success, Salesforce OM Fulfilled, every Adobe Commerce sales_shipment row whether or not its status is populated, NULL status counting as shipped); tracking is required downstream (tracking_numbers > 0); pending, open, cancelled / failed / voided and reship, replacement, return or exchange fulfillments are removed
outbound_fulfillments AS (
  SELECT
    f.fulfillment_id,
    f.order_id,
    f.created_at,
    COUNT(DISTINCT NULLIF(TRIM(t.tracking_number), '')) AS tracking_numbers
  FROM `your-project.oms.fulfillments` AS f
  LEFT JOIN `your-project.oms.fulfillment_tracking` AS t
    ON t.fulfillment_id = f.fulfillment_id
  WHERE f.order_id IN (SELECT order_id FROM original_orders)
    AND (f.status IS NULL OR LOWER(f.status) IN ('success', 'fulfilled', 'shipped', 'complete'))
    AND LOWER(COALESCE(f.status, '')) NOT IN ('cancelled', 'canceled', 'error', 'failure', 'voided', 'pending', 'open')
    AND LOWER(COALESCE(f.fulfillment_reason, 'original')) NOT IN ('reship', 'replacement', 'return', 'exchange')
    AND f.fulfillment_id IN (
      SELECT fl.fulfillment_id
      FROM `your-project.oms.fulfillment_lines` AS fl
      JOIN shippable_lines AS sl
        ON sl.line_id = fl.line_id
    )
  GROUP BY f.fulfillment_id, f.order_id, f.created_at
),
line_progress AS (
  SELECT
    sl.order_id,
    sl.line_id,
    sl.quantity_ordered,
    sl.quantity_cancelled,
    sl.cancelled_at,
    COALESCE(SUM(shipped.quantity), 0) AS quantity_shipped
  FROM shippable_lines AS sl
  LEFT JOIN (
    SELECT fl.line_id, fl.quantity
    FROM `your-project.oms.fulfillment_lines` AS fl
    JOIN outbound_fulfillments AS f
      ON f.fulfillment_id = fl.fulfillment_id
  ) AS shipped
    ON shipped.line_id = sl.line_id
  GROUP BY sl.order_id, sl.line_id, sl.quantity_ordered, sl.quantity_cancelled, sl.cancelled_at
),
order_progress AS (
  SELECT
    o.order_id,
    o.transaction_id,
    o.fulfillment_channel,
    o.order_date,
    COUNT(lp.line_id) AS shippable_lines,
    COUNTIF(lp.quantity_shipped + lp.quantity_cancelled < lp.quantity_ordered) AS open_lines,
    COALESCE(SUM(lp.quantity_shipped), 0) AS units_shipped,
    MAX(IF(lp.quantity_cancelled > 0, lp.cancelled_at, NULL)) AS last_line_cancelled_at
  FROM original_orders AS o
  LEFT JOIN line_progress AS lp
    ON lp.order_id = o.order_id
  GROUP BY o.order_id, o.transaction_id, o.fulfillment_channel, o.order_date
),
fulfillment_counts AS (
  SELECT
    order_id,
    COUNTIF(tracking_numbers > 0) AS tracked_fulfillments,
    COUNTIF(tracking_numbers = 0) AS untracked_fulfillments,
    MAX(created_at) AS last_fulfillment_at
  FROM outbound_fulfillments
  GROUP BY order_id
),
-- Band is fixed only when every shippable line is shipped or cancelled; report_date = final-fulfillment date (pending orders are dated by order date)
order_shipments AS (
  SELECT
    p.order_id,
    p.transaction_id,
    COALESCE(c.tracked_fulfillments, 0) AS number_of_shipments,
    COALESCE(c.untracked_fulfillments, 0) AS untracked_fulfillments,
    CASE
      WHEN p.shippable_lines = 0 THEN '(not applicable)'
      WHEN p.open_lines > 0 THEN '(pending)'
      WHEN p.units_shipped = 0 THEN '(not applicable)'
      WHEN COALESCE(c.tracked_fulfillments, 0) = 0 AND p.fulfillment_channel = 'marketplace' THEN '(not applicable)'
      WHEN COALESCE(c.tracked_fulfillments, 0) = 0 THEN '(not set)'
      WHEN c.tracked_fulfillments = 1 THEN '1'
      WHEN c.tracked_fulfillments = 2 THEN '2'
      ELSE '3+'
    END AS shipments_band,
    COALESCE(
      CASE
        WHEN p.open_lines > 0 THEN p.order_date
        WHEN c.last_fulfillment_at IS NULL THEN DATE(p.last_line_cancelled_at)
        WHEN p.last_line_cancelled_at IS NULL OR c.last_fulfillment_at >= p.last_line_cancelled_at THEN DATE(c.last_fulfillment_at)
        ELSE DATE(p.last_line_cancelled_at)
      END,
      p.order_date
    ) AS report_date
  FROM order_progress AS p
  LEFT JOIN fulfillment_counts AS c
    ON c.order_id = p.order_id
),
reporting_orders AS (
  SELECT *
  FROM order_shipments
  WHERE report_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),
-- Order status page views: event_params pivoted per event with MAX(IF())
order_status_page_views 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 = 'page_type', ep.value.string_value, NULL)) AS page_type,
    MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)) AS transaction_id,
    MAX(IF(ep.key = 'shipment_count', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.double_value AS INT64), SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS web_shipment_count
  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 180 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'page_view'
  GROUP BY e.user_pseudo_id, e.event_timestamp, e.event_bundle_sequence_id
),
web_by_order AS (
  SELECT
    transaction_id,
    COUNT(*) AS order_status_views,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING))) AS order_status_sessions,
    MAX(web_shipment_count) AS max_web_shipment_count
  FROM order_status_page_views
  WHERE page_type = 'order_status'
    AND transaction_id IS NOT NULL
  GROUP BY transaction_id
),
banded AS (
  SELECT
    r.order_id,
    r.shipments_band,
    r.number_of_shipments,
    r.untracked_fulfillments,
    COALESCE(w.order_status_views, 0) AS order_status_views,
    COALESCE(w.order_status_sessions, 0) AS order_status_sessions,
    w.max_web_shipment_count
  FROM reporting_orders AS r
  LEFT JOIN web_by_order AS w
    ON w.transaction_id = r.transaction_id
),
-- Split Shipment Rate denominator: every fully fulfilled order with at least one tracked shipment (bands 1, 2 and 3+)
shipped_base AS (
  SELECT
    COUNTIF(shipments_band IN ('1', '2', '3+')) AS shipped_orders,
    COUNTIF(shipments_band IN ('2', '3+')) AS split_shipment_orders
  FROM banded
)
SELECT
  b.shipments_band AS number_of_shipments_band,
  COUNT(DISTINCT b.order_id) AS orders,
  IF(b.shipments_band IN ('1', '2', '3+'), ROUND(SAFE_DIVIDE(COUNT(DISTINCT b.order_id), MAX(s.shipped_orders)), 4), NULL) AS share_of_shipped_orders,
  ROUND(SAFE_DIVIDE(MAX(s.split_shipment_orders), MAX(s.shipped_orders)), 4) AS split_shipment_rate,
  ROUND(AVG(IF(b.shipments_band IN ('1', '2', '3+'), b.number_of_shipments, NULL)), 2) AS avg_shipments_per_order,
  SUM(b.untracked_fulfillments) AS untracked_fulfillments_for_review,
  COUNTIF(b.order_status_views > 0) AS orders_with_order_status_view,
  ROUND(SAFE_DIVIDE(COUNTIF(b.order_status_views > 0), COUNT(DISTINCT b.order_id)), 4) AS order_status_view_rate,
  ROUND(SAFE_DIVIDE(SUM(b.order_status_views), COUNT(DISTINCT b.order_id)), 2) AS order_status_views_per_order,
  ROUND(SAFE_DIVIDE(SUM(b.order_status_sessions), COUNT(DISTINCT b.order_id)), 2) AS order_status_sessions_per_order,
  COUNTIF(b.shipments_band IN ('1', '2', '3+') AND b.max_web_shipment_count > b.number_of_shipments) AS orders_web_count_above_oms
FROM banded AS b
CROSS JOIN shipped_base AS s
GROUP BY b.shipments_band
ORDER BY CASE b.shipments_band WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3+' THEN 3 WHEN '(not set)' THEN 4 WHEN '(pending)' THEN 5 ELSE 6 END;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Number of Shipments per fully fulfilled order (bands 1 / 2 / 3+) from the OMS fulfillment datasets, Split Shipment Rate over all shipped orders,
-- and order status page view behaviour by band from web events (the OMS count is authoritative; eVar58 shipment count is only compared)
-- oms_* datasets = Shopify fulfillments / Salesforce Order Management FulfillmentOrder + Shipment / Adobe Commerce sales_shipment + sales_shipment_track at order, line, fulfillment and tracking grain
WITH shippable_lines AS (
  SELECT
    l.order_id,
    l.line_id,
    l.quantity_ordered,
    COALESCE(l.quantity_cancelled, 0) AS quantity_cancelled,
    l.cancelled_at
  FROM oms_order_lines_dataset l
  WHERE COALESCE(l.requires_shipping, true) = true
    AND LOWER(COALESCE(l.fulfillment_method, 'ship')) NOT IN ('digital', 'gift_card', 'e_gift_card', 'warranty', 'service', 'pickup_in_store', 'ship_to_store')
),
original_orders AS (
  SELECT
    o.order_id,
    o.transaction_id,
    LOWER(COALESCE(o.fulfillment_channel, 'merchant')) AS fulfillment_channel,
    to_date(o.created_at) AS order_date
  FROM oms_orders_dataset o
  WHERE to_date(o.created_at) >= date_sub(current_date(), 180)
    AND o.cancelled_at IS NULL
    AND LOWER(COALESCE(o.order_type, 'standard')) NOT IN ('exchange', 'replacement', 'reship')
),
-- Outbound fulfillments against original shippable lines in a shipped state only (Shopify success, Salesforce OM Fulfilled, every Adobe Commerce sales_shipment row whether or not its status is populated, NULL status counting as shipped); tracking is required downstream (tracking_numbers > 0); pending, open, cancelled / failed / voided and reship, replacement, return or exchange fulfillments are removed
outbound_fulfillments AS (
  SELECT
    f.fulfillment_id,
    f.order_id,
    f.created_at,
    COUNT(DISTINCT NULLIF(TRIM(t.tracking_number), '')) AS tracking_numbers
  FROM oms_fulfillments_dataset f
  LEFT JOIN oms_fulfillment_tracking_dataset t
    ON t.fulfillment_id = f.fulfillment_id
  WHERE f.order_id IN (SELECT order_id FROM original_orders)
    AND (f.status IS NULL OR LOWER(f.status) IN ('success', 'fulfilled', 'shipped', 'complete'))
    AND LOWER(COALESCE(f.status, '')) NOT IN ('cancelled', 'canceled', 'error', 'failure', 'voided', 'pending', 'open')
    AND LOWER(COALESCE(f.fulfillment_reason, 'original')) NOT IN ('reship', 'replacement', 'return', 'exchange')
    AND f.fulfillment_id IN (
      SELECT fl.fulfillment_id
      FROM oms_fulfillment_lines_dataset fl
      JOIN shippable_lines sl
        ON sl.line_id = fl.line_id
    )
  GROUP BY f.fulfillment_id, f.order_id, f.created_at
),
line_progress AS (
  SELECT
    sl.order_id,
    sl.line_id,
    sl.quantity_ordered,
    sl.quantity_cancelled,
    sl.cancelled_at,
    COALESCE(SUM(shipped.quantity), 0) AS quantity_shipped
  FROM shippable_lines sl
  LEFT JOIN (
    SELECT fl.line_id, fl.quantity
    FROM oms_fulfillment_lines_dataset fl
    JOIN outbound_fulfillments f
      ON f.fulfillment_id = fl.fulfillment_id
  ) shipped
    ON shipped.line_id = sl.line_id
  GROUP BY sl.order_id, sl.line_id, sl.quantity_ordered, sl.quantity_cancelled, sl.cancelled_at
),
order_progress AS (
  SELECT
    o.order_id,
    o.transaction_id,
    o.fulfillment_channel,
    o.order_date,
    COUNT(lp.line_id) AS shippable_lines,
    SUM(CASE WHEN lp.quantity_shipped + lp.quantity_cancelled < lp.quantity_ordered THEN 1 ELSE 0 END) AS open_lines,
    COALESCE(SUM(lp.quantity_shipped), 0) AS units_shipped,
    MAX(CASE WHEN lp.quantity_cancelled > 0 THEN lp.cancelled_at END) AS last_line_cancelled_at
  FROM original_orders o
  LEFT JOIN line_progress lp
    ON lp.order_id = o.order_id
  GROUP BY o.order_id, o.transaction_id, o.fulfillment_channel, o.order_date
),
fulfillment_counts AS (
  SELECT
    order_id,
    SUM(CASE WHEN tracking_numbers > 0 THEN 1 ELSE 0 END) AS tracked_fulfillments,
    SUM(CASE WHEN tracking_numbers = 0 THEN 1 ELSE 0 END) AS untracked_fulfillments,
    MAX(created_at) AS last_fulfillment_at
  FROM outbound_fulfillments
  GROUP BY order_id
),
-- Band is fixed only when every shippable line is shipped or cancelled; report_date = final-fulfillment date (pending orders are dated by order date)
order_shipments AS (
  SELECT
    p.order_id,
    p.transaction_id,
    COALESCE(c.tracked_fulfillments, 0) AS number_of_shipments,
    COALESCE(c.untracked_fulfillments, 0) AS untracked_fulfillments,
    CASE
      WHEN p.shippable_lines = 0 THEN '(not applicable)'
      WHEN p.open_lines > 0 THEN '(pending)'
      WHEN p.units_shipped = 0 THEN '(not applicable)'
      WHEN COALESCE(c.tracked_fulfillments, 0) = 0 AND p.fulfillment_channel = 'marketplace' THEN '(not applicable)'
      WHEN COALESCE(c.tracked_fulfillments, 0) = 0 THEN '(not set)'
      WHEN c.tracked_fulfillments = 1 THEN '1'
      WHEN c.tracked_fulfillments = 2 THEN '2'
      ELSE '3+'
    END AS shipments_band,
    COALESCE(
      CASE
        WHEN p.open_lines > 0 THEN p.order_date
        WHEN c.last_fulfillment_at IS NULL THEN to_date(p.last_line_cancelled_at)
        WHEN p.last_line_cancelled_at IS NULL OR c.last_fulfillment_at >= p.last_line_cancelled_at THEN to_date(c.last_fulfillment_at)
        ELSE to_date(p.last_line_cancelled_at)
      END,
      p.order_date
    ) AS report_date
  FROM order_progress p
  LEFT JOIN fulfillment_counts c
    ON c.order_id = p.order_id
),
reporting_orders AS (
  SELECT *
  FROM order_shipments
  WHERE report_date >= date_sub(current_date(), 90)
    AND report_date < current_date()
),
-- Order status page views (web.webPageDetails.siteSection = order_status), sessionised with a 30-minute timeout
order_status_page_views AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp AS event_ts,
    commerce.order.purchaseID AS transaction_id,
    CAST(_experience.analytics.customDimensions.eVars.eVar58 AS INT) AS web_shipment_count,
    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(), 180)
    AND timestamp < current_date()
    AND eventType = 'web.webpagedetails.pageViews'
    AND web.webPageDetails.siteSection = 'order_status'
    AND commerce.order.purchaseID IS NOT NULL
),
web_by_order AS (
  SELECT
    transaction_id,
    COUNT(*) AS order_status_views,
    COUNT(DISTINCT CONCAT(ecid, '.', CAST(session.num AS STRING))) AS order_status_sessions,
    MAX(web_shipment_count) AS max_web_shipment_count
  FROM order_status_page_views
  GROUP BY transaction_id
),
banded AS (
  SELECT
    r.order_id,
    r.shipments_band,
    r.number_of_shipments,
    r.untracked_fulfillments,
    COALESCE(w.order_status_views, 0) AS order_status_views,
    COALESCE(w.order_status_sessions, 0) AS order_status_sessions,
    w.max_web_shipment_count
  FROM reporting_orders r
  LEFT JOIN web_by_order w
    ON w.transaction_id = r.transaction_id
),
-- Split Shipment Rate denominator: every fully fulfilled order with at least one tracked shipment (bands 1, 2 and 3+)
shipped_base AS (
  SELECT
    SUM(CASE WHEN shipments_band IN ('1', '2', '3+') THEN 1 ELSE 0 END) AS shipped_orders,
    SUM(CASE WHEN shipments_band IN ('2', '3+') THEN 1 ELSE 0 END) AS split_shipment_orders
  FROM banded
)
SELECT
  b.shipments_band AS number_of_shipments_band,
  COUNT(DISTINCT b.order_id) AS orders,
  CASE WHEN b.shipments_band IN ('1', '2', '3+')
       THEN ROUND(CAST(COUNT(DISTINCT b.order_id) AS DOUBLE) / NULLIF(MAX(s.shipped_orders), 0), 4) END AS share_of_shipped_orders,
  ROUND(CAST(MAX(s.split_shipment_orders) AS DOUBLE) / NULLIF(MAX(s.shipped_orders), 0), 4) AS split_shipment_rate,
  ROUND(AVG(CASE WHEN b.shipments_band IN ('1', '2', '3+') THEN b.number_of_shipments END), 2) AS avg_shipments_per_order,
  SUM(b.untracked_fulfillments) AS untracked_fulfillments_for_review,
  SUM(CASE WHEN b.order_status_views > 0 THEN 1 ELSE 0 END) AS orders_with_order_status_view,
  ROUND(CAST(SUM(CASE WHEN b.order_status_views > 0 THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(DISTINCT b.order_id), 0), 4) AS order_status_view_rate,
  ROUND(CAST(SUM(b.order_status_views) AS DOUBLE) / NULLIF(COUNT(DISTINCT b.order_id), 0), 2) AS order_status_views_per_order,
  ROUND(CAST(SUM(b.order_status_sessions) AS DOUBLE) / NULLIF(COUNT(DISTINCT b.order_id), 0), 2) AS order_status_sessions_per_order,
  SUM(CASE WHEN b.shipments_band IN ('1', '2', '3+') AND b.max_web_shipment_count > b.number_of_shipments THEN 1 ELSE 0 END) AS orders_web_count_above_oms
FROM banded b
CROSS JOIN shipped_base s
GROUP BY b.shipments_band
ORDER BY CASE b.shipments_band WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3+' THEN 3 WHEN '(not set)' THEN 4 WHEN '(pending)' THEN 5 ELSE 6 END;