Project Feedback for ANALYZE REAL DATA WITH SQL Attribution Queries

Hello Everyone!

Part of my assignment is to post my code online. I would love receive feedback on my code and see what could have been done to make it much easier on my part.

/* Q1. 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;

/* Q2. What pages are on the CoolTShirts website?*/
select distinct page_name
from page_visits
where utm_campaign like '%Cool%TShirts%';

/*Q3. 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;

/* Q5. How many visitors make a purchase? */

SELECT page_name, count(distinct user_id)
from page_visits
where page_name = '4 - purchase'
group by 1;

/* Q4. How many last touches is each campaign responsible for?
Q6. How many last touches on the purchase page is each campaign responsible for?
Q7. CoolTShirts can re-invest in 5 campaigns. Given your findings in the project, which should they pick and why?*/ 

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 lt_attr.utm_source,
lt_attr.utm_campaign,
count(*)
from lt_attr
group by 1,2
order by 3 desc
LIMIT 5;

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.