Guest Checkout Rate

{
  "digitalData": {
    "pageInstanceID": "prod-web-checkout-confirmation",
    "page": {
      "pageInfo": {
        "pageID": "checkout-confirmation",
        "pageName": "Checkout: Order Confirmation",
        "destinationURL": "https://www.example.com/checkout/confirmation",
        "sysEnv": "mobile",
        "language": "en-US"
      },
      "category": {
        "primaryCategory": "checkout",
        "subCategory1": "order-confirmation"
      },
      "attributes": {
        "checkoutType": "guest",
        "channelOfPurchase": "web",
        "deviceCategory": "mobile"
      }
    },
    "user": [
      {
        "profile": [
          {
            "profileInfo": {
              "profileID": null,
              "userName": null
            },
            "attributes": {
              "loginState": "unauthenticated",
              "authenticationMethod": "none",
              "accountStatus": "guest",
              "customerType": "unknown"
            }
          }
        ]
      }
    ],
    "transaction": {
      "transactionID": "ORD-10045872",
      "profile": {
        "profileInfo": {
          "profileID": null
        },
        "attributes": {
          "checkoutType": "guest",
          "loginState": "unauthenticated",
          "accountCreatedAtCheckout": false
        }
      },
      "total": {
        "basePrice": 178,
        "voucherCode": "SPRING10",
        "voucherDiscount": 10,
        "currency": "USD",
        "taxRate": 0.08,
        "shipping": 9.95,
        "shippingMethod": "standard",
        "priceWithTax": 181.44,
        "transactionTotal": 191.39
      },
      "attributes": {
        "checkoutType": "guest",
        "paymentType": "paypal_express",
        "fulfillmentType": "ship",
        "channelOfPurchase": "web",
        "deviceCategory": "mobile",
        "orderStatus": "completed",
        "testOrder": false
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-48219",
            "productName": "Merino Wool Crew Sweater",
            "sku": "SKU-48219",
            "manufacturer": "Northbound",
            "color": "Charcoal",
            "size": "M"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Knitwear"
          },
          "quantity": 2,
          "price": {
            "basePrice": 89,
            "voucherCode": "SPRING10",
            "voucherDiscount": 10,
            "currency": "USD",
            "taxRate": 0.08,
            "priceWithTax": 181.44
          }
        }
      ]
    },
    "version": "1.0"
  }
}

{
  "event": "purchase",
  "checkout_type": "guest",
  "login_state": "unauthenticated",
  "authentication_method": "none",
  "account_created_at_checkout": false,
  "payment_type": "paypal_express",
  "fulfillment_type": "ship",
  "channel_of_purchase": "web",
  "device_category": "mobile",
  "order_status": "completed",
  "test_order": false,
  "ecommerce": {
    "transaction_id": "ORD-10045872",
    "value": 191.39,
    "tax": 13.44,
    "shipping": 9.95,
    "currency": "USD",
    "coupon": "SPRING10",
    "items": [
      {
        "item_id": "SKU-48219",
        "item_name": "Merino Wool Crew Sweater",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Knitwear",
        "item_variant": "Charcoal / M",
        "item_list_name": "Search Results",
        "price": 89,
        "discount": 10,
        "coupon": "SPRING10",
        "quantity": 2,
        "index": 1
      }
    ]
  }
}

{
  "event": "purchase",
  "eventInfo": {
    "path": "commerce.order"
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-10045872",
      "purchaseOrderNumber": "ORD-10045872",
      "currencyCode": "USD",
      "priceTotal": 191.39,
      "taxAmount": 13.44,
      "shippingAmount": 9.95,
      "discountAmount": 10,
      "couponCode": "SPRING10",
      "orderType": "ship",
      "payments": [
        {
          "paymentType": "paypal_express",
          "paymentAmount": 191.39,
          "currencyCode": "USD"
        }
      ]
    },
    "productListItems": [
      {
        "SKU": "SKU-48219",
        "name": "Merino Wool Crew Sweater",
        "productCategory": "Apparel",
        "productBrand": "Northbound",
        "selectedOptions": "Charcoal / M",
        "priceTotal": 178,
        "quantity": 2,
        "currencyCode": "USD"
      }
    ]
  },
  "checkout": {
    "checkoutType": "guest",
    "authenticationMethod": "none",
    "expressCheckout": true,
    "accountCreatedAtCheckout": false,
    "orderStatus": "completed",
    "testOrder": false
  },
  "userAccount": {
    "loginStatus": "unauthenticated",
    "accountStatus": "guest",
    "customerType": "unknown"
  },
  "device": {
    "deviceCategory": "mobile"
  },
  "channel": {
    "typeAtSource": "web",
    "channelOfPurchase": "web"
  }
}

