When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Monday, January 29, 2001

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.

- continued -

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 %>
<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>" & _
    Response.Write "<tr><th>Total Clicks</th></tr>"
  End IF
  Do While Not objRS.EOF
    if iID = 0 then %>
      <td><a href="LinkReport.asp?ID=<%=objRS("LinkID")%>">
      <td align=center><%=FormatNumber(objRS("Clicks"),0)%></td>
    <% else %>
<%  end if
  Response.Write "</TABLE>" & vbNewLine
  Set objRS = Nothing
  Set objConn = Nothing
[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)
 IF @ID = 0
    SELECT L.LinkID, Name, Description,
           Clicks=(SELECT COUNT(*)
                   FROM tblLinkClicks C
                   WHERE C.LinkID = L.LinkID)
	FROM tblLinks L
    SELECT DateClicked FROM tblLinkClicks
    WHERE LinkID = @ID ORDER BY DateClicked DESC

Happy Programming!

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article