Building a Link Tracker

Why track links?

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 Concept

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.

The Data

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:

See the support materials at the end of the article for a downloadable sample Access 2000 database.

The Code

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 same time.

<%@ 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 rsLinkTracker

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:

' http://server/linktracker.asp?name=asp101

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

'Response.Write strSQL



' 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

	rsLinkTracker.Close

	

	' 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() & _

		DATE_DELIMITER

	

	' Standard debugging step when something goes wrong!

	'Response.Write strSQL

	

	' 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.AddNew

		

		rsLinkTracker.Fields("link_id").Value   = iRedirectId

		rsLinkTracker.Fields("hit_date").Value  = Date()

		rsLinkTracker.Fields("hit_count").Value = 1

	Else

		rsLinkTracker.Fields("hit_count").Value = _

			rsLinkTracker.Fields("hit_count").Value + 1

	End If



	' Save changes to the data

	rsLinkTracker.Update

Else

	' If no match send em to our home page

	strName = "/"

End If



' Close our recordset object

rsLinkTracker.Close

Set rsLinkTracker = Nothing



' Kill our connection

cnnLinkTracker.Close

Set cnnLinkTracker = Nothing



' Send them on their merry way using the location we got

' from the database!

Response.Redirect strName

%>

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.

Support Materials

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