Interview Guides

Analyze Asana Onboarding Funnel Dropoff | Dataford Interview Questions - Dataford - Ace your Interview

Analyze Asana Onboarding Funnel Dropoff

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsDate FunctionsFunnel Analysis

Also asked at

Problem

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

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
country_code	VARCHAR(2)	User country code

event_log

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
event_name	VARCHAR(50)	Name of the Asana event
event_ts	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Asana surface where the event occurred

workspace_memberships

Column	Type	Description
membership_idPK	INT	Unique membership record
user_id	INT	User identifier
workspace_id	INT	Workspace identifier
role_name	VARCHAR(30)	Role in the workspace
joined_ts	TIMESTAMP	Timestamp when the user joined the workspace

Sample Data

Example 1

Input

{"users":[[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"],[7,"2024-04-03 09:15:00","organic","BR"],[8,"2024-04-03 17:45:00",null,"US"],[9,null,"paid_search","FR"],[10,"2024-04-01 23:50:00","partner",null],["1","2024-03-30 21:29:17","webinar","null"],["2","2024-03-30 02:01:03","startup_progra

Output

[["2024-03-29","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"],["2024-04-01","5","3","2","1","0.6000","0.4000","0.2000","workspace_created"],["2024-04-02","4","1","2","0","0.2500","0.5000","0.0000","workspace_created"],["2024-04-03","2","1","1","0","0.5000","0.5000","0.0000","workspace_created"],["2024-04-06","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"]]

Problem

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

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
country_code	VARCHAR(2)	User country code

event_log

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
event_name	VARCHAR(50)	Name of the Asana event
event_ts	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Asana surface where the event occurred

workspace_memberships

Column	Type	Description
membership_idPK	INT	Unique membership record
user_id	INT	User identifier
workspace_id	INT	Workspace identifier
role_name	VARCHAR(30)	Role in the workspace
joined_ts	TIMESTAMP	Timestamp when the user joined the workspace

Sample Data

Example 1

Input

{"users":[[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"],[7,"2024-04-03 09:15:00","organic","BR"],[8,"2024-04-03 17:45:00",null,"US"],[9,null,"paid_search","FR"],[10,"2024-04-01 23:50:00","partner",null],["1","2024-03-30 21:29:17","webinar","null"],["2","2024-03-30 02:01:03","startup_progra

Output

[["2024-03-29","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"],["2024-04-01","5","3","2","1","0.6000","0.4000","0.2000","workspace_created"],["2024-04-02","4","1","2","0","0.2500","0.5000","0.0000","workspace_created"],["2024-04-03","2","1","1","0","0.5000","0.5000","0.0000","workspace_created"],["2024-04-06","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Signup Funnel Drop-OffMedium

Find Onboarding Funnel Drop-OffMedium

7-Day Retention by Integration SignupMedium

Next question

Analyze Asana Onboarding Funnel Dropoff

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsDate FunctionsFunnel Analysis

Also asked at

Problem

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

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
country_code	VARCHAR(2)	User country code

event_log

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
event_name	VARCHAR(50)	Name of the Asana event
event_ts	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Asana surface where the event occurred

workspace_memberships

Column	Type	Description
membership_idPK	INT	Unique membership record
user_id	INT	User identifier
workspace_id	INT	Workspace identifier
role_name	VARCHAR(30)	Role in the workspace
joined_ts	TIMESTAMP	Timestamp when the user joined the workspace

Sample Data

Example 1

Input

{"users":[[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"],[7,"2024-04-03 09:15:00","organic","BR"],[8,"2024-04-03 17:45:00",null,"US"],[9,null,"paid_search","FR"],[10,"2024-04-01 23:50:00","partner",null],["1","2024-03-30 21:29:17","webinar","null"],["2","2024-03-30 02:01:03","startup_progra

Output

[["2024-03-29","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"],["2024-04-01","5","3","2","1","0.6000","0.4000","0.2000","workspace_created"],["2024-04-02","4","1","2","0","0.2500","0.5000","0.0000","workspace_created"],["2024-04-03","2","1","1","0","0.5000","0.5000","0.0000","workspace_created"],["2024-04-06","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"]]

Problem

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

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
country_code	VARCHAR(2)	User country code

event_log

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who triggered the event
event_name	VARCHAR(50)	Name of the Asana event
event_ts	TIMESTAMP	Timestamp when the event occurred
surface	VARCHAR(50)	Asana surface where the event occurred

workspace_memberships

Column	Type	Description
membership_idPK	INT	Unique membership record
user_id	INT	User identifier
workspace_id	INT	Workspace identifier
role_name	VARCHAR(30)	Role in the workspace
joined_ts	TIMESTAMP	Timestamp when the user joined the workspace

Sample Data

Example 1

Input

{"users":[[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"],[7,"2024-04-03 09:15:00","organic","BR"],[8,"2024-04-03 17:45:00",null,"US"],[9,null,"paid_search","FR"],[10,"2024-04-01 23:50:00","partner",null],["1","2024-03-30 21:29:17","webinar","null"],["2","2024-03-30 02:01:03","startup_progra

Output

[["2024-03-29","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"],["2024-04-01","5","3","2","1","0.6000","0.4000","0.2000","workspace_created"],["2024-04-02","4","1","2","0","0.2500","0.5000","0.0000","workspace_created"],["2024-04-03","2","1","1","0","0.5000","0.5000","0.0000","workspace_created"],["2024-04-06","2","0","0","0","0.0000","0.0000","0.0000","workspace_created"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Analyze Signup Funnel Drop-OffMedium

Find Onboarding Funnel Drop-OffMedium

7-Day Retention by Integration SignupMedium

Next question