Statistics on Website

[Northern Monkey]

Ok, no shouts of "Buy a product in" as I hate doing that because I can't adapt it if needs be. I'm looking to collect stats for my members on how many people have visited their properties website and then present it to them in loverly charts etc etc.

Anyone got any good ideas of how to organise the tables so that they don't become unmanagable and are scalable? My first thoughts were:

Table<Year>
Date\Time Page referring page count

So the a record would be:

2006
00031354354 signup.php index.php 4

Does that sound ok?

Cheers NM.

[Tony Harrison]

I'm just going to assume your using MySQL. The merge table type is ideal for this sort of thing.

http://mysql.com/doc/refman/5.0/en/m...ge-engine.html

[limboInternet]

well I think webalizer is free, Ive got that and its already in nice charts and very detailed, and it shouldnt be too hard to edit if needs be, but I dont really see anything Id edit about it.

[Northern Monkey]

Apologies, yep am using MYSQL and Php. I use webalizer myself but want to give user friendly charts etc based on my own database.

NM.

[Northern Monkey]

Ok,

Have just started to write the simple bit of code and the database is annoying me. Say I have the following data coming in scenario:

Time LandingPage Referer ReferalPage Pviews Keywords
01:00 - 02:00
index.php MSN n\a 1 villa holiday
villa.php Google n\a 1 villa
villa.php internal index.php 1 n\a
index.php MSN n\a 1 caravans
index.php Google n\a 1 caravans

Then surely my database would need to be consolidated to:

Time LandingPage Referer ReferralPage Pviews Keywords
01:00 index.php MSN(2), Google(1) NULL 3 villa holiday(1), caravans(2)
01:00 villa.php Google(1), Internal(1) index.php(1) 2 caravans(2)

The only thing I'm worried about is I'll be forever pulling records from the database, breaking the columns apart (i.e. splitting Google(1),MSN(2) into an array, incrementing it and putting it back) and then putting them back and when (hopefully) I'll be getting thousands of visitors a day won't there be some confliction?

Any ideas as I have brain ache!

NM.

[Tony Harrison]

No you don't have to change it, you can insert the data as is, then use the GROUP BY syntax to get aggregated stats, a much easier and inexpensive method.

so a query to find the most popular keyword and show frequency for a page might look like:

SELECT keywords, count(*) As frequency
FROM statsTable
WHERE landingPage = 'index.php'
GROUP BY keywords
ORDER BY frequency DESC

[Northern Monkey]

So your saying just log every hit by the nanosecond and then use the statement you mentioned to group it all together? Wouldn't the tables grow to be huge?

[Tony Harrison]

If you use the merge storage engine you can spread each month (for example) over multiple tables. Providing you create the appropriate indexes and optimise your queries MySQL can handle millions of rows comfortably.

[Northern Monkey]

Cheers Tony,

So now for the real cheek. I get what your saying, what do you think of my column headings? Any suggestions on improving them?

NM.

[Tony Harrison]

The names are fine, as long as you know what they mean.