Published: Monday, January 29, 2001
Creating a Click-Through Tracking System
Introduction
Have you ever wondered how often users click a particular hyperlink on your site?
Perhaps you have a banner whose click throughs you wish to monitor, or are curious how often
various hyperlinks are clicked depending on their position on the Web page. I
(Scott Mitchell) was wanting
such a system for 4Guys so I decided to create one and present the entire
application as an article.
The Data Model
When designing the database structure for the link clickthrough tracking system I decided
to create two tables. The first database table, tblLinks would contain information
about each of the "tracked" hyperlinks. The second table, tblLinkClicks would contain
a row for each time a user clicked a "tracked" hyperlink. The structure for these two database
tables can be seen below:
tblLinks |
|---|
LinkID | int | Primary Key / IDENTITY |
Name | varchar(50) | |
Description | varchar(2000) | |
SendToURL | varchar(150) | |
tblLinkClicks |
|---|
ClickID | int | Primary Key / IDENTITY |
LinkID | int | Foreign Key to tblLinks.LinkID |
DateClicked | datetime | Contains a default value of getdate() |
The tblLinks table contains a row for each hyperlink that you wish to track.
Each tracked hyperlink can have a Name and Description. (These two columns are used solely for
identifying the tracked link from an administration standpoint; these values will not be outputted
into an ASP page.) The SendToURL column contains the URL that you want the user
to be sent when they click on the tracked hyperlink. This is the URL you would have in the
HREF HTML tag if the link was not being tracked.
The tblLinkClicks table has a row automatically added to it whenever a user clicks
on a "tracked" hyperlink. Each row represents a clickthrough. The LinkID column
identifies what "tracked" hyperlink was clicked, while the DateClicked column
identifies the date and time the clickthrough occurred (which I setup with a default of
getdate(), which returns the current date/time).
Adding New "Trackable" Links via an Administration Page
Now that we have these two tables, let's look at how we can easily add new "trackable" links.
While we could simply add a new row to the table by hand through SQL Enterprise Manager, I
went to the trouble of creating a simple administration page to facilitate adding new
"trackable" links. This entire administration page can be created with a single ASP page
using a post-back form. A screenshot of this administration page in action, as well as the
source code for this page can be seen below. When creating this page you should name it
AddLink.asp.
Once you've checked out the screenshot and sample code below, move on to
Part 2, where we'll look at how to wire up our old
hyperlinks into trackable hyperlinks.
Read Part 2!
Screenshot of the Administration Page
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim iID, strSQL, objConn
'open a database connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=MyDSN"
'Determine if the form has been submitted. If it has, insert a new record
If Len(Request("btnSubmit")) > 0 then
'Add a new link
strSQL = "INSERT INTO tblLinks (Name, Description, SendToURL) " & _
"VALUES('" & Replace(Request("txtName"),"'","''") & "','" & _
Replace(Request("txtDescription"),"'","''") & "','" & _
Request("txtSendToURL") & "');select @@identity"
'We get the ID of the row just inserted using a method explained at:
'http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml
Dim objRSTemp
Set objRSTemp = objConn.Execute(strSQL).NextRecordset
iID = CInt(objRSTemp(0).Value)
objRSTemp.Close
Set objRSTemp = Nothing
End If
%>
<HTML><BODY>
<% If Len(iID) > 0 then %>
<b><u>New Link URL</u></b><br>
To create a new tracking link, use the following URL:<br>
http://www.4GuysFromRolla.com/ASPScripts/Goto.asp?ID=<%=iID%>
<p><hr><p>
<% End If %>
<form method=post action="AddLink.asp">
<b><u>Add a Link</u></b><br>
Password: <input type=password name="txtPassword">
<br>Name: <input type=text name="txtName">
<br>Description:<br>
<textarea wrap=virtual cols=50 rows=5 name="txtDescription"></textarea>
<br>
URL: <input type=text name="txtSendToURL">
<p>
<input type=submit value="Create Link" name="btnSubmit">
</form>
<!--Display a list of existing links-->
<p><hr><p>
<b><u>Current Links in System</u></b><br>
<%
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblLinks", objConn
Do While Not objRS.EOF
Response.Write objRS("LinkID") & ".) " & objRS("Name") & _
" - " & objRS("Description") & "(<a href=""" & _
objRS("SendToURL") & """>" & objRS("SendToURL") & "</a><br>"
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
</BODY></HTML>
|