{
  "eventType": "commerce.purchases",
  "timestamp": "2026-09-17T14:32:08.000Z",
  "channel": {
    "_type": "https://ns.adobe.com/xdm/channels/web",
    "typeAtSource": "web"
  },
  "commerce": {
    "purchases": {
      "value": 1
    },
    "order": {
      "purchaseID": "ORD-10045872",
      "purchaseOrderNumber": "ORD-10045872",
      "currencyCode": "USD",
      "priceTotal": 191.39,
      "discountAmount": 10,
      "payments": [
        {
          "paymentType": "paypal_express",
          "paymentAmount": 191.39,
          "currencyCode": "USD",
          "transactionID": "ORD-10045872-PAY-1"
        }
      ]
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-48219",
      "name": "Merino Wool Crew Sweater",
      "productCategory": "Apparel",
      "priceTotal": 178,
      "quantity": 2,
      "currencyCode": "USD"
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "Checkout: Order Confirmation",
      "URL": "https://www.example.com/checkout/confirmation",
      "siteSection": "checkout"
    }
  },
  "device": {
    "type": "mobile"
  },
  "identityMap": {
    "ECID": [
      {
        "id": "79352389423048239042",
        "primary": true,
        "authenticatedState": "ambiguous"
      }
    ]
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar27": {
            "value": "guest"
          },
          "eVar28": {
            "value": "none"
          }
        },
        "props": {
          "prop27": {
            "value": "guest"
          }
        }
      }
    }
  },
  "_openkpis": {
    "checkout": {
      "checkoutType": "guest",
      "authenticationMethod": "none",
      "accountCreatedAtCheckout": false,
      "channelOfPurchase": "web",
      "orderStatus": "completed",
      "testOrder": false
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- KPI      : Guest Checkout Rate = (Guest Orders / Total Orders) * 100
-- Grain    : one row per completed order, keyed on transaction_id from the unified `purchase` event.
-- Base     : EVERY completed order in the window (guest + registered + unclassified). No identity,
--            consent or account-status predicate may shrink the denominator.
-- Breakdown: Checkout Type (returned as the guest / registered / unclassified order columns),
--            Payment Type, Device Category, Channel of Purchase.

DECLARE start_date STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY));
DECLARE end_date   STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

WITH purchase_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date)  AS order_date,
    event_timestamp,
    user_pseudo_id,
    device.category                   AS device_category,
    ecommerce.transaction_id          AS ecommerce_transaction_id,
    ecommerce.purchase_revenue        AS order_revenue,
    event_params
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
    AND event_name = 'purchase'
),

-- Pivot the event_params array in a single pass: one row per purchase beacon, one column per parameter.
order_attributes AS (
  SELECT
    order_date,
    event_timestamp,
    user_pseudo_id,
    ANY_VALUE(device_category) AS device_category,
    ANY_VALUE(order_revenue)   AS order_revenue,
    COALESCE(
      MAX(IF(ep.key = 'transaction_id', ep.value.string_value, NULL)),
      ANY_VALUE(ecommerce_transaction_id)
    ) AS transaction_id,
    LOWER(MAX(IF(ep.key = 'checkout_type',       ep.value.string_value, NULL))) AS checkout_type_param,
    LOWER(MAX(IF(ep.key = 'login_state',         ep.value.string_value, NULL))) AS login_state,
    LOWER(MAX(IF(ep.key = 'payment_type',        ep.value.string_value, NULL))) AS payment_type,
    LOWER(MAX(IF(ep.key = 'channel_of_purchase', ep.value.string_value, NULL))) AS channel_of_purchase_param,
    LOWER(MAX(IF(ep.key = 'order_status',        ep.value.string_value, NULL))) AS order_status,
    LOWER(MAX(IF(ep.key = 'test_order',
                 COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL))) AS test_order_flag
  FROM purchase_events, UNNEST(event_params) AS ep
  GROUP BY order_date, event_timestamp, user_pseudo_id
),

