Purchase Conversion Rate

{
  "pageInstanceID": "order-confirmation-production",
  "page": {
    "pageInfo": {
      "pageID": "checkout-order-confirmation",
      "pageName": "Order Confirmation",
      "destinationURL": "https://www.example-retail.com/checkout/confirmation",
      "sysEnv": "web"
    },
    "category": {
      "primaryCategory": "checkout",
      "pageType": "order confirmation"
    }
  },
  "transaction": {
    "transactionID": "ORD-100482",
    "total": {
      "basePrice": 69.97,
      "currency": "USD",
      "priceWithTax": 69.97,
      "transactionTotal": 69.97
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-20417",
          "productName": "Classic Crew Neck T-Shirt",
          "sku": "SKU-20417"
        },
        "category": {
          "primaryCategory": "Apparel"
        },
        "quantity": 2,
        "price": {
          "basePrice": 24.99,
          "currency": "USD"
        }
      },
      {
        "productInfo": {
          "productID": "SKU-31885",
          "productName": "Canvas Tote Bag",
          "sku": "SKU-31885"
        },
        "category": {
          "primaryCategory": "Accessories"
        },
        "quantity": 1,
        "price": {
          "basePrice": 19.99,
          "currency": "USD"
        }
      }
    ]
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "purchase",
        "eventAction": "transaction complete",
        "type": "conversion",
        "timeStamp": "2026-09-14T15:42:07.000Z"
      },
      "category": {
        "primaryCategory": "ecommerce"
      }
    }
  ]
}

{
  "event": "purchase",
  "ecommerce": {
    "transaction_id": "ORD-100482",
    "value": 69.97,
    "currency": "USD",
    "items": [
      {
        "item_id": "SKU-20417",
        "item_name": "Classic Crew Neck T-Shirt",
        "item_category": "Apparel",
        "price": 24.99,
        "quantity": 2,
        "index": 0
      },
      {
        "item_id": "SKU-31885",
        "item_name": "Canvas Tote Bag",
        "item_category": "Accessories",
        "price": 19.99,
        "quantity": 1,
        "index": 1
      }
    ]
  }
}

{
  "event": "commerce.purchases",
  "web": {
    "webPageDetails": {
      "name": "Order Confirmation",
      "URL": "https://www.example-retail.com/checkout/confirmation"
    }
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-100482",
      "priceTotal": 69.97,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-20417",
      "name": "Classic Crew Neck T-Shirt",
      "quantity": 2,
      "priceTotal": 49.98,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-31885",
      "name": "Canvas Tote Bag",
      "quantity": 1,
      "priceTotal": 19.99,
      "currencyCode": "USD"
    }
  ]
}

{
  "_id": "7d0c8f9e-4b1a-4e7b-9c52-3f6a1d2e8b10",
  "eventType": "commerce.purchases",
  "timestamp": "2026-09-14T15:42:07.000Z",
  "identityMap": {
    "ECID": [
      {
        "id": "45216371098473628190537481926354710293",
        "authenticatedState": "ambiguous",
        "primary": true
      }
    ]
  },
  "endUserIDs": {
    "_experience": {
      "ecid": {
        "id": "45216371098473628190537481926354710293"
      }
    }
  },
  "web": {
    "webPageDetails": {
      "name": "Order Confirmation",
      "URL": "https://www.example-retail.com/checkout/confirmation"
    }
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-100482",
      "priceTotal": 69.97,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-20417",
      "name": "Classic Crew Neck T-Shirt",
      "quantity": 2,
      "priceTotal": 49.98,
      "currencyCode": "USD"
    },
    {
      "SKU": "SKU-31885",
      "name": "Canvas Tote Bag",
      "quantity": 1,
      "priceTotal": 19.99,
      "currencyCode": "USD"
    }
  ]
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Purchase Conversion Rate (session scope) = eligible sessions with >= 1 deduplicated purchase / all eligible sessions x 100.
-- Reporting window 2026-08-01 to 2026-08-31 (property time zone). Daily tables are scanned one day either side so sessions crossing midnight are complete and attributed in full to their start date.
-- Known bots and spiders are removed by GA4 automatic bot filtering before export; every other exclusion is applied per session to numerator and denominator alike.
-- Test orders use the reserved transaction_id prefix TEST; align it with the order-management convention.
WITH raw_events AS (
  SELECT
    e.user_pseudo_id,
    e.event_date,
    e.event_timestamp,
    e.event_name,
    e.platform,
    e.ecommerce.transaction_id AS transaction_id,
    LOWER(e.device.web_info.hostname) AS hostname,
    e.privacy_info.analytics_storage AS analytics_storage,
    (
      SELECT AS STRUCT
        MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
        MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type,
        MAX(IF(ep.key = 'debug_mode', COALESCE(CAST(ep.value.int_value AS STRING), ep.value.string_value), NULL)) AS debug_mode
      FROM UNNEST(e.event_params) AS ep
    ) AS params
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN '20260731' AND '20260901'
),
session_events AS (
  SELECT
    CONCAT(user_pseudo_id, '.', CAST(params.ga_session_id AS STRING)) AS session_key,
    event_date,
    event_timestamp,
    event_name,
    platform,
    transaction_id,
    hostname,
    analytics_storage,
    params.traffic_type AS traffic_type,
    params.debug_mode AS debug_mode
  FROM raw_events
  WHERE user_pseudo_id IS NOT NULL
    AND params.ga_session_id IS NOT NULL
),
first_purchases AS (
  SELECT
    transaction_id,
    session_key
  FROM session_events
  WHERE event_name = 'purchase'
    AND transaction_id IS NOT NULL
    AND transaction_id NOT IN ('', '(not set)')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),
