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.

why reinvent the wheel? take a look at piwik.org or big brother google analytics ;) — cypherabe
– cypherabe, Commented Jun 13, 2012 at 12:25
You can reduce the number of new rows if you don't want too accurate analytics. Like instead of store activities by timestamp (int 10, I suppose), you can group/store them by day. It's not a "definitive" solution by the way. — HBv6
– HBv6, Commented Jun 13, 2012 at 12:27

Lars · Accepted Answer · 2012-06-13 12:29:43Z

3

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

answered Jun 13, 2012 at 12:29

Lars

5,8194 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

drandom Over a year ago

Thanks for your response,Lars. I already have G-Analytics on the site, but I need to build a custom page where each user that has an account with us can login and see their own statistics (rather than site-wide statistics). So you would save every action to a new row in the database with a timestamp? then use MySQL to only show data for last 30 days, 12 months etc...?

drandom Over a year ago

Is storing every individual pageview and link click with a timestap a scalable solution? I could end up with millions of rows of data pretty quickly, no? thanks again for your time Lars

Lars Over a year ago

Yes, your system will already to that in the weblogs of your webserver, which is actually slower than putting the data into your favorite SQL database. As the logic part will only be done while viewing the statistics, you may create a cache table instead of a view, which is regenerated every minute or so and discard logdata, after it isn't needed anymore (or reduce it to cumulated data for later reference, e.g. to daily information after 6month). I believe, PiWik has the ability to have clients, so you could use it to distinguish different websites from different clients of yours.

Collectives™ on Stack Overflow

Tracking Users - Custom PHP/MySQL Website Analytics

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related