Introduction
Have you ever wondered how often users click a particular hyperlink on your site?
Perhaps you have a banner whose click throughs you wish to monitor, or are curious how often
various hyperlinks are clicked depending on their position on the Web page. I
(Scott Mitchell) was wanting
such a system for 4Guys so I decided to create one and present the entire
application as an article.
The Data Model
When designing the database structure for the link clickthrough tracking system I decided
to create two tables. The first database table, tblLinks would contain information
about each of the "tracked" hyperlinks. The second table, tblLinkClicks would contain
a row for each time a user clicked a "tracked" hyperlink. The structure for these two database
tables can be seen below:
tblLinks | ||
|---|---|---|
LinkID | int | Primary Key / IDENTITY |
Name | varchar(50) | |
Description | varchar(2000) | |
SendToURL | varchar(150) | |
tblLinkClicks | ||
|---|---|---|
ClickID | int | Primary Key / IDENTITY |
LinkID | int | Foreign Key to tblLinks.LinkID |
DateClicked | datetime | Contains a default value of getdate() |
The tblLinks table contains a row for each hyperlink that you wish to track.
Each tracked hyperlink can have a Name and Description. (These two columns are used solely for
identifying the tracked link from an administration standpoint; these values will not be outputted
into an ASP page.) The SendToURL column contains the URL that you want the user
to be sent when they click on the tracked hyperlink. This is the URL you would have in the
HREF HTML tag if the link was not being tracked.
The tblLinkClicks table has a row automatically added to it whenever a user clicks
on a "tracked" hyperlink. Each row represents a clickthrough. The LinkID column
identifies what "tracked" hyperlink was clicked, while the DateClicked column
identifies the date and time the clickthrough occurred (which I setup with a default of
getdate(), which returns the current date/time).
Adding New "Trackable" Links via an Administration Page
Now that we have these two tables, let's look at how we can easily add new "trackable" links.
While we could simply add a new row to the table by hand through SQL Enterprise Manager, I
went to the trouble of creating a simple administration page to facilitate adding new
"trackable" links. This entire administration page can be created with a single ASP page
using a post-back form. A screenshot of this administration page in action, as well as the
source code for this page can be seen below. When creating this page you should name it
AddLink.asp.
Once you've checked out the screenshot and sample code below, move on to Part 2, where we'll look at how to wire up our old hyperlinks into trackable hyperlinks.
|