sessions AS (
  SELECT
    session_key,
    PARSE_DATE('%Y%m%d', MIN(event_date)) AS session_date,
    CASE MAX(platform)
      WHEN 'WEB' THEN 'Web'
      WHEN 'IOS' THEN 'iOS app'
      WHEN 'ANDROID' THEN 'Android app'
      ELSE 'Other'
    END AS stream_platform,
    LOGICAL_OR(IFNULL(analytics_storage = 'No', FALSE)) AS is_consent_denied,
    LOGICAL_OR(IFNULL(traffic_type = 'internal', FALSE)) AS is_internal,
    LOGICAL_OR(IFNULL(debug_mode IN ('1', 'true'), FALSE)) AS is_debug,
    LOGICAL_OR(IFNULL(REGEXP_CONTAINS(hostname, r'(^|[.])(staging|stage|dev|qa|uat|preview)[.-]|localhost'), FALSE)) AS is_staging,
    LOGICAL_OR(IFNULL(UPPER(transaction_id) LIKE 'TEST%', FALSE)) AS is_test_order
  FROM session_events
  GROUP BY session_key
),
eligible_sessions AS (
  SELECT
    s.session_date,
    s.stream_platform,
    s.session_key,
    fp.session_key IS NOT NULL AS has_purchase
  FROM sessions AS s
  LEFT JOIN (SELECT DISTINCT session_key FROM first_purchases) AS fp
    ON fp.session_key = s.session_key
  WHERE s.session_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
    AND NOT s.is_consent_denied
    AND NOT s.is_internal
    AND NOT s.is_debug
    AND NOT s.is_staging
    AND NOT s.is_test_order
)
SELECT
  session_date,
  IF(GROUPING(stream_platform) = 1, 'All platforms', stream_platform) AS platform,
  COUNT(DISTINCT session_key) AS sessions,
  COUNT(DISTINCT IF(has_purchase, session_key, NULL)) AS sessions_with_purchase,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT IF(has_purchase, session_key, NULL)), COUNT(DISTINCT session_key)) * 100, 2) AS purchase_conversion_rate
