I wanted to share my Marketing Attribution Project from the Analyze Data with SQL Skill Path. I really enjoy how this path pushes me and encourages me to share my projects. Here is my finalized code:
<-- this first with statement finds all of the first touches with first_touch as ( select user_id, min(timestamp) as first_touch_at from page_visits group by user_id ), -- this second statement brings the two table together into ft_attr so we can use them jointly ft_attr as ( select ft.user_id, ft.first_touch_at, pv.utm_source, pv.utm_campaign from first_touch as 'ft' join page_visits as 'pv' on ft.user_id = pv.user_id and ft.first_touch_at = pv.timestamp ) -- the final statement selects the source, campaign, and counts all columns select ft_attr.utm_source, ft_attr.utm_campaign, count(*) as 'Number of First Touches' -- it groups the values by the source and/or campaign and orders them by how many first touches they have in descending order from ft_attr group by 1, 2 order by 3 desc; -- the first with statement finds all of the last touches by equating them to the maximum timestamp of each user_id with last_touch as ( select user_id, max(timestamp) as last_touch_at from page_visits where page_name = '4 - purchase' group by user_id ), -- the second with statement joins last_touch with page_vists lt_attr as ( select lt.user_id, lt.last_touch_at, pv.utm_source, pv.page_name, pv.utm_campaign from last_touch as 'lt' join page_visits as 'pv' on lt.user_id = pv.user_id and lt.last_touch_at = pv.timestamp ) -- this final statement selects the source, campaign, and counts all of the columns to find the number of last touches select lt_attr.utm_source, lt_attr.utm_campaign, lt_attr.page_name, count(*) as 'Number of Last Touches' from lt_attr group by 1, 2 order by 4 desc; select count(distinct user_id), page_name from page_visits group by page_name;
My findings were that it would benefit CoolTShirts the most to reinvest in five campaigns:: 3 campaigns that focused on first touches, and two campaigns that focused on last touches
Here is a sheet with my tables and findings.
I would appreciate any constructive feedback. I work in Ontology, not marketing, and have no desire to change career paths. Learning to manipulate data and learn how to use databases to support my conclusions is something I am sure will come in handy one day.