Payment Info Added

{
  "digitalData": {
    "pageInstanceID": "checkout-payment-prod",
    "page": {
      "pageInfo": {
        "pageID": "checkout-payment",
        "pageName": "Checkout - Payment",
        "destinationURL": "https://www.example.com/checkout/payment",
        "sysEnv": "mobile-web"
      },
      "category": {
        "primaryCategory": "Checkout",
        "subCategory1": "Payment"
      },
      "attributes": {
        "checkoutId": "CHK-2026-0917-884213",
        "checkoutStep": 3,
        "checkoutStepName": "payment",
        "checkoutType": "guest"
      }
    },
    "cart": {
      "cartID": "CART-9F3E21B7",
      "price": {
        "basePrice": 184.5,
        "currency": "USD",
        "cartTotal": 199.45,
        "shipping": 9.95,
        "taxRate": 0.0825,
        "priceWithTax": 199.45
      },
      "attributes": {
        "paymentType": "credit_card",
        "paymentBrand": "visa",
        "walletProvider": null,
        "shippingTier": "standard",
        "cartSizeBand": "2-3 items",
        "coupon": "SPRING10"
      },
      "item": [
        {
          "productInfo": {
            "productID": "SKU-44821",
            "productName": "Merino Crew Sweater",
            "sku": "SKU-44821-NVY-M",
            "manufacturer": "Northbound",
            "color": "Navy",
            "size": "M"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Knitwear"
          },
          "quantity": 1,
          "price": {
            "basePrice": 129,
            "currency": "USD"
          }
        },
        {
          "productInfo": {
            "productID": "SKU-77310",
            "productName": "Cotton Socks 3-Pack",
            "sku": "SKU-77310-GRY-OS",
            "manufacturer": "Northbound",
            "color": "Grey",
            "size": "OS"
          },
          "category": {
            "primaryCategory": "Apparel",
            "subCategory1": "Accessories"
          },
          "quantity": 1,
          "price": {
            "basePrice": 55.5,
            "currency": "USD"
          }
        }
      ]
    },
    "user": [
      {
        "segment": {
          "loginState": "guest",
          "customerType": "new",
          "loyaltyTier": "none"
        }
      }
    ],
    "events": [
      {
        "eventInfo": {
          "eventName": "add_payment_info",
          "eventAction": "Checkout: Add Payment Info",
          "type": "checkout",
          "timeStamp": "2026-09-17T17:47:12.418Z"
        },
        "category": {
          "primaryCategory": "Checkout"
        },
        "attributes": {
          "checkoutId": "CHK-2026-0917-884213",
          "checkoutStep": 3,
          "paymentType": "credit_card",
          "value": 184.5,
          "currency": "USD",
          "serialization": "once per checkoutId"
        }
      }
    ]
  }
}

{
  "event": "add_payment_info",
  "checkout_id": "CHK-2026-0917-884213",
  "checkout_step": 3,
  "checkout_step_name": "payment",
  "checkout_type": "guest",
  "shipping_tier": "standard",
  "cart_size_band": "2-3 items",
  "entry_method": "assisted",
  "ecommerce": {
    "currency": "USD",
    "value": 184.5,
    "coupon": "SPRING10",
    "payment_type": "credit_card",
    "items": [
      {
        "item_id": "SKU-44821",
        "item_name": "Merino Crew Sweater",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Knitwear",
        "item_variant": "Navy / M",
        "affiliation": "Online Store",
        "coupon": "SPRING10",
        "discount": 10,
        "price": 129,
        "quantity": 1,
        "index": 1
      },
      {
        "item_id": "SKU-77310",
        "item_name": "Cotton Socks 3-Pack",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Accessories",
        "item_variant": "Grey / OS",
        "affiliation": "Online Store",
        "price": 55.5,
        "quantity": 1,
        "index": 2
      }
    ]
  }
}

