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:
|
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:
|
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.