I am trying to do a funnel analysis where I want to observe how many users reached from start event to end event. Let's say I want to analyze funnel made of 3 events i.e. Visit, Sign Up and Activate.
I have a table where all the user events are stored with columns like user_id, epoch_utc, event_date, event_name and the medium through which the user event came in the system. I need to show the users who completed the funnel (events in the given order) and break them up by which users dropped at which event and their count wrt to medium.
I have written the query as discussed in this link -
https://popsql.com/sql-templates/marketing/running-a-funnel-analysis
The query is as follows:
with visit_users as (
select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from events_table
where date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')
and event_name = 'Visit'
group by 1
),
signup_users as (
select su.user_id, su.min_time, su.medium, su.total_events from
(
select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from (
SELECT user_id, epoch_utc, medium, event_name FROM events_table
where date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d') AND
( event_name = 'Sign Up' )
) group by 1
) su, visit_users acu
where su.user_id = acu.user_id
and su.min_time > acu.min_time
),
activate_users as (
select icu.user_id, icu.min_time, icu.medium, icu.total_events from
(
select user_id, min(epoch_utc) as min_time, min(medium) as medium, count(event_name) as total_events from (
SELECT user_id, epoch_utc, medium, event_name FROM events_table
where date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d') and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d') AND
( event_name = 'Activate' )
) group by 1
) icu, signup_users su
where icu.user_id = su.user_id
and icu.min_time > su.min_time
)
select * from (
select step, medium, count(user_id) as total_users, 0 as total_time, 0 as avg_time, sum(total_events) as total_events from (
select 'Visit' as step, acu.medium, acu.user_id,
0, acu.total_events from visit_users acu
) group by 1, 2
UNION
select step, medium, count(user_id) as total_users, sum(diff) as total_time, (sum(diff) / count(user_id) ) as avg_time, sum(total_events) as total_events from (
select 'Sign Up' as step, su.medium, su.user_id,
date_diff('second', from_unixtime(acu.min_time/1000) , from_unixtime(su.min_time/1000)) as diff,
su.total_events
from visit_users acu, signup_users su
where acu.user_id = su.user_id
) group by 1, 2
UNION
select step, medium, count(user_id) as total_users, sum(diff) as total_time, (sum(diff) / count(user_id) ) as avg_time, sum(total_events) as total_events from (
select 'Activate' as step, icu.medium, icu.user_id,
date_diff('second', from_unixtime(su.min_time/1000) , from_unixtime(icu.min_time/1000)) as diff,
icu.total_events
from signup_users su, activate_users icu
where su.user_id = icu.user_id
) group by 1, 2
) order by step, total_users desc
Now the user can define a funnel at runtime and instead of these 3 events, it can be more number of events in a funnel. Is there any way to optimize the above query to work for n events with slight change.
We use java based application to generate the query. Is there any way to optimize it?