FROM eligible_sessions
GROUP BY GROUPING SETS ((session_date, stream_platform), (session_date))
ORDER BY session_date, platform;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Purchase Conversion Rate (session scope) = eligible sessions with >= 1 deduplicated purchase / all eligible sessions x 100.
-- Reporting window 2026-08-01 to 2026-08-31 in the reporting time zone (America/New_York below; replace with the report suite zone). Events are read one day either side so sessions crossing midnight are complete and attributed to their start date.
-- Sessions are rebuilt across ALL experience events per ECID with a 30-minute inactivity timeout.
-- _tenant.trafficType (internal flag set by collection rules) and _tenant.analyticsConsent (CMP analytics purpose, y / n) are tenant field-group paths to be adapted; map them to the schema paths in use.
-- Test orders use the reserved purchaseID prefix TEST; align it with the order-management convention.
WITH base AS (
  SELECT
    timestamp,
    eventType,
    COALESCE(identityMap['ECID'][0].id, endUserIDs._experience.ecid.id) AS ecid,
    commerce.order.purchaseID AS purchase_id,
    lower(web.webPageDetails.URL) AS page_url,
    lower(environment.browserDetails.userAgent) AS user_agent,
    CASE
      WHEN environment.type = 'application' AND lower(environment.operatingSystem) LIKE '%ios%' THEN 'iOS app'
      WHEN environment.type = 'application' AND lower(environment.operatingSystem) LIKE '%android%' THEN 'Android app'
      WHEN environment.type = 'application' THEN 'Other app'
      ELSE 'Web'
    END AS stream_platform,
    _tenant.trafficType AS traffic_type,
    lower(_tenant.analyticsConsent) AS analytics_consent
  FROM your_aep_web_events_dataset
  WHERE timestamp >= to_timestamp('2026-07-31 00:00:00')
    AND timestamp < to_timestamp('2026-09-02 00:00:00')
),
sessionized AS (
  SELECT
    base.*,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (PARTITION BY ecid ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
  FROM base
  WHERE ecid IS NOT NULL
),
session_events AS (
  SELECT
    concat(ecid, '-', CAST(session.num AS STRING)) AS session_key,
    timestamp,
    eventType,
    purchase_id,
    page_url,
    user_agent,
    stream_platform,
    traffic_type,
    analytics_consent
  FROM sessionized
),
first_purchases AS (
  SELECT session_key
  FROM (
    SELECT
      session_key,
      ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY timestamp) AS purchase_rank
    FROM session_events
    WHERE eventType = 'commerce.purchases'
      AND purchase_id IS NOT NULL
      AND purchase_id <> ''
  ) ranked_purchases
  WHERE purchase_rank = 1
  GROUP BY session_key
),
sessions AS (
  SELECT
    session_key,
    to_date(from_utc_timestamp(MIN(timestamp), 'America/New_York')) AS session_date,
    MAX(stream_platform) AS stream_platform,
    MAX(CASE WHEN analytics_consent = 'n' THEN 1 ELSE 0 END) AS is_consent_denied,
    MAX(CASE WHEN traffic_type = 'internal' THEN 1 ELSE 0 END) AS is_internal,
    MAX(CASE WHEN user_agent RLIKE 'bot|spider|crawl|slurp|headless|phantomjs|lighthouse|pingdom' THEN 1 ELSE 0 END) AS is_bot,
    MAX(CASE WHEN page_url RLIKE '^https?://([^/]*[.])?(staging|stage|dev|qa|uat|preview)[.-]|^https?://localhost' THEN 1 ELSE 0 END) AS is_staging,
    MAX(CASE WHEN upper(purchase_id) LIKE 'TEST%' THEN 1 ELSE 0 END) AS is_test_order
  FROM session_events
  GROUP BY session_key
),
eligible_sessions AS (
  SELECT
    s.session_date,
    s.stream_platform,
    s.session_key,
    CASE WHEN fp.session_key IS NOT NULL THEN 1 ELSE 0 END AS has_purchase
  FROM sessions s
  LEFT JOIN first_purchases fp
    ON fp.session_key = s.session_key
  WHERE s.session_date BETWEEN to_date('2026-08-01') AND to_date('2026-08-31')
    AND s.is_consent_denied = 0
    AND s.is_internal = 0
    AND s.is_bot = 0
    AND s.is_staging = 0
    AND s.is_test_order = 0
)
SELECT
  session_date,
  stream_platform AS platform,
  COUNT(DISTINCT session_key) AS sessions,
  SUM(has_purchase) AS sessions_with_purchase,
  ROUND(CAST(SUM(has_purchase) AS DOUBLE) / NULLIF(COUNT(DISTINCT session_key), 0) * 100, 2) AS purchase_conversion_rate
FROM eligible_sessions
GROUP BY session_date, stream_platform
UNION ALL
SELECT
  session_date,
  'All platforms' AS platform,
  COUNT(DISTINCT session_key) AS sessions,
  SUM(has_purchase) AS sessions_with_purchase,
  ROUND(CAST(SUM(has_purchase) AS DOUBLE) / NULLIF(COUNT(DISTINCT session_key), 0) * 100, 2) AS purchase_conversion_rate
FROM eligible_sessions
GROUP BY session_date
ORDER BY session_date, platform;