Build an account activation funnel

Product analyticsIntermediate

Build an account activation funnel

How many signed-up accounts connect data, run a first query, and create a dashboard?

Join the account cohort to ordered product milestones so the denominator includes every eligible account, including those that never progress.

Read-only SQL

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

The dataset and query remain in this browser.

Published expected result

Signup-to-dashboard activation funnel

Chart value

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

How to read the query

The account table establishes the cohort independently from product-event volume.
MIN reduces repeated attempts to the first successful milestone for each account.
Ordering conditions stop an out-of-sequence event from incorrectly advancing an account.

Decisions the SQL cannot make

1Choose the account, user, or workspace as the funnel unit before writing SQL.
2Set a cohort window and allow every account the same activation opportunity.
3Document whether a later retry can satisfy a failed milestone.

Continue from this lesson

Activation SQL recipe SQL funnel analysis Retention use case