계정 활성화 퍼널 구축

제품 분석중급

데이터를 연결하고 첫 번째 쿼리를 실행하고 대시보드를 생성하는 가입된 계정은 몇 개입니까?

주문된 제품 마일스톤에 대한 계정 집단에 참여하여 분모에는 진행되지 않는 계정을 포함하여 모든 적격 계정이 포함됩니다.

읽기 전용 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이 내릴 수 없는 결정

1SQL을 작성하기 전에 퍼널 단위로 계정, 사용자 또는 작업공간을 선택하십시오.
2코호트 창을 설정하고 모든 계정에 동일한 활성화 기회를 허용하십시오.
3This funnel allows later successful retries and treats equal timestamps as ordered. Use stricter timestamps if your product requires them.

이 강의에서 계속

SQL 레시피 활성화 SQL 깔때기 분석 보존 활용 사례

결과를 유지하세요

자신의 이벤트에 대해 이 쿼리를 실행하세요.

쿼리를 저장하고, 실제 이벤트를 연결하고, 결과를 모니터링할 준비가 된 경우에만 무료 작업공간을 생성하세요. 신용카드는 필요하지 않습니다.

내 이벤트와 함께 저장