1

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?

Eran Betzalel
  • 4,105
  • 3
  • 38
  • 66
  • Maybe is just me, but it seems logical to first gather the requirements (what analysis information you need) and then design the necessary storage structure that satisfies the requirement, not the other way around... – Remus Rusanu Jan 04 '10 at 17:51
  • I'm already past that. I'm looking for something beyond the defined requirements. – Eran Betzalel Jan 05 '10 at 13:40

2 Answers2

1

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
  • 3,308
  • 2
  • 19
  • 17
  • I know I can do that, but I hoped for some existing software to generically analyze my raw data. – Eran Betzalel Jan 05 '10 at 14:46
  • OK - but that's not really a programming question is it? Google Funnel Web Analyzer. – Bork Blatt Jan 07 '10 at 09:09
  • 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 Jan 07 '10 at 09:12
0

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

Filburt
  • 17,626
  • 12
  • 64
  • 115