asp.netNOW Q&A
LANGUAGES: SQL | VB.NET | C#
ASP.NET VERSIONS: 1.0 | 1.1
Track Link Clicks
Keep a log of when people click links that lead them away from your site.
By Josef Finsel
Q. My Web site contains a lot of links to external sites. Is there a way to track when users have clicked on a link, so I can tell which are popular?
- MA, Newport, Ken.
A: This is a fairly simple task that can be handled with a tiny bit of misdirection and a bit more redirection. Let's begin by defining the data you want to capture. Obviously you want the URL of the link. Knowing which page it came from would also be good, as would knowing the date and time the URL was clicked on. Finally, you can get the IP address and one other field that can be used for getting values from cookies such as username or some such. You'll store the data in two tables - a history table and a summary table - and you'll use a simple stored procedure to load the data into the database.
The concept of what you're going to do is simple. You need to create a page to process the URL and you need a way to pass the URL to the page. Fortunately, you can do this using something that looks like a query string. The reason you can't use an actual query string has to do with problems that arise in the parser if the link you are passing contains a query string. To get around this problem, use the RawURL and parse it. Take a look at what such a link would look like in HTML:
<a href="Redirect.aspx?http://www.aspnetpro.com">Click here</a>
With this link you can parse the RawURL to get everything after the question mark (?) and you'll be all set. So start by creating your database tables and stored procedure (you can download the complete sample project at http://www.aspnetPRO.com/download):
CREATE TABLE dbo.URLHistory (
LinkID int IDENTITY (1, 1) NOT NULL ,
URL varchar (900) NULL ,
OriginatingURL varchar (900) NULL ,
IPAddress varchar (20) NULL ,
DateClicked smalldatetime NOT NULL ,
MiscInformation varchar (50) NULL
) ON PRIMARY
GO
CREATE TABLE dbo.URLSummary (
URL varchar (900) NOT NULL ,
Counter int NOT NULL
) ON PRIMARY
GO
If you look at the table layout for a second, you might wonder why I limit the URL sizes to 900 characters. After all, a URL could be much longer than that. SQL Server, on the other hand, has a limitation that an index cannot be larger than 900 characters long. And I have rarely encountered URLs that hit 900 characters unless they are posting large text messages, so I'm going to keep this at 900 characters - but I'll discuss some ways around this at the end of the article.
Next, you to need a stored procedure:
CREATE PROCEDURE InsertURL
@URL varchar(900),
@OriginatingURL varchar(900)='',
@IPAddress varchar(20),
@MiscInformation varchar(50)
AS
BEGIN
-- Add record to the database
INSERT INTO URLHistory(URL, OriginatingURL, IPAddress,
DateClicked, MiscInformation)
VALUES(@URL, @OriginatingURL, @IPAddress, getdate(),
@MiscInformation)
-- Check to see if the record exists in the Summary
IF EXISTS(SELECT * FROM URLSummary WHERE URL = @URL)
-- If it does, add 1 to the counter
UPDATE URLSummary set Counter = Counter + 1
WHERE URL = @URL
ELSE
-- Add a new record
INSERT INTO URLSummary (URL, Counter)
VALUES (@URL, 1)
END
This simple procedure inserts a row into your history table and updates a counter in the summary table. You'll notice that I give the second parameter, @OriginatingURL, a default value of ''." This is because testing shows if you attempt to use the redirect by typing it directly into the Address line of your browser, there is no originating URL value and the redirect attempts to use the default value of the parameter.
One last thing on the database side and you're done. When you run this article's downloadable scripts you'll be creating a new login URLCounter. This login doesn't have a password, but it does have access to run the previous stored procedure.
Now look at the page that will actually handle logging the clicks and redirecting the user. After adding a Web form named Redirect, open the server view and add a data connection that points to the server where the database is connecting using the URLCounter login. Then drag the InsertURL stored procedure onto the page. Finally, rename the connection and stored procedure and add this code to the Page_Load event.
private void Page_Load(object sender, System.EventArgs e)
{
string RedirectURL =
Request.RawUrl.Substring(Request.RawUrl.IndexOf("?")+1);
cmdInsertURL.Parameters["@URL"].Value = RedirectURL ;
cmdInsertURL.Parameters["@OriginatingURL"].Value =
Request.ServerVariables["HTTP_REFERER"];
cmdInsertURL.Parameters["@IPAddress"].Value =
Request.ServerVariables["REMOTE_ADDR"];
cmdInsertURL.Parameters["@MiscInformation"].Value ="";
connClickTracker.Open;
cmdInsertURL.ExecuteNonQuery;
connClickTracker.Close;
Response.Redirect(RedirectURL,true);
}
The first thing you do is get the value of the URL you are going to be redirecting to. This would be everything in the RawUrl from the ? on. Then set the values of the parameters of the stored procedure. From ServerVariables you get the Originating URL from HTTP_REFERER and the IP Address of the browser from REMOTE_ADDR. Although I am not passing any information for the@ MiscInformation parameter, this easily could be a user id stored in a cookie or a session variable if you desired to use such a thing.
One advantage to this form of tracking clicks is the user can still get to a link even if this Web page fails to work for some reason.
If you find you need flexibility to use URLs longer than 900 characters, you'll need to make a couple of modifications. First, add a third table to store URLS and an ID column. Then modify the History and Summary table to store the ID instead of the URL. Finally, modify the stored procedure to check for the existence of the URL or insert a new record. Because SQL Server can't have an index with data more than 900 bytes long, these searches won't use an index and there are performance penalties because things take longer. I have, however, included the tables and stored procedure in the database for you to play with to try these out.
Keep your ASP.NET questions coming to me at [email protected].
The sample code in this article is available for download.
Josef Finsel is a software developer specializing in .NET and SQL Server with Avanade, the premier global technology integrator for Microsoft solutions in the enterprise. He has published a number of VB, .NET, and SQL Server articles and, when he isn't hanging around the aspnetpro forums, you can find him working on his own take on Shakespeare's classics. He's also author of The Handbook for Reluctant Database Administrators.
