建立帐户激活渠道

产品分析中级

建立帐户激活渠道

有多少注册帐户连接数据、运行第一个查询并创建仪表板？

加入帐户队列以订购产品里程碑，以便分母包括每个符合条件的帐户，包括那些从未取得进展的帐户。

只读 SQL

WITH signups AS (
  SELECT a.account_id, MIN(p.timestamp_utc) AS signed_up_at
  FROM accounts AS a
  LEFT JOIN product_events AS p
    ON p.account_id = a.account_id
    AND p.event_name = 'account_signed_up' AND p.status = 'success'
  GROUP BY a.account_id
),
connections AS (
  SELECT s.account_id, s.signed_up_at, MIN(p.timestamp_utc) AS connected_at
  FROM signups AS s
  LEFT JOIN product_events AS p
    ON p.account_id = s.account_id
    AND p.event_name = 'integration_connected' AND p.status = 'success'
    AND p.timestamp_utc >= s.signed_up_at
  GROUP BY s.account_id, s.signed_up_at
),
first_queries AS (
  SELECT c.account_id, c.signed_up_at, c.connected_at,
    MIN(p.timestamp_utc) AS first_query_at
  FROM connections AS c
  LEFT JOIN product_events AS p
    ON p.account_id = c.account_id
    AND p.event_name = 'first_query_completed' AND p.status = 'success'
    AND p.timestamp_utc >= c.connected_at
  GROUP BY c.account_id, c.signed_up_at, c.connected_at
),
milestones AS (
  SELECT q.account_id, q.signed_up_at, q.connected_at, q.first_query_at,
    MIN(p.timestamp_utc) AS dashboard_at
  FROM first_queries AS q
  LEFT JOIN product_events AS p
    ON p.account_id = q.account_id
    AND p.event_name = 'dashboard_created' AND p.status = 'success'
    AND p.timestamp_utc >= q.first_query_at
  GROUP BY q.account_id, q.signed_up_at, q.connected_at, q.first_query_at
),
stages AS (
  SELECT 1 AS step, 'Signed up' AS stage, COUNT(signed_up_at) AS accounts
  FROM milestones
  UNION ALL
  SELECT 2, 'Connected data', COUNT(connected_at) FROM milestones
  UNION ALL
  SELECT 3, 'Ran first query', COUNT(first_query_at) FROM milestones
  UNION ALL
  SELECT 4, 'Created dashboard', COUNT(dashboard_at) FROM milestones
)
SELECT
  stage,
  accounts,
  ROUND(100.0 * accounts
    / NULLIF(MAX(CASE WHEN step = 1 THEN accounts END) OVER (), 0), 1)
    AS conversion_pct
FROM stages
ORDER BY step;

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

Published expected result

注册到仪表板激活漏斗

图表值

Signed up

100%

Connected data

83.3%

Ran first query

66.7%

Created dashboard

50%

stage	accounts	conversion_pct
Signed up	6	100
Connected data	5	83.3
Ran first query	4	66.7
Created dashboard	3	50

如何读取查询

账户表独立于产品事件量来建立群组。
Each stage selects the first successful milestone at or after the previous eligible stage; repeated events count once.
A missing or out-of-order milestone blocks every later stage. A later successful retry can restore valid progression.

SQL 无法做出的决定

1在编写 SQL 之前，选择帐户、用户或工作区作为漏斗单元。
2设置队列窗口并允许每个帐户都有相同的激活机会。
3This funnel allows later successful retries and treats equal timestamps as ordered. Use stricter timestamps if your product requires them.

继续本课

激活 SQL 查询示例 SQL漏斗分析保留用例

保留结果

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

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

保存我的活动