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

Creating a Click-Through Tracking System, Part 3


Read Part 1
Read Part 2


In Part 2 we looked at how to create the script that recorded the clickthrough and sent the user onto the appropriate URL. In this final part, we'll examine how to create a reporting page that lists how many each "trackable" hyperlink has received.

Creating a Reporting Page
For the "trackable" links application I really didn't need a super fancy reporting screen. The metric I was most interested in was the total number of clicks for each "trackable" hyperlink. I also wanted to be able to, for a given "trackable" hyperlink, be able to list each date/time the link was clicked. I created a simple page to display this information. Below you'll find the source code (and a live demo) for this reporting page:

<% Option Explicit %>
<html><body>
<h1>Links Click Through Report</h1>
<%
  Dim iID, objConn
  iID = Request.QueryString("ID")
  If Len(iID) = 0 then iID = 0

  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open "DSN=MyDSN"
  
  Dim objRS
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "sp_LinkReport " & iID, objConn
  
  Response.Write "<TABLE Border=1 CellPadding=5 cellspacing=1" & _
                 " align=center>" & vbNewLine  
  if iID = 0 then
    Response.Write "<tr><th>Name</th><th>Description</th>" & _
                   "<th>Clicks</th></tr>"
  Else
    Response.Write "<tr><th>Total Clicks</th></tr>"
  End IF
  
  Do While Not objRS.EOF
    if iID = 0 then %>
      <tr>
      <td><a href="LinkReport.asp?ID=<%=objRS("LinkID")%>">
            <%=objRS("name")%>
      </a></td>
      <td><%=objRS("description")%></td>
      <td align=center><%=FormatNumber(objRS("Clicks"),0)%></td>
      </tr>
    <% else %>
      <tr>
      <td><%=FormatDateTime(objRS("DateClicked"))%></td>
      </tr>
<%  end if
    objRS.MoveNext
  Loop
  Response.Write "</TABLE>" & vbNewLine
  
  objRS.Close
  Set objRS = Nothing
  
  objConn.Close
  Set objConn = Nothing
%>
</body></html>
[live demo]

In the report screen you'll see a "trackable" link titled "Article Demo." This is an example of a "trackable" link I made for this article. This "trackable" link points to /demos/Tracked.asp but, to successfully track the click, I must point the hyperlink to /ASPScripts/Goto.asp?ID=3. Go ahead and visit /ASPScripts/Goto.asp?ID=3 and notice that it increments the count in the report screen. Clicking on a Name for a particular "trackable" link lists the complete list of date/times the particular link was clicked.

Note that, in the above code, a stored procedure, sp_LinkReport is called and passed an ID. If the ID is zero, the summary report is returned; if the ID is non-zero, a list of clickthrough date/times for the "trackable" hyperlink represented by the ID are returned. The contents of the sp_LinkReport stored procedure can be seen below.

Create Procedure sp_LinkReport (@ID int)
As
 IF @ID = 0
    SELECT L.LinkID, Name, Description,
           Clicks=(SELECT COUNT(*)
                   FROM tblLinkClicks C
                   WHERE C.LinkID = L.LinkID)
	FROM tblLinks L
	ORDER BY LinkID
 ELSE
    SELECT DateClicked FROM tblLinkClicks
    WHERE LinkID = @ID ORDER BY DateClicked DESC

Happy Programming!


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


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