Capturing Basic Site Statistics Using ASP (Article)


Author: Phil Fresle

Version Compatibility: ASP


Capturing Basic Site Statistics

Capturing site statistics is the basis of understanding how well
the marketing of our web site is progressing. Unless we know how many
visitors we have had, what pages are most popular, where our visitors
came from, and what types of browsers they are using we are left in the
dark as to how to improve our web site or target visitors more effectively.

This article demonstrates how we can capture site statistics quite simply
and record them in an Access database.

At the very top of our web page we will add the following code:

Option Explicit

We will be making sure our html is buffered in order to speed
up the initial delivery of our HTML when we flush it later on. We
are setting the CacheControl to private to try to stop our page from
being cached by proxy servers, and we will set a large negative
Expires to try to stop our page from being cached by the web server
or browsers, if the user is seeing cached pages our site statistics
will not be being updated.

After this initial code will go the main body of our existing HTML
from the <HTML> tag all the way down to the </HTML> tag. We
then insert the following code:

<!-- #include file = "weeknumber.asp" -->
Dim adCmdText
Dim sPath
Dim lWeekNumber
Dim sDatabase
Dim oFSO
Dim oConnection
Dim sSQL
Dim oCommand
Dim rsResults
Dim sConnection

If Not Response.IsClientConnected Then
  On Error Resume Next

  ' Put together the name of the database which 
  ' is stored outside of the domain
  ' structure in the data sub-directory 
  ' and is called sitestatsNN.mdb where NN
  ' is the week number
  sDatabase=Left(sPath,InStrRev(sPath,"\")) & "..\data\sitestats" _
      & Right(0 & lWeekNumber, 2) & ".mdb"

  Set oFSO = CreateObject("Scripting.FileSystemObject")

  If (oFSO.FileExists(sDatabase)) Then
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Mode = 3
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
    sConnection = sConnection & "Data Source=" & sDatabase & ";"
    sConnection = sConnection & "User Id=Admin;Password=;"
    oConnection.Open sConnection

    sSQL = "SELECT * FROM tblStats WHERE StatID=0"

    Set oCommand = Server.CreateObject("ADODB.Command")
    Set oCommand.ActiveConnection = oConnection
    oCommand.CommandText = sSQL
    oCommand.CommandType = 1

    Set rsResults = Server.CreateObject("ADODB.Recordset")
    rsResults.Open oCommand,,1,3
    Set oCommand = Nothing

    With rsResults        
    End With
    Set rsResults = Nothing
    Set oConnection=Nothing
  End If
  Set oFSO = Nothing
End If

The first thing this code does is to flush the HTML code we
have generated so far to the user's browser. The rest of the ASP
script will be writing our stats away and we do not want to delay
the user seeing the page while this activity is completed.

The 'weeknumber.asp' file contains a function that will
accurately calculate the week number for us. We are going to do
this so we have a separate database file for each week. This will
prevent our database file from getting too bloated and allow us to
download it locally for analysis.

We then check to make sure the client is still connected as
we do not want to waste unnecessary processing on the server.

It is then necessary to put together the name of our database
file which is stored above the domain for security in a directory
called data with a filename that includes the week number. We will
have had to have uploaded the appropriately named database files for
the script to work.

We then use the FileSystemObject to check that the datbase file
exists, as there is little point continuing if it does not.

We then open up a connection to the database, and use a command object
to build a recordset that we can update.

We use the AddNew method of the recordset to add a new recordset and
assign our text to the fields as we then do not have to worry about
characters that might mess up an INSERT statement.

We use the ServerVariables to get information about where the visitor
came from, their IP address (or their proxy's IP address), and the browser
they are using. We also record the name of the script file they are accessing.

Finally, we tidy up by closing our database objects and setting the
variables to nothing.

This is our 'weeknumber.asp' script:

Public Function WeekNumber(InDate)
    Dim DayNo
    Dim StartDays
    Dim StopDays
    Dim StartDay
    Dim StopDay
    Dim VNumber
    Dim ThurFlag
    DayNo = Days(InDate)
    StartDay = Weekday(DateSerial(Year(InDate), 1, 1)) - 1
    StopDay = Weekday(DateSerial(Year(InDate), 12, 31)) - 1
    ' Number of days belonging to first calendar week
    StartDays = 7 - (StartDay - 1)
    ' Number of days belonging to last calendar week
    StopDays = 7 - (StopDay - 1)
    ' Test to see if the year will have 53 weeks or not
    If StartDay = 4 Or StopDay = 4 Then
        ThurFlag = True
        ThurFlag = False
    End If
    VNumber = (DayNo - StartDays - 4) / 7
    ' If first week has 4 or more days, it will be calendar week 1
    ' If first week has less than 4 days, it will belong to last year's
    ' last calendar week
    If StartDays >= 4 Then
        WeekNumber = Fix(VNumber) + 2
        WeekNumber = Fix(VNumber) + 1
    End If
    ' Handle years whose last days will belong to coming year's first
    ' calendar week
    If WeekNumber > 52 And Not ThurFlag Then
        WeekNumber = 1
    End If
    ' Handle years whose first days will belong to the last year's
    ' last calendar week
    If WeekNumber = 0 Then
        WeekNumber = WeekNumber(DateSerial(Year(InDate) - 1, 12, 31))
    End If
End Function

Public Function Days(DayNo)
    Days = DayNo - DateSerial(Year(DayNo), 1, 0)
End Function

The Access database consists of just the one table (tblStats) that
has the following structure:

StatID - AutoNumber
Recorded - Date/Time
RemoteAddress - Text(20)
RemoteHost - Text(20)
HttpReferer - Text(255)
HttpUserAgent - Text(255)
WebPage - Text(255)

Having recorded the information we can download the database file on
a weekly basis and analyse it to see how we might improve our web-site
in the future.