Click counting

Stressed

OK i am trying to decide on the best way to count click data on our site. I know a lot of people write it out to a file then run a cron job to import into the database, but I am wondering if that is really necessary. I am talking about a lot of traffic, hundreds of thousands of clicks per day. If any of you have had experience with counting clicks on high traffic sites, what is your experience? Using shared memory then dumping to a db, using text files then dumping to a db or just straight up using a database?

drawmack

I would just use a db and then create a table that had four columns.

clicks
    id : bigint ->auto_increment
    page : varchar(255)
    ip : varchar(20)
    clicked : timestamp
    primary key (id)
    index page
    index ip
    index clicked

Then just create a php script like this:

<?php
/*connect to the database or ensure that you are connected*/
$file = explode('/',$_SERVER['FILE_NAME'];
$file = $file[(count($file)-1)];
$sql  = "INSERT INTO clicks (page,ip) VALUES (";
$sql .= '"' . $file . '",';
$sql .= '"' . $_SERVER['REMOTE_ADDR'] . '")';
mysql_query($sql);
?>

Then just include this file in all your other pages and you're click tracking like a mad man. The reason I also grab IP is so that you can expound upon the reports generated and see what paths people are following through the site. You may also wnat to grab the referrer so you can get an idea what pages people are originally coming to your site through.

Stressed

Originally posted by drawmack
I would just use a db and then create a table that had four columns.
clicks
    id : bigint ->auto_increment
    page : varchar(255)
    ip : varchar(20)
    clicked : timestamp
    primary key (id)
    index page
    index ip
    index clicked
Then just create a php script like this:
<?php
/*connect to the database or ensure that you are connected*/
$file = explode('/',$_SERVER['FILE_NAME'];
$file = $file[(count($file)-1)];
$sql  = "INSERT INTO clicks (page,ip) VALUES (";
$sql .= '"' . $file . '",';
$sql .= '"' . $_SERVER['REMOTE_ADDR'] . '")';
mysql_query($sql);
?>
Then just include this file in all your other pages and you're click tracking like a mad man. The reason I also grab IP is so that you can expound upon the reports generated and see what paths people are following through the site. You may also wnat to grab the referrer so you can get an idea what pages people are originally coming to your site through. [/B]

Yeah I think my concern was more of a usage one. Ie., on a high traffic site is counting each click right to the db going to kill the database? I figured it might be easier on the database to count to a file then import every X minutes. Right now we have multiple web front ends running so logging directly to a db would be great and save time and ensure stats are accurate, however, it could also kill the db with 2-3 webservers all sending traffic like mad.

drawmack

I would probably have each site maintain it's own database and then update to a global database on a schedule.

That way you get the best of both worlds. No file maintenence headaches and the database server shouldn't be overloaded with requests.

yelvington

High traffic sites generally outsource this to a company that can provide the necessary analytical tools. Logging really doesn't solve the problem; you just wind up with a mountain of data, not information.

http://www.omniture.com/
http://www.hitbox.com/
http://www.webtrends.com/

and so forth. They all employ embedded-pixel tracking methods, which automatically despiders your data.