保留活動和收入| 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不要將活動與收入之間的相關性呈現為因果提升。

繼續本課

佇列保留秘訣淨保留和總保留查詢範例產品旅程指南

保留結果

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

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

儲存我的活動