Get Visitor Paths from MS SQL DB

[paladin]

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

[FalseDawn]

You are asking a lot if you expect to be able to do that sort of statistical analysis in pure SQL.
I would advise you to rethink your strategy - personally, I wouldn't waste my time and would just buy a commercial log analyzer - but if you really want to do it yourself, my preference would be to try to do at least some of the analysis in code rather than SQL.

Like 40% of people that landed on pags A went to Page B

SELECT L.user_id FROM log L WHERE L.landing_page='a.html' AND L.referrer IS NULL AND EXISTS (SELECT * FROM log L2 WHERE L2.user_id=L.user_id AND L2.landing_page='b.html')

This should give you a list of users landing on a, then going to b - you will then need to calculate what % of the total this is.

[edited by: FalseDawn at 5:49 pm (utc) on Nov. 22, 2006]

[aspdaddy]

You need to add date/time to do path analysis.

Then you will have the base data but need to build a better model for those queries and then load your data into it for analysis, I use something like .

tblSession(SessionID, ExternalRefererID,RefererQuery)
tblPageView(SessionID,DateTime,PageID)
tblPage(PageID,Url,PageName,PageTypeID)
tblPageType(PageTypeID,PageTypeName)
tblExternalReferer(RefererID,RefererName)

Just use internal refers for path building when loading the data, no need to keep them if your happy with you path building code.