Are you able to review my SQL code and let me know if this is correct and there are more efficient ways to do?
SELECT COUNT(DISTINCT utm_campaign) AS ‘campaigns’
FROM page_visits;
SELECT COUNT(DISTINCT utm_source) AS ‘campaigns’
FROM page_visits;
SELECT DISTINCT utm_campaign, utm_source
FROM page_visits;
SELECT DISTINCT page_name
FROM page_visits;
First touch
WITH ‘first_touch’ AS (
SELECT user_id, MIN(timestamp) AS ‘-- first_touch_at’
– FROM page_visits
– GROUP BY user_id
– )
– SELECT COUNT (ft.first_touch_at) AS ‘first_touches’, pv.utm_campaign
– FROM first_touch AS ‘ft’
– JOIN page_visits AS ‘pv’
– GROUP BY pv.utm_campaign;
Last touch
WITH ‘last_touch’ AS (
SELECT user_id, MAX(timestamp) AS ‘last_touch_at’
FROM page_visits
GROUP BY user_id
)
SELECT COUNT (lt.last_touch_at) AS ‘last_touches’, pv.utm_campaign
FROM last_touch AS ‘lt’
JOIN page_visits AS ‘pv’
GROUP BY pv.utm_campaign;
Number of user on purchase
SELECT COUNT(DISTINCT user_id) AS ‘users’, page_name
FROM page_visits
GROUP BY page_name;
TOTAL: 361
Last touches on purchase
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
)
SELECT COUNT (lt.last_touch_at) AS ‘last_touches’, pv.utm_campaign
FROM last_touch AS ‘lt’
JOIN page_visits AS ‘pv’
GROUP BY pv.utm_campaign
ORDER BY 1 DESC;
TOP CAMPAIGNS:
getting-to-know-cool-tshirts
ten-crazy-cool-tshirts-facts
interview-with-cool-tshirts-founder
weekly-newsletter
retargetting-ad