Triage billing failures with operational context

RevenueAdvanced

Triage billing failures with operational context

Which failed payments coincide with API errors, unresolved jobs, or incomplete activation?

Start from failed billing outcomes, then add reduced operational signals so a review queue explains why each account needs attention.

Read-only SQL

WITH api_health AS (
  SELECT
    account_id,
    SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS api_errors
  FROM api_requests
  GROUP BY account_id
),
logical_jobs AS (
  SELECT
    job_id,
    account_id,
    MAX(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS had_failure,
    MAX(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed
  FROM job_runs
  GROUP BY job_id, account_id
),
job_health AS (
  SELECT
    account_id,
    SUM(CASE
      WHEN had_failure = 1 AND completed = 0 THEN 1 ELSE 0
    END) AS unresolved_jobs
  FROM logical_jobs
  GROUP BY account_id
),
activation AS (
  SELECT
    account_id,
    MAX(CASE
      WHEN event_name = 'dashboard_created' AND status = 'success'
      THEN 1 ELSE 0
    END) AS activated
  FROM product_events
  GROUP BY account_id
)
SELECT
  a.account_id,
  a.plan,
  b.amount_usd AS failed_payment_usd,
  COALESCE(api.api_errors, 0) AS api_errors,
  COALESCE(jobs.unresolved_jobs, 0) AS unresolved_jobs,
  COALESCE(activation.activated, 0) AS activated,
  1
    + CASE WHEN COALESCE(api.api_errors, 0) > 0 THEN 1 ELSE 0 END
    + CASE WHEN COALESCE(jobs.unresolved_jobs, 0) > 0 THEN 1 ELSE 0 END
    + CASE WHEN COALESCE(activation.activated, 0) = 0 THEN 1 ELSE 0 END
    AS review_signals
FROM billing_events AS b
JOIN accounts AS a ON a.account_id = b.account_id
LEFT JOIN api_health AS api ON api.account_id = b.account_id
LEFT JOIN job_health AS jobs ON jobs.account_id = b.account_id
LEFT JOIN activation ON activation.account_id = b.account_id
WHERE b.event_name = 'payment_failed' AND b.status = 'error'
ORDER BY review_signals DESC, failed_payment_usd DESC;

The dataset and query remain in this browser.

Published expected result

Operational review signals for failed payments

Chart value

acct_ember

acct_cinder

account_id	plan	failed_payment_usd	api_errors	unresolved_jobs	activated	review_signals
acct_ember	starter	199	1	1	0	4
acct_cinder	starter	299	0	1	0	3

How to read the query

The failed payment is the review-queue grain; API and job tables are reduced before joining.
Activation is a visible context field, not an assertion that onboarding caused payment failure.
The signal count prioritizes investigation but deliberately avoids an opaque health score.

Decisions the SQL cannot make

1Define the support workflow and access boundary for billing context.
2Choose incident windows so unrelated historical failures do not inflate current risk.
3Keep payment recovery, product recovery, and account retention as separate outcomes.

Continue from this lesson

Payment recovery recipe Customer impact guide Billing analytics use case