Building an advertising system with PHP, Part 3
(Page 2 out of 3)Analyzing the traffic
At the moment the traffic isn't really helpful, because we aren't getting any real statistics. Things like CTR (Click-Through-Rate) and impressions are important. We need to write another script that will analyze the raw traffic, and change it into something we can use.
This script has to do the following things:
- Collect the number of impressions of each day, week, and month
- Collect the number of clicks of each day, week, and month
- Calculate CTR for each day, week, and month
- Calculate total number of impressions, clicks and average CTR so far
Before we start writing any code, let's first create the table that will hold our stats:
`statsid` mediumint(10) NOT NULL AUTO_INCREMENT,
`type` enum('day','week','month','total') NOT NULL DEFAULT 'day',
`day` int(5) NOT NULL DEFAULT '0',
`week` int(5) NOT NULL DEFAULT '0',
`month` int(5) NOT NULL DEFAULT '0',
`year` int(5) NOT NULL DEFAULT '0',
`views` int(5) NOT NULL DEFAULT '0',
`clicks` int(5) NOT NULL DEFAULT '0',
`ctr` decimal(5,0) NOT NULL DEFAULT '0',
`ad` mediumint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`statsid`),
KEY `ad` (`ad`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
Let's start with the actual script now. Obviously, the first thing it must do is get all the traffic:
$traffic = $db->sql_query ("SELECT trafficid, datetimestamp, type, ipaddress, ad FROM traffic");
if (count($traffic) == 0) { die('No traffic logged'); }
The next step is to loop through all the traffic records, and actually do the analyzing. What the script will be doing is examining each individual record, and then updating the proper records. For example, if it sees a traffic record with the following values:
- date/time: 17/12/2005
- type: view
- ad: 1
Then it will update the following records in the stats table (all for ad 1):
- December 2005 (impressions, CTR)
- week 51 in 2005 (impressions, CTR)
- day 17/12/2005 (impressions, CTR)
- total (impressions, CTR)
The analyze script looks like this:
foreach ($traffic as $t) {
// Get values
$day = date('d', $t['datetimestamp']);
$week = date('W', $t['datetimestamp']);
$month = date('n', $t['datetimestamp']);
$year = date('Y', $t['datetimestamp']);
$ad = $t['ad'];
// Which type?
if ($t['type'] == 'view') {
$views = 'views + 1';
$clicks = 'clicks';
} else {
$views = 'views';
$clicks = 'clicks + 1';
}
records_exist($day, $week, $month, $year, $ad);
// Update day record
$db->query ("UPDATE stats SET views = $views, clicks = $clicks, ctr = ((clicks/views)*100) WHERE type = 'day' AND day = $day AND month = $month AND year = $year AND ad = $ad");
// Update week record
$db->query ("UPDATE stats SET views = $views, clicks = $clicks, ctr = ((clicks/views)*100) WHERE type = 'week' AND week = $week AND year = $year AND ad = $ad");
// Update month record
$db->query ("UPDATE stats SET views = $views, clicks = $clicks, ctr = ((clicks/views)*100) WHERE type = 'month' AND month = $month AND year = $year AND ad = $ad");
// Update total record
$db->query ("UPDATE stats SET views = $views, clicks = $clicks, ctr = ((clicks/views)*100) WHERE type = 'total' AND ad = $ad");
}
$delete .= ')';
All it does is update each record with the new values (either a new view or a new click), and it lets MySQL calculate the new CTR. Another thing worth mentioning is the records_exist() function, which is used to check whether all the records that will be updated already exist, and if necessary creates them.
One last thing the analyze script must do is delete the traffic data which has been analyzed, otherwise you might get duplicate data. The following code does what we want:
It uses the $delete var created in the record loop.
The best way to use this analyze script is to create a cronjob (or use some other scheduling program), and have this script run every 6 hours or so. That means that it won't have to analyze a lot of records at the same time (thus not causing your server to slow down), and your graphs will be up-to-date. If you want to have a look at my analyze script, click here to download the full analyze script.
Let's move on to the final part of the tutorial: actually creating some neat graphs. If you want to following along with this part, you might want to download some fake data by clicking here, or else you won't be getting any useful graphs.
