保留活动和收入| SaaS SQL 实验室

收入高级

将保留的产品活动与收入背景联系起来

哪些计划会激活、7 天后返回并贡献经常性收入？

在加入经常性收入和收集的计费结果之前，将产品事件减少到一个帐户级别的生命周期。

只读 SQL

WITH account_activity AS (
  SELECT
    a.account_id,
    a.plan,
    a.monthly_revenue_usd,
    MAX(CASE
      WHEN p.event_name = 'dashboard_created' AND p.status = 'success'
      THEN 1 ELSE 0
    END) AS activated,
    MAX(CASE
      WHEN p.timestamp_utc >= a.signup_at + INTERVAL '7 days'
        AND p.status = 'success'
      THEN 1 ELSE 0
    END) AS returned_after_7d
  FROM accounts AS a
  LEFT JOIN product_events AS p ON p.account_id = a.account_id
  GROUP BY a.account_id, a.plan, a.monthly_revenue_usd
),
collections AS (
  SELECT
    account_id,
    SUM(CASE
      WHEN event_name = 'invoice_paid' AND status = 'success'
      THEN amount_usd ELSE 0
    END) AS collected_usd
  FROM billing_events
  GROUP BY account_id
)
SELECT
  activity.plan,
  COUNT(*) AS accounts,
  SUM(activity.activated) AS activated_accounts,
  SUM(activity.returned_after_7d) AS returned_accounts,
  SUM(activity.monthly_revenue_usd) AS recurring_revenue_usd,
  SUM(COALESCE(collections.collected_usd, 0)) AS collected_usd
FROM account_activity AS activity
LEFT JOIN collections ON collections.account_id = activity.account_id
GROUP BY activity.plan
ORDER BY recurring_revenue_usd DESC, activity.plan;

数据集和查询保留在此浏览器中。

Published expected result

保留产品活动的经常性收入

图表值

growth

4,200 USD

business

1,200 USD

starter

498 USD

free

0 USD

plan	accounts	activated_accounts	returned_accounts	recurring_revenue_usd	collected_usd
growth	2	2	2	4,200	4,200
business	1	1	0	1,200	1,200
starter	2	0	0	498	0
free	1	0	0	0	0

如何读取查询

在引入收入之前，产品行会折叠为激活和返回标志。
7 天回报测试以每个帐户的注册时间而不是全球日历周为基础。
经常性收入和收取的发票价值保持分开，因为合同价值不是付款证明。

SQL 无法做出的决定

1选择一个可预测该产品价值的持久激活里程碑。
2在比较保留率之前，为每个队列提供相同的返回窗口。
3不要将活动与收入之间的相关性呈现为因果提升。

继续本课

队列保留秘诀净保留和总保留查询示例产品旅程指南

保留结果

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

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

保存我的活动