sql
WITH events AS (
SELECT
user_id,
session_id,
event_time,
event_type, -- 'view' | 'add_to_cart' | 'purchase'
channel
FROM raw_events
WHERE event_type IN ('view','add_to_cart','purchase')
),
-- assign an incremental funnel_instance per user-session by counting views
with_funnel AS (
SELECT
*,
SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) OVER (
PARTITION BY user_id, session_id
ORDER BY event_time, event_type
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS funnel_instance
FROM events
),
-- keep only events that belong to a funnel_instance >=1 (ignore actions before first view)
funnel_events AS (
SELECT *
FROM with_funnel
WHERE funnel_instance >= 1
),
-- dedupe: for each user/session/funnel_instance/event_type keep earliest event_time and channel
dedup AS (
SELECT
user_id, session_id, funnel_instance, event_type,
MIN(event_time) AS ts,
ANY_VALUE(channel) AS channel -- BigQuery: ANY_VALUE; in Postgres use MAX(channel) if consistent
FROM funnel_events
GROUP BY user_id, session_id, funnel_instance, event_type
),
-- pivot to get the first timestamps in order: view_ts, add_ts (must be after view), purchase_ts (after add)
pivoted AS (
SELECT
d_view.user_id,
d_view.session_id,
d_view.funnel_instance,
d_view.ts AS view_ts,
d_view.channel AS funnel_channel,
-- first add_to_cart after view: join to ensure ordering
MIN(d_add.ts) AS add_ts,
-- first purchase after that add: will compute later
NULL::timestamp AS purchase_ts -- placeholder
FROM dedup d_view
LEFT JOIN dedup d_add
ON d_add.user_id = d_view.user_id
AND d_add.session_id = d_view.session_id
AND d_add.funnel_instance = d_view.funnel_instance
AND d_add.event_type = 'add_to_cart'
AND d_add.ts >= d_view.ts
WHERE d_view.event_type = 'view'
GROUP BY d_view.user_id, d_view.session_id, d_view.funnel_instance, d_view.ts, d_view.channel
),
-- attach purchase which must be after add_ts (if add exists), otherwise after view_ts (but requirement says purchase counts only if prior add & view)
with_purchase AS (
SELECT
p.*,
MIN(d_pur.ts) AS purchase_ts
FROM pivoted p
LEFT JOIN dedup d_pur
ON d_pur.user_id = p.user_id
AND d_pur.session_id = p.session_id
AND d_pur.funnel_instance = p.funnel_instance
AND d_pur.event_type = 'purchase'
-- require purchase after add (if add exists), else this will be NULL and we won't count
AND (p.add_ts IS NOT NULL AND d_pur.ts >= p.add_ts)
GROUP BY p.user_id, p.session_id, p.funnel_instance, p.view_ts, p.funnel_channel, p.add_ts
),
-- compute funnel step flags per instance
funnel_flags AS (
SELECT
user_id, session_id, funnel_instance, funnel_channel,
view_ts IS NOT NULL AS viewed,
add_ts IS NOT NULL AS added,
purchase_ts IS NOT NULL AS purchased
FROM with_purchase
)
-- final aggregation: counts by channel and totals and conversion rates
SELECT
funnel_channel AS channel,
COUNT(*) FILTER (WHERE viewed) AS views,
COUNT(*) FILTER (WHERE added) AS adds,
COUNT(*) FILTER (WHERE purchased) AS purchases,
ROUND(100.0 * COUNT(*) FILTER (WHERE added) / NULLIF(COUNT(*) FILTER (WHERE viewed),0),2) AS add_rate_pct,
ROUND(100.0 * COUNT(*) FILTER (WHERE purchased) / NULLIF(COUNT(*) FILTER (WHERE added),0),2) AS purchase_rate_from_add_pct,
ROUND(100.0 * COUNT(*) FILTER (WHERE purchased) / NULLIF(COUNT(*) FILTER (WHERE viewed),0),2) AS purchase_rate_from_view_pct
FROM funnel_flags
GROUP BY funnel_channel
ORDER BY views DESC;