{
  "event": "add_payment_info",
  "eventInfo": {
    "eventName": "add_payment_info",
    "linkName": "Checkout: Add Payment Info",
    "linkType": "other",
    "path": "commerce.checkouts",
    "eventTimestamp": "2026-09-17T17:47:12.418Z"
  },
  "commerce": {
    "checkouts": {
      "value": 1,
      "id": "CHK-2026-0917-884213",
      "step": 3,
      "stepName": "payment",
      "checkoutType": "guest"
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 184.5,
      "discountAmount": 10,
      "shippingTier": "standard",
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentBrand": "visa",
          "walletProvider": null,
          "paymentAmount": 184.5,
          "currencyCode": "USD"
        }
      ]
    },
    "productListItems": [
      {
        "SKU": "SKU-44821",
        "name": "Merino Crew Sweater",
        "productCategory": "Apparel",
        "productBrand": "Northbound",
        "quantity": 1,
        "priceTotal": 129,
        "currencyCode": "USD"
      },
      {
        "SKU": "SKU-77310",
        "name": "Cotton Socks 3-Pack",
        "productCategory": "Apparel",
        "productBrand": "Northbound",
        "quantity": 1,
        "priceTotal": 55.5,
        "currencyCode": "USD"
      }
    ]
  },
  "web": {
    "webPageDetails": {
      "name": "Checkout - Payment",
      "siteSection": "Checkout",
      "URL": "https://www.example.com/checkout/payment"
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar21": "credit_card",
          "eVar22": "guest",
          "eVar23": "payment",
          "eVar24": "CHK-2026-0917-884213",
          "eVar25": "standard",
          "eVar26": "SPRING10"
        }
      }
    }
  },
  "identityMap": {
    "ECID": [
      {
        "id": "79162398127498127349817234",
        "primary": true,
        "authenticatedState": "ambiguous"
      }
    ]
  }
}

