Checkout Type

{
  "page": {
    "pageInfo": {
      "pageID": "checkout-confirmation",
      "pageName": "checkout:order confirmation"
    },
    "category": {
      "primaryCategory": "checkout"
    }
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "CUST-000482193"
          },
          "attributes": {
            "loginStatus": "logged_in"
          }
        }
      ]
    }
  ],
  "transaction": {
    "transactionID": "T-100245",
    "total": {
      "basePrice": 119.9,
      "shipping": 10,
      "transactionTotal": 129.9,
      "currency": "USD"
    },
    "attributes": {
      "checkoutType": "registered",
      "checkoutStepEvaluated": "purchase",
      "paymentMethod": "credit_card"
    }
  }
}

{
  "event": "purchase",
  "user_id": "CUST-000482193",
  "login_status": "logged_in",
  "checkout_type": "registered",
  "ecommerce": {
    "transaction_id": "T-100245",
    "value": 129.9,
    "currency": "USD",
    "shipping": 10,
    "payment_type": "credit_card",
    "items": [
      {
        "item_id": "SKU-20931",
        "item_name": "Merino Crew Sweater",
        "item_category": "Apparel",
        "price": 119.9,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "eventInfo": {
    "path": "transaction"
  },
  "site": {
    "trafficType": "external"
  },
  "user": {
    "profileID": "CUST-000482193",
    "loginStatus": "logged_in"
  },
  "transaction": {
    "transactionID": "T-100245",
    "checkoutType": "registered",
    "total": 129.9,
    "currency": "USD",
    "paymentMethod": "credit_card",
    "items": [
      {
        "sku": "SKU-20931",
        "name": "Merino Crew Sweater",
        "category": "Apparel",
        "price": 119.9,
        "quantity": 1
      }
    ]
  }
}

{
  "eventType": "commerce.purchases",
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "T-100245",
      "priceTotal": 129.9,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-20931",
      "name": "Merino Crew Sweater",
      "priceTotal": 119.9,
      "quantity": 1
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar21": "registered",
          "eVar22": "logged_in",
          "eVar23": "external"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Checkout Type (guest / registered / unknown) at begin_checkout (first per session) and at purchase (latest per transaction_id)
-- Filters: internal traffic (traffic_type = 'internal') is removed from all rows, NULL treated as external; purchase rows are anti-joined on transaction_id to your-project.commerce.order_exclusions (test, internal staff and assisted orders).
WITH checkout_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    NULLIF(user_id, '') AS user_id,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) AS ga_session_id,
    LOWER(MAX(IF(ep.key = 'checkout_type', ep.value.string_value, NULL))) AS checkout_type_param,
    LOWER(MAX(IF(ep.key = 'login_status', ep.value.string_value, NULL))) AS login_status,
    ANY_VALUE(ecommerce.transaction_id) AS transaction_id,
    ANY_VALUE(ecommerce.purchase_revenue) AS purchase_revenue
  FROM `your-project.analytics_123456789.events_*`
  LEFT JOIN UNNEST(event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND event_name IN ('begin_checkout', 'purchase')
    AND COALESCE((SELECT p.value.string_value FROM UNNEST(event_params) AS p WHERE p.key = 'traffic_type' LIMIT 1), 'external') <> 'internal'
  GROUP BY event_date, event_name, event_timestamp, user_pseudo_id, user_id, event_bundle_sequence_id
),
classified AS (
  SELECT
    *,
    CASE
      WHEN checkout_type_param IN ('guest', 'registered') THEN checkout_type_param
      WHEN login_status = 'logged_in' OR user_id IS NOT NULL THEN 'registered'
      WHEN login_status IN ('logged_out', 'guest') THEN 'guest'
      ELSE 'unknown'
    END AS checkout_type
  FROM checkout_events
),
checkout_starts AS (
  SELECT
    event_date,
    'begin_checkout' AS checkout_step,
    checkout_type,
    CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS unit_key,
    CAST(0 AS FLOAT64) AS revenue
  FROM classified
  WHERE event_name = 'begin_checkout'
    AND ga_session_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) = 1
),
orders AS (
  SELECT
    c.event_date,
    'purchase' AS checkout_step,
    c.checkout_type,
    c.transaction_id AS unit_key,
    COALESCE(c.purchase_revenue, 0) AS revenue
  FROM classified AS c
  LEFT JOIN `your-project.commerce.order_exclusions` AS excl
    ON excl.transaction_id = c.transaction_id
  WHERE c.event_name = 'purchase'
    AND c.transaction_id IS NOT NULL
    AND c.transaction_id NOT IN ('', '(not set)')
    AND excl.transaction_id IS NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY c.transaction_id ORDER BY c.event_timestamp DESC) = 1
),
combined AS (
  SELECT * FROM checkout_starts
  UNION ALL
  SELECT * FROM orders
)
SELECT
  event_date,
  checkout_step,
  checkout_type,
  COUNT(DISTINCT unit_key) AS checkouts_or_orders,
  ROUND(SUM(revenue), 2) AS revenue,
  SAFE_DIVIDE(
    COUNT(DISTINCT unit_key),
    SUM(COUNT(DISTINCT unit_key)) OVER (PARTITION BY event_date, checkout_step)
  ) AS share_of_step_total
FROM combined
GROUP BY event_date, checkout_step, checkout_type
ORDER BY event_date, checkout_step, checkout_type;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Checkout Type (guest / registered / unknown) at checkout start (first per session) and at purchase (latest per purchaseID)
-- Filters: internal traffic (eVar23 traffic type = 'internal') is removed from all rows, NULL treated as external; purchase rows are anti-joined on purchaseID to order_exclusions_dataset (test, internal staff and assisted orders).
WITH checkout_events AS (
  SELECT
    timestamp,
    CAST(timestamp AS DATE) AS event_date,
    eventType,
    endUserIDs._experience.ecid.id AS ecid,
    NULLIF(identityMap['CRMID'][0].id, '') AS crm_id,
    LOWER(_experience.analytics.customDimensions.eVars.eVar21) AS checkout_type_param,
    LOWER(_experience.analytics.customDimensions.eVars.eVar22) AS login_status,
    commerce.order.purchaseID AS purchase_id,
    commerce.order.priceTotal AS order_total,
    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(), 30)
    AND timestamp < current_date()
    AND (commerce.checkouts.value > 0 OR commerce.purchases.value > 0)
    AND COALESCE(LOWER(_experience.analytics.customDimensions.eVars.eVar23), 'external') <> 'internal'
),
classified AS (
  SELECT
    timestamp,
    event_date,
    ecid,
    session.num AS session_num,
    purchase_id,
    order_total,
    CASE WHEN eventType = 'commerce.purchases' THEN 'purchase' ELSE 'begin_checkout' END AS checkout_step,
    CASE
      WHEN checkout_type_param IN ('guest', 'registered') THEN checkout_type_param
      WHEN login_status = 'logged_in' OR crm_id IS NOT NULL THEN 'registered'
      WHEN login_status IN ('logged_out', 'guest') THEN 'guest'
      ELSE 'unknown'
    END AS checkout_type
  FROM checkout_events
),
checkout_starts AS (
  SELECT event_date, checkout_step, checkout_type, unit_key, CAST(0 AS DOUBLE) AS revenue
  FROM (
    SELECT
      event_date,
      checkout_step,
      checkout_type,
      CONCAT(ecid, '.', CAST(session_num AS STRING)) AS unit_key,
      ROW_NUMBER() OVER (PARTITION BY ecid, session_num ORDER BY timestamp) AS rn
    FROM classified
    WHERE checkout_step = 'begin_checkout'
      AND ecid IS NOT NULL
  ) s
  WHERE rn = 1
),
orders AS (
  SELECT event_date, checkout_step, checkout_type, unit_key, revenue
  FROM (
    SELECT
      c.event_date,
      c.checkout_step,
      c.checkout_type,
      c.purchase_id AS unit_key,
      CAST(COALESCE(c.order_total, 0) AS DOUBLE) AS revenue,
      ROW_NUMBER() OVER (PARTITION BY c.purchase_id ORDER BY c.timestamp DESC) AS rn
    FROM classified c
    LEFT JOIN order_exclusions_dataset excl
      ON excl.purchase_id = c.purchase_id
    WHERE c.checkout_step = 'purchase'
      AND c.purchase_id IS NOT NULL
      AND c.purchase_id <> ''
      AND excl.purchase_id IS NULL
  ) o
  WHERE rn = 1
),
combined AS (
  SELECT * FROM checkout_starts
  UNION ALL
  SELECT * FROM orders
),
summary AS (
  SELECT
    event_date,
    checkout_step,
    checkout_type,
    COUNT(DISTINCT unit_key) AS checkouts_or_orders,
    ROUND(SUM(revenue), 2) AS revenue
  FROM combined
  GROUP BY event_date, checkout_step, checkout_type
)
SELECT
  event_date,
  checkout_step,
  checkout_type,
  checkouts_or_orders,
  revenue,
  CAST(checkouts_or_orders AS DOUBLE)
    / NULLIF(SUM(checkouts_or_orders) OVER (PARTITION BY event_date, checkout_step), 0) AS share_of_step_total
FROM summary
ORDER BY event_date, checkout_step, checkout_type;