Checkout Abandonment Rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "Begin Checkout",
        "eventAction": "begin_checkout",
        "eventPoints": 0
      },
      "cart": {
        "cartID": "cart_9f8a7b6c",
        "price": {
          "basePrice": 129.99,
          "currency": "USD",
          "voucherCode": "SUMMER10"
        },
        "item": [
          {
            "productID": "SKU12345",
            "productInfo": {
              "productName": "Wireless Headphones"
            },
            "price": {
              "basePrice": 129.99
            },
            "quantity": 1
          }
        ]
      },
      "checkout": {
        "step": 1,
        "stepName": "Checkout Start",
        "type": "guest"
      },
      "attributes": {
        "trafficType": "customer"
      }
    }
  ]
}

{
  "event": "begin_checkout",
  "ecommerce": {
    "currency": "USD",
    "value": 129.99,
    "coupon": "SUMMER10",
    "items": [
      {
        "item_id": "SKU12345",
        "item_name": "Wireless Headphones",
        "price": 129.99,
        "quantity": 1
      }
    ]
  },
  "checkout_step": 1,
  "checkout_step_name": "Checkout Start",
  "checkout_type": "guest",
  "cart_id": "cart_9f8a7b6c",
  "traffic_type": "customer"
}

{
  "event": "scCheckout",
  "checkout_step": 1,
  "checkout_step_name": "Checkout Start",
  "checkout_type": "guest",
  "cart_id": "cart_9f8a7b6c",
  "currency": "USD",
  "value": 129.99,
  "coupon": "SUMMER10",
  "items": [
    {
      "item_id": "SKU12345",
      "item_name": "Wireless Headphones",
      "price": 129.99,
      "quantity": 1
    }
  ],
  "traffic_type": "customer"
}

{
  "eventType": "commerce.checkouts",
  "commerce": {
    "checkouts": {
      "value": 1
    },
    "order": {
      "currencyCode": "{currency}",
      "priceTotal": "{value}",
      "promotionIDs": "{coupon}"
    }
  },
  "productListItems": "{items}",
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "checkout_type",
          "eVar2": "cart_id",
          "eVar3": "traffic_type"
        },
        "props": {
          "prop1": "checkout_step",
          "prop2": "checkout_step_name"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Reporting window on the begin_checkout date; the last 48 hours are provisional, so the default window ends two days ago
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 92 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY);

WITH events AS (
  -- begin_checkout and purchase events (window plus one day for sessions crossing midnight) with session key and traffic_type pivoted
  SELECT
    event_name,
    PARSE_DATE('%Y%m%d', event_date) AS event_day,
    event_timestamp,
    CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type,
    ecommerce.transaction_id AS transaction_id
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 1 DAY))
    AND event_name IN ('begin_checkout', 'purchase')
),
checkout_sessions AS (
  -- One row per session with a begin_checkout in the window; internal, test and bot traffic excluded, checkout_error sessions kept
  SELECT session_key, MIN(event_timestamp) AS first_checkout_ts
  FROM events
  WHERE event_name = 'begin_checkout'
    AND event_day BETWEEN start_date AND end_date
    AND session_key IS NOT NULL
  GROUP BY session_key
  HAVING COUNTIF(LOWER(traffic_type) IN ('internal', 'test', 'bot')) = 0
),
purchases AS (
  -- Purchases deduplicated on transaction_id: only the first hit of each order counts
  SELECT session_key, event_timestamp
  FROM 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
),
session_outcomes AS (
  -- A checkout session is completed when the same session has a purchase at or after its first begin_checkout
  SELECT c.session_key, COUNTIF(p.event_timestamp >= c.first_checkout_ts) > 0 AS completed
  FROM checkout_sessions c
  LEFT JOIN purchases p ON p.session_key = c.session_key
  GROUP BY c.session_key
)
-- Checkout Abandonment Rate: checkout sessions without a purchase over all checkout sessions
SELECT
  COUNT(*) AS checkout_sessions,
  COUNTIF(NOT completed) AS abandoned_checkout_sessions,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(NOT completed), COUNT(*)), 2) AS checkout_abandonment_rate_pct
FROM session_outcomes;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH params AS (
  -- Reporting window on the begin_checkout date; the last 48 hours are provisional, so the default window ends two days ago
  SELECT DATE_SUB(CURRENT_DATE, 92) AS start_date, DATE_SUB(CURRENT_DATE, 2) AS end_date
),
events AS (
  -- Web events (window plus one day for sessions crossing midnight) sessionized per ECID with a 30-minute timeout
  SELECT
    e.eventType,
    TO_DATE(e.timestamp) AS event_day,
    e.timestamp,
    e.commerce.order.purchaseID AS purchase_id,
    e._experience.analytics.customDimensions.eVars.eVar3 AS traffic_type,
    CONCAT(e.identityMap['ECID'][0].id, '.', CAST(SESS_TIMEOUT(e.timestamp, 60 * 30) OVER (PARTITION BY e.identityMap['ECID'][0].id ORDER BY e.timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).num AS STRING)) AS session_key
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE TO_DATE(e.timestamp) BETWEEN p.start_date AND DATE_ADD(p.end_date, 1)
),
checkout_sessions AS (
  -- One row per session with a begin_checkout in the window; internal, test and bot traffic excluded, checkout_error sessions kept
  SELECT ev.session_key, MIN(ev.timestamp) AS first_checkout_ts
  FROM events ev
  CROSS JOIN params p
  WHERE ev.eventType = 'commerce.checkouts'
    AND ev.event_day BETWEEN p.start_date AND p.end_date
    AND ev.session_key IS NOT NULL
  GROUP BY ev.session_key
  HAVING SUM(CASE WHEN LOWER(ev.traffic_type) IN ('internal', 'test', 'bot') THEN 1 ELSE 0 END) = 0
),
purchases AS (
  -- Purchases deduplicated on purchaseID: only the first hit of each order counts
  SELECT session_key, timestamp
  FROM (
    SELECT session_key, timestamp, ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY timestamp) AS rn
    FROM events
    WHERE eventType = 'commerce.purchases'
      AND purchase_id IS NOT NULL
      AND purchase_id <> ''
  ) d
  WHERE rn = 1
),
session_outcomes AS (
  -- A checkout session is completed when the same session has a purchase at or after its first begin_checkout
  SELECT c.session_key, MAX(CASE WHEN pu.timestamp >= c.first_checkout_ts THEN 1 ELSE 0 END) AS completed
  FROM checkout_sessions c
  LEFT JOIN purchases pu ON pu.session_key = c.session_key
  GROUP BY c.session_key
)
-- Checkout Abandonment Rate: checkout sessions without a purchase over all checkout sessions
SELECT
  COUNT(*) AS checkout_sessions,
  SUM(1 - completed) AS abandoned_checkout_sessions,
  ROUND(100.0 * SUM(1 - completed) / NULLIF(COUNT(*), 0), 2) AS checkout_abandonment_rate_pct
FROM session_outcomes;