{
  "eventType": "commerce.checkouts",
  "timestamp": "2026-09-17T17:47:12.418Z",
  "commerce": {
    "checkouts": {
      "value": 1,
      "id": "CHK-2026-0917-884213"
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 184.5,
      "discountAmount": 10,
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentAmount": 184.5,
          "currencyCode": "USD"
        }
      ]
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-44821",
      "name": "Merino Crew Sweater",
      "quantity": 1,
      "priceTotal": 129,
      "currencyCode": "USD",
      "productCategories": [
        {
          "categoryID": "apparel-knitwear"
        }
      ]
    },
    {
      "SKU": "SKU-77310",
      "name": "Cotton Socks 3-Pack",
      "quantity": 1,
      "priceTotal": 55.5,
      "currencyCode": "USD",
      "productCategories": [
        {
          "categoryID": "apparel-accessories"
        }
      ]
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "Checkout - Payment",
      "siteSection": "Checkout",
      "URL": "https://www.example.com/checkout/payment"
    }
  },
  "device": {
    "type": "mobile"
  },
  "_experience": {
    "analytics": {
      "event1to100": {
        "event15": {
          "value": 1
        }
      },
      "customDimensions": {
        "eVars": {
          "eVar21": "credit_card",
          "eVar22": "guest",
          "eVar23": "payment",
          "eVar24": "CHK-2026-0917-884213",
          "eVar25": "standard",
          "eVar26": "SPRING10"
        },
        "props": {
          "prop21": "credit_card",
          "prop22": "Checkout: Add Payment Info"
        }
      }
    }
  },
  "identityMap": {
    "ECID": [
      {
        "id": "79162398127498127349817234",
        "primary": true,
        "authenticatedState": "ambiguous"
      }
    ]
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Payment Info Added: qualifying add_payment_info events, serialized to one occurrence per checkout_id
-- (falling back to the GA4 session when the property does not emit checkout_id, which is flagged as degraded).
-- Event parameters are pivoted once per event with UNNEST + MAX(IF(key = ...)) so payment_type, value, currency,
-- coupon and the checkout context can be read side by side.
-- Exclusions: internal / QA traffic_type, staging-QA-preview-dev hostnames, agent-assisted checkouts keyed by
-- Customer Service. Gift card, store credit and loyalty-point tenders are counted like any other payment_type.
-- value mirrors the cart subtotal at submission and is reported as intent value only, never as recognised revenue;
-- non-USD checkouts are converted with the finance daily booking rate.
-- event_date is already in the reporting-property timezone; the trailing 3 days stay provisional (72h late-hit window).
WITH raw_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date)                       AS event_date,
    e.event_timestamp                                        AS event_timestamp,
    e.user_pseudo_id                                         AS user_pseudo_id,
    COALESCE(e.device.category, '(not set)')                 AS device_category,
    LOWER(COALESCE(e.device.web_info.hostname, ''))          AS hostname,
    COALESCE(e.traffic_source.medium, '(not set)')           AS traffic_medium,
    COALESCE(e.traffic_source.source, '(not set)')           AS traffic_source,
    e.event_params                                           AS event_params,
    (SELECT COALESCE(SUM(it.quantity), 0) FROM UNNEST(e.items) AS it) AS item_units
  FROM `your-project.analytics_123456789.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND e.event_name = 'add_payment_info'
),
pivoted AS (
  SELECT
    r.event_date,
    r.event_timestamp,
    r.user_pseudo_id,
    r.device_category,
    r.hostname,
    r.traffic_medium,
    r.traffic_source,
    r.item_units,
    MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL))                                           AS ga_session_id,
    MAX(IF(ep.key = 'checkout_id', ep.value.string_value, NULL))                                          AS checkout_id,
    MAX(IF(ep.key = 'checkout_step', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS checkout_step,
    LOWER(MAX(IF(ep.key = 'checkout_step_name', ep.value.string_value, NULL)))                            AS checkout_step_name,
    LOWER(MAX(IF(ep.key = 'checkout_type', ep.value.string_value, NULL)))                                 AS checkout_type,
    LOWER(MAX(IF(ep.key = 'payment_type', ep.value.string_value, NULL)))                                  AS payment_type,
    LOWER(MAX(IF(ep.key = 'shipping_tier', ep.value.string_value, NULL)))                                 AS shipping_tier,
    COALESCE(MAX(IF(ep.key = 'cart_size_band', ep.value.string_value, NULL)),
             CASE WHEN r.item_units <= 1 THEN '1 item' WHEN r.item_units <= 3 THEN '2-3 items'
                  WHEN r.item_units <= 6 THEN '4-6 items' ELSE '7+ items' END)                       AS cart_size_band,
    MAX(IF(ep.key = 'coupon', ep.value.string_value, NULL))                                               AS coupon_code,
    UPPER(MAX(IF(ep.key = 'currency', ep.value.string_value, NULL)))                                      AS currency_code,
    MAX(IF(ep.key = 'value', COALESCE(ep.value.double_value, ep.value.float_value, SAFE_CAST(ep.value.int_value AS FLOAT64)), NULL)) AS checkout_value_local,
    LOWER(MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)))                                  AS traffic_type,
    LOWER(MAX(IF(ep.key = 'entry_method', ep.value.string_value, NULL)))                                  AS entry_method
  FROM raw_events AS r, UNNEST(r.event_params) AS ep
  GROUP BY
    r.event_date, r.event_timestamp, r.user_pseudo_id, r.device_category,
    r.hostname, r.traffic_medium, r.traffic_source, r.item_units
),
qualified AS (
  SELECT
    p.*,
    CONCAT(p.user_pseudo_id, '-', CAST(COALESCE(p.ga_session_id, 0) AS STRING))            AS session_key,
    COALESCE(p.checkout_id, CONCAT('sess:', p.user_pseudo_id, '-', CAST(COALESCE(p.ga_session_id, 0) AS STRING))) AS serialization_key,
    p.checkout_id IS NULL                                                                  AS is_degraded_serialization,
    CASE
      WHEN p.payment_type IN ('apple_pay', 'google_pay', 'paypal', 'paypal_express', 'shop_pay', 'amazon_pay', 'link') THEN 'express_wallet'
      ELSE 'classic_form'
    END                                                                                    AS payment_channel
  FROM pivoted AS p
  WHERE COALESCE(p.traffic_type, '') NOT IN ('internal', 'qa', 'test')
    AND NOT REGEXP_CONTAINS(p.hostname, r'^(staging|stage|qa|uat|preview|dev)[.-]|localhost')
    AND COALESCE(p.checkout_type, '') NOT IN ('agent_assisted', 'call_center', 'customer_service')
    AND p.payment_type IS NOT NULL
),
serialized AS (
  SELECT
    q.*,
    ROW_NUMBER() OVER (PARTITION BY q.serialization_key ORDER BY q.event_timestamp) AS occurrence_rank
  FROM qualified AS q
),
first_submission AS (
  SELECT * FROM serialized WHERE occurrence_rank = 1
),
daily AS (
  SELECT
    f.event_date,
    f.device_category,
    f.checkout_type,
    f.payment_type,
    f.payment_channel,
    f.shipping_tier,
    f.cart_size_band,
    COUNT(*)                                                                    AS payment_info_added,
    COUNT(DISTINCT f.session_key)                                               AS sessions_with_payment_info,
    COUNT(DISTINCT f.user_pseudo_id)                                            AS users_with_payment_info,
    SUM(f.item_units)                                                           AS units_at_payment_step,
    COUNTIF(f.is_degraded_serialization)                                        AS events_missing_checkout_id,
    COUNTIF(COALESCE(f.coupon_code, '') <> '')                                  AS events_with_coupon,
    ROUND(SUM(f.checkout_value_local *
              CASE WHEN f.currency_code = 'USD' THEN 1.0 ELSE fx.rate_to_usd END), 2) AS checkout_intent_value_usd,
    COUNTIF(COALESCE(f.currency_code, '') <> 'USD' AND fx.rate_to_usd IS NULL)  AS events_missing_fx_rate
  FROM first_submission AS f
  LEFT JOIN `your-project.finance.fx_daily_rates` AS fx
    ON fx.rate_date = f.event_date
   AND UPPER(fx.currency_code) = f.currency_code
  GROUP BY
    f.event_date, f.device_category, f.checkout_type,
    f.payment_type, f.payment_channel, f.shipping_tier, f.cart_size_band
)
SELECT
  d.event_date,
  d.device_category,
  d.checkout_type,
  d.payment_type,
  d.payment_channel,
  d.shipping_tier,
  d.cart_size_band,
  d.payment_info_added,
  d.sessions_with_payment_info,
  d.users_with_payment_info,
  d.units_at_payment_step,
  d.checkout_intent_value_usd,
  SAFE_DIVIDE(d.checkout_intent_value_usd, d.payment_info_added)          AS avg_intent_value_usd,
  d.events_with_coupon,
  d.events_missing_checkout_id,
  SAFE_DIVIDE(d.events_missing_checkout_id, d.payment_info_added)         AS degraded_serialization_share,
  d.events_missing_fx_rate,
  d.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)                AS is_provisional
FROM daily AS d
ORDER BY d.event_date DESC, d.payment_info_added DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Payment Info Added: qualifying commerce.checkouts ExperienceEvents that carry the payment step
-- (commerce.order.payments populated or the payment-step success event15 set), serialized to one occurrence per
-- checkout id (commerce.checkouts.id / eVar24), falling back to ECID + visit number when the
-- property does not emit a checkout id - those events are flagged as degraded serialization.
-- Exclusions: internal / QA / test traffic (tenant trafficType flag set by the internal-IP processing rule),
-- bot and synthetic user agents, staging-QA-preview-dev hosts, and agent-assisted checkouts keyed by Customer Service.
-- Gift card, store credit and loyalty-point tenders are counted like any other payment_type.
-- commerce.order.payments.paymentAmount mirrors the cart subtotal at submission and is intent value only, never
-- recognised revenue; non-USD checkouts are converted with the finance daily booking rate.
-- Coupon code is read from eVar26; cart size band is derived from productListItems quantities.
-- Dates use the reporting timezone; the trailing 3 days stay provisional (72h late-hit window).
WITH base AS (
  SELECT
    to_date(from_utc_timestamp(e.timestamp, 'America/New_York'))                       AS event_date,
    e.timestamp                                                                        AS event_ts,
    COALESCE(e.endUserIDs._experience.mcid.id, e.identityMap['ECID'][0].id)             AS ecid,
    e._experience.analytics.session.num                                                AS visit_num,
    COALESCE(e.device.type, 'unknown')                                                 AS device_type,
    COALESCE(e.marketing.trackingCode, 'direct')                                       AS tracking_code,
    lower(COALESCE(element_at(e.commerce.order.payments, 1).paymentType,
                   e._experience.analytics.customDimensions.eVars.eVar21))              AS payment_type,
    lower(COALESCE(e._experience.analytics.customDimensions.eVars.eVar22, 'unknown'))   AS checkout_type,
    lower(COALESCE(e._experience.analytics.customDimensions.eVars.eVar23, 'unknown'))   AS checkout_step_name,
    COALESCE(e.commerce.checkouts.id,
             e._experience.analytics.customDimensions.eVars.eVar24)                     AS checkout_id,
    lower(COALESCE(e._experience.analytics.customDimensions.eVars.eVar25, 'unknown'))   AS shipping_tier,
    COALESCE(e._experience.analytics.customDimensions.eVars.eVar26, '')                 AS coupon_code,
    upper(COALESCE(element_at(e.commerce.order.payments, 1).currencyCode,
                   e.commerce.order.currencyCode, 'USD'))                               AS currency_code,
    CAST(COALESCE(element_at(e.commerce.order.payments, 1).paymentAmount,
                  e.commerce.order.priceTotal, 0) AS DOUBLE)                            AS checkout_value_local,
    CASE WHEN e.productListItems IS NULL THEN 0
         ELSE aggregate(e.productListItems, 0, (acc, x) -> acc + COALESCE(CAST(x.quantity AS INT), 0)) END AS item_units,
    COALESCE(e.commerce.order.payments IS NOT NULL AND size(e.commerce.order.payments) > 0, false) AS has_payment_instrument,
    COALESCE(e._experience.analytics.event1to100.event15.value, 0)                      AS payment_step_event
  FROM web_experience_events_dataset AS e
  WHERE e.timestamp >= date_sub(current_date(), 91)
    AND e.timestamp <  current_date()
    AND e.eventType = 'commerce.checkouts'
    AND COALESCE(e.commerce.checkouts.value, 0) > 0
    AND lower(COALESCE(e.web.webPageDetails.URL, '')) NOT RLIKE '^https?://(staging|stage|qa|uat|preview|dev)[.-]|localhost'
    AND lower(COALESCE(e.environment.browserDetails.userAgent, '')) NOT RLIKE 'bot|spider|crawler|headless|synthetic'
    -- internal traffic flag: replace _tenant with your tenant namespace (e.g. _acmecorp)
    AND lower(COALESCE(e._tenant.trafficType, '')) NOT IN ('internal', 'qa', 'test')
),
qualified AS (
  SELECT
    b.*,
    concat(b.ecid, '-', CAST(b.visit_num AS STRING))                                   AS visit_key,
    COALESCE(b.checkout_id, concat('visit:', b.ecid, '-', CAST(b.visit_num AS STRING))) AS serialization_key,
    b.checkout_id IS NULL                                                              AS is_degraded_serialization,
    CASE
      WHEN b.payment_type IN ('apple_pay', 'google_pay', 'paypal', 'paypal_express', 'shop_pay', 'amazon_pay', 'link')
        THEN 'express_wallet'
      ELSE 'classic_form'
    END                                                                                AS payment_channel,
    CASE WHEN b.item_units <= 1 THEN '1 item' WHEN b.item_units <= 3 THEN '2-3 items'
         WHEN b.item_units <= 6 THEN '4-6 items' ELSE '7+ items' END                  AS cart_size_band
  FROM base AS b
  WHERE b.event_date >= date_sub(current_date(), 90)
    AND (b.has_payment_instrument OR b.payment_step_event > 0)
    AND b.payment_type IS NOT NULL
    AND b.checkout_type NOT IN ('agent_assisted', 'call_center', 'customer_service')
),
serialized AS (
  SELECT
    q.*,
    ROW_NUMBER() OVER (PARTITION BY q.serialization_key ORDER BY q.event_ts) AS occurrence_rank
  FROM qualified AS q
),
first_submission AS (
  SELECT * FROM serialized WHERE occurrence_rank = 1
),
daily AS (
  SELECT
    f.event_date,
    f.device_type,
    f.checkout_type,
    f.payment_type,
    f.payment_channel,
    f.shipping_tier,
    f.cart_size_band,
    COUNT(*)                                                                      AS payment_info_added,
    COUNT(DISTINCT f.visit_key)                                                   AS visits_with_payment_info,
    COUNT(DISTINCT f.ecid)                                                        AS visitors_with_payment_info,
    SUM(f.item_units)                                                             AS units_at_payment_step,
    SUM(CASE WHEN f.is_degraded_serialization THEN 1 ELSE 0 END)                  AS events_missing_checkout_id,
    SUM(CASE WHEN f.coupon_code <> '' THEN 1 ELSE 0 END)                          AS events_with_coupon,
    ROUND(SUM(f.checkout_value_local *
              CASE WHEN f.currency_code = 'USD' THEN CAST(1.0 AS DOUBLE) ELSE fx.rate_to_usd END), 2) AS checkout_intent_value_usd,
    SUM(CASE WHEN f.currency_code <> 'USD' AND fx.rate_to_usd IS NULL THEN 1 ELSE 0 END) AS events_missing_fx_rate
  FROM first_submission AS f
  LEFT JOIN fx_daily_rates_dataset AS fx
    ON fx.rate_date = f.event_date
   AND upper(fx.currency_code) = f.currency_code
  GROUP BY
    f.event_date, f.device_type, f.checkout_type,
    f.payment_type, f.payment_channel, f.shipping_tier, f.cart_size_band
)
SELECT
  d.event_date,
  d.device_type,
  d.checkout_type,
  d.payment_type,
  d.payment_channel,
  d.shipping_tier,
  d.cart_size_band,
  d.payment_info_added,
  d.visits_with_payment_info,
  d.visitors_with_payment_info,
  d.units_at_payment_step,
  d.checkout_intent_value_usd,
  d.checkout_intent_value_usd / NULLIF(d.payment_info_added, 0)                   AS avg_intent_value_usd,
  d.events_with_coupon,
  d.events_missing_checkout_id,
  CAST(d.events_missing_checkout_id AS DOUBLE) / NULLIF(d.payment_info_added, 0)  AS degraded_serialization_share,
  d.events_missing_fx_rate,
  d.event_date >= date_sub(current_date(), 3)                                     AS is_provisional
FROM daily AS d
ORDER BY d.event_date DESC, d.payment_info_added DESC;