Marketing Channel Attribution
MediumPremium
You are given the following tables:
attribution table:
user_sessions table:
Identify the first touch attribution for all high value customers (based on CLV > 100). In other words, what marketing channel did these customers first encounter the product. Your output should have the following columns: user_id, marketing_channel
WITH high_value AS (SELECT u.user_id, SUM(a.purchase_value) AS CLV
FROM user_sessions AS u
INNER JOIN attribution AS a
ON a.session_id = u.session_id
GROUP BY u.user_id
HAVING SUM(a.purchase_value) > 100
ORDER BY SUM(a.purchase_value) DESC),
first_session AS (
SELECT user_id, MIN(ad_click_timestamp) AS first_touch
FROM user_sessions
GROUP BY user_id)
SELECT f.user_id, a.marketing_channel
FROM first_session AS f
INNER JOIN high_value AS h
ON f.user_id = h.user_id
INNER JOIN user_sessions AS u
ON u.user_id = f.user_id AND u.ad_click_timestamp = f.first_touch
INNER JOIN attribution AS a
ON u.session_id = a.session_id;
select user_id, b.marketing_channel from user_sessions a Left join attribution b on b.session_id = a.session_id group by 1,2 HAVING sum(purchase_value)>100 and min(ad_click_timestamp)