snowplow_conversion_rate.sql

WITH events AS (
  SELECT domain_userid,
         network_userid,
         collector_tstamp,
         page_urlpath,
         dvce_type,
         user_id,
         LAG(collector_tstamp, 1) OVER (PARTITION BY domain_userid ORDER BY collector_tstamp) AS last_event
    FROM snowplow_atomic.events
   WHERE event = 'page_view'
     AND page_urlhost = 'YOUR_HOST'
),
final AS (
  SELECT *,
         CASE WHEN DATE_PART('EPOCH', collector_tstamp) - DATE_PART('EPOCH', last_event) >= 3600 OR last_event IS NULL THEN 1 ELSE 0 END AS is_new_session
    FROM events
),
sessions_events AS (
  SELECT *,
         SUM(is_new_session) OVER (PARTITION BY domain_userid ORDER BY collector_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS user_session_id
    FROM final
),
sessions AS (
  SELECT DISTINCT
         domain_userid,
         user_session_id,
         MIN(collector_tstamp) OVER(PARTITION BY domain_userid, user_session_id) started_at,
         MAX(collector_tstamp) OVER(PARTITION BY domain_userid, user_session_id) ended_at,
         FIRST_VALUE(page_urlpath) OVER(PARTITION BY domain_userid, user_session_id ORDER BY collector_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) landing_page,
         LAST_VALUE(page_urlpath) OVER(PARTITION BY domain_userid, user_session_id ORDER BY collector_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bouncing_page,
         CASE WHEN MAX(user_id) OVER(PARTITION BY domain_userid, user_session_id) IS NULL THEN 0 ELSE 1 END identified,
         SUM(CASE WHEN page_urlpath ILIKE '%WELCOME_PAGE%' THEN 1 ELSE 0 END) OVER(PARTITION BY domain_userid, user_session_id) welcome_num,
         MIN(CASE WHEN page_urlpath ILIKE '%WELCOME_PAGE%' THEN collector_tstamp ELSE NULL END) OVER(PARTITION BY domain_userid, user_session_id) welcome_at,
         MAX(dvce_type) OVER(PARTITION BY domain_userid, user_session_id) device_type
    FROM sessions_events
),
sessions_conv AS (
  SELECT domain_userid,
         user_session_id,
         started_at,
         ended_at,
         device_type,
         CASE WHEN SUM(identified) OVER(PARTITION BY domain_userid ORDER BY user_session_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > 0 OR SUM(welcome_num) OVER(PARTITION BY domain_userid ORDER BY user_session_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > 0 THEN 0 ELSE 1 END not_converted_visitor,
         welcome_num
    FROM sessions
)

  SELECT DATE(started_at) date,
         device_type,
         COUNT(1) sessions,
         SUM(CASE WHEN not_converted_visitor >= 1 AND welcome_num >= 1 THEN 1 ELSE 0 END) conversions,
         CAST(SUM(CASE WHEN not_converted_visitor >= 1 AND welcome_num >= 1 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(1) * 100 conversion_rate
    FROM sessions_conv
GROUP BY 1,2
ORDER BY 1,2