Creating a Click-Through Tracking System, Part 3

Creating a Click-Through Tracking System, Part 3

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 %> <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!