Analyze Stripe Failed Payments and Dunning Recovery from CSV

How to export failed payment and retry data from Stripe

To see individual card decline reasons (insufficient_funds, do_not_honor, expired_card), export from Transactions rather than Subscriptions.

1. In the Stripe Dashboard, go to Transactions (or Payments > All payments).
2. Filter to All payments (including Failed and Paid), click Export, set your date range, and select All columns.
3. Download the CSV file. Drop it above for a first chart, or import it into the xmetric app to build the recovery view.

Stripe Payments CSV columns used

• id: unique payment intent or charge ID (py_... or ch_...).
• Customer ID: links failed attempts and later successful retries to the same customer.
• Invoice ID: groups multiple automatic Smart Retry attempts against the same recurring invoice (in_...).
• Created date (UTC) (or Created (UTC)): timestamp of each charge attempt.
• Amount: charge amount attempted.
• Status: Failed, Paid (or Succeeded), or Refunded.
• Decline Reason (and Seller Message in Payments CSV exports, or failure_code / decline_code in API exports): card network decline code.

Tableau LOD formulas and SQL for dunning recovery

If a recurring invoice fails three times before succeeding on the fourth retry, a naive COUNT of failed charges overstates lost revenue by 3x. Evaluate recovery at the Invoice ID (or Customer + Month) level using a FIXED Level of Detail expression. You can test each formula in the formula playground before building the view in the xmetric app.

1. Identify invoices that failed on first attempt (FIXED LOD)

// [Had Failed Attempt]
{ FIXED [Invoice ID] : MAX(IF [Status] = "Failed" THEN 1 ELSE 0 END) } = 1

2. Check whether the failed invoice eventually succeeded (FIXED LOD)

// [Eventually Recovered]
{ FIXED [Invoice ID] : MAX(IF [Status] = "Paid" OR [Status] = "Succeeded" THEN 1 ELSE 0 END) } = 1

3. Involuntary Churn vs. Recovered Revenue (FIXED LOD)

// [Invoice Recovery Status]
IF [Had Failed Attempt] AND [Eventually Recovered] THEN "Recovered via Retry/Dunning"
ELSEIF [Had Failed Attempt] AND NOT [Eventually Recovered] THEN "Unrecovered (Involuntary Churn)"
ELSE "Paid First Try"
END

Equivalent SQL

WITH invoice_attempts AS (
  SELECT
    COALESCE(NULLIF("Invoice ID", ''), "Customer ID" || '-' || CAST(DATE_TRUNC('month', CAST("Created date (UTC)" AS TIMESTAMP)) AS VARCHAR)) AS billing_cycle_key,
    "Customer ID" AS customer_id,
    MIN(CAST("Created date (UTC)" AS TIMESTAMP)) AS first_attempt_at,
    MAX("Amount") AS invoice_amount,
    COUNT(*) FILTER (WHERE "Status" = 'Failed') AS failed_attempt_count,
    MAX(CASE WHEN "Status" IN ('Paid', 'Succeeded') THEN 1 ELSE 0 END) AS is_recovered,
    FIRST("Decline Reason") FILTER (WHERE "Status" = 'Failed') AS initial_decline_code
  FROM stripe_payments
  GROUP BY 1, 2
)
SELECT
  initial_decline_code,
  COUNT(*) AS invoices_with_failure,
  SUM(invoice_amount) AS at_risk_revenue,
  SUM(CASE WHEN is_recovered = 1 THEN invoice_amount ELSE 0 END) AS recovered_revenue,
  SUM(CASE WHEN is_recovered = 0 THEN invoice_amount ELSE 0 END) AS lost_revenue,
  ROUND(100.0 * SUM(CASE WHEN is_recovered = 1 THEN invoice_amount ELSE 0 END) / NULLIF(SUM(invoice_amount), 0), 1) AS dollar_recovery_rate_pct
FROM invoice_attempts
WHERE failed_attempt_count > 0
GROUP BY 1
ORDER BY at_risk_revenue DESC;

Traps when analyzing Stripe payment failures

• Counting charge attempts instead of invoices: A single invoice of 100 retried 4 times generates 400 in failed charge rows in unified_payments.csv. Always aggregate with { FIXED [Invoice ID] : MAX([Amount]) } so at-risk revenue is counted once per invoice.
• Checkout declines mixed with recurring billing declines: New prospects typing a bad card number at initial checkout (Invoice ID is blank or billing_reason = subscription_create) are acquisition funnel drop-offs, not involuntary subscription churn. Filter to renewal invoices (Invoice ID present) when measuring dunning performance.
• Hard vs. soft declines: Hard decline codes like stolen_card, lost_card, or incorrect_number cannot be recovered via automatic retries and require the customer to provide a new payment method, whereas soft declines like insufficient_funds and do_not_honor often clear on subsequent retries.

Inspect dunning performance with AI agents

Every filter and LOD formula in xmetric is a structured command. An MCP server for Claude and ChatGPT is in development, so your agent will be able to audit payment exports and pinpoint which decline codes are leaking MRR. Live connections to Postgres and BigQuery are also coming.