Funnel Metrics: Step-over-Step Drop Rates

Funnels are an extremely flexible analysis tool that can provide clear and actionable insights. You can apply them to any situation with a notion of linear progression where dropoff per step is important.

Completion Rate

Overall funnel completion rate is one of the big KPIs in funnel analysis. This number tells you what percentage of users or potential customers who enter your funnel also successfully emerge.

Raw Events

To calculate completion rate with raw events, simply divide the number of users who exited the funnel by the number of users who entered the funnel. For a funnel with nine steps, the query looks like:

select 
  count(distinct case when event = 'Step 9' 
    then user_id else null end)/count(distinct user_id)
from event_logs
where event in ('Step 1', 'Step 9')

Aggregated Data

Suppose we’ve already pre-processed the data into a rollup table with daily totals for each step of the funnel.

From here, the query is very simple:

select sum(step_1_count)/sum(step_9_count)
from funnel_rollup

Step-over-Step Drop Rate

While the completion rate is very important, it does not provide a granular view of the funnel. With step-over-step drop rates, you can quickly see at which step you are losing the most users and where your strongest points are.

Raw Events Data

To calculate completion rate from raw events, first calculate each step’s total number of users:

select
  event
  , count(distinct user_id) as count
from event_logs
group by 1
order by 2 desc

This will give us the total number of users at each step. To get the step-over-step drop rate, we can use our favorite Redshift function type — window functions — to get the previous step’s numbers:

select 
  event
  , count
  , 1 - (count/lag(count) over (order by count desc)) as drop_rate
from step_counts
order by 2

The lag function allows you to pull the number of users from the previous step in the funnel. Putting it together, we get:

select 
  event
  , count
  , 1 - (count/lag(count) over (order by count desc)) as drop_rate
from (
  select
  event
  , count(distinct user_id) as count
  from event_logs
  group by 1)
order by 2

By plotting the count on the first axis and the drop rate on the second, we can see a clear picture of where our funnel leaks the most:

Aggregated Data

If your data is pre-aggregated, it is a little trickier to get these numbers in a useful form.

One option is to add an additional column for each step’s drop rate, however, that would be difficult to visualize, and does not scale well. Instead, we can pivot the data using union functions to get the data in the following format:

To do this, write a query that unions the sum of each step:

select 'Step 1' as event, step_1_count as count from funnel_rollup
union all
select 'Step 2' as event, step_2_count as count from funnel_rollup
union all
select 'Step 3' as event, step_3_count as count from funnel_rollup
union all
select 'Step 4' as event, step_4_count as count from funnel_rollup
union all
select 'Step 5' as event, step_5_count as count from funnel_rollup
union all
select 'Step 6' as event, step_6_count as count from funnel_rollup
union all
select 'Step 7' as event, step_7_count as count from funnel_rollup
union all
select 'Step 8' as event, step_8_count as count from funnel_rollup
union all
select 'Step 9' as event, step_9_count as count from funnel_rollup

Now that our data is pivoted, we can use the same query as above to calculate the step-over-step drop rate:

select 
  event
  , count
  , 1 - (count/lag(count) over (order by count desc)) as drop_rate
from (
  select 'Step 1' as event, step_1_count as count 
  from funnel_rollup
  union all
  select 'Step 2' as event, step_2_count as count 
  from funnel_rollup
  union all
  select 'Step 3' as event, step_3_count as count 
  from funnel_rollup
  union all
  select 'Step 4' as event, step_4_count as count 
  from funnel_rollup
  union all
  select 'Step 5' as event, step_5_count as count 
  from funnel_rollup
  union all
  select 'Step 6' as event, step_6_count as count 
  from funnel_rollup
  union all
  select 'Step 7' as event, step_7_count as count 
  from funnel_rollup
  union all
  select 'Step 8' as event, step_8_count as count 
  from funnel_rollup
  union all
  select 'Step 9' as event, step_9_count as count 
  from funnel_rollup
  )
order by 2

Next Steps

Now that you’ve got your basic funnel set up, you can quickly filter and group by different dimensions. See which countries are your best performers, which ad campaigns do well at certain stages, or if Android users have a harder time with certain tutorial steps than iOS users!

Tags: data team