← Back to KPIs

Payment Failure Rate

Also known as: Payment Decline Rate, Payment Error Rate, Transaction Failure Rate, Authorization Failure Rate

ConversionkpiE-commerce: Cart & Checkout Funnel AnalyticsE-commerce

Payment Failure Rate is the share of payment attempts submitted at checkout that are declined by the issuer or acquirer, or that error out in the gateway, before an order is confirmed. It isolates payment-layer revenue loss from the rest of checkout abandonment, so the business can separate friction it can fix in the form from declines it must fix with routing, retries or additional payment methods.

Business Use Case

1️⃣ The Payments Manager in Finance monitors the hourly rate by Payment Type and payment provider; when any method runs more than 2 percentage points above its trailing 28-day baseline for two consecutive hours, they open a severity ticket with the gateway and shift that method's traffic to the backup acquirer until the rate normalises.,2️⃣ The Checkout Product Manager reviews the weekly rate split by device category, browser and card brand; when failures concentrate in one combination (for example wallet attempts on in-app browsers), they prioritise the corresponding checkout fixes — field formatting, tokenised wallet fallback, inline retry messaging — into the next sprint.,3️⃣ The Customer Service Operations Lead uses the daily failure volume by error code to staff and script contact channels, pushing proactive 'try another card or wallet' guidance into the checkout error state and the post-failure email whenever soft declines exceed the weekly norm.,4️⃣ The Head of Revenue Management quantifies recoverable revenue at the monthly trading review by multiplying failed attempts that never converted by average order value, and uses that figure to justify adding a payment method, a local acquirer or an account-updater service for stored cards.

Business Criticality

High

Journey / Navigation Area

Checkout

Owning Function

Finance

Scope

Event

Formula

(Failed Payment Attempts / (Successful Payment Attempts + Failed Payment Attempts)) × 100

Calculation Notes

1️⃣ The grain is the payment attempt, not the session or the checkout. Denominator = every payment submission that reached the processor: successful submissions (one per `purchase`) plus failed submissions (`checkout_error` where `error_type = 'payment'`). Numerator = the failed submissions only. The success side is the standard, separate `purchase` push (ecommerce.transaction_id, value, currency, items; XDM eventType `commerce.purchases` with `commerce.purchases.value = 1`) carrying the same payment_type, payment_provider, checkout_type, payment_attempt_id, payment_attempt_number and traffic_type parameters, mapped to the shared `_openkpis.checkout`, `_openkpis.paymentAttempt` and `_openkpis.trafficType` fields.,2️⃣ `add_payment_info` is deliberately excluded from both numerator and denominator: entering card details is a preceding funnel step, not an authorization attempt, and using it as the base inflates the denominator with shoppers who never pressed Pay. It is not a dependency of this KPI.,3️⃣ Retries are separate attempts. A shopper who is declined twice and succeeds on the third try contributes 3 attempts and 2 failures (66.7%), never one attempt per checkout. Report a companion 'attempts per successful order' figure so a rising rate driven purely by retry behaviour is not read as a rising decline rate.,4️⃣ Failure is qualified by the contextual parameter `error_type = 'payment'` on the unified `checkout_error` event, with `error_code` carrying the gateway or issuer reason. Fragmented event names such as `payment_failed` or `card_declined` are not accepted in the definition; non-payment checkout errors (address validation, shipping eligibility, inventory, coupon, tax service) are excluded.,5️⃣ Exclude internal QA and test orders, zero-authorization amounts, orders settled entirely by gift card or store credit (no external authorization is requested), and known bot or synthetic-monitor traffic. Deduplicate repeated `checkout_error` hits carrying the same attempt or transaction identifier within a 2-second window, which are client re-renders rather than new submissions.,6️⃣ Attempts are stamped to the timestamp of the attempt itself in the reporting time zone, not to the order date; a retry that lands after the 30-minute session inactivity timeout starts a new session but is still counted as its own attempt, so hourly and daily totals must be built from event time and never from session-level aggregation.,7️⃣ Redirect and 3-D Secure challenge flows only count as a failure when the return leg fires `checkout_error` with `error_type = 'payment'`; challenges abandoned without a return leg are reported separately as unknown outcome. Payment Type is a mandatory breakdown — a blended rate hides card, wallet and BNPL behaving very differently — and the monthly total is reconciled against the processor's authorization report with a ±1 percentage point tolerance.

Technical Details

Measure TypePercentage
Aggregation WindowHourly, Daily, Weekly, Monthly

Events

