Tracking clicks .. slow stats :(

[aalaap]

I need to store visitor data for every ad displayed at every page load - every text link, and ad image. Every ad is identified by an adcode - that is the main index in the 'ads' table.

While making the database I pondered over simply incrementing the count of a hits row everytime a click is made ... but I didnt do that because I need to be able to track raw and unique clicks seperately. I.e. if the page is loaded 100 times, the impressions row value is 100. But there's no way I can find out if all those 100 were from the same guy refreshing his browser 100 times or actually 100 different people coming.

So to be able to tell that, I decided to add a row to the hits table for each click or impression, along with the ip address. This has become a problem, since one pageload makes several entries into the hits database (as different ads in the page get loaded, they all make their respective impression entries + the clicks after that...).

Is this wrong? Is there any otherway to track the same data? Applications like webalizer take raw flat-file data from webserver logs and process them live to generate information. How do those work so fast?

• Aalaap

[matjaz]

i do such things with search engines - keep track of clicks/unique clicks/proxy clicks, etc.

i dont count number of total clicks but make log [record] of every click made. click data look like:

• timestamp
• ip
• unique/not-unique (*1)
• proxy click or not

you might count total clicks in user's table as well or you create special table and keep clicks for each month, week, whatever you need. just make a good plan and use "log" tables so you wont put everything in final "clicks" table coz you will have terrible time when something will go wrong and your table will be HUGE! try to avoid this - i mean it. i had serious headache because of huge tables with corrupted indexes, etc..

*1 = to determine if click was unique or not, you can use another "log" table which can be cleaned regulary.

if you need more help, send a private message... coz right now i am in a hurry... oh god, i have to run... laundry is waiting...

[matjaz]

and keep track if a click was direct click or proxy click... your members will want to know. and you will need that info when some of them will try to scam your sistem, sending you 100.000 clicks per day through proxy servers 🆒

[Sxooter]

/me agrees, log analysis is the best way to get statistics.

Now, if you need real time numbers for things like automatically blocking flooding clients and such, then you put it in a table and track that.