Home > Uncategorized > Visitor Tracker in ASP.NET

Visitor Tracker in ASP.NET

Visitor trackers aren’t new, and even novice HTML coders can place a free tracker in their webpage, but there comes a point when
you outgrow what’s available free, and you really need to get hold of the raw data behind your tracker.
 
Recording data for every visitor to the site may be out of the question, as you end up filling your database with too much archival data. Generally a tracker will keep detailed information about the last day’s traffic, then aggregated information for archival material. For example, it might be of interest to view which websites the last 20 visitors came from, but just a count of how many people came to the site last march might be sufficient.
 
I therefore created two tables in my database, RecentVisitors and AggregatedVisitors. With a trigger set up between the two, to automatically populate the AggregatedVisitors table based on new inserts into the RecentVisitors table, thus:
 
CREATE TRIGGER trigger_RecentVisitors
ON RecentVisitors
FOR INSERT
AS
BEGIN
 — note this will track hits not unique visitors.
 declare @websiteID int
 select top 1 @websiteID = websiteid from inserted  
 declare @VisitorsToday int
 select @VisitorsToday = count(*) from RecentVisitors  
 where dateDiff(day,Date,getdate())=0
 and websiteId=@websiteID
  
 delete from AggregatedVisitors where
 websiteID = @websiteID
 and
 Datediff(day,date,getDate())=0
 insert into AggregatedVisitors (Number,websiteID)
 values (@VisitorsToday,@websiteID)
 
 delete from recentVisitors where dateDiff(day,Date,getdate())>1
 
END
 
 
I then, put together some javascript to load a hidden image, which would execute an insert into RecentVisitors
 
<img name="Tracker" style="display:none">
 <script language="javascript">
 var WebsiteID = 1;
 var Tracker = window.document.images["Tracker"];
 Tracker.src = "default.aspx?WebsiteId=" + WebsiteID + "&Referrer=" + escape(document.referrer);
 </script>
 
To return a 1px x 1px image from asp.net is really easy, and combined with an insert into the database, I came up with:
 

if (Session["FirstTime"]==null)
{
   string strReferrer = Request.QueryString["Referrer"].ToString();
  
int intWebsiteID = Convert.ToInt32(Request.QueryString["WebsiteID"]);
  
string strSQL = "insert into RecentVisitors (IP,Referrer,websiteId) values (";
   strSQL += "’" + Request.ServerVariables["REMOTE_ADDR"] + "’,";
   strSQL += "’" + strReferrer.Replace("’","”") + "’,";
   strSQL += intWebsiteID + ")";
  
base.ExecuteNonQuery(strSQL);
   Session["FirstTime"]=
false;
}
Bitmap bmpCanvas =
new Bitmap(1,1);
Response.ContentType = "image/gif";
bmpCanvas.Save(Response.OutputStream,ImageFormat.Gif);

ExcecuteNonQuery is a function I wrote to run a statement against the database, and is outside the scope of this blog.

Then, to view this data in a meaningful way, I created a page with two dataGrids, dgRecentVisitors and dgAggregatedVisitors, which used the following SQL to display their results:

select top 20 rv.id,referrer,date from recentvisitors rv
join websites w on w.id = rv.websiteid
where charindex(w.websiteaddress,rv.referrer)=0
order by date desc

and

select * from aggregatedVisitors order by date desc.

and, that suffices for a tracker for my purposes, and I have access to the raw data, should I need to run queries to work out sales conversion rates etc. The only downfall is that it does not correctly measure visitors rather than hits, but that’s work for another day.

Njoy.

 

 

 
 
 
 
 
Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: