Codecademy export

test.sqlite

/*
Here's the first-touch query, in case you need it
*/
/*
WITH first_touch AS (
    SELECT user_id,
        MIN(timestamp) as first_touch_at
    FROM page_visits
    GROUP BY user_id)
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 count(distinct utm_campaign) as distinct_campaigns
from page_visits;

select count(distinct utm_source) as distinct_sources
from page_visits;

select distinct utm_campaign, utm_source
from page_visits;

select distinct page_name
from page_visits;

WITH first_touch AS (
    SELECT user_id,
        MIN(timestamp) as first_touch_at
    FROM page_visits
    GROUP BY user_id)
select pv.utm_campaign, count(*) as first_touches
from first_touch ft
inner join page_visits pv on ft.user_id = pv.user_id
                          and ft.first_touch_at = pv.timestamp
group by pv.utm_campaign;

with last_touch as (
  select user_id
  ,max(timestamp) as last_touch_at
  from page_visits
  group by user_id
)
select pv.utm_campaign, count(*) as last_touches
from last_touch lt
inner join page_visits pv on lt.user_id = pv.user_id
                          and lt.last_touch_at = pv.timestamp
group by pv.utm_campaign;

select count(distinct user_id) as distinct_user_purchases
from page_visits
where page_name = '4 - purchase' ;

with last_touch as (
  select user_id
  ,max(timestamp) as last_touch_at
  from page_visits
  group by user_id
)
select pv.utm_campaign, count(*) as purchases
from last_touch lt
inner join page_visits pv on lt.user_id = pv.user_id
                          and lt.last_touch_at = pv.timestamp
where pv.page_name = '4 - purchase'
group by pv.utm_campaign;

--Q 7.
--each user only has 1 purchase max in this dataset

with purchases as (
  select utm_campaign
  ,count(*) as purchases
  from page_visits
  where page_name = '4 - purchase'
  group by utm_campaign
), first_touch AS (
  select user_id
  ,min(timestamp) as first_touch_at
  from page_visits
  group by user_id
), first_touch_agg as (
  select pv.utm_campaign
  ,count(*) as first_touches
  from page_visits pv
  inner join first_touch ft on pv.user_id = ft.user_id
                            and pv.timestamp = ft.first_touch_at 
  group by utm_campaign
)
select pv.utm_campaign, ft.first_touches, p.purchases
from page_visits pv
left join first_touch_agg ft on pv.utm_campaign = ft.utm_campaign
left join purchases p on pv.utm_campaign = p.utm_campaign
group by pv.utm_campaign;

--Q 7. alternate answer, this each distinct channel a customer traveled
--on, and allows analyst to understand how each first_touch campaign
--relates to purchases down the line
with purchases as (
  select user_id
  ,utm_campaign as  purch_campaign
  from page_visits
  where page_name = '4 - purchase'
), first_touch AS (
  select user_id
  ,min(timestamp) as first_touch_at
  from page_visits
  group by user_id
), first_touch_campaign as (
  select pv.user_id
  ,pv.utm_campaign as first_campaign
  from page_visits pv
  inner join first_touch ft on pv.user_id = ft.user_id
                            and pv.timestamp = ft.first_touch_at
), unique_channels as (
 select distinct pv.user_id, ftc.first_campaign, p.purch_campaign
 from page_visits pv
 left join first_touch_campaign ftc on pv.user_id = ftc.user_id
 left join purchases p on pv.user_id = p.user_id
)

select first_campaign, purch_campaign, count(*) as frequency
from unique_channels
--where purch_campaign is not null; 
--^you can add/remove this based on whether you want to see how many times this channel does not end in a purchase
group by first_campaign, purch_campaign
order by first_campaign, count(*) desc;

--Based on the alternate answer above I would keep:
--getting-to-know-cool-tshirts, interview-with-cool-tshirts-founder, and ten-crazy-cool-tshirts-facts, retargetting-ad and weekly-newletter. Together, these campaigns result in the highest number of purchases.