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:
|Primary Key / IDENTITY|
|Primary Key / IDENTITY|
|Foreign Key to |
|Contains a default value of |
tblLinks table contains a row for each hyperlink that you wish to track.
Each tracked hyperlink can have a
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.
tblLinkClicks table has a row automatically added to it whenever a user clicks
on a "tracked" hyperlink. Each row represents a clickthrough. The
identifies what "tracked" hyperlink was clicked, while the
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
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.