Marketing Attribution

/*
Here’s the first-touch query, in case you need it
*/

–How many campaigns and sources does CoolTShirts use? Which source is used for each campaign?
select count(distinct utm_campaign)
from page_visits ;

select count(distinct utm_source)
from page_visits ;

Select distinct utm_campaign, utm_source
from page_visits;

–What pages are on the CoolTShirts website?
select distinct page_name
from page_visits;
where utm_campaign like ‘%Cool%TShirts%’;

–How many first touches is each campaign responsible for?
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) as first_touch_at
FROM page_visits
GROUP BY user_id),
ft_attr as (SELECT ft.user_id,
ft.first_touch_at,
pv.utm_source,
pv.utm_campaign
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp)
SELECT ft_attr.utm_source,
ft_attr.utm_campaign,
COUNT(*)
FROM ft_attr
GROUP BY 1, 2
ORDER BY 3 DESC;

–How many last touches is each campaign responsible for?
WITH last_touch AS (
SELECT user_id,
Max(timestamp) as last_touch_at
FROM page_visits
GROUP BY user_id),
lt_attr AS (
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
)
SELECT utm_campaign,
COUNT(last_touch_at)
FROM lt_attr
GROUP BY 1
order by 2 desc;

–How many visitors make a purchase?
SELECT page_name, count(distinct user_id)
from page_visits
where page_name = ‘4 - purchase’
group by 1;

–How many last touches on the purchase page is each campaign responsible for?
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),
lt_attr AS (
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
)
SELECT page_name, count(distinct user_id)
from page_visits
where page_name = ‘4 - purchase’
group by 1
order by 3 desc;

–CoolTShirts can re-invest in 5 campaigns. Given your findings in the project, which should they pick and why?
SELECT utm_campaign,
COUNT(last_touch_at)
FROM lt_attr
GROUP BY 1
order by 2 desc
limit 5;