Help with funnel analysis query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with funnel analysis query

Author

Topic

aandarian2
Starting Member

3 Posts

Posted - 2011-08-11 : 12:47:54

Hi all. First post - excuse any noob mistakes.

I'm conducting a funnel analysis and need some help.

I have a #sessions table which has session_id, sequence_num, from_page, to_page.

Meaning, each session is defined as 1 or more sequence numbers which record from_page and to_page visits.

I'm trying to write a query that calculates count distinct sessions that have from_page = 'editaccount' given same session has 'addaccount' in a lower sequence number. Inversely, how many 'addaccount' sessions have 'editaccount' in a greater sequence number.

so somehow, my query needs to tie in the relative sequence number to the appropriate steps (addaccount then editjob) to give me all the sessions that go through that flow.

This way I can do above query divided count all sessions with 'addaccount' to see which percentage complete 'editaccount' flow.

I hope I am articulating this well - let me know if my articulation is poor.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:52:57

[code]
select count(*)
from
(
select session_id
from sessions
group by session_id
having max(case when from_page = 'editaccount' then sequence_num else null end) > max(case when from_page = 'addaccount' then sequence_num else null end)
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aandarian2
Starting Member

3 Posts

Posted - 2011-08-11 : 15:00:43

Thanks visakh, this is very helpful.

I have a question - i am trying to add another max statement so that there will be three - a three page flow but the system returns message - incorrect syntax near '>'

What would be best method of doing query for 3 page flow?

max(case when s.from_page = 'gaaltsummary' then s.seq_num else null end) >
max(case when s.from_page = 'gachoosefundfmly' then s.seq_num else null end) >
max(case when s.from_page = 'acctadd' then s.seq_num else null end)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:29:38

nope you cant put them in single condition. then it should be


max(case when s.from_page = 'gaaltsummary'	 then s.seq_num else null end) > 
max(case when s.from_page = 'gachoosefundfmly'	 then s.seq_num else null end) 
and  max(case when s.from_page = 'gachoosefundfmly'	 then s.seq_num else null end)>
max(case when s.from_page = 'acctadd'	 then s.seq_num else null end)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aandarian2
Starting Member

3 Posts

Posted - 2011-08-12 : 12:11:24

thank you Visakh, I really appreciate this.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-24 : 14:31:08

quote:
I'm trying to write a query that calculates count distinct sessions that have from_page = 'editaccount' given same session has 'addaccount' in a lower sequence number.


select count(distinct later.session_id)
from #session later
where later.from_page = 'editaccount'
  and exists(select *
               from #session former
               where former.session_id = later.session_id
                 and former.from_page = 'addaccount'
                 and former.sequence_number < later.sequence_number)

quote:
Inversely, how many 'addaccount' sessions have 'editaccount' in a greater sequence number.

There is similar solution for the inverse problem.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 01:58:18

quote:
Originally posted by aandarian2

thank you Visakh, I really appreciate this.

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources