Checkout Error Type

{
  "event": [
    {
      "eventInfo": {
        "eventName": "Checkout Error",
        "eventAction": "checkout_error",
        "timeStamp": "2026-09-21T16:20:00Z",
        "trafficType": "{{traffic_type}}"
      },
      "cart": {
        "checkout": {
          "step": "{{checkout_step}}",
          "error": {
            "type": "{{error_type}}",
            "code": "{{error_code}}",
            "source": "{{error_source}}"
          }
        }
      }
    }
  ]
}

{
  "event": "checkout_error",
  "error_type": "payment_declined",
  "error_code": "card_declined",
  "error_source": "payment_gateway",
  "checkout_step": 3,
  "traffic_type": "external"
}

{
  "event": "checkout:error",
  "checkoutError": {
    "errorType": "payment_declined",
    "errorCode": "card_declined",
    "errorSource": "payment_gateway",
    "checkoutStep": 3
  },
  "trafficType": "external"
}

{
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "checkoutError.errorType",
          "eVar2": "checkoutError.errorCode",
          "eVar3": "checkoutError.errorSource"
        }
      }
    }
  },
  "_your_tenant": {
    "checkoutError": {
      "errorType": "{error_type}",
      "errorCode": "{error_code}",
      "errorSource": "{error_source}",
      "checkoutStep": "{checkout_step}"
    },
    "trafficType": "{traffic_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);

-- Gateway test-mode payments and bot-flagged sessions carry no field in the event export, so they must be filtered before collection.
WITH checkout_errors AS (
  -- One row per checkout_error event with its parameters pivoted from event_params
  SELECT
    event_date,
    user_pseudo_id,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT COALESCE(value.int_value, SAFE_CAST(value.string_value AS INT64)) FROM UNNEST(event_params) WHERE key = 'checkout_step') AS checkout_step,
    (SELECT LOWER(TRIM(value.string_value)) FROM UNNEST(event_params) WHERE key = 'error_type') AS raw_error_type,
    (SELECT LOWER(value.string_value) FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'checkout_error'
),
typed_errors AS (
  -- Drop internal and QA traffic, key sessions split at midnight (event_date is in the property time zone) and map values outside the closed list to other
  SELECT
    event_date,
    CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING), '.', event_date) AS session_key,
    event_timestamp,
    checkout_step,
    CASE
      WHEN raw_error_type IN ('payment_declined', 'payment_authentication_failed', 'payment_details_invalid', 'payment_processing_error',
                              'address_invalid', 'shipping_unavailable', 'out_of_stock', 'price_changed', 'coupon_invalid',
                              'required_field_missing', 'session_expired', 'system_error')
        THEN raw_error_type
      ELSE 'other'
    END AS error_type
  FROM checkout_errors
  WHERE COALESCE(traffic_type, '') NOT IN ('internal', 'qa')
)
-- Event-grain error type with the session rollup of first and last error type
SELECT
  event_date,
  session_key,
  event_timestamp,
  checkout_step,
  error_type,
  FIRST_VALUE(error_type) OVER session_window AS first_error_type,
  LAST_VALUE(error_type) OVER session_window AS last_error_type
FROM typed_errors
WINDOW session_window AS (PARTITION BY session_key ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY session_key, event_timestamp;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Gateway test-mode payments and bot-flagged sessions carry no field in the event dataset, so they must be filtered before collection.
WITH params AS (
  -- Relative reporting window and the property time zone used for the midnight session split
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date, 'America/New_York' AS property_tz
),
local_events AS (
  -- All web events in the window with the local event date, needed to rebuild sessions from full activity
  SELECT
    e.identityMap['ECID'][0].id AS ecid,
    e.timestamp AS event_timestamp,
    to_date(from_utc_timestamp(e.timestamp, p.property_tz)) AS event_date,
    e.eventType AS event_type,
    e._your_tenant.checkoutError.checkoutStep AS checkout_step,
    lower(trim(e._your_tenant.checkoutError.errorType)) AS raw_error_type,
    lower(e._your_tenant.trafficType) AS traffic_type
  FROM your_aep_web_dataset e
  CROSS JOIN params p
  WHERE to_date(from_utc_timestamp(e.timestamp, p.property_tz)) BETWEEN p.start_date AND p.end_date
),
sessionised AS (
  -- Assign each event to a 30-minute inactivity session that also splits at local midnight
  SELECT
    *,
    SESS_TIMEOUT(event_timestamp, 60 * 30) OVER (PARTITION BY ecid, event_date ORDER BY event_timestamp
                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
  FROM local_events
),
typed_errors AS (
  -- Keep checkout:error events, drop internal and QA traffic and map values outside the closed list to other
  SELECT
    event_date,
    concat(ecid, '.', cast(event_date AS STRING), '.', cast(session.num AS STRING)) AS session_key,
    event_timestamp,
    checkout_step,
    CASE
      WHEN raw_error_type IN ('payment_declined', 'payment_authentication_failed', 'payment_details_invalid', 'payment_processing_error',
                              'address_invalid', 'shipping_unavailable', 'out_of_stock', 'price_changed', 'coupon_invalid',
                              'required_field_missing', 'session_expired', 'system_error')
        THEN raw_error_type
      ELSE 'other'
    END AS error_type
  FROM sessionised
  WHERE event_type = 'checkout:error'
    AND coalesce(traffic_type, '') NOT IN ('internal', 'qa')
)
-- Event-grain error type with the session rollup of first and last error type
SELECT
  event_date,
  session_key,
  event_timestamp,
  checkout_step,
  error_type,
  first_value(error_type) OVER (PARTITION BY session_key ORDER BY event_timestamp
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_error_type,
  last_value(error_type) OVER (PARTITION BY session_key ORDER BY event_timestamp
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_error_type
FROM typed_errors
ORDER BY session_key, event_timestamp;