Analyze Asana Onboarding Funnel Dropoff | Dataford Interview Questions

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

Problem

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

Problem

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

Problem

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