Tracking Users - Custom PHP/MySQL Website Analytics

Question

I run a local directory website (think yelp/yell.com etc) and need to provide analytical data to the businesses listed on the site.

I need to track the following:

1) Number of visitors to specific pages (ie: Jim's widgets was viewed 65 times)

2) Number of times a user clicks a link (ie: 25 users clicked to visit your website)

I am able to do this by simply adding one to the relevant number every time an action occurs.

What I would like to be able to do is split this into date ranges, for example, last 30 days, last 12 months, all time.

How do I store this data in the database? I only need the theory, not the code! If someone can explain the best way to store this information, I would be extremely grateful.

For example, do I use one table for dates, one for the pages/links and another for the user data (links clicked/pages visited)? The only solution I have so far is to add a new row to the DB every time one of these actions happens, which isn't going to scale very well.

Thanks to anyone that can help.

Answer 1

I would not reinvent the wheel and use an already available solutions such as Piwik. It can actually read your normal weblogs to provide all the information you asked for.

If for some reason you still need a custom solution, I would not save the tracking data in ranges, but rather use exact time and url-data for each individual page call (what your normal weblog provides). The cumulated data should be generated on-the-fly in your logic section, e.g. through a SQL-view:

SELECT count(url),url
FROM   calllog
WHERE  calldate > NOW()-30days