Funnel Analysis

Funnel Analysis for Product Analysts

Funnel analysis tracks how users progress through a sequence of key steps (for example: Landing → Sign up → Onboarding → Activation). It reveals where people drop off, how long it takes to convert, which segments perform better, and what to improve. For Product Analysts, funnels turn raw events into clear opportunities for growth and product quality.

Who this is for

• Product Analysts and Growth Analysts who need to quantify friction and prioritize fixes.
• PMs and Designers who want evidence-backed UX decisions.
• Engineers and Data Engineers validating event instrumentation quality.

Prerequisites

• Basic SQL (SELECT, WHERE, GROUP BY, JOIN, window functions helpful).
• Understanding of event-based analytics (users, sessions, timestamps, event properties).
• Clear product steps or hypotheses to measure.

Why this skill matters in Product Analytics

• Prioritization: Funnels quantify the business impact of each step’s drop-off.
• UX quality: Time-to-convert and step-level failures spotlight usability issues.
• Growth: Segmented funnels identify high-ROI channels and segments.
• Experimentation: Before/after comparisons validate product changes.

What you'll be able to do

• Define clean funnel steps tied to product goals.
• Build user- and session-level funnels from raw events.
• Measure conversion, drop-off, and time-to-convert.
• Segment funnels by channel, device, cohort, or plan.
• Compare funnels before/after a change and recommend improvements.

Learning path

1. Define the funnel
   Write step definitions aligned to user value (activation, checkout, onboarding). Clarify events and the valid window between steps.
2. Query a basic user funnel
   Build a minimal SQL funnel from raw events. Get step counts and overall conversion.
3. Measure drop-off and time
   Compute step-to-step conversion, drop-off, and time-to-convert percentiles.
4. Segment and compare
   Slice by channel, device, plan, or cohort. Compare before/after a release.
5. Recommend improvements
   Translate findings into prioritized product changes and a simple monitoring loop.

Worked examples

Data model used below (generic): events(user_id, session_id, event_name, event_time, properties, channel, device, plan).

1) Basic user funnel counts

Funnel: landing_view → signup_submit → signup_success.

-- Per user, first time they hit each step within 7 days of first step
WITH first_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'landing_view' THEN event_time END) AS t1,
    MIN(CASE WHEN event_name = 'signup_submit' THEN event_time END) AS t2,
    MIN(CASE WHEN event_name = 'signup_success' THEN event_time END) AS t3
  FROM events
  GROUP BY user_id
), valid AS (
  SELECT * FROM first_steps
  WHERE t1 IS NOT NULL
    AND (t2 IS NULL OR t2 >= t1)
    AND (t3 IS NULL OR t3 >= t2)
    AND (t2 IS NULL OR TIMESTAMP_DIFF(t2, t1, DAY) <= 7)
    AND (t3 IS NULL OR TIMESTAMP_DIFF(t3, t1, DAY) <= 7)
)
SELECT
  COUNT(*) AS step1_users,
  COUNTIF(t2 IS NOT NULL) AS step2_users,
  COUNTIF(t3 IS NOT NULL) AS step3_users,
  SAFE_DIVIDE(COUNTIF(t3 IS NOT NULL), COUNT(*)) AS overall_conversion
FROM valid;

2) User vs session funnels

Session-based funnels answer questions like “What fraction of sessions that started checkout ended in purchase?”

-- Session funnel: product_view → add_to_cart → purchase within the same session
WITH s AS (
  SELECT
    session_id,
    MIN(CASE WHEN event_name = 'product_view' THEN event_time END) AS v,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS a,
    MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS p
  FROM events
  GROUP BY session_id
)
SELECT
  COUNT(*) AS step1_sessions,
  COUNTIF(a IS NOT NULL AND a >= v) AS step2_sessions,
  COUNTIF(p IS NOT NULL AND p >= a) AS step3_sessions,
  SAFE_DIVIDE(COUNTIF(p IS NOT NULL AND p >= a), COUNT(*)) AS overall_session_conversion
FROM s;

3) Drop-off by step, with reasons

Estimate where people abandon and tag common reasons from properties.

-- Compute step-to-step conversion and tag a common failure reason
WITH f AS (
  SELECT user_id,
    MIN(IF(event_name='onboarding_start', event_time, NULL)) AS s,
    MIN(IF(event_name='onboarding_form_submit', event_time, NULL)) AS fs,
    MIN(IF(event_name='activation', event_time, NULL)) AS act,
    -- Example property: validation_error captured on form events
    ANY_VALUE(IF(event_name='onboarding_form_submit', properties.validation_error, NULL)) AS form_error
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(*) AS start_users,
  COUNTIF(fs IS NOT NULL) AS form_submit_users,
  COUNTIF(act IS NOT NULL) AS activated_users,
  SAFE_DIVIDE(COUNTIF(fs IS NOT NULL), COUNT(*)) AS s_to_fs_conv,
  SAFE_DIVIDE(COUNTIF(act IS NOT NULL), COUNTIF(fs IS NOT NULL)) AS fs_to_act_conv,
  form_error,
  COUNTIF(fs IS NULL AND form_error IS NOT NULL) AS drop_with_error
FROM f
GROUP BY form_error
ORDER BY drop_with_error DESC;

Interpretation: Focus first on the highest-frequency errors to reduce form drop-off.

4) Time to convert (p50/p90)

