The SQL course includes a project on marketing attribution using utm_parameters. For curiosity sake I tried building a usage funnel from this data. Using LEFT JOINs to take advantage of NULL values I was able to track conversion and segment it by utm_source. The only thing is it only tracks conversion of users who started on the landing page and eventually made it to purchase page, not the users who started at the checkout because a facebook ad sent them directly there. For this reason the utm_sources that don’t also direct to the landing page are omitted from my results.
I’d really appreciate feedback on how I could refine my code to include users who started on the pages after the landing page. I could write 4 separate queries that each start at page 1, page 2, page 3, and page 4 respectively, but I’m wondering if there is a better solution.
-- Measure conversion from page to page -- Can GROUP BY campaign or source WITH page_one AS ( SELECT * FROM page_visits WHERE page_name = '1 - landing_page' ), page_two AS ( SELECT * FROM page_visits WHERE page_name = '2 - shopping_cart' ), page_three AS ( SELECT * FROM page_visits WHERE page_name = '3 - checkout' ), page_four AS ( SELECT * FROM page_visits WHERE page_name = '4 - purchase' ), funnel_attr AS ( SELECT DISTINCT p1.utm_source, -- p1.utm_campaign, p1.user_id, -- p2.user_id, CASE WHEN p2.user_id IS NOT NULL THEN 1 ELSE 0 END as added_to_cart, -- p3.user_id, CASE WHEN p3.user_id IS NOT NULL THEN 1 ELSE 0 END as went_to_checkout, -- p4.user_id, CASE WHEN p4.user_id IS NOT NULL THEN 1 ELSE 0 END as made_purchase FROM page_one p1 LEFT JOIN page_two p2 ON p2.user_id = p1.user_id LEFT JOIN page_three p3 ON p3.user_id = p1.user_id LEFT JOIN page_four p4 ON p4.user_id = p1.user_id LIMIT 200) -- Unsegmented conversion rates -- SELECT COUNT(DISTINCT user_id), SUM(added_to_cart), SUM(went_to_checkout), SUM(made_purchase) FROM funnel_attr; -- Conversion rates segmented by utm_source -- to segment by utm_campaign adjust above -- below results don't include email or fb -- because neither lead to p1 SELECT utm_source, -- utm_campaign, COUNT(DISTINCT user_id) as total_starting_p1, SUM(added_to_cart) as added_to_bag, SUM(went_to_checkout) as started_checkout, SUM(made_purchase) as made_purchase FROM funnel_attr GROUP BY 1 ORDER BY 2 DESC;