views:

79

answers:

2

I've created a web tracking system that simply insert an event information (click or page view) into a simple SQL server table:

 Column    |  Type       | NULL?
-------------------------------------
RequestId  | bigint      | NOT NULL
PagePath   | varchar(50) | NOT NULL
EventName  | varchar(50) | NULL
Label      | varchar(50) | NULL
Value      | float       | NULL
UserId     | int         | NOT NULL
LoggedDate | datetime    | NOT NULL

How can I harvest/analayze/display this raw information?

A: 

First decide what trends you are most interested in. Perhaps looking at some existing web analytics software - there is free software available - to see what options exist.

If your requirements are simple, you have enough data. If you want a breakdown of which countries are accessing your website, you need to log IP addresses and get a database that ties IP ranges to countries - these are not 100% reliable but will get you fairly good accuracy.

Some simple examples of reporting you can do with your current data:

  • Number of hits per hour, day, week, month
  • Top 20 accessed pages
  • Top Users
  • Number of users accessing the site per hour, day, week, month
  • etc.

Most of these you can pull with a single SQL query using the group by clause and date functions.

Example MS SQL Server query to achieve hits per day (untested):

SELECT COUNT(RequestID) AS NumberOfHits, 
  YEAR(LoggedDate) AS EventYear, 
  MONTH(LoggedDate) AS EventMonth, 
  DAY(LoggedDate) AS EventDay
FROM MyTable
GROUP BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)
ORDER BY YEAR(LoggedDate), MONTH(LoggedDate), DAY(LoggedDate)
Bork Blatt
I know I can do that, but I hoped for some existing software to generically analyze my raw data.
Eran Betzalel
OK - but that's not really a programming question is it? Google Funnel Web Analyzer.
Bork Blatt
I don't know of any web analyzers that can work over custom tables. I would suggest you either roll your own, or make your data conform to the standard web log formats supported by most of these programs.
Bork Blatt
A: 

Maybe Logparser is sufficient for your needs: http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Filburt
It looks like a good idea, but sadly - it does not support SQL input (only as output).
Eran Betzalel