lesson 52 Advanced product analysis · advanced

Ordered conversion funnels

Count users who complete events in the required order.

the idea

An ordered funnel counts users who complete steps in sequence: each step must happen at or after the previous step. Find each user’s earliest qualifying time for step 1, then the earliest step-2 time at or after it, and so on. Users who skip a step stop there.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Compare naive step counts. Run this. These totals count anyone who ever hit each step—they ignore whether onboarding came before the first project.the pattern

   example.sql Run

   sql›

   SELECT event_name, COUNT(DISTINCT user_id) AS users
   FROM events
   WHERE event_name IN ('user_signed_up', 'onboarding_completed', 'project_created')
   GROUP BY event_name;

2. 2

   Walk one user’s timeline. Run this. User 100’s funnel events in time order—use this picture when you enforce signup → onboarding → project.

   step-2.sql Run

   sql›

   SELECT user_id, event_name, occurred_at
   FROM events
   WHERE user_id = 100
     AND event_name IN ('user_signed_up', 'onboarding_completed', 'project_created')
   ORDER BY occurred_at;

3. 3

   Pivot first time per step. Run this. CASE inside MIN gives each user’s earliest timestamp per event name; you still must require each step to come after the previous one.

   step-3.sql Run

   sql›

   SELECT
     user_id,
     MIN(CASE
       WHEN event_name = 'user_signed_up' THEN occurred_at
     END) AS signed_up,
     MIN(CASE
       WHEN event_name = 'onboarding_completed' THEN occurred_at
     END) AS first_onboarding,
     MIN(CASE
       WHEN event_name = 'project_created' THEN occurred_at
     END) AS first_project
   FROM events
   WHERE user_id BETWEEN 100 AND 110
   GROUP BY user_id;

common mistake

Counting users who ever did each event ignores order: a project created before onboarding would wrongly count as a completed funnel.