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;
Select utm_source, count(DISTINCT(utm_source))
from page_visits
group by utm_source;
select DISTINCT utm_campaign, utm_source
from page_visits;
–2--------------------------------------------
select DISTINCT(page_name)
from page_visits;
–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:
–5---------------------------------------------
SELECT
count (*)
from page_visits
where page_name = ‘4 - purchase’;
–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;
–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.
