Interview Guides

Conversion Rate by Acquisition Channel | Dataford Interview Questions - Dataford - Ace your Interview

Conversion Rate by Acquisition Channel

Medium

SQL & Data Manipulation

Asked at 3 companies3JoinsAggregationsCase When

Also asked at

Problem

Task

BrightCart wants to measure how well each acquisition channel converts visitors into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.

Requirements

Count total acquired users per channel from the users table.
Count converted users per channel, where a user is considered converted if they have at least one completed order.
Return acquisition_channel, total_users, converted_users, and conversion_rate_pct rounded to 2 decimal places.
Include channels with zero conversions, and sort by conversion rate descending, then channel name ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	User signup date

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status such as completed or cancelled
order_amount	DECIMAL(10,2)	Order value

Sample Data

`users`

user_id	acquisition_channel	signup_date
3	Referral	2024-01-12
1	Paid Search	2024-01-05
8	Email	2024-02-10
6	Organic Search	2024-02-01
2	Paid Search	2024-01-08
10	NULL	2024-02-20
5	Social	2024-01-25
4	Social	2024-01-20
7	Organic Search	2024-02-05
9	Affiliate	2024-02-15
11	Email	2024-02-22
12	Referral	2024-02-25

`orders`

order_id	user_id	order_date	order_status	order_amount
105	5	2024-02-03	pending	40.00
101	1	2024-01-10	completed	120.00
109	9	2024-02-18	completed	95.00
103	2	2024-01-18	cancelled	60.00
111	11	2024-02-25	completed	70.00
107	7	2024-02-09	completed	85.00
112	12	2024-02-28	refunded	55.00
102	1	2024-01-15	completed	80.00
106	6	2024-02-06	completed	150.00
104	4	2024-01-28	completed	200.00
108	8	2024-02-12	cancelled	30.00
110	99	2024-02-20	completed	110.00

Expected Output

acquisition_channel	total_users	converted_users	conversion_rate_pct
Affiliate	1	1	100.00
Organic Search	2	2	100.00
Email	2	1	50.00
Paid Search	2	1	50.00
Social	2	1	50.00
Referral	2	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	Date the user signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Lifecycle status of the order
order_amount	DECIMAL(10,2)	Monetary value of the order

Sample Data

Example 1

Input