-- Time from first product_view to purchase per user
WITH t AS (
  SELECT user_id,
    MIN(IF(event_name='product_view', event_time, NULL)) AS v,
    MIN(IF(event_name='purchase', event_time, NULL)) AS p
  FROM events
  GROUP BY user_id
), d AS (
  SELECT
    user_id,
    TIMESTAMP_DIFF(p, v, MINUTE) AS minutes_to_purchase
  FROM t
  WHERE v IS NOT NULL AND p IS NOT NULL AND p >= v
)
SELECT
  APPROX_QUANTILES(minutes_to_purchase, 100)[OFFSET(50)] AS p50_minutes,
  APPROX_QUANTILES(minutes_to_purchase, 100)[OFFSET(90)] AS p90_minutes
FROM d;

5) Segmented funnel and before/after

-- Compare conversion by channel and by release date
DECLARE release_ts TIMESTAMP DEFAULT TIMESTAMP('2025-01-15 00:00:00');
WITH u AS (
  SELECT user_id, channel,
    MIN(IF(event_name='landing_view', event_time, NULL)) AS lv,
    MIN(IF(event_name='signup_success', event_time, NULL)) AS su
  FROM events
  GROUP BY user_id, channel
), cohort AS (
  SELECT *, IF(lv < release_ts, 'before', 'after') AS period FROM u
  WHERE lv IS NOT NULL
)
SELECT
  period,
  channel,
  COUNT(*) AS users_at_start,
  COUNTIF(su IS NOT NULL AND su >= lv) AS users_converted,
  SAFE_DIVIDE(COUNTIF(su IS NOT NULL AND su >= lv), COUNT(*)) AS conversion
FROM cohort
GROUP BY period, channel
ORDER BY period, conversion DESC;

Interpretation: Identify channels that improved or regressed after the release and investigate.

Drills and quick exercises

• Define a 3–5 step activation funnel for your product. Write down each event and its success criteria.
• Build a user-level funnel query that returns step counts and overall conversion.
• Add step-to-step rates and time-to-convert metrics (p50/p90).
• Segment by at least two dimensions (channel, device, plan).
• Do a simple before/after comparison around a release date; summarize the result in 3 bullet points.

Common mistakes and debugging tips

• Mixed identifiers: Using both user and session IDs in one funnel can double-count. Pick one per analysis.
• Out-of-order events: Ensure step N+1 occurs after step N. Filter or reorder by timestamp.
• Unlimited window: Users may convert weeks later. Set a reasonable window (e.g., 7 or 14 days) to reflect intent.
• Leaky step definitions: Vague event names inflate counts. Use precise events and properties.
• Unsegmented conclusions: Averages hide problems. Always check key segments.
• Forgetting data quality: Check event volume spikes, missing properties, and timestamp timezone consistency.

Mini project: Improve activation funnel

Goal: Find the biggest friction in activation and propose fixes.

1. Define a 4-step activation funnel (start, key action, milestone, activation).
2. Compute step counts, step-to-step conversion, and overall conversion.
3. Measure p50/p90 time between each step.
4. Segment by channel and device; identify the worst-performing segment.
5. Run a before/after comparison around a recent UI change.
6. Deliverables:
   • One slide with funnel diagram and metrics.
   • One slide with top 3 issues and impact estimate.
   • One slide with 2–3 prioritized product recommendations.

Next steps

• Instrument missing events for ambiguous steps.
• Automate weekly funnel monitoring and alert on step conversion deltas.
• Partner with PM/Design to test one high-impact improvement.

Subskills

Each subskill below is a short, focused lesson with examples and tasks.

• Funnel Step Definition — Craft clear, measurable steps that map to user value.
• User Versus Session Funnels — Choose the right unit of analysis and avoid double counting.
• Drop Off Analysis — Locate friction and quantify why users abandon.
• Time To Convert Measurement — Use percentiles to measure speed and latency.
• Funnel Segmentation — Compare performance across channels, devices, and plans.
• Activation Funnel Analysis — Prove users reached first value, not just sign-up.
• Before After Funnel Comparison — Validate feature impact with simple comparisons.
• Product Improvement Recommendations — Turn funnel findings into prioritized actions.