Attribution & Conversion Measurement Questions

WITH purchases AS (
  SELECT
    e.event_id,
    e.user_id,
    e.event_time,
    s.session_id,
    s.session_start,
    s.campaign AS session_campaign
  FROM events e
  JOIN sessions s USING (session_id)
  WHERE e.event_name = 'purchase'
),
lookback AS (
  SELECT
    p.*,
    lb.campaign AS last_non_direct_campaign
  FROM purchases p
  LEFT JOIN LATERAL (
    SELECT campaign
    FROM sessions s2
    WHERE s2.user_id = p.user_id
      AND s2.session_start <= p.event_time           -- session occurred on or before purchase
      AND s2.session_start >= p.event_time - INTERVAL '30 days' -- lookback window
      AND s2.campaign IS NOT NULL
      AND LOWER(s2.campaign) <> 'direct'
    ORDER BY s2.session_start DESC
    LIMIT 1
  ) lb ON true
)
SELECT
  event_id,
  user_id,
  event_time,
  session_id,
  -- prefer current session campaign unless it's 'direct' or null, then use last_non_direct_campaign
  CASE
    WHEN session_campaign IS NOT NULL AND LOWER(session_campaign) <> 'direct' THEN session_campaign
    ELSE last_non_direct_campaign
  END AS attributed_campaign
FROM lookback;