Marketing Attribution Project - For Peer Review

Hi everyone! Looking for some peer review on a completed project!

Finally came to the end of the Analyze Data w/ SQL path and this was a fun and challenging project to cap it all off with!

–1---------------------------------------------
Select utm_campaign, count(DISTINCT(utm_campaign))
from page_visits
group by utm_campaign;
1

Select utm_source, count(DISTINCT(utm_source))
from page_visits
group by utm_source;
1-b

select DISTINCT utm_campaign, utm_source
from page_visits;
1-c

–2--------------------------------------------
select DISTINCT(page_name)
from page_visits;
2

–3--------------------------------------------
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 AS ft
JOIN page_visits AS 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;

–4--------------------------------------------
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 as lt
JOIN page_visits as 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;

Vis covers 3&4:
3 & 4

–5---------------------------------------------
SELECT
count (*)
from page_visits
where page_name = ‘4 - purchase’;
5

–6---------------------------------------------
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 as lt
JOIN page_visits as 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;
6

–7----------------------------------------------
–Due to the fact that the bottom 4 campaigns did not generate many purchases, I would renew the top 4 campaigns (in terms of purchases):
– weekly-newsletter (email)
–retargetting-ad (facebook)
–retargetting-campaign (email)
–paid-search (google)

–and, instead of a 5th, take the funds from the bottom four and invest more into the paid-search (google) option or the retargetting-ad for Facebook.
7

1 Like

Hi!
I also recently completed this project (Questions about my Marketing Attribution Project - CoolTShirts), and I’m glad to see how others did it here on the forum. :taco:

I like how you embedded the result tables in your post – how did you do that? I used the three backticks around code, but didn’t know how to format the tables.

I like how you coded for questions 3 and 4. I see where I can improve mine (I included two unnecessary columns, and grouped results in a strange way for some reason). Can you explain why we need the second ‘with’ statement in these two queries (ft_att… and lt_att…)? This confused me in the lesson and still confuses me in this project. I added the utm_source and utm_campaign columns by joining the tables within my SELECT query, and it seems to produce the same results. But if there’s a reason why joining these tables temporarily before selecting from them is better, I’d love to understand that.

Lastly, I like that we came to different conclusions in the final question, and I can see how your conclusion is supported. If I wanted to spend more time on this project, I’d like to create a usage funnel to determine if certain combinations of campaigns led to more purchases. I noticed that none of the users in this data set started their CoolTShirts journey with the campaigns that led to the most purchases. Will first touches from certain campaigns lead to more return visits later on? Lots to think about…

Congrats on a job well done, and thank you for sharing your project!

Please can you answer the following questions:

  1. Do you agree with my findings in the following 14 separate JPEG files?
  2. Is there a more efficient way to query this data?