Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate Average Time Spent on a site via SQL ?

Posted on 2004-03-31
1
Medium Priority
?
934 Views
Last Modified: 2007-12-19
I wrote a script to track my website visitors using a really simple method:

TABLE: Visitors
  Column: URL (varchar)
  Column: IPAddress (numeric)
  Column: Date/Time (DateTime)


Using this I can count how many visits/unique vistors, etc to a specific page or to the entire site. within a date range.  I can also calculate average page views per visitor and other basic web stats.

What I would like to figure out now is how to view the "average visit length" - or the average amount of time each user spent on the site before they went away.  Obviously this won't be 100% accurate, since they could spend 20 minutes on the LAST page they looked at and then just close their browser, but I can get a pretty good idea.

I'm wondering if there is a way to do this through SQL with the data present.  I would basically need to do 3 things:

(1) Count the number of unique visitors (ie; unique numbers in the IPAddress field)

(2) Get the amount of time spent on the site for each visitor.  Sometimes visitors will come to the site 2 or 3 times a day so I'm not sure exactly how to come up with this.  My first thought was to take the DateDiff from Max(Datetime) and Min(datetime) but if the user comes on and 8:00 AM and then again at 8:00 PM this method would show they were on the site for 12 hours, which we know isn't realistic.  Not really sure what to do here - maybe base it on a 15 minute window, where if a particular visitor's hits are more than 15 minutes apart it doesn't count that last 15 minutes?  

In other words... someone has hits at 8:00 AM, 8:05 AM, 8:10 AM, 9:30 AM, 9:35 AM ... we will assume that they were connected from 8:00 AM to 8:10 AM simultaneously since those are close together, but their last two visits at 9:30 AM and 9:35 AM were because they opened their browser later and came back.  So THAT one person's "average visit length" would be 7.5 minutes, NOT 1 hour and 35 minutes.

(I came up with 7.5 by assuming two sessions, one from 8:00 to 8:10 for 10 minutes, and one from 9:30 to 9:35 for 5 minutes.  15 Minutes total, two sessions, = average of 7.5 minutes)

Then of course this leads to the third step ... which is to take the total SUM of the average session for each visitor and divide that by the total number of visitors to create an "Average visit length" for the site.

The question is can this be done through a stored procedure that wouldn't kill the server every time it was run?  I don't even know where to begin coding something like this.
0
Comment
Question by:visionmultimedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Accepted Solution

by:
ctcampbell earned 620 total points
ID: 10727512
Here's one way to do it.  This requires SQL Server 2000 because of a table variable.  I have written it for a 15 minute window (900s).  Change 900 if you want a different window.  This should perform fine up to a couple of hundred thousand rows, based on server memory.  If you need more, just segment the data based on something like an IP range.

--- Begin T-SQL ---
declare @Visits table (
      IPAddress varchar(15) not null,
      LogTime datetime not null,
      VisitID int null,
      LogID int not null identity,
      primary key clustered (IPAddress, LogTime, LogID)
)

insert into @Visits (IPAddress, LogTime)
select IPAddress, LogTime
  from Visitors
 order by IPAddress, LogTime

declare @LastIPAddress varchar(15), @LastLogTime datetime, @VisitID int
set @VisitID = 0
set @LastIPAddress = ''
set @LastLogTime = 0

update @Visits set
      @VisitID = VisitID = case when @LastIPAddress <> IPAddress or datediff(second, @LastLogTime, LogTime) > 900 then @VisitID + 1 else @VisitID end,
      @LastIPAddress = IPAddress,
      @LastLogTime = LogTime

select VisitID, IPAddress, MIN(LogTime) as FirstVisit, MAX(LogTime) AS LastVisit,
      VisitLength = datediff(minute, MIN(LogTime), MAX(LogTime))
  from @Visits
 group by VisitID, IPAddress

select IPAddress, AVG(VisitLength) as AvgVisit
  from (
      select VisitID, IPAddress, VisitLength = cast(datediff(minute, MIN(LogTime), MAX(LogTime)) as smallmoney)
        from @Visits
       group by VisitID, IPAddress
      ) data
 group by IPAddress
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question