Add SessionId to ac_PageViews table

[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

[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?

[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.

[AbleMods]

Ahhh ok, I understand now.