計費失敗風險分類 | SaaS SQL 實驗室

收入高階

根據運營環境對計費失敗進行分類

哪些失敗的付款與 API 錯誤、未解決的作業或不完整的啟用同時發生？

從失敗的計費結果開始，然後新增減少的操作訊號，以便審查佇列解釋為什麼每個帳戶需要關注。

只讀 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;

資料集和查詢保留在此瀏覽器中。

Published expected result

付款失敗的操作審查訊號

圖表值

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

如何讀取查詢

支付失敗為審查佇列糧食； API 和作業資料表在加入之前會減少。
啟用是一個可見的上下文欄位，而不是入職導致支付失敗的斷言。
訊號計數優先考慮調查，但有意避免不透明的健康評分。

SQL 無法做出的決定

1定義計費上下文的支援工作流程和存取邊界。
2選擇事件視窗，以便不相關的歷史故障不會增加當前風險。
3將付款恢復、產品恢復和帳戶保留作為單獨的結果。

繼續本課

付款恢復秘訣客戶影響指南計費分析用例

保留結果

對您自己的事件執行此查詢

僅當您準備好儲存查詢、連線真實事件並監視結果時，才建立空閒工作區。不需要信用卡。

儲存我的活動