Have you ever asked yourself: "I wonder which links
on my site people are clicking on?" or "Where is my traffic actually coming
from?" If you're running a site on which your traffic actually matters,
then you've asked these questions quite a bit. In an effort to find an
answer most people start by trying to analyze their log files. While this can
get you part of the answer and tell you what pages are being hit it doesn't
always tell you how the user got there or what they clicked on from there.
The main offenders are links from your site to other sites and links to your
site from newsletters. The traffic just seems to happen and rarely can you
trace where it actually came from or went. While people running sophisticated sites have come up with tracking systems, a new webmaster
may not have the time to build a system or even realize
one is needed. I'm hoping this article will help to rectify that situation. In it I will
outline a simple system that can give you daily statistics about each link
you track and can be run using a single script and a couple of database tables.
The secret behind this script is the Response.Redirect
command. The command takes a URL as a parameter and sends it to the
browser, which tells the browser where to go in order to find the requested
page. If we send requests to our tracking script (so it can log
them and determine where to send the user) we can then send them on their
merry way none the wiser that they didn't go there directly.
The only real question then becomes how do we figure out
where to send them? Well simplicity is always preferable so many
sites take the simple route and just pass the redirect script the URL
to go to. It makes sense and is easy enough to do. Personally I
don't do it for two reasons. First, I hate having to URLEncode stuff
to make it QueryString safe. Second, it makes for long and
ugly looking links. Instead, I use a short keyword. Each one has
to be unique, but it makes for much prettier links and also lets you track multiple links to the same
page independently. This can be a big plus if you're trying to
figure out which of your ads referencing one particular page are working best.
Let's start with the data we're trying to get and how
were going to store it. Each link we set up needs a couple pieces of
information in order for it to do its job: a unique identifier by which we can
specify it and the location we want to send the user to. Next comes
logging... this gets a little ugly but I do a breakdown by day and simply
get a count per link per day. This table needs a link identifier
specifying which link from the link table the data is for,
a date field so we know what date the action happened on and a hit count
field to do the actual counting. That gives us a table setup that
looks something like this:
Data Type (SQL Server / Access)
int(identity) / long integer(autonumber)
a unique identifier for linking to out logging table
char(25) / text(25)
a unique friendly name by which we specify the link to use
varchar(255) / text(255)
the url we want to send users to
Data Type (SQL Server / Access)
int / long integer
the link from tblLinkTracker the data belongs to
smalldatetime / date
the date on which the hits we're logging took place
int / long integer
a count of hits for the link in the link_id field on the day in the date field
See the support materials at the end of the article for a downloadable sample
Access 2000 database.
Now that we've got our tables set up we need to build our script and set it
up to not only get the people to the right place, but also log the activity to
the database. I figure it's probably stupid for me to tell you what the
code is going to look like and then show you it later so here's a heavily
commented version of the code so you can read along and look at the code at the
<%@ Language=VBScript %>
Option Explicit ' Never code without it!
Response.Buffer = True ' Make sure we can redirect later
' The constants I use in this script...
' pulled directly from adovbs.inc
Const adOpenForwardOnly = 0
Const adOpenDynamic = 2
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adCmdText = &H0001
' This needs to be ' for SQL Server and # for Access
Const DATE_DELIMITER = "#"
Dim strName ' Friendlyname of the redirect
Dim cnnLinkTracker ' ADO objects
Dim strSQL ' SQL command building area
Dim iRedirectId ' id of the redirect for logging process
' Get name of location to go to
' I simply call this name so to call the script it would look
' something like this:
' where asp101 is the friendly name from the database
strName = Request.QueryString("name")
' Make it DB friendly just in case a ' got in somehow
strName = Replace(strName, "'", "''")
' Create a new connection
Set cnnLinkTracker = Server.CreateObject("ADODB.Connection")
' Your connection string goes here!
' This one expects an Access database in the same place as this script
cnnLinkTracker.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("linktracker.mdb"), "admin", ""
' Create a recordset
Set rsLinkTracker = Server.CreateObject("ADODB.Recordset")
' Get the record we're looking for by passing it the name
' we got from the QueryString. I'm prebuilding the SQL command
' to make it easier to debug if we need to.
strSQL = "SELECT id, location FROM tblLinkTracker " & _
"WHERE name='" & strName & "';"
' Quick and dirty debugging when something goes wrong.
' Send the command to the database to get the appropriate records
rsLinkTracker.Open strSQL, cnnLinkTracker, _
adOpenForwardOnly, adLockReadOnly, adCmdText
' If we got back any results then we know where to send them
' o/w I just send them to our home page for lack of a better
' place to send them.
If Not rsLinkTracker.EOF Then
' Get redirect Id # from recordset
iRedirectId = rsLinkTracker.Fields("id").Value
' Get location to send the user to
strName = rsLinkTracker.Fields("location").Value
' We've got all the info we need so close our recordset
' I now recycle the recordset for the logging process.
' Lots of people would argue with me about this, but I
' know I'm doing it and this is my code so if you don't
' like it feel free to change it, but I'm not going to! ;)
' Start logging process
' Build out SQL String ahead of time.
' This should get us the record containing the information
' for the selected link for today's date if one exists.
strSQL = "SELECT link_id, hit_date, hit_count " & _
"FROM tblLinkTrackerLog " & _
"WHERE link_id = " & iRedirectId & " " & _
"AND hit_date = " & DATE_DELIMITER & Date() & _
' Standard debugging step when something goes wrong!
' Send the command.
rsLinkTracker.Open strSQL, cnnLinkTracker, _
adOpenDynamic, adLockPessimistic, adCmdText
' If it's EOF then it's the first hit of the day and we need
' to add a new record o/w we simply update the existing hit
' count of the record by adding one to it.
If rsLinkTracker.EOF Then
rsLinkTracker.Fields("link_id").Value = iRedirectId
rsLinkTracker.Fields("hit_date").Value = Date()
rsLinkTracker.Fields("hit_count").Value = 1
rsLinkTracker.Fields("hit_count").Value = _
rsLinkTracker.Fields("hit_count").Value + 1
' Save changes to the data
' If no match send em to our home page
strName = "/"
' Close our recordset object
Set rsLinkTracker = Nothing
' Kill our connection
Set cnnLinkTracker = Nothing
' Send them on their merry way using the location we got
' from the database!
Using the system
Now that we've built this great system the next step is
obviously to start using it. To add a new redirect you simply add a new
entry to the Link Tracker table with an appropriate keyword and the
corresponding URL. I've enclosed a sample script called add_tracker.asp
in the zip file that shows a quick web interface for this
process. It's pretty straight forward so I won't go into it
here, but it can be really useful and makes things a lot easier then
adding entries by hand.
Once the entry is in the table you can link to it and it will keep track of
the clicks by itself. You can link to it yourself or give it to other
sites to link to. It can really be used as a replacement for the URL and
the end user shouldn't notice any difference but you gain the benefit of the
statistics you didn't have before.
Taking a look at the data
Now that we've got the system built and in use to gather daily
statistics on clicks, we need to do something with the data.
Otherwise, what's the point of gathering it in the first place? As you've
probably figured out already
we're not big on UI design so the sample reporting area I've included is pretty
ugly, but it'll get you started. If you want to make it pretty please feel
free to do so!
Again, I'm not going to get into the code much because it doesn't do all that
much and it's sort of off topic of the actual link tracking I set out to cover,
but basically there are two pages. The first, index.asp, simply lists
the links in the table and shows you the total number of clicks each link
has gotten. To get more detailed information on a particular link you
can then click on any link to go to details.asp. This page just shows a
simple table with the dates and the number of times it was clicked. At the
bottom I've added some summary data including the number of clicks in the last 7
days and the number of clicks this month.
A zip file of the scripts and sample database is available for download
here. Installation should just involve
unzipping it into a directory on
your web server. The only potential problems that come to mind are the
standard Access problems of NTFS permissions not allowing writing to the
database (the NTFS permissions on the mdb file need to allow change access) and
not having the latest version of OLE DB on the server (get it from http://www.microsoft.com/data). Besides
that it should run "out of the box." That being said once you have it
working you should definitely move the database to an area that is not
accessable via the web so that people can't download it directly or move it to
SQL Server. Along the same lines, securing the admin and stats directories
is also relatively important unless you don't mind sharing the stats with