计费失败风险分类 | 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将付款恢复、产品恢复和帐户保留作为单独的结果。

继续本课

付款恢复秘诀客户影响指南计费分析用例

保留结果

对您自己的事件运行此查询

仅当您准备好保存查询、连接真实事件并监视结果时，才创建空闲工作区。不需要信用卡。

保存我的活动