Marketing Attribution Project - Analyze Data with SQL Skill Path

Projects
1

Hi everyone,

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.