Build and Analyze User Funnels

This skill builds conversion-tracking funnel analysis systems - sequential event modeling, cohort analysis, and multi-step user journey optimization - that translate business requirements into robust analytical frameworks. Funnel steps are defined with specific event triggers and success criteria, sequenced with time-based constraints, and designed to handle parallel paths, alternative conversion routes, and multi-session user re-entry, distinguishing strict sequential funnels from more flexible path analysis. The underlying data architecture requires a consistent event schema (stable user identifiers and timestamps), proper sessionization and identity resolution, cross-device tracking with anonymous-to-identified transitions, and data-quality validation and deduplication.

Use it when building funnel or conversion analysis that needs real time-window logic and cohort tracking, not a simple one-off conversion-rate count. It is not meant to run on unvalidated event data: the data architecture principles require deduplication and identity resolution up front, since a funnel built on dirty event data produces misleading conversion rates regardless of how sophisticated the query is.

Given a defined event schema, it produces SQL funnel queries (a multi-step funnel pivoting each user's first occurrence of each event into columns and computing step-to-step conversion rates, and a time-windowed variant that only counts a step as valid if it occurred within a configured window of the prior step - for example, within 1 hour of landing, 30 minutes of a product view, or 24 hours of adding to cart), a cohort-based funnel builder (grouping users by a cohort date, tracking per-step conversion counts, and computing the median hours-to-conversion for each step), and a real-time funnel monitor (computing rolling-window conversion rates between adjacent steps and firing an alert when a rate drops below a configured threshold).

-- Multi-step funnel with conversion rates
WITH funnel_events AS (
  SELECT 
    user_id,
    event_name,
    event_time,
    ROW_NUMBER() OVER (PARTITION BY user_id, event_name ORDER BY event_time) as event_rank
  FROM events 
  WHERE event_time >= '2024-01-01'
    AND event_name IN ('page_view', 'signup_start', 'signup_complete', 'purchase')
),
funnel_steps AS (
  SELECT 
    user_id,
    MAX(CASE WHEN event_name = 'page_view' THEN event_time END) as step_1,
    MAX(CASE WHEN event_name = 'signup_start' THEN event_time END) as step_2,
    MAX(CASE WHEN event_name = 'signup_complete' THEN event_time END) as step_3,
    MAX(CASE WHEN event_name = 'purchase' THEN event_time END) as step_4
  FROM funnel_events 
  WHERE event_rank = 1  -- First occurrence only
  GROUP BY user_id
)
SELECT 
  COUNT(DISTINCT user_id) as total_users,
  COUNT(DISTINCT CASE WHEN step_1 IS NOT NULL THEN user_id END) as step_1_users,
  COUNT(DISTINCT CASE WHEN step_2 IS NOT NULL AND step_2 > step_1 THEN user_id END) as step_2_users,
  COUNT(DISTINCT CASE WHEN step_3 IS NOT NULL AND step_3 > step_2 THEN user_id END) as step_3_users,
  COUNT(DISTINCT CASE WHEN step_4 IS NOT NULL AND step_4 > step_3 THEN user_id END) as step_4_users,
  ROUND(COUNT(DISTINCT CASE WHEN step_2 IS NOT NULL AND step_2 > step_1 THEN user_id END) * 100.0 / 
        NULLIF(COUNT(DISTINCT CASE WHEN step_1 IS NOT NULL THEN user_id END), 0), 2) as step_1_to_2_rate
FROM funnel_steps;

Dashboards built on top support drill-down segment analysis, time-range selectors and cohort comparisons, drop-off analysis that identifies specific failure points, statistical significance testing for A/B funnel comparisons, and dynamic step and event filtering - tracking KPIs like overall and step-wise conversion, time-to-conversion distributions and medians, cohort trends and seasonality, segment differences by traffic source, device, and geography, and revenue or lifetime-value correlation.

Product and marketing analytics teams building conversion-tracking systems who also need it to perform at scale - proper indexing on user_id, event_time, and event_name, incremental processing and pre-aggregated tables for large datasets, efficient window-function usage, time-based table partitioning, real-time streaming with late-arriving-data handling, horizontal scaling, and caching for frequently accessed funnel reports.