PlatformEvent
Google Analytics 4checkout_error
AdobeCheckout Error: Payment

Data Mappings

W3 Data Layer
{
  "pageInstanceID": "checkout-payment-prod",
  "page": {
    "pageInfo": {
      "pageID": "checkout-payment",
      "pageName": "Checkout - Payment",
      "destinationURL": "https://www.example.com/checkout/payment",
      "language": "en-US",
      "geoRegion": "US"
    },
    "category": {
      "primaryCategory": "Checkout",
      "subCategory1": "Payment"
    }
  },
  "cart": {
    "cartID": "cart-8837412",
    "price": {
      "basePrice": 189.9,
      "cartTotal": 189.9,
      "currency": "USD"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-4471",
          "productName": "Merino Crew Sweater",
          "productBrand": "Northbound",
          "category": "Apparel/Tops"
        },
        "quantity": 2,
        "price": {
          "basePrice": 94.95,
          "currency": "USD"
        }
      }
    ]
  },
  "transaction": {
    "transactionID": "",
    "total": {
      "basePrice": 189.9,
      "transactionTotal": 189.9,
      "currency": "USD"
    },
    "attributes": {
      "paymentType": "credit_card",
      "paymentProvider": "primary_acquirer",
      "checkoutStep": "payment",
      "checkoutType": "guest",
      "paymentAttemptID": "pa-2f41c9",
      "paymentAttemptNumber": 2,
      "paymentAttemptOutcome": "failed"
    }
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "checkout_error",
        "eventAction": "Checkout Error: Payment",
        "type": "error",
        "timeStamp": "2026-09-17T14:32:08.000Z"
      },
      "attributes": {
        "errorType": "payment",
        "errorCode": "do_not_honor_05",
        "errorMessage": "Issuer declined the authorization. Ask the customer to try another card or wallet.",
        "errorSource": "issuer",
        "paymentType": "credit_card",
        "paymentProvider": "primary_acquirer",
        "checkoutStep": "payment",
        "paymentAttemptID": "pa-2f41c9",
        "paymentAttemptNumber": 2,
        "threeDSecureOutcome": "challenged_returned",
        "cartValue": 189.9,
        "currency": "USD"
      }
    },
    {
      "eventInfo": {
        "eventName": "purchase",
        "eventAction": "Purchase",
        "type": "transaction",
        "timeStamp": "2026-09-17T14:33:41.000Z"
      },
      "attributes": {
        "paymentType": "credit_card",
        "paymentProvider": "primary_acquirer",
        "checkoutStep": "payment",
        "paymentAttemptID": "pa-2f41c9-r3",
        "paymentAttemptNumber": 3,
        "paymentAttemptOutcome": "succeeded",
        "transactionID": "T-10058821",
        "transactionTotal": 189.9,
        "currency": "USD"
      }
    }
  ]
}
GA4 Data Layer
{
  "event": "checkout_error",
  "error_type": "payment",
  "error_code": "do_not_honor_05",
  "error_message": "Issuer declined the authorization. Ask the customer to try another card or wallet.",
  "error_source": "issuer",
  "payment_type": "credit_card",
  "payment_provider": "primary_acquirer",
  "checkout_step": "payment",
  "checkout_type": "guest",
  "payment_attempt_id": "pa-2f41c9",
  "payment_attempt_number": 2,
  "three_d_secure_outcome": "challenged_returned",
  "traffic_type": "external",
  "ecommerce": {
    "currency": "USD",
    "value": 189.9,
    "cart_id": "cart-8837412",
    "coupon": "FALL10",
    "items": [
      {
        "item_id": "SKU-4471",
        "item_name": "Merino Crew Sweater",
        "item_brand": "Northbound",
        "item_category": "Apparel",
        "item_category2": "Tops",
        "price": 94.95,
        "quantity": 2
      }
    ]
  }
}
Adobe Client Data Layer
{
  "event": "checkout_error",
  "eventInfo": {
    "eventName": "Checkout Error: Payment",
    "path": "commerce.checkout.payment",
    "timestamp": "2026-09-17T14:32:08.000Z"
  },
  "commerce": {
    "checkout": {
      "step": 3,
      "stepName": "payment",
      "checkoutType": "guest"
    },
    "paymentAttempt": {
      "attemptID": "pa-2f41c9",
      "attemptNumber": 2,
      "outcome": "failed",
      "paymentType": "credit_card",
      "paymentProvider": "primary_acquirer",
      "threeDSecureOutcome": "challenged_returned"
    },
    "error": {
      "errorType": "payment",
      "errorCode": "do_not_honor_05",
      "errorMessage": "Issuer declined the authorization. Ask the customer to try another card or wallet.",
      "errorSource": "issuer"
    },
    "cart": {
      "cartID": "cart-8837412",
      "currencyCode": "USD",
      "cartValue": 189.9
    },
    "productListItems": [
      {
        "SKU": "SKU-4471",
        "name": "Merino Crew Sweater",
        "quantity": 2,
        "priceTotal": 189.9,
        "currencyCode": "USD"
      }
    ]
  },
  "trafficType": "external"
}
XDM Mapping
{
  "eventType": "commerce.checkoutError",
  "timestamp": "2026-09-17T14:32:08.000Z",
  "commerce": {
    "order": {
      "currencyCode": "USD",
      "priceTotal": 189.9,
      "payments": [
        {
          "paymentType": "credit_card",
          "paymentAmount": 189.9,
          "currencyCode": "USD"
        }
      ]
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-4471",
      "name": "Merino Crew Sweater",
      "quantity": 2,
      "priceTotal": 189.9,
      "currencyCode": "USD"
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar41": "payment",
          "eVar42": "do_not_honor_05",
          "eVar43": "credit_card",
          "eVar44": "primary_acquirer",
          "eVar45": "payment"
        },
        "props": {
          "prop41": "checkout_error:payment",
          "prop42": "attempt:2"
        }
      }
    }
  },
  "_openkpis": {
    "checkout": {
      "checkoutStep": "payment",
      "checkoutType": "guest"
    },
    "checkoutError": {
      "errorType": "payment",
      "errorCode": "do_not_honor_05",
      "errorMessage": "Issuer declined the authorization. Ask the customer to try another card or wallet.",
      "errorSource": "issuer"
    },
    "paymentAttempt": {
      "attemptID": "pa-2f41c9",
      "attemptNumber": 2,
      "outcome": "failed",
      "paymentType": "credit_card",
      "paymentProvider": "primary_acquirer",
      "threeDSecureOutcome": "challenged_returned"
    },
    "trafficType": "external"
  }
}

