Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...DNN Platform (o...DNN Platform (o...User ActivityUser Activity
Previous
 
Next
New Post
7/20/2009 2:24 PM
 

I meant some sort of coupon offered for your products or services. Using a unique code, you can track who received the coupon and if it was used or not for management evaluation. Another alternative is a customer reward such as "mention this webpage for a free gift or discount". These are simple marketing tools that can be accurately measured.

A website is much like a sign on the side of the road. You may be able to tell how many people drove by, but you have no real idea who actually looked at (or read) your sign. Knowing how long someone stared at your sign would be extremely difficult to determine and to attempt to analyze the information would offer very little insight.

I'm sorry; I did not intend to imply that something like this was built in to DNN. I did intend to point out that the effort would be futile.


Dwayne J. Baldwin
 
New Post
7/21/2009 4:20 PM
 

Thanks for the info Dwayne, I get that and I agree with you 100%. 

I was still required to do a something to track something yet.  What I decided to do was create a new table and run a script every night to export all of the 'LOGIN_SUCCESS' records from the dnn_EventLog table and all of the records with a valid userid and tabid in the dnn_sitelog table into a tblSiteHistory table. 

I created a stored procedure which parses through the data and grabs each login and finds the last page log between before the next page login.  Then I calculate the amount of time between those two times and display minutes active.  I know it doesn't give perfect numbers, but it does give us an idea of the amount of time the user was on our website.  From login to last page request.

Here's the Stored  Procedure. It seems to work fairly well.  If anyone has any comments, suggestions please let me know.  It runs slowly for myself (admin users) because of the amount of records it goes through, but it's pretty quick with your standard web user.

CREATE PROCEDURE spGetUserLogHistory
 @StartDate datetime,
 @EndDate datetime ,
 @UserId int = -1

 AS


Select  sh.Userid,  'LoginDate' = sh.LogDate,
 'LastName' = ltrim(u.FirstName), 'FirstName' = ltrim(u.LastName),
 'LastUserActivty' = max(sh2.LogDate),
 'TimeElapsed' = (datepart(hour, (max(sh2.LogDate) - sh.LogDate)) * 60) +  datepart(minute, (max(sh2.LogDate) - sh.LogDate)) + 1
   

from tblSiteHistory sh inner join
 tblSiteHistory sh2 on sh.Userid = sh2.Userid inner join
 dnn_users u on u.UserId = sh.Userid


where
 --Get by UserId or All Users by default
  (sh.UserId = @UserId  or @UserId = -1)
 --make sure you grab the login Records with this table
 and sh.isLoginRecord = 1
 --Grab records that aren't Login Records
 and sh2.IsLoginRecord = 0

 -- Date Range Filter
 and sh.LogDate >= @StartDate
 and sh.LogDate < @EndDate
 

 -- find the last log before the next login records, if null get last record before timeout (today)
 and sh2.LogDate < isnull((Select top 1 cast(str(datepart(year, sh3.LogDate)) + '-'
      + str(datepart(month, sh3.LogDate)) + '-'
      + str(datepart(day, sh3.LogDate)) + ' '
      + str(datepart(hour, sh3.LogDate)) + ':'
      + str(datepart(minute, sh3.LogDate)  ) as datetime)
    from tblSiteHistory sh3
    where sh3.LogDate > sh.LogDate
     and sh3.isLoginRecord = 1
     and sh3.UserId = sh.UserId
    order by sh3.LogDate), dateadd(day, 1,getdate()))
 
 --make sure max logdate is greater than login date
 and sh2.LogDate > sh.LogDate

 --make sure logdates are the same date
 and Datepart(year, sh.LogDate) = DatePart(year, sh2.LogDate)
 and Datepart(month, sh.LogDate) = DatePart(month, sh2.LogDate)
 and Datepart(day, sh.LogDate) = DatePart(day, sh2.LogDate)

group by sh.Userid, u.FirstName, u.LastName, sh.HistoryId, sh.LogGUID,  sh.LogDate

order by  ltrim(u.LastName), ltrim(u.FirstName), sh.LogDate
GO
 

 

 

 
Previous
 
Next
HomeHomeDevelopment and...Development and...DNN Platform (o...DNN Platform (o...User ActivityUser Activity


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out