funnel-analysis

name	funnel-analysis
description	Build conversion funnel analyses to identify drop-off points and optimisation opportunities. Outputs funnel SQL queries, drop-off attribution, segment comparison, and experiment prioritisation.
argument-hint	["funnel steps","event tracking system","time window","segment dimensions"]
allowed-tools	Read, Write, Bash

Funnel Analysis

Funnel analysis tracks how users progress through a defined sequence of steps toward a goal. It reveals where users drop off, how long each stage takes, and which segments convert better. It is the primary tool for identifying the highest-ROI optimisation opportunities in a product.

Process

1. Define the funnel. What is the goal event? What are the required prerequisite steps?
2. Set the time window. How long can a user take to complete the funnel? (e.g., signup must be within 7 days of first visit)
3. Write the SQL. Track each step as a distinct event; compute conversion at each stage.
4. Segment the analysis. By acquisition channel, device, plan tier, geography.
5. Identify the biggest drop-off. That is your primary optimisation target.
6. Generate hypotheses. Why do users drop? Session recordings, user interviews, support tickets.

Funnel SQL

-- E-commerce checkout funnel


 funnel_events  (
    
        user_id,
        event_type,
        event_time,
        device_type,
        acquisition_channel
     events
    
        event_type  (
            , ,
            , , 
        )
         event_time    
),

step1  (
    
        user_id,
        (event_time)  step1_time,
        (device_type)  device_type,
        (acquisition_channel)  channel
     funnel_events
     event_type  
      user_id
),

step2  (
      s1.user_id, (e.event_time)  step2_time
     step1 s1
     funnel_events e  s1.user_id  e.user_id
         e.event_type  
         e.event_time  s1.step1_time
         e.event_time  s1.step1_time   
      s1.user_id
),

step3  (
      s2.user_id, (e.event_time)  step3_time
     step2 s2
     funnel_events e  s2.user_id  e.user_id
         e.event_type  
         e.event_time  s2.step2_time
      s2.user_id
),

step4  (
      s3.user_id, (e.event_time)  step4_time
     step3 s3
     funnel_events e  s3.user_id  e.user_id
         e.event_type  
         e.event_time  s3.step3_time
      s3.user_id
),

step5  (
      s4.user_id, (e.event_time)  step5_time
     step4 s4
     funnel_events e  s4.user_id  e.user_id
         e.event_type  
         e.event_time  s4.step4_time
      s4.user_id
)


    ( s1.user_id)   step1_product_viewed,
    ( s2.user_id)   step2_add_to_cart,
    ( s3.user_id)   step3_checkout_started,
    ( s4.user_id)   step4_payment_entered,
    ( s5.user_id)   step5_order_placed,

    ROUND(  ( s2.user_id)  (( s1.user_id), ), )  s1_to_s2_pct,
    ROUND(  ( s3.user_id)  (( s2.user_id), ), )  s2_to_s3_pct,
    ROUND(  ( s4.user_id)  (( s3.user_id), ), )  s3_to_s4_pct,
    ROUND(  ( s5.user_id)  (( s4.user_id), ), )  s4_to_s5_pct,
    ROUND(  ( s5.user_id)  (( s1.user_id), ), )  overall_cvr

 step1 s1
  step2 s2  s1.user_id  s2.user_id
  step3 s3  s2.user_id  s3.user_id
  step4 s4  s3.user_id  s4.user_id
  step5 s5  s4.user_id  s5.user_id;