Initial Contact Attribution
HardPremium
You are given the following two tables:
attribution table:
session_id(string): The unique identifier for a user session.marketing_channel(string): The marketing channel that led to the session.purchase_value(float): The monetary value of purchases made during the session.
user_sessions table:
session_id(string): The unique identifier for a user session (linked toattributiontable).ad_click_timestamp(datetime): The timestamp when the user clicked on an ad, indicating their entry into the session.user_id(string): The unique identifier for the user associated with the session.
Determine the percentage of high-value customers attributed to each marketing channel based on their initial point of contact. A high-value customer is defined as a user whose total purchase value (across all sessions) exceeds 100. The initial point of contact is the user's first recorded session, based on the ad_click_timestamp.
Your output should include the following columns:
marketing_channel(string): The marketing channel associated with the user's first session.perc_high_value(float): The percentage of high-value customers that originated from each marketing channel, relative to the total number of high-value customers.
Example output:
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
a.marketing_channel,
CAST(COUNT(f.user_id) AS FLOAT) / (SELECT COUNT(*) FROM high_value) AS perc_high_value
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
GROUP BY a.marketing_channel
ORDER BY COUNT(f.user_id) DESC
too many questions for clarification on this to start