{"users":[["3","Referral","2024-01-12"],["1","Paid Search","2024-01-05"],["8","Email","2024-02-10"],["6","Organic Search","2024-02-01"],["2","Paid Search","2024-01-08"],["10",null,"2024-02-20"],["5","Social","2024-01-25"],["4","Social","2024-01-20"],["7","Organic Search","2024-02-05"],["9","Affiliate","2024-02-15"],["11","Email","2024-02-22"],["12","Referral","2024-02-25"],["13","Email","2024-02-16"],["14","Email","2024-02-19"],["15","Paid Search","2024-02-19"],["16","Email","2024-02-10"],["17",

Output

[["Affiliate","1","1","100.00"],["Organic Search","3","2","66.67"],["Social","2","1","50.00"],["Paid Search","3","1","33.33"],["Email","5","1","20.00"],["Direct","2","0","0.00"],["Partnerships","1","0","0.00"],["Podcast","3","0","0.00"],["Referral","2","0","0.00"],["SMS","2","0","0.00"]]

Problem

Task

BrightCart wants to measure how well each acquisition channel converts visitors into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.

Requirements

Count total acquired users per channel from the users table.
Count converted users per channel, where a user is considered converted if they have at least one completed order.
Return acquisition_channel, total_users, converted_users, and conversion_rate_pct rounded to 2 decimal places.
Include channels with zero conversions, and sort by conversion rate descending, then channel name ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	User signup date

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status such as completed or cancelled
order_amount	DECIMAL(10,2)	Order value

Sample Data

`users`

user_id	acquisition_channel	signup_date
3	Referral	2024-01-12
1	Paid Search	2024-01-05
8	Email	2024-02-10
6	Organic Search	2024-02-01
2	Paid Search	2024-01-08
10	NULL	2024-02-20
5	Social	2024-01-25
4	Social	2024-01-20
7	Organic Search	2024-02-05
9	Affiliate	2024-02-15
11	Email	2024-02-22
12	Referral	2024-02-25

`orders`

order_id	user_id	order_date	order_status	order_amount
105	5	2024-02-03	pending	40.00
101	1	2024-01-10	completed	120.00
109	9	2024-02-18	completed	95.00
103	2	2024-01-18	cancelled	60.00
111	11	2024-02-25	completed	70.00
107	7	2024-02-09	completed	85.00
112	12	2024-02-28	refunded	55.00
102	1	2024-01-15	completed	80.00
106	6	2024-02-06	completed	150.00
104	4	2024-01-28	completed	200.00
108	8	2024-02-12	cancelled	30.00
110	99	2024-02-20	completed	110.00

Expected Output

acquisition_channel	total_users	converted_users	conversion_rate_pct
Affiliate	1	1	100.00
Organic Search	2	2	100.00
Email	2	1	50.00
Paid Search	2	1	50.00
Social	2	1	50.00
Referral	2	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	Date the user signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Lifecycle status of the order
order_amount	DECIMAL(10,2)	Monetary value of the order

Sample Data

Example 1

Input

{"users":[["3","Referral","2024-01-12"],["1","Paid Search","2024-01-05"],["8","Email","2024-02-10"],["6","Organic Search","2024-02-01"],["2","Paid Search","2024-01-08"],["10",null,"2024-02-20"],["5","Social","2024-01-25"],["4","Social","2024-01-20"],["7","Organic Search","2024-02-05"],["9","Affiliate","2024-02-15"],["11","Email","2024-02-22"],["12","Referral","2024-02-25"],["13","Email","2024-02-16"],["14","Email","2024-02-19"],["15","Paid Search","2024-02-19"],["16","Email","2024-02-10"],["17",

Output

[["Affiliate","1","1","100.00"],["Organic Search","3","2","66.67"],["Social","2","1","50.00"],["Paid Search","3","1","33.33"],["Email","5","1","20.00"],["Direct","2","0","0.00"],["Partnerships","1","0","0.00"],["Podcast","3","0","0.00"],["Referral","2","0","0.00"],["SMS","2","0","0.00"]]

Practice SQL

PostgreSQL

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

Up next

Conversion Rate by Acquisition ChannelMedium

Conversion Rate by Channel SegmentMedium

Channel Conversion Rate CalculationEasy

Next question

Conversion Rate by Acquisition Channel

Medium

SQL & Data Manipulation

Asked at 3 companies3JoinsAggregationsCase When

Also asked at

Problem

Task

BrightCart wants to measure how well each acquisition channel converts visitors into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.

Requirements

Count total acquired users per channel from the users table.
Count converted users per channel, where a user is considered converted if they have at least one completed order.
Return acquisition_channel, total_users, converted_users, and conversion_rate_pct rounded to 2 decimal places.
Include channels with zero conversions, and sort by conversion rate descending, then channel name ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	User signup date

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status such as completed or cancelled
order_amount	DECIMAL(10,2)	Order value

Sample Data

`users`

user_id	acquisition_channel	signup_date
3	Referral	2024-01-12
1	Paid Search	2024-01-05
8	Email	2024-02-10
6	Organic Search	2024-02-01
2	Paid Search	2024-01-08
10	NULL	2024-02-20
5	Social	2024-01-25
4	Social	2024-01-20
7	Organic Search	2024-02-05
9	Affiliate	2024-02-15
11	Email	2024-02-22
12	Referral	2024-02-25

`orders`

order_id	user_id	order_date	order_status	order_amount
105	5	2024-02-03	pending	40.00
101	1	2024-01-10	completed	120.00
109	9	2024-02-18	completed	95.00
103	2	2024-01-18	cancelled	60.00
111	11	2024-02-25	completed	70.00
107	7	2024-02-09	completed	85.00
112	12	2024-02-28	refunded	55.00
102	1	2024-01-15	completed	80.00
106	6	2024-02-06	completed	150.00
104	4	2024-01-28	completed	200.00
108	8	2024-02-12	cancelled	30.00
110	99	2024-02-20	completed	110.00

Expected Output

acquisition_channel	total_users	converted_users	conversion_rate_pct
Affiliate	1	1	100.00
Organic Search	2	2	100.00
Email	2	1	50.00
Paid Search	2	1	50.00
Social	2	1	50.00
Referral	2	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	Date the user signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Lifecycle status of the order
order_amount	DECIMAL(10,2)	Monetary value of the order

Sample Data

Example 1

Input

{"users":[["3","Referral","2024-01-12"],["1","Paid Search","2024-01-05"],["8","Email","2024-02-10"],["6","Organic Search","2024-02-01"],["2","Paid Search","2024-01-08"],["10",null,"2024-02-20"],["5","Social","2024-01-25"],["4","Social","2024-01-20"],["7","Organic Search","2024-02-05"],["9","Affiliate","2024-02-15"],["11","Email","2024-02-22"],["12","Referral","2024-02-25"],["13","Email","2024-02-16"],["14","Email","2024-02-19"],["15","Paid Search","2024-02-19"],["16","Email","2024-02-10"],["17",

Output

[["Affiliate","1","1","100.00"],["Organic Search","3","2","66.67"],["Social","2","1","50.00"],["Paid Search","3","1","33.33"],["Email","5","1","20.00"],["Direct","2","0","0.00"],["Partnerships","1","0","0.00"],["Podcast","3","0","0.00"],["Referral","2","0","0.00"],["SMS","2","0","0.00"]]

Problem

Task

BrightCart wants to measure how well each acquisition channel converts visitors into paying customers. Write a SQL query to calculate the conversion rate by acquisition channel.

Requirements

Count total acquired users per channel from the users table.
Count converted users per channel, where a user is considered converted if they have at least one completed order.
Return acquisition_channel, total_users, converted_users, and conversion_rate_pct rounded to 2 decimal places.
Include channels with zero conversions, and sort by conversion rate descending, then channel name ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	User signup date

`orders`

column	type	description
order_id	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Order date
order_status	VARCHAR(20)	Order status such as completed or cancelled
order_amount	DECIMAL(10,2)	Order value

Sample Data

`users`

user_id	acquisition_channel	signup_date
3	Referral	2024-01-12
1	Paid Search	2024-01-05
8	Email	2024-02-10
6	Organic Search	2024-02-01
2	Paid Search	2024-01-08
10	NULL	2024-02-20
5	Social	2024-01-25
4	Social	2024-01-20
7	Organic Search	2024-02-05
9	Affiliate	2024-02-15
11	Email	2024-02-22
12	Referral	2024-02-25

`orders`

order_id	user_id	order_date	order_status	order_amount
105	5	2024-02-03	pending	40.00
101	1	2024-01-10	completed	120.00
109	9	2024-02-18	completed	95.00
103	2	2024-01-18	cancelled	60.00
111	11	2024-02-25	completed	70.00
107	7	2024-02-09	completed	85.00
112	12	2024-02-28	refunded	55.00
102	1	2024-01-15	completed	80.00
106	6	2024-02-06	completed	150.00
104	4	2024-01-28	completed	200.00
108	8	2024-02-12	cancelled	30.00
110	99	2024-02-20	completed	110.00

Expected Output

acquisition_channel	total_users	converted_users	conversion_rate_pct
Affiliate	1	1	100.00
Organic Search	2	2	100.00
Email	2	1	50.00
Paid Search	2	1	50.00
Social	2	1	50.00
Referral	2	0	0.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
acquisition_channel	VARCHAR(50)	Marketing channel that acquired the user
signup_date	DATE	Date the user signed up

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Lifecycle status of the order
order_amount	DECIMAL(10,2)	Monetary value of the order

Sample Data

Example 1

Input

{"users":[["3","Referral","2024-01-12"],["1","Paid Search","2024-01-05"],["8","Email","2024-02-10"],["6","Organic Search","2024-02-01"],["2","Paid Search","2024-01-08"],["10",null,"2024-02-20"],["5","Social","2024-01-25"],["4","Social","2024-01-20"],["7","Organic Search","2024-02-05"],["9","Affiliate","2024-02-15"],["11","Email","2024-02-22"],["12","Referral","2024-02-25"],["13","Email","2024-02-16"],["14","Email","2024-02-19"],["15","Paid Search","2024-02-19"],["16","Email","2024-02-10"],["17",

Output

[["Affiliate","1","1","100.00"],["Organic Search","3","2","66.67"],["Social","2","1","50.00"],["Paid Search","3","1","33.33"],["Email","5","1","20.00"],["Direct","2","0","0.00"],["Partnerships","1","0","0.00"],["Podcast","3","0","0.00"],["Referral","2","0","0.00"],["SMS","2","0","0.00"]]

Practice SQL

PostgreSQL

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

Up next

Conversion Rate by Acquisition ChannelMedium

Conversion Rate by Channel SegmentMedium

Channel Conversion Rate CalculationEasy

Next question