What is failed-payment rate, and how do you measure it in Metabase?

Failed-payment rate is the share of recurring charges that fail — expired cards, insufficient funds, declines. It's the leading cause of involuntary churn, and unlike voluntary cancellations it's often recoverable with better dunning. Measure it in Metabase from billing data synced into a database (Stripe, Chargebee, Paddle, or Recurly).

How is failed-payment rate calculated?

Failed-payment rate = failed charges ÷ attempted charges, scoped to recurring renewals (not one-time or manual invoices). Then track two follow-on numbers:

• Recovery rate — of the payments that failed, how many were eventually collected through retries and dunning.
• Involuntary churn — failures that were never recovered and ended the subscription.

Dunning & recovery

Dunning is the retry-and-notify process after a payment fails: smart retries on optimal days, email/in-app prompts to update the card, and a grace period before cancellation. Most billing tools run this automatically — measuring recovery rate tells you whether it's working.

• Watch the failure reason mix — expired cards recover far better than hard declines.
• Report recovered revenue as an offset to gross involuntary churn.

What data does failed-payment rate need?

• Invoice or charge records with a status (paid, failed, uncollectible).
• A way to isolate recurring renewals from one-time charges.
• Retry/attempt counts and failure reason codes for recovery analysis.

SQL patterns

SELECT
  date_trunc('month', created) AS month,
  COUNT(*)                                            AS attempted,
  COUNT(*) FILTER (WHERE status = 'failed')           AS failed,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'failed')
    / NULLIF(COUNT(*), 0), 2)                         AS failed_payment_pct
FROM invoices
WHERE billing_reason = 'subscription_cycle'
GROUP BY 1
ORDER BY 1;

-- Of invoices that failed first attempt, how many were later paid?
SELECT
  date_trunc('month', created) AS month,
  COUNT(*) FILTER (WHERE status = 'paid'
    AND had_failed_attempt)                           AS recovered,
  COUNT(*) FILTER (WHERE had_failed_attempt)          AS ever_failed,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'paid' AND had_failed_attempt)
    / NULLIF(COUNT(*) FILTER (WHERE had_failed_attempt), 0), 2) AS recovery_pct
FROM invoices
GROUP BY 1
ORDER BY 1;

Pitfalls

Where this metric applies

• Stripe + Metabase — invoices, charges, and Smart Retries
• Chargebee + Metabase — invoices, transactions, and dunning
• Paddle + Metabase — transactions and payment failures (Paddle handles retries)
• Recurly + Metabase — transactions and revenue recovery

Related

Analytics

Metrics

FAQ