-- Order grain: a re-fired purchase beacon (confirmation reload, payment re-authorisation) keeps only its first hit.
-- Exclusions are applied here, symmetrically to numerator and denominator, before the ratio is formed.
deduplicated_orders AS (
  SELECT * EXCEPT (row_num)
  FROM (
    SELECT
      a.*,
      ROW_NUMBER() OVER (PARTITION BY a.transaction_id ORDER BY a.event_timestamp) AS row_num
    FROM order_attributes a
    WHERE a.transaction_id IS NOT NULL
      AND COALESCE(a.test_order_flag, 'false') NOT IN ('true', '1')
      AND COALESCE(a.order_status, 'completed') NOT IN ('cancelled', 'voided')
      AND COALESCE(a.order_revenue, 0) > 0
  )
  WHERE row_num = 1
),

-- Scope: digital orders only. CRM (Salesforce) is account enrichment only: it stamps a checkout type on a digital
-- order that carries neither checkout_type nor login_state. LEFT JOIN, so no digital order is ever joined out of the base.
crm_order_enrichment AS (
  SELECT
    order_id              AS transaction_id,
    LOWER(account_status) AS crm_account_status
  FROM `your-project.crm_salesforce.orders`
  WHERE order_date BETWEEN PARSE_DATE('%Y%m%d', start_date) AND PARSE_DATE('%Y%m%d', end_date)
),

orders AS (
  SELECT
    d.order_date,
    d.transaction_id,
    CASE
      WHEN d.checkout_type_param IN ('guest', 'registered')  THEN d.checkout_type_param
      WHEN d.login_state = 'authenticated'                   THEN 'registered'
      WHEN c.crm_account_status IN ('guest', 'registered')   THEN c.crm_account_status
      ELSE 'unclassified'
    END AS checkout_type,
    COALESCE(d.payment_type, '(not set)')    AS payment_type,
    COALESCE(d.device_category, '(not set)') AS device_category,
    COALESCE(d.channel_of_purchase_param, '(not set)') AS channel_of_purchase
  FROM deduplicated_orders d
  LEFT JOIN crm_order_enrichment c
    ON d.transaction_id = c.transaction_id
)

SELECT
  order_date,
  payment_type,
  device_category,
  channel_of_purchase,
  COUNT(DISTINCT transaction_id)                          AS total_orders,
  COUNTIF(checkout_type = 'guest')                        AS guest_orders,
  COUNTIF(checkout_type = 'registered')                   AS registered_orders,
  COUNTIF(checkout_type = 'unclassified')                 AS unclassified_orders,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(checkout_type = 'guest'), COUNT(DISTINCT transaction_id)), 2) AS guest_checkout_rate_pct
FROM orders
GROUP BY order_date, payment_type, device_category, channel_of_purchase
ORDER BY order_date DESC, total_orders DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- KPI      : Guest Checkout Rate = (Guest Orders / Total Orders) * 100
-- Dataset  : ExperienceEvent, eventType = 'commerce.purchases'; XDM paths exactly as mapped in this specification.
-- Base     : EVERY completed order in the window; identity and CRM account status enrich, they never filter.
-- Breakdown: Checkout Type (guest / registered / unclassified order columns), Payment Type,
--            Device Category, Channel of Purchase.