SQL Queries

Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Payment Failure Rate = Failed Payment Attempts / (Successful + Failed Payment Attempts) * 100
-- Grain: ONE PAYMENT ATTEMPT submitted to the processor. Retries are separate attempts, never
-- collapsed to one row per transaction, per checkout or per session.
-- Base population (denominator) = successful submissions, carried by the unified `purchase` event,
-- UNION ALL failed submissions, carried by the unified `checkout_error` event whose contextual
-- parameter error_type = 'payment'. The numerator is the failed subset of that same population.
-- Grain of output: hour (America/Chicago) and date; roll hours up for daily/weekly/monthly views.
-- Breakdown: payment_type (required), payment_provider and device_category; error_code as the
-- secondary drill-down, reported on the failure side only so it cannot distort the denominator.

DECLARE date_start STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/Chicago'), INTERVAL 28 DAY));
DECLARE date_end   STRING DEFAULT FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('America/Chicago'), INTERVAL 1 DAY));

WITH base_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date)  AS attempt_date,
    TIMESTAMP_MICROS(event_timestamp) AS attempt_ts,
    TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), HOUR, 'America/Chicago') AS attempt_hour,
    event_timestamp,
    event_name,
    user_pseudo_id,
    device.category                   AS device_category,
    geo.country                       AS country,
    event_params
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN date_start AND date_end
    AND event_name IN ('purchase', 'checkout_error')
),

-- Pivot event_params once per event row: a repeated record can never satisfy two key predicates
-- at the same time, so every contextual parameter is lifted with MAX(IF(key = '...', value, NULL)).
attempt_facts AS (
  SELECT
    attempt_date,
    attempt_ts,
    attempt_hour,
    event_timestamp,
    event_name,
    user_pseudo_id,
    device_category,
    country,
    MAX(IF(p.key = 'error_type',             p.value.string_value, NULL)) AS error_type,
    MAX(IF(p.key = 'error_code',             p.value.string_value, NULL)) AS error_code,
    MAX(IF(p.key = 'error_source',           p.value.string_value, NULL)) AS error_source,
    MAX(IF(p.key = 'payment_type',           p.value.string_value, NULL)) AS payment_type,
    MAX(IF(p.key = 'payment_provider',       p.value.string_value, NULL)) AS payment_provider,
    MAX(IF(p.key = 'payment_attempt_id',     p.value.string_value, NULL)) AS payment_attempt_id,
    MAX(IF(p.key = 'payment_attempt_number', p.value.int_value,    NULL)) AS payment_attempt_number,
    MAX(IF(p.key = 'checkout_type',          p.value.string_value, NULL)) AS checkout_type,
    MAX(IF(p.key = 'transaction_id',         p.value.string_value, NULL)) AS transaction_id,
    MAX(IF(p.key = 'currency',               p.value.string_value, NULL)) AS currency,
    MAX(IF(p.key = 'traffic_type',           p.value.string_value, NULL)) AS traffic_type,
    MAX(IF(p.key = 'value', COALESCE(p.value.double_value, CAST(p.value.int_value AS FLOAT64)), NULL)) AS attempt_value
  FROM base_events, UNNEST(event_params) AS p
  GROUP BY attempt_date, attempt_ts, attempt_hour, event_timestamp, event_name, user_pseudo_id, device_category, country
),

