SQL Attribution Queries: CoolTshirts Project

I don’t understand why, for question 3, only four campaigns appear in the results when there are 8 campaigns. This is the result using the help code provided in the hint section by codecademy:

utm_source utm_campaign COUNT(*)
medium interview-with-cool-tshirts-founder 622
nytimes getting-to-know-cool-tshirts 612
buzzfeed ten-crazy-cool-tshirts-facts 576
google cool-tshirts-search 169

What am I missing?

Thanks!

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-marketing-attribution/projects/analyze-data-sql-attribution-queries

Please post your code so ppl can help.

I had a similar issue too but using (distinct function) resolves the issue as seen here:

select count(distinct utm_campaign)
from page_visits;

If you have the time; please do share feedback on this same project that I completed here

1 Like

Question 1 asks about campaigns and their sources. There are 8 distinct campaigns.

SELECT DISTINCT utm_campaign, utm_source
FROM page_visits
ORDER BY 2;
utm_campaign utm_source
ten-crazy-cool-tshirts-facts buzzfeed
weekly-newsletter email
retargetting-campaign email
retargetting-ad facebook
paid-search google
cool-tshirts-search google
interview-with-cool-tshirts-founder medium
getting-to-know-cool-tshirts nytimes

For question 3 it asks “How many first touches is each campaign responsible for?”
I had trouble figuring it out, so I copy/pasted the hint code from the lesson. It only shows 4 campaigns.
I was expecting 8.

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;
utm_source utm_campaign COUNT(*)
medium interview-with-cool-tshirts-founder 622
nytimes getting-to-know-cool-tshirts 612
buzzfeed ten-crazy-cool-tshirts-facts 576
google cool-tshirts-search 169

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