Payment Method

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "add_payment_info",
          "eventAction": "add_payment_info"
        },
        "attributes": {
          "paymentMethod": "credit_card"
        }
      }
    ],
    "transaction": {
      "total": {
        "basePrice": 129.98,
        "currency": "USD"
      },
      "attributes": {
        "paymentMethod": "credit_card"
      }
    }
  }
}

{
  "event": "add_payment_info",
  "ecommerce": {
    "currency": "USD",
    "value": 129.98,
    "coupon": "SPRING10",
    "payment_type": "credit_card",
    "items": [
      {
        "item_id": "SKU_12345",
        "item_name": "Trail Running Shoe",
        "item_category": "Footwear",
        "price": 64.99,
        "quantity": 2
      }
    ]
  }
}

{
  "event": "add_payment_info",
  "eventInfo": {
    "checkoutStep": "payment"
  },
  "transaction": {
    "paymentMethod": "credit_card",
    "currency": "USD",
    "value": 129.98
  }
}

{
  "eventType": "commerce.checkouts",
  "commerce": {
    "checkouts": {
      "value": 1
    },
    "order": {
      "currencyCode": "USD",
      "priceTotal": 129.98,
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentAmount": 129.98,
          "currencyCode": "USD"
        }
      ]
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Payment Method (GA4 payment_type): checkout sessions, transactions and revenue by method, last 30 complete days
WITH ev AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    (SELECT MAX(IF(key = 'ga_session_id', value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_id,
    (SELECT MAX(IF(key = 'payment_type', value.string_value, NULL)) FROM UNNEST(event_params)) AS payment_type_raw,
    ecommerce.transaction_id,
    ecommerce.purchase_revenue_in_usd AS revenue_usd
  FROM `your-project.analytics_123456789.events_*`
  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 ('add_payment_info', 'purchase')
),
last_selection AS (
  -- customers may switch methods during checkout; the last selection in the session wins
  SELECT
    user_pseudo_id,
    ga_session_id,
    ARRAY_AGG(LOWER(TRIM(payment_type_raw)) ORDER BY event_timestamp DESC LIMIT 1)[OFFSET(0)] AS selected_method
  FROM ev
  WHERE event_name = 'add_payment_info' AND payment_type_raw IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
),
orders AS (
  -- one row per transaction; the settled method sent on purchase overrides the checkout selection
  SELECT
    user_pseudo_id,
    ga_session_id,
    transaction_id,
    LOWER(TRIM(payment_type_raw)) AS settled_method,
    revenue_usd
  FROM ev
  WHERE event_name = 'purchase' AND transaction_id IS NOT NULL AND transaction_id != '(not set)'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_timestamp) = 1
),
sessions AS (
  SELECT
    CONCAT(COALESCE(s.user_pseudo_id, o.user_pseudo_id), '.', CAST(COALESCE(s.ga_session_id, o.ga_session_id) AS STRING)) AS session_key,
    s.selected_method IS NOT NULL AS had_payment_info,
    COALESCE(o.settled_method, s.selected_method, '(not set)') AS payment_method,
    o.transaction_id,
    o.revenue_usd
  FROM last_selection s
  FULL OUTER JOIN orders o
    ON o.user_pseudo_id = s.user_pseudo_id AND o.ga_session_id = s.ga_session_id
)
SELECT
  payment_method,
  COUNT(DISTINCT IF(had_payment_info, session_key, NULL)) AS sessions_with_payment_info,
  COUNT(DISTINCT transaction_id) AS transactions,
  ROUND(SUM(revenue_usd), 2) AS purchase_revenue_usd,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(had_payment_info AND transaction_id IS NOT NULL, session_key, NULL)),
    COUNT(DISTINCT IF(had_payment_info, session_key, NULL))), 4) AS payment_to_purchase_rate,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT transaction_id), SUM(COUNT(DISTINCT transaction_id)) OVER ()), 4) AS transaction_share
FROM sessions
GROUP BY payment_method
ORDER BY transactions DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Payment Method from XDM commerce.order.payments (Web SDK / Analytics source dataset), last 30 complete days
WITH tenders AS (
  SELECT
    _id AS event_id,
    timestamp,
    eventType,
    commerce.order.purchaseID AS purchase_id,
    commerce.order.priceTotal AS order_total,
    posexplode(commerce.order.payments) AS (tender_seq, pmt)
  FROM web_experience_events
  WHERE timestamp >= date_sub(current_date, 30)
    AND timestamp < current_date
    AND eventType IN ('commerce.checkouts', 'commerce.purchases')
    AND commerce.order.payments IS NOT NULL
),
primary_tender AS (
  -- split tender: the largest settled amount is primary, ties broken by tender sequence
  SELECT event_id, eventType, purchase_id, order_total, lower(trim(pmt.paymentType)) AS payment_method
  FROM (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY pmt.paymentAmount DESC NULLS LAST, tender_seq ASC) AS rn
    FROM tenders t
  ) r
  WHERE rn = 1
),
orders AS (
  SELECT purchase_id, payment_method, order_total
  FROM (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY event_id) AS dup
    FROM primary_tender p
    WHERE eventType = 'commerce.purchases' AND purchase_id IS NOT NULL
  ) d
  WHERE dup = 1
),
selections AS (
  SELECT payment_method, COUNT(DISTINCT event_id) AS payment_selection_events
  FROM primary_tender
  WHERE eventType = 'commerce.checkouts'
  GROUP BY payment_method
),
order_totals AS (
  SELECT payment_method, COUNT(*) AS orders, ROUND(SUM(order_total), 2) AS order_revenue
  FROM orders
  GROUP BY payment_method
)
SELECT
  COALESCE(s.payment_method, o.payment_method) AS payment_method,
  COALESCE(s.payment_selection_events, 0) AS payment_selection_events,
  COALESCE(o.orders, 0) AS orders,
  COALESCE(o.order_revenue, 0) AS order_revenue,
  ROUND(COALESCE(o.orders, 0) / NULLIF(SUM(COALESCE(o.orders, 0)) OVER (), 0), 4) AS order_share
FROM selections s
FULL OUTER JOIN order_totals o ON s.payment_method = o.payment_method
ORDER BY orders DESC;