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.