Find Conversion Rates
Medium
You are given the following tables:
Sample Data
attribution table:
user_sessions table:
Examples
Output: marketing_channel | avg_purchase_value | conversion_rate ------------------|--------------------|----------------- paid_search | 60.00 | 50.00% email | 46.66 | 66.67% social_media | 17.50 | 50.00%
Explanation:
emailhad 3 sessions. 2 resulted in a purchase (s001, s007). Conversion rate = 2/3 = 66.67%. Avg purchase value = (49.99 + 0 + 89.99) / 3 = 46.66paid_searchhad 2 sessions. 1 resulted in a purchase (s004). Conversion rate = 1/2 = 50.00%. Avg purchase value = (120.00 + 0) / 2 = 60.00social_mediahad 2 sessions. 1 resulted in a purchase (s005). Conversion rate = 1/2 = 50.00%. Avg purchase value = (35.00 + 0) / 2 = 17.50
Results are ordered by descending conversion rate.
Find what percentage of link clicks convert to a purchase for each marketing channel and arrange them in decreasing conversion rate. Your output should have the following columns: marketing_channel, avg_purchase_value, conversion_rate
SELECT
marketing_channel,
AVG(purchase_value) AS avg_purchase_value,
AVG(CASE WHEN purchase_value > 0 THEN 1 ELSE 0 END) AS conversion_rate
FROM attribution
GROUP BY marketing_channel
ORDER BY AVG(CASE WHEN purchase_value > 0 THEN 1 ELSE 0 END) DESC;
-- Write your query here select marketing_channel, avg(purchase_value) as avg_purchase_value, avg(case when purchase_value > 0 then 1 else 0 end) as conversion_rate from attribution group by 1 order by 3 desc