Get Visitor Paths from MS SQL DB

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

Development Tools

Reporting Services Development

Get Visitor Paths from MS SQL DB

Author

Topic

paladin
Starting Member

1 Post

Posted - 2006-11-22 : 12:29:48

I've stumbled on this one and could use some help. I am trying to get the visitor paths thourgh our site. Every page hit is logged in the database in the following columns:

userID
landing page
referring page

So it looks something like:
user ¦ Landing Page ¦ Referrer
------------------------------
1 ¦ a.html ¦ empty
1 ¦ b.html ¦ a.html
1 ¦ c.html ¦ b.html
2 ¦ a.html ¦ empty
2 ¦ b.html ¦ a.html
2 ¦ c.html ¦ b.html

I'm not looking to get every single user, just some general stats. Like 40% of people that landed on pags A went to Page B, 20% of the visitors left the site on page D etc...

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 12:52:48

[code]-- prepare test data
declare @t table (usr int, ToPage varchar(100), FromPage varchar(100))

insert @t
select 1, 'a.html', null union all
select 1, 'b.html', 'a.html' union all
select 1, 'c.html', 'b.html' union all
select 2, 'a.html', null union all
select 2, 'b.html', 'a.html' union all
select 2, 'd.html', 'b.html'

-- do the work
select p.frompage,
p.topage,
p.cnt Hits,
100 * round(p.cnt / t.tot, 4) [Percent of total hits]
from (
select frompage,
topage,
count(*) cnt
from @t
group by frompage,
topage
) p
cross join (
select 1.0 * count(*) tot from @t
) t[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources