— note this will track hits not unique visitors.
select @VisitorsToday = count(*) from RecentVisitors
delete from AggregatedVisitors where
websiteID = @websiteID
delete from recentVisitors where dateDiff(day,Date,getdate())>1
var WebsiteID = 1;
var Tracker = window.document.images["Tracker"];
Tracker.src = "default.aspx?WebsiteId=" + WebsiteID + "&Referrer=" + escape(document.referrer);
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 + ")";
Bitmap bmpCanvas = new Bitmap(1,1);
Response.ContentType = "image/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
order by date desc
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.