Skill path - SQL - Marketing attribution project

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

Hi Ginnie,

Your code looks very similar to mine!
Taking a look at your first touch query, I have a few notes. Same issues are with the second touch query

Your query has some extra parenthesis and dashes. I’m not sure if it is a formatting issue when you copy and paste, or if you are putting it into the query editor.
For example, in your first touch query, there are extra dashes and parenthesis.

You forgot to join your table on specific columns, so when I run it, nothing comes up.

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;

You could format it like this instead, which is in line with what Codecademy has been teaching us:

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;