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

Creating a Click-Through Tracking System, Part 2

Read Part 1

In Part 1 we looked at the basic database design of the "trackable" link system and at an administration page for adding new "trackable" links. Note that you could bypass this administration section and simply add a new row to the tblLinks table by hand through SQL Enterprise Manager if you so chose. Also note the administration page screenshot after a new link is added. Notice that, at the top, the user is presented with a URL to use in place of the old hyperlink that you wish to track.

That is, say that you use to have a hyperlink in an HTML page that pointed to http://www.foo.com/SomePage.asp and you now wanted to track how often that link was clicked. You would go to those pages that contained a link to http://www.foo.com/SomePage.asp and replace the hyperlink with the link presented in the administration page screenshot, something like: http://www.yourserver.com/scripts/Goto.asp?ID=4. (In the administration page screenshot the trackable link is set to http://www.4GuysFromRolla.com/ASPScripts/Goto.asp?ID=3. For your application you'd want to change the www.4GuysFromRolla.com part to the name of your Web server and the /ASPScripts/Goto.asp to the URL of your ASP page that handles clickthroughs for trackable links (we'll look at this shortly).)

Tracking Clickthroughs
Each time a user clicks on a "trackable" link they will be taken to an ASP page that handles clickthroughs. In my administration page I setup the link to use as /ASPScripts/Goto.asp, but you can specify any ASP page. This ASP page will be called whenever the user clicks a "trackable" link and will be passed an ID in the QueryString representing the ID of the specific hyperlink clicked. The source code for this page is simple. It calls a stored procedure that performs some database maintenance and then redirects the user to the proper URL specified by the SendToURL column of the tblLinks table. The source code for this page can be seen below: 

<% Option Explicit %>
<%
  'Read in the ID from the querystring
  Dim iID
  iID = Request.QueryString("ID")
  
  'If ID is not present, display an error mesage
  If Len(iID) = 0 or Not IsNumeric(iID) then
    Response.Write "An invalid ID has been passed into Goto.asp.<br>"
    Response.Write "[<a href=""/"">Return to 4GuysFromRolla.com</a>]"
    Response.End
  Else
    'We have a valid ID, update the linkClicks table and get the URL %>
    Dim objRS, objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DSN=MyDSN"
    
    Set objRS = objConn.Execute("SendOffSite " & iID)
    
    Dim strURL
    strURL = objRS(0).Value
    
    objRS.Close
    Set objRS = Nothing
    
    objConn.Close
    Set objConn = Nothing

    'Send the user to the URL
    Response.Redirect(strURL)
  End If
%>

Note that we are calling a stored procedure, SendOffSite, and passing it the ID that was passed into this page through the QueryString. To learn more about stored procedures be sure to read: Writing a Stored Procedure. This stored procedure needs to do two things:

    1.) Add a new row to the tblLinkClicks table for the "trackable" link represented by the ID passed into the stored procedure.
    2.) Get the SendToURL column value from the tblLinks table for the "trackable" linked clicked.

The code for this stored procedure can be seen below: 

Create Procedure SendOffSite (@ID int)
As
  SET NOCOUNT ON

  -- add a row to tblLinkClicks
  INSERT INTO tblLinkClicks (LinkID) VALUES(@ID)
	
  -- grab the URL for the appropriate linkID
  SELECT SendToURL FROM tblLinks WHERE LinkID = @ID
	
  SET NOCOUNT OFF

Pretty straight forward. Note that in the INSERT statement, in which we add a new row to the tblLinkClicks table to represent a clicked hyperlink, we do not need to specify a value for the DateClicked column since it has a default value of getdate(). Also note that the SELECT statement will return the URL specified by the SendToURL column of the tblLinks table. This URL is passed back to the ASP page and is the URL the user is redirected to via the /ASPScripts/Goto.asp page.

Now that we have an administration page to add new "trackable" links and a page to record clickthroughs and successfully redirect the visitors, you may think that we are done. While we do have all of the essential parts, there is one more piece that would be very useful: a report screen that lists the total number of clickthroughs for our "trackable" links. We'll examine this in Part 3.

  • Read Part 3!

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

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