Page 1 of 1
Add SessionId to ac_PageViews table
Posted: Thu Apr 10, 2008 2:01 pm
by nickc
The ac_PageViews table does not seem to have an easy way to measure "visits" or sessions. I'm looking to get a simple metric like this:
Code: Select all
SELECT
convert(char,ActivityDate,101) as [Period],
count(*) as [PageViews],
count(DISTINCT(UserId)) as [Visitors],
count(DISTINCT(SessionId)) as [Visits]
FROM
dbo.ac_PageViews
WHERE
convert(char, ActivityDate, 101) between @StartDate and @EndDate
GROUP BY
convert(char,ActivityDate,101)
Can a SessionId column be added to this table to facilitate this?
Thanks,
-Nick
Re: Add SessionId to ac_PageViews table
Posted: Tue Apr 15, 2008 9:31 pm
by AbleMods
IIS logfiles with a good logfile analyzer will go alot further for you - have you looked at some of the latest analyzers out on the market?
Re: Add SessionId to ac_PageViews table
Posted: Wed Apr 16, 2008 4:55 pm
by nickc
Analytics isn't my problem - I was given the task of wrapping up a number of stats into a single datatable to be bound to a pivot table in Excel - views, visitors, visits, sales, coupons, shipping, payments, etc.
I can get it all from a single view in Able's database - except visits.
For now, "select 0 as [Visits]" works ok.
Re: Add SessionId to ac_PageViews table
Posted: Wed Apr 16, 2008 5:01 pm
by AbleMods
Ahhh ok, I understand now.