Omnichannel Customer Rate

{
  "digitalData": {
    "transaction": {
      "transactionID": "T-88213",
      "profile": {
        "profileInfo": {
          "userID": "cust_5f2a19"
        }
      },
      "total": {
        "transactionTotal": 129.99,
        "currency": "USD"
      },
      "attributes": {
        "fulfillment_method": "pickup_in_store",
        "customer_type": "returning",
        "channel": "digital"
      }
    }
  }
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "T-88213",
    "value": 129.99,
    "currency": "USD",
    "items": [
      {
        "item_id": "SKU-4471",
        "item_name": "Wireless Earbuds",
        "quantity": 1,
        "price": 129.99
      }
    ]
  },
  "user_id": "cust_5f2a19",
  "customer_type": "returning",
  "fulfillment_method": "pickup_in_store",
  "traffic_type": "returning"
}

{
  "event": "purchase",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-88213",
      "priceTotal": 129.99
    }
  },
  "user": {
    "userID": "cust_5f2a19",
    "customer_type": "returning"
  },
  "fulfillment_method": "pickup_in_store"
}

{
  "identityMap": {
    "CRMID": [
      {
        "id": "cust_5f2a19",
        "authenticatedState": "authenticated"
      }
    ]
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-88213",
      "priceTotal": 129.99,
      "fulfillmentMethod": "pickup_in_store"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "fulfillment_method",
          "eVar2": "customer_type"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

WITH
-- Eligible resolved customers: B2B / reseller accounts and employees excluded
eligible_customers AS (
  SELECT customer_id
  FROM `your-project.crm.customers`
  WHERE account_type NOT IN ('b2b', 'reseller')
    AND NOT is_employee
),
-- Non-qualifying orders: cancelled, auto-cancelled uncollected pickups, test and employee orders
non_qualifying_orders AS (
  SELECT order_id
  FROM `your-project.oms.orders`
  WHERE order_status IN ('cancelled', 'auto_cancelled')
    OR is_test
    OR is_employee_order
),
-- Digital leg: non-zero web / app purchase events dated by order placement, resolved from the hashed user_id
digital_leg AS (
  SELECT DISTINCT m.customer_id
  FROM `your-project.analytics_123456789.events_*` AS e
  JOIN `your-project.crm.customer_identity_map` AS m
    ON m.id_namespace = 'ga4_user_id' AND m.id_value = e.user_id
  WHERE e._TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND e.event_name = 'purchase'
    AND e.ecommerce.purchase_revenue > 0
    AND e.ecommerce.transaction_id NOT IN (SELECT order_id FROM non_qualifying_orders)
),
-- Physical leg (pickup): collected BOPIS, curbside and ship-to-store orders dated by collection timestamp
pickup_leg AS (
  SELECT DISTINCT customer_id
  FROM `your-project.oms.orders`
  WHERE fulfillment_method IN ('pickup_in_store', 'curbside', 'ship_to_store')
    AND collected_at IS NOT NULL
    AND DATE(collected_at) BETWEEN start_date AND end_date
    AND order_value > 0
    AND order_id NOT IN (SELECT order_id FROM non_qualifying_orders)
),
-- Physical leg (POS): non-zero in-store sales dated by receipt time in store local time; anonymous receipts that cannot be resolved are excluded
pos_sales AS (
  SELECT DISTINCT m.customer_id
  FROM `your-project.pos.store_transactions` AS t
  JOIN `your-project.crm.customer_identity_map` AS m
    ON m.id_namespace = t.id_namespace AND m.id_value = t.id_value
  WHERE t.transaction_type = 'sale'
    AND NOT t.is_voided
    AND NOT t.is_test
    AND NOT t.is_employee_sale
    AND t.net_value > 0
    AND DATE(t.receipt_local_ts) BETWEEN start_date AND end_date
),
-- Channel legs per eligible purchasing customer (anonymous store receipts excluded from both sides)
customer_legs AS (
  SELECT
    l.customer_id,
    LOGICAL_OR(l.leg = 'digital') AS has_digital,
    LOGICAL_OR(l.leg = 'store') AS has_store
  FROM (
    SELECT customer_id, 'digital' AS leg FROM digital_leg
    UNION ALL SELECT customer_id, 'store' FROM pickup_leg
    UNION ALL SELECT customer_id, 'store' FROM pos_sales
  ) AS l
  JOIN eligible_customers AS c ON c.customer_id = l.customer_id
  GROUP BY l.customer_id
)
-- Omnichannel customers (digital and store leg) over all purchasing customers
SELECT
  COUNT(*) AS purchasing_customers,
  COUNTIF(has_digital AND has_store) AS omnichannel_customers,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(has_digital AND has_store), COUNT(*)), 2) AS omnichannel_customer_rate_pct
FROM customer_legs;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH
-- Reporting window: last 90 complete days
params AS (
  SELECT CAST(CURRENT_DATE - INTERVAL '90' DAY AS DATE) AS start_date,
         CAST(CURRENT_DATE - INTERVAL '1' DAY AS DATE) AS end_date
),
-- Eligible resolved customers: B2B / reseller accounts and employees excluded
eligible_customers AS (
  SELECT customer_id
  FROM aep_backend_crm_customers
  WHERE account_type NOT IN ('b2b', 'reseller')
    AND NOT is_employee
),
-- Non-qualifying orders: cancelled, auto-cancelled uncollected pickups, test and employee orders
non_qualifying_orders AS (
  SELECT order_id
  FROM aep_backend_oms_orders
  WHERE order_status IN ('cancelled', 'auto_cancelled')
    OR is_test
    OR is_employee_order
),
-- Digital leg: non-zero web / app purchase events dated by order placement, resolved from the CRMID
digital_leg AS (
  SELECT DISTINCT m.customer_id
  FROM aep_web_experience_events AS e
  CROSS JOIN params AS p
  JOIN aep_backend_customer_identity_map AS m
    ON m.id_namespace = 'CRMID' AND m.id_value = e.identityMap['CRMID'][0].id
  WHERE CAST(e.timestamp AS DATE) BETWEEN p.start_date AND p.end_date
    AND e.commerce.purchases.value = 1
    AND e.commerce.order.priceTotal > 0
    AND e.commerce.order.purchaseID NOT IN (SELECT order_id FROM non_qualifying_orders)
),
-- Physical leg (pickup): collected BOPIS, curbside and ship-to-store orders dated by collection timestamp
pickup_leg AS (
  SELECT DISTINCT o.customer_id
  FROM aep_backend_oms_orders AS o
  CROSS JOIN params AS p
  WHERE o.fulfillment_method IN ('pickup_in_store', 'curbside', 'ship_to_store')
    AND o.collected_at IS NOT NULL
    AND CAST(o.collected_at AS DATE) BETWEEN p.start_date AND p.end_date
    AND o.order_value > 0
    AND o.order_id NOT IN (SELECT order_id FROM non_qualifying_orders)
),
-- Physical leg (POS): non-zero in-store sales dated by receipt time in store local time; anonymous receipts that cannot be resolved are excluded
pos_sales AS (
  SELECT DISTINCT m.customer_id
  FROM aep_backend_pos_store_transactions AS t
  CROSS JOIN params AS p
  JOIN aep_backend_customer_identity_map AS m
    ON m.id_namespace = t.id_namespace AND m.id_value = t.id_value
  WHERE t.transaction_type = 'sale'
    AND NOT t.is_voided
    AND NOT t.is_test
    AND NOT t.is_employee_sale
    AND t.net_value > 0
    AND CAST(t.receipt_local_ts AS DATE) BETWEEN p.start_date AND p.end_date
),
-- Channel legs per eligible purchasing customer (anonymous store receipts excluded from both sides)
customer_legs AS (
  SELECT
    l.customer_id,
    BOOL_OR(l.leg = 'digital') AS has_digital,
    BOOL_OR(l.leg = 'store') AS has_store
  FROM (
    SELECT customer_id, 'digital' AS leg FROM digital_leg
    UNION ALL SELECT customer_id, 'store' FROM pickup_leg
    UNION ALL SELECT customer_id, 'store' FROM pos_sales
  ) AS l
  JOIN eligible_customers AS c ON c.customer_id = l.customer_id
  GROUP BY l.customer_id
)
-- Omnichannel customers (digital and store leg) over all purchasing customers
SELECT
  COUNT(*) AS purchasing_customers,
  COUNT_IF(has_digital AND has_store) AS omnichannel_customers,
  ROUND(100.0 * COUNT_IF(has_digital AND has_store) / NULLIF(COUNT(*), 0), 2) AS omnichannel_customer_rate_pct
FROM customer_legs;