WITH purchase_events AS (
  SELECT
    to_date(timestamp)                                              AS order_date,
    timestamp                                                       AS event_timestamp,
    commerce.order.purchaseID                                       AS transaction_id,
    commerce.order.priceTotal                                       AS order_revenue,
    commerce.purchases.value                                        AS purchase_count,
    lower(_openkpis.checkout.checkoutType)                          AS checkout_type_field,
    lower(_openkpis.checkout.authenticationMethod)                  AS authentication_method,
    lower(_openkpis.checkout.channelOfPurchase)                     AS channel_of_purchase_field,
    lower(_experience.analytics.customDimensions.eVars.eVar27.value) AS checkout_type_evar,
    lower(commerce.order.payments[0].paymentType)                   AS payment_type,
    lower(device.type)                                              AS device_category,
    lower(_openkpis.checkout.orderStatus)                           AS order_status,
    lower(cast(_openkpis.checkout.testOrder AS STRING))             AS test_order_flag
  FROM your_experience_event_dataset
  WHERE eventType = 'commerce.purchases'
    AND timestamp >= date_sub(current_date, 90)
    AND timestamp <  current_date
    AND commerce.order.purchaseID IS NOT NULL
),

-- Order grain: one row per purchaseID, the first beacon wins, so a confirmation reload cannot double count.
-- Exclusions match the BigQuery query: test orders, cancelled / voided orders and zero-value orders.
deduplicated_orders AS (
  SELECT *
  FROM (
    SELECT
      p.*,
      row_number() OVER (PARTITION BY p.transaction_id ORDER BY p.event_timestamp) AS row_num
    FROM purchase_events p
    WHERE coalesce(p.order_revenue, 0) > 0
      AND coalesce(p.purchase_count, 1) > 0
      AND coalesce(p.test_order_flag, 'false') NOT IN ('true', '1')
      AND coalesce(p.order_status, 'completed') NOT IN ('cancelled', 'voided')
  ) ranked
  WHERE row_num = 1
),

-- Scope: digital orders only. CRM (Salesforce) orders dataset is account enrichment only: it stamps a checkout
-- type on a digital order that carries no checkout type or authentication method.
-- LEFT JOIN by construction, so the digital order population stays whole.
crm_order_enrichment AS (
  SELECT
    orderId              AS transaction_id,
    lower(accountStatus) AS crm_account_status
  FROM crm_salesforce_orders_dataset
  WHERE orderDate >= date_sub(current_date, 90)
),

orders AS (
  SELECT
    d.order_date,
    d.transaction_id,
    CASE
      WHEN coalesce(d.checkout_type_field, d.checkout_type_evar) IN ('guest', 'registered')
        THEN coalesce(d.checkout_type_field, d.checkout_type_evar)
      WHEN d.authentication_method IS NOT NULL AND d.authentication_method <> 'none' THEN 'registered'
      WHEN c.crm_account_status IN ('guest', 'registered') THEN c.crm_account_status
      ELSE 'unclassified'
    END AS checkout_type,
    coalesce(d.payment_type, '(not set)')    AS payment_type,
    coalesce(d.device_category, '(not set)') AS device_category,
    coalesce(d.channel_of_purchase_field, '(not set)') AS channel_of_purchase
  FROM deduplicated_orders d
  LEFT JOIN crm_order_enrichment c
    ON d.transaction_id = c.transaction_id
)

SELECT
  order_date,
  payment_type,
  device_category,
  channel_of_purchase,
  count(DISTINCT transaction_id)                                          AS total_orders,
  sum(CASE WHEN checkout_type = 'guest'        THEN 1 ELSE 0 END)         AS guest_orders,
  sum(CASE WHEN checkout_type = 'registered'   THEN 1 ELSE 0 END)         AS registered_orders,
  sum(CASE WHEN checkout_type = 'unclassified' THEN 1 ELSE 0 END)         AS unclassified_orders,
  round(100.0 * sum(CASE WHEN checkout_type = 'guest' THEN 1 ELSE 0 END)
        / nullif(count(DISTINCT transaction_id), 0), 2)                   AS guest_checkout_rate_pct
FROM orders
GROUP BY order_date, payment_type, device_category, channel_of_purchase
ORDER BY order_date DESC, total_orders DESC;