To read the article online, visit http://www.4GuysFromRolla.com/webtech/012901-1.shtml

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
LinkIDintPrimary Key / IDENTITY
Namevarchar(50) 
Descriptionvarchar(2000) 
SendToURLvarchar(150) 

tblLinkClicks
ClickIDintPrimary Key / IDENTITY
LinkIDintForeign Key to tblLinks.LinkID
DateClickeddatetimeContains 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
    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>
    


  • Article Information
    Article Title: Creating a Click-Through Tracking System
    Article Author: Scott Mitchell
    Published Date: Monday, January 29, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/012901-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers