Managing Site Visits In Database

[eluser]Jay Logan[/eluser]
So I built a custom analytics web system. Every time I get a page hit on any of my sites, it stores the record in a database. Everything works perfect but I foresee a problem. I've had it up and running for about 2 months now and the table is 20MB. I feel like with time, this table will become too big to ever back up or move. Is there any other solutions where I can store visitor data? Keep in mind that I use a visitors "conversion path" when looking at lead information so I need to be able to join customer records with stat records.

[eluser]skunkbad[/eluser]
Perhaps you can archive monthly records older than 90 days, and then delete them from the table(s)? If you need this data in the future, you could always back up to a local machine for review. If they are auto incremented, archives from month to month could be put back together for analysis.

I use a service, statcounter, and it only allows for the last 500 visitors. Most of the time this is about 10 days worth or stats, because my site is quite low traffic. I review the stats frequently, so the old stats would mean nothing to me. How long do you need to go back?

A while back I was looking to build a custom analytics system too. It would be nice to see your system in the wiki.

[eluser]Nerijus[/eluser]
Why not using Google Analytics?

[eluser]Jay Logan[/eluser]
There is no telling how far we may need to go back. The thing is, we have a big network of web sites that act as lead farms in different markets. And when a customer fills out a form it creates a record for that customer that joins to all of their records in the PAGE HITS table which tells us what pages the user has visited and when. I've recently noticed customers who have filled out forms in 2007 and then again this year. So if we had PAGE HIT data from 2007, it would be nice to show it. And sometimes, it's fun to query how much traffic we've gotten the past month or for the whole year.

Right now, as you probably can tell, the system is pretty custom. So releasing it in the WiKi would take some downsizing. I'm thinking about releasing the entire lead farm CMS though. It's more of a Franchise management system.

[eluser]Jay Logan[/eluser]
And I don't use Google Analytics only because of the custom conversion path tracking and ability to track page hit data for over 100 web sites. Haven't quite mastered their API.

[eluser]eokorie[/eluser]
Well there are other options that you could consider, PIWIK is a pretty good analytics system. Been studying their API and it seems pretty reasonable.

[eluser]BrianDHall[/eluser]
Well, here's an idea. Create a new table for each time period, such as one a year or one a quarter, whatever you need to keep the table size manageable. Then once the time period is over that table will never get bigger, need only be backed up a single time, and you can optionally query multiple time period tables when you want to reach waaaay back.

Otherwise for only recent data the query would be a lot faster, you needn't do full backups of old data and can just do per-table backups if you would like, yet you don't lose anything or lose any functionality.

You could also optionally restrict who can do long-ago queries if they should become 'dangerous', meaning the query gets really resource intensive, so you can prevent just anyone from doing unlimited queries that could bring the server to its knees. You could just make sure a user authorized for old records appreciated the intensive nature of what they were asking - just as an extra safeguard.

So you might have tables with prefix analytics_, then:
analytics_2009|2010 or 2009_quarter1|2009_quarter2, etc.