Hi all, I am completely new at this and wanted to throw this out into the community. Any feedback would be appreciated.
β1. How many campaigns and sources does CoolTShirts use?
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 page_visits AS pv
JOIN first_touch AS ft
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
LIMIT 50;
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
FROM page_visits
GROUP BY user_id)
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign
FROM page_visits AS pv
JOIN last_touch AS lt
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
LIMIT 50;
SELECT DISTINCT utm_source AS sources
FROM page_visits;
SELECT DISTINCT utm_campaign AS campaign
FROM page_visits;
β2. What pages are on the CoolTShirt website?
SELECT DISTINCT page_name
FROM page_visits;
β3. How many first touches is each campaign responsible for?
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. How many last touches is each campaign responsible for?
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;
β5. How many visitors make a purchase?
SELECT page_name, COUNT(*)
FROM page_visits
GROUP BY 1;
β6. How many last touches on the purchase page is each campaign responsible for?
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 lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign, COUNT(*)
FROM page_visits AS pv
JOIN last_touch AS lt
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
GROUP BY 4;
SELECT (2+9+7+52+113+54+9+115);
SELECT (361./2000.);
SELECT (1431./2000.);