Task
Asana's growth team wants to understand where newly signed-up users are dropping off during onboarding. Write a SQL query to measure conversion through a 7-day onboarding funnel for each signup date.
Requirements
- Treat the funnel steps as:
signup workspace_created project_created task_completed.
- Only count events that happen within 7 days of the user's signup timestamp.
- Return one row per
signup_date with:
- total signed up users
- users who reached each funnel step
- conversion rate from signup to each step
- the step with the largest dropoff in user count
- Exclude users whose
signup_ts is NULL.
Use the tables below.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| signup_ts | TIMESTAMP | When the user signed up |
| acquisition_channel | VARCHAR(50) | Signup acquisition source |
| country_code | VARCHAR(2) | User country |
| | |
event_log
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| event_name | VARCHAR(50) | Product event in Asana |
| event_ts | TIMESTAMP | Event timestamp |
| surface | VARCHAR(50) | Asana surface where the event happened |
| | |
workspace_memberships
| column | type | description |
|---|
| membership_id | INT | Unique membership record |
| user_id | INT | User identifier |
| workspace_id | INT | Workspace identifier |
| role_name | VARCHAR(30) | Member role |
| joined_ts | TIMESTAMP | When the user joined the workspace |
| | |
Sample Data
users
| user_id | signup_ts | acquisition_channel | country_code |
|---|
| 1 | 2024-04-01 09:00:00 | organic | US |
| 2 | 2024-04-01 10:00:00 | paid_search | CA |
| 3 | 2024-04-01 11:00:00 | referral | GB |
| 4 | 2024-04-02 08:30:00 | organic | US |
| 5 | 2024-04-02 12:00:00 | paid_social | IN |
| 6 | 2024-04-02 15:00:00 | referral | DE |
event_log
| event_id | user_id | event_name | event_ts | surface |
|---|
| 101 | 1 | workspace_created | 2024-04-01 09:10:00 | web |
| 102 | 1 | project_created | 2024-04-01 09:20:00 | web |
| 103 | 1 | task_completed | 2024-04-02 10:00:00 | web |
| 104 | 2 | workspace_created | 2024-04-03 08:00:00 | ios |
| 105 | 3 | workspace_created | 2024-04-10 09:00:00 | web |
| 106 | 4 | workspace_created | 2024-04-02 09:00:00 | web |
workspace_memberships
| membership_id | user_id | workspace_id | role_name | joined_ts |
|---|
| 201 | 1 | 9001 | owner | 2024-04-01 09:10:00 |
| 202 | 2 | 9002 | owner | 2024-04-03 08:00:00 |
| 203 | 4 | 9003 | owner | 2024-04-02 09:00:00 |
Expected Output
| signup_date | signed_up_users | reached_workspace_created | reached_project_created | reached_task_completed | signup_to_workspace_rate | signup_to_project_rate | signup_to_task_rate | largest_dropoff_step |
|---|
| 2024-04-01 | 3 | 2 | 1 | 1 | 0.6667 | 0.3333 | 0.3333 | workspace_created |
| 2024-04-02 | 3 | 2 | 1 | 0 | 0.6667 | 0.3333 | 0.0000 | task_completed |