-- One row per payment attempt: successes and failures unified into a single base population.
payment_attempts AS (
  SELECT
    attempt_date,
    attempt_ts,
    attempt_hour,
    user_pseudo_id,
    device_category,
    country,
    COALESCE(checkout_type, '(not set)')     AS checkout_type,
    COALESCE(payment_type, '(not set)')      AS payment_type,
    COALESCE(payment_provider, '(not set)')  AS payment_provider,
    error_code,
    error_source,
    payment_attempt_number,
    attempt_value,
    currency,
    IF(event_name = 'checkout_error', 1, 0)  AS is_failed_attempt,
    COALESCE(payment_attempt_id, transaction_id,
             CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING))) AS attempt_key
  FROM attempt_facts
  WHERE (
          event_name = 'purchase'
          OR (event_name = 'checkout_error' AND error_type = 'payment')
        )
    -- Exclusions: internal QA and synthetic traffic, zero-authorisation amounts and orders settled
    -- entirely by gift card or store credit, where no external authorisation is requested.
    AND COALESCE(traffic_type, 'external') NOT IN ('internal', 'test', 'bot')
    AND COALESCE(payment_type, '(not set)') NOT IN ('gift_card', 'store_credit')
    AND COALESCE(attempt_value, 0) > 0
),

-- Client re-renders repeat the same attempt identifier within ~2 seconds; a genuine retry keeps its
-- own attempt id or arrives later, so it survives here and is counted as an additional attempt.
flagged_attempts AS (
  SELECT
    pa.*,
    LAG(attempt_ts) OVER (
      PARTITION BY user_pseudo_id, attempt_key, is_failed_attempt
      ORDER BY attempt_ts
    ) AS prior_attempt_ts
  FROM payment_attempts pa
),

attempts AS (
  SELECT *
  FROM flagged_attempts
  WHERE prior_attempt_ts IS NULL
     OR TIMESTAMP_DIFF(attempt_ts, prior_attempt_ts, SECOND) >= 2
),

top_error AS (
  SELECT attempt_date, payment_type, error_code, failed_attempts
  FROM (
    SELECT
      attempt_date,
      payment_type,
      COALESCE(error_code, '(not set)') AS error_code,
      COUNT(*) AS failed_attempts,
      ROW_NUMBER() OVER (PARTITION BY attempt_date, payment_type ORDER BY COUNT(*) DESC) AS rn
    FROM attempts
    WHERE is_failed_attempt = 1
    GROUP BY attempt_date, payment_type, COALESCE(error_code, '(not set)')
  )
  WHERE rn = 1
)

SELECT
  a.attempt_date,
  a.attempt_hour,                                                             -- hourly grain (America/Chicago) for intraday monitoring
  a.payment_type,
  a.payment_provider,
  a.device_category,
  a.country,
  COUNT(*)                                                                    AS payment_attempts,             -- denominator: full base population
  COUNTIF(a.is_failed_attempt = 1)                                            AS failed_payment_attempts,      -- numerator: failed subset
  COUNTIF(a.is_failed_attempt = 0)                                            AS successful_payment_attempts,
  ROUND(SAFE_DIVIDE(COUNTIF(a.is_failed_attempt = 1), COUNT(*)) * 100, 2)     AS payment_failure_rate_pct,
  ROUND(SAFE_DIVIDE(COUNT(*), COUNTIF(a.is_failed_attempt = 0)), 2)           AS attempts_per_successful_order,
  ROUND(SUM(IF(a.is_failed_attempt = 1, a.attempt_value, 0)), 2)              AS failed_attempt_value,
  ANY_VALUE(t.error_code)                                                     AS top_error_code,
  ANY_VALUE(t.failed_attempts)                                                AS top_error_code_attempts
FROM attempts a
LEFT JOIN top_error t
  ON t.attempt_date = a.attempt_date
 AND t.payment_type = a.payment_type
GROUP BY a.attempt_date, a.attempt_hour, a.payment_type, a.payment_provider, a.device_category, a.country
ORDER BY a.attempt_hour DESC, payment_attempts DESC;
Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Payment Failure Rate = Failed Payment Attempts / (Successful + Failed Payment Attempts) * 100
-- ExperienceEvent dataset, attempt grain. The denominator unions the two attempt populations
-- mapped in XDM: successes on eventType = 'commerce.purchases' (commerce.purchases.value) and
-- failures on the non-counting error interaction eventType = 'commerce.checkoutError' (no
-- commerce.checkouts value, so standard Checkouts is not inflated) qualified by the contextual
-- field _openkpis.checkoutError.errorType = 'payment'. Retries stay separate attempts.
-- Checkout type is read from the shared _openkpis.checkout object set on both event types;
-- internal, test and bot traffic is excluded via _openkpis.trafficType in both branches.
-- Grain of output: hour (America/Chicago) and date; roll hours up for daily/weekly/monthly views.
-- Breakdown: _openkpis.paymentAttempt.paymentType (required), paymentProvider, device type;
-- _openkpis.checkoutError.errorCode as the secondary, failure-side drill-down.

WITH payment_attempts AS (
  -- Failed attempts: checkout error carrying the payment error type.
  SELECT
    to_date(timestamp)                                              AS attempt_date,
    timestamp                                                       AS attempt_ts,
    date_trunc('hour', from_utc_timestamp(timestamp, 'America/Chicago')) AS attempt_hour,
    endUserIDs._experience.mcid.id                                  AS visitor_id,
    coalesce(_openkpis.paymentAttempt.paymentType, '(not set)')     AS payment_type,
    coalesce(_openkpis.paymentAttempt.paymentProvider, '(not set)') AS payment_provider,
    coalesce(_openkpis.checkout.checkoutType, '(not set)')          AS checkout_type,
    coalesce(device.type, '(not set)')                              AS device_type,
    coalesce(placeContext.geo.countryCode, '(not set)')             AS country,
    coalesce(_openkpis.checkoutError.errorCode, '(not set)')        AS error_code,
    _openkpis.checkoutError.errorSource                             AS error_source,
    _openkpis.paymentAttempt.attemptNumber                          AS payment_attempt_number,
    commerce.order.priceTotal                                       AS attempt_value,
    commerce.order.currencyCode                                     AS currency,
    coalesce(_openkpis.paymentAttempt.attemptID,
             concat(endUserIDs._experience.mcid.id, '-', cast(timestamp AS string))) AS attempt_key,
    1                                                               AS is_failed_attempt
  FROM your_experience_event_dataset
  WHERE timestamp >= date_trunc('day', current_timestamp - interval '28 day')
    AND timestamp <  date_trunc('day', current_timestamp)
    AND eventType = 'commerce.checkoutError'
    AND _openkpis.checkoutError.errorType = 'payment'
    AND coalesce(_openkpis.trafficType, 'external') NOT IN ('internal', 'test', 'bot')
    AND coalesce(_openkpis.paymentAttempt.paymentType, '(not set)') NOT IN ('gift_card', 'store_credit')
    AND coalesce(commerce.order.priceTotal, 0) > 0

  UNION ALL

  -- Successful attempts: the authorised submission that produced the order.
  SELECT
    to_date(timestamp)                                              AS attempt_date,
    timestamp                                                       AS attempt_ts,
    date_trunc('hour', from_utc_timestamp(timestamp, 'America/Chicago')) AS attempt_hour,
    endUserIDs._experience.mcid.id                                  AS visitor_id,
    coalesce(_openkpis.paymentAttempt.paymentType,
             commerce.order.payments[0].paymentType, '(not set)')   AS payment_type,
    coalesce(_openkpis.paymentAttempt.paymentProvider, '(not set)') AS payment_provider,
    coalesce(_openkpis.checkout.checkoutType, '(not set)')          AS checkout_type,
    coalesce(device.type, '(not set)')                              AS device_type,
    coalesce(placeContext.geo.countryCode, '(not set)')             AS country,
    '(not set)'                                                     AS error_code,
    cast(null AS string)                                            AS error_source,
    _openkpis.paymentAttempt.attemptNumber                          AS payment_attempt_number,
    commerce.order.priceTotal                                       AS attempt_value,
    commerce.order.currencyCode                                     AS currency,
    coalesce(_openkpis.paymentAttempt.attemptID, commerce.order.purchaseID,
             concat(endUserIDs._experience.mcid.id, '-', cast(timestamp AS string))) AS attempt_key,
    0                                                               AS is_failed_attempt
  FROM your_experience_event_dataset
  WHERE timestamp >= date_trunc('day', current_timestamp - interval '28 day')
    AND timestamp <  date_trunc('day', current_timestamp)
    AND eventType = 'commerce.purchases'
    AND commerce.purchases.value >= 1
    AND coalesce(_openkpis.trafficType, 'external') NOT IN ('internal', 'test', 'bot')
    AND coalesce(_openkpis.paymentAttempt.paymentType,
                 commerce.order.payments[0].paymentType, '(not set)') NOT IN ('gift_card', 'store_credit')
    AND coalesce(commerce.order.priceTotal, 0) > 0
),

-- Drop client re-renders of the same attempt within 2 seconds; genuine retries carry their own
-- attempt id or land later, so they remain distinct attempts in the base population.
flagged_attempts AS (
  SELECT
    pa.*,
    lag(attempt_ts) OVER (
      PARTITION BY visitor_id, attempt_key, is_failed_attempt
      ORDER BY attempt_ts
    ) AS prior_attempt_ts
  FROM payment_attempts pa
),

attempts AS (
  SELECT *
  FROM flagged_attempts
  WHERE prior_attempt_ts IS NULL
     OR (cast(attempt_ts AS double) - cast(prior_attempt_ts AS double)) >= 2
),

top_error AS (
  SELECT attempt_date, payment_type, error_code, failed_attempts
  FROM (
    SELECT
      attempt_date,
      payment_type,
      error_code,
      count(*) AS failed_attempts,
      row_number() OVER (PARTITION BY attempt_date, payment_type ORDER BY count(*) DESC) AS rn
    FROM attempts
    WHERE is_failed_attempt = 1
    GROUP BY attempt_date, payment_type, error_code
  ) ranked_errors
  WHERE rn = 1
)

SELECT
  a.attempt_date,
  a.attempt_hour,
  a.payment_type,
  a.payment_provider,
  a.device_type,
  a.country,
  count(*)                                                                                  AS payment_attempts,             -- denominator: full base population
  sum(a.is_failed_attempt)                                                                  AS failed_payment_attempts,      -- numerator: failed subset
  sum(1 - a.is_failed_attempt)                                                              AS successful_payment_attempts,
  round(100.0 * sum(a.is_failed_attempt) / nullif(count(*), 0), 2)                          AS payment_failure_rate_pct,
  round(count(*) * 1.0 / nullif(sum(1 - a.is_failed_attempt), 0), 2)                        AS attempts_per_successful_order,
  round(sum(CASE WHEN a.is_failed_attempt = 1 THEN coalesce(a.attempt_value, 0) ELSE 0 END), 2) AS failed_attempt_value,
  max(t.error_code)                                                                         AS top_error_code,
  max(t.failed_attempts)                                                                    AS top_error_code_attempts
FROM attempts a
LEFT JOIN top_error t
  ON t.attempt_date = a.attempt_date
 AND t.payment_type = a.payment_type
GROUP BY a.attempt_date, a.attempt_hour, a.payment_type, a.payment_provider, a.device_type, a.country
ORDER BY a.attempt_hour DESC, payment_attempts DESC;

Source Data

Digital Analytics (GA4)

Dependencies

Prerequisite: List the dependencies required for this KPI

Events:

purchase

Metrics:

begin-checkout

Dimensions:

payment-methodcheckout-step

Report Attributes

Payment Type Payment Error Code Checkout Step Checkout Type (Guest / Registered) Device Category Country/Region Currency Cart Size Band

Recommended Reporting Views

Views
Checkout Funnel HealthPayment Performance MonitorRevenue Operations Daily ScorecardTrading & Conversion Weekly Review

Segment Eligibility

true

Governance

Data SensitivityInternal

Governance

Created bydevyendarm
Created on9/17/2026
Last modified bydevyendarm
Last modified on9/19/2026
Version1.0.0
Validation stateqa passed
StatusPUBLISHED

GitHub

View related Pull Requestcatalog/kpis/payment-failure-rate.yml

Community Discussion