Forum Moderators: martinibuster
None of the client-side (your pc) monitors that I have tried seemed to do exactly what I wanted and leave a nice little log, most were written in script languages I'd rather disable entirely in windows to prevent viruses - and also there is downtime on your home PC if you crash, turn it off or are upgrading hardware etc.
So I whipped up some basic PHP to do it and I am using a cron job.
Would anyone like to see the code? I am sure it can be vastly improved by some of the pros around here ;) You could make it email you when it reaches certain highs or lows, etc. (using some IM libraries out there for PHP, you could even make it IM you).
Note, this is NOT click tracker code (that's already been done).
Now I just need to find a way to make pretty graphs :)
Now I just need to find a way to make pretty graphs :)
I wrote a perl script to do the same thing when I started with Adsense a while ago. To do graphs, I dump the data into a round robin database (RRD) and generate the graphs using rrdgraph. The nice thing about rrds is that you can keep a list of events (ie changed ad placement), and it will draw vertical lines at the appropriate place so you can see the effects of changes (if any).
Sean
<?
$username="email@address.com";
$password="adsensepassword";
$cookie="/home/serverpath/public_html/adsense/cookiefile";
$log="/home/serverpath/public_html/adsense/adsense.log.txt";
$destination="/adsense/reports-aggregate?product=afc&dateRange.dateRangeType=simple&dateRange.simpleDate=today";
$postdata="destination=".urlencode($destination)."&username=".urlencode($username)."&password=".urlencode($password)."&null=Login";
$ch = curl_init();
curl_setopt ($ch, CURLOPT_URL,"https://www.google.com/adsense/login.do");
curl_setopt ($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt ($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)");
curl_setopt ($ch, CURLOPT_TIMEOUT, 20);
curl_setopt ($ch, CURLOPT_FOLLOWLOCATION,1);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_COOKIEJAR, $cookie);
curl_setopt ($ch, CURLOPT_COOKIEFILE, $cookie);
curl_setopt ($ch, CURLOPT_POSTFIELDS, $postdata);
curl_setopt ($ch, CURLOPT_POST, 1);
$result = curl_exec ($ch);
curl_close($ch);
preg_match('/\<tr class\="totals"\>.*\<td.*\>.*\<\/td\>.*\<td.*\>(.*)\<\/td\>.*\<td.*\>(.*)\<\/td\>.*\<td.*\>(.*)\<\/td\>.*\<td.*\>(.*)\<\/td\>.*\<td.*\>(.*)\<\/td\>.*<\/tr>/simU', $result, $array);
foreach ($array as $key => $value) {$array[$key] = trim($value, "\x22\x27\n\r ");} // strip $ % , here if desired
list($full,$Impressions,$Clicks,$Rate,$CPM,$Earnings) = $array;
putenv('TZ=US/Pacific'); // match Google time no matter where you or your server live
$output=date("Y-m-d H:i:s")." \t ".$Impressions." \t ".$Clicks." \t ".$Rate." \t ".$CPM." \t ".$Earnings."\r\n";
if ($handle = fopen($log, 'a')) {fwrite($handle, $output); fclose($handle);} else {echo "error writing";}
?>
[edited by: amznVibe at 6:57 pm (utc) on Jan. 19, 2005]
I did it all with WWW::Mechanize, and the parsing with HTML::TokeParser (I don't recommend the latter)
Updating an RRD and then emailing the results is pretty trivial:
[perl]
my $cmd = sprintf ("/usr/bin/rrdtool update adsense.rrd --template impressions:clicks:earnings %d:%0.2f:%0.2f:%0.2f", time()-$seconds, $results[0], $results[1], $results[4]);
system "$cmd";
system "echo ¦ mail -s \"Google earnings \$ $results[4] $results[0] $results[1]\" me\@example.com"
[/perl]
And then to generate the graph:
[perl]
RRDs::graph ("$outdir/adsense-month.png", "-s " . ($now - $month),
'-v clicks/impressions',
'-t Monthly AdSense summary',
"DEF:clicks=adsense.rrd:clicks:AVERAGE",
"DEF:imp=adsense.rrd:impressions:AVERAGE",
"AREA:imp#dddddd:Impressions",
"LINE1:clicks#00FF00:Clicks",
"COMMENT:\\s",
"COMMENT:\\s",
valid_events($now-$month),
"COMMENT:\\s",
"COMMENT:\\s",
"COMMENT:Last Updated: $str_now",
);
[/perl]
PM me with your email address if you want the actual code -- it's super ugly but it does the job.
The code could also be improved to "defeat" Google's caching of the data.
(If you check every 15 minutes with the exact same request it tends not to recalculate it unless you give it a different date range).
Is there a quick and dirty way in PHP to strip those high level UTF8 (or is it unicode) characters in the CSV data?
On the code above, change the $destination string to this:
$destination="/adsense/reports-aggregate?product=afc&dateRange.dateRangeType=simple&dateRange.simpleDate=thisweek&csv=true";I also changed it to use "thisweek" instead of "today" so it could capture the last few clicks of the previous day since Google lags
and change the code after curl_close($ch); to this:
$array=preg_split('/\n/simU',preg_replace('/[\x00]¦[\x80-\xFF]/simU','',$result));note: remember that this forum software changes ¦ characters, you'll have to fix the one in the first line manually
list($full,$Impressions,$Clicks,$Rate,$CPM,$Earnings)=preg_split('/\t/simU', $array[count($array)-3]);
putenv('TZ=US/Pacific'); // match Google time no matter where you or your server live
$output=date("Y-m-d")."\t".date("H:i:s")."\t".$Impressions."\t".$Clicks."\t".$Rate."\t$".$CPM."\t$".$Earnings."\r\n";
if ($handle = fopen($log, 'a')) {fwrite($handle, $output); fclose($handle);} else {echo "error writing";}
I know this code can be improved, please share your changes here :)
I still haven't coded the "cache defeat" which will require random picking of a date range. Set your cron job accordingly to how often you know your stats update. I have it at 15 minutes and rarely get old repeating data but 20 minutes should also be good for many people.
Last but not least, this data would probably be even more useful in a mysql database but flatfile will do for now (and can be imported later).
even more useful in a mysql database
I regret I can't help much here, but I am following this with some interest.
Outputting "INSERT into ADSENSEDB.... [var]..[var] etc" lines into a .sql file as well as your log would be pretty easy. Then one commandline entry (or just called direct from your script) and you could have the lot dumped to MySQL as a batch process.
Assuming you had the data in MySQL - how do you go about charting it? I presume there are standard open-source tools around for taking an SQL DB and auto-generating graphs?
TJ
Here is the google data-cache defeating code, replace the $destination line with these lines:
putenv('TZ=US/Pacific'); $randate=mktime(0, 0, 0, date("m"), date("d")-rand(3,26),date("Y"));
$destination="/adsense/reports-aggregate?product=afc&dateRange.dateRangeType=custom&dateRange.customDate.start.month=".date("n",$randate)
."&dateRange.customDate.start.day=".date("j",$randate)."&dateRange.customDate.start.year=".date("Y",$randate)
."&dateRange.customDate.end.month=".date("n")."&dateRange.customDate.end.day=".date("j")."&dateRange.customDate.end.year=".date("Y")."&csv=true";
That selects a random date in the past (up to 26 days max to keep the returned data short) to attempt to force google to recalculate today's numbers.
[edited by: amznVibe at 2:10 pm (utc) on Jan. 20, 2005]
curl_setopt ($ch, CURLOPT_FRESH_CONNECT, TRUE);
For the caching issue?
Not saying it will work, cause I really don't know :) But the manual says:
"TRUE to force the use of a new connection instead of a cached one."
Nice script!
If they see the exact same request that you did recently, they just return the same data.
I could be wrong, but I think they do this on purpose to keep their database from overloading ;)
I have never been able to get this direct PHP way to work but in theory:
/usr/bin/php -e /full/local/path/to/script.php
so I use this backhanded but fail-safe way:
wget -O /dev/null http://yourwebserver.com/path/script.php
If anyone is positive on how to make the first way work with cpanel, let me know.
I should probably also remind people to chmod the log file to 777 if you want to upload/download/manipulate the file via ftp.
I have also just changed the last code line to this:
$pw_info = posix_getpwnam ("server_user_name"); $uid = $pw_info["uid"]; posix_setuid ($uid);
if ($handle = fopen($log, 'ab')) {fwrite($handle, $output); fclose($handle);} else {echo "error writing";}
The first new line changes PHP to use your own username's permissions instead of the root so you can delete the file or edit it if you wish via ftp. The second line was simply edited to use binary mode for better compatibility on various servers.
I probably should post a new full copy of the program now ;)
I will start working on the ImageMagicK graphing and mysql backend.
/usr/local/bin/php /home/username/public_html/script.php
Any thoughts?
Assuming you had the data in MySQL - how do you go about charting it? I presume there are standard open-source tools around for taking an SQL DB and auto-generating graphs?
Excel would be the easiest way. In Perl there are several graphing modules (GD::Graph). A quick google for "php line graph" came up with a few classes to handle graphing in PHP (CGraph looks promising).
Sean
I simply cannot get the login part to work.
If I output the result after CURL fetched it, I get the invalid username or password screen. And yes, I entered the right username and password.
Very strange. Mine works fine of course. Anyone else having that problem?
You are using your username as your full adsense email address right?
Are you running this on an apache or microsoft box?
Some people do not have ssl support with their curl, unsure if that might be it.
As far as graphing, I am finding a dozen free (GPL) php graphing packages:
[phplot.com...]
[sourceforge.net...]
[aditus.nu...]
CURL support
enabled
CURL Information
libcurl/7.10.5 OpenSSL/0.9.7b ipv6 zlib/1.1.4
Im on OS X.
Ill muck around with it some more. See if I can get it to work.
Oh, and yes. Im using the right user/password. Even tried copy/paste my username directly from the box on the Adsense site.
Update: You know those times when you cant get something to work and you are 100% certain that its not your fault? Must be something in the atmosphere interfering with the code at execution, but definitely not a case of you forgetting what password to use where.
Consider me humiliated.
Three cut&paste's, a quick creation of a table in MySQL and maybe a change of the username/password/database variables in the function, are all it takes.
Put the following function at the top of the file:
function myq($sql)
{$db_user = ''; // Username
$db_pass = ''; // Password
$db_dbname = 'adsense'; // Database name
$db_server = 'localhost'; // Servername of database, may need IP if port is nonstandard
$db_type = 'mysql'; // Type of the DB. Dont know if anything else than MySQL will work
$db_port = '3306'; // Port 3306 is the default. Change only if DB is on a nonstandard port
require_once('DB.php');
$db_dsn = $db_type.'://'.$db_user.':'.$db_pass.'@'.$db_server.':'.$db_port.'/'.$db_dbname;
$db = DB::connect($db_dsn);
if (DB::isError($db)) {
die ($db->getMessage());
}
$result = $db->query($sql);
if (DB::isError($result)) {
die ($result->getMessage());
}
return $result;
}
And the following line after the "Putenv"-line:
myq('INSERT INTO `results` (`date`,`time`,`impressions`,`clicks`,`rate`,`cpm`,`earnings`) VALUES ("'.date("Y-m-d").'","'.date("H:i:s").'","'.$Impressions.'","'.$Clicks.'","'.$Rate.'","'.$CPM.'","'.$Earnings.'")');
The sql-code for the table is here:
CREATE TABLE `results` (
`id` int(11) unsigned NOT NULL auto_increment,
`date` varchar(35) default NULL,
`time` varchar(35) default NULL,
`impressions` int(11) unsigned default NULL,
`clicks` int(11) unsigned default NULL,
`rate` varchar(25) default NULL,
`cpm` varchar(255) default NULL,
`earnings` varchar(255) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
Hmm... Cant see how they would, but Ill better write and ask them.
same thing if you log from home, the clicks better not come from that ip either
its the remote IP they care about (the visitor to your site)
if that IP is your home or server IP clicking, then you have a problem
[edited by: amznVibe at 5:00 pm (utc) on Jan. 22, 2005]
My code is the first PHP code I know of to do this but its far from the first logger that Google knows of and permits.
My goal is to give a report how the day is progressing compared to other days,
also to show how the week is doing compared to other weeks (which of course you can do now with the csv data and a spreadsheet).
I was thinking of doing a chart like awstats does for hourly usage, peak hour etc.
Wouldn't even need a graphing package, just stretch a colored .gif to the right length for each hour.
Now I just have to figure out the proper mysql queries.
/*
CREATE TABLE tblAdsenseIncome (
ID INT unsigned NOT NULL auto_increment PRIMARY KEY,
Date DATETIME,
Impressions INT unsigned default NULL,
Clicks INT unsigned default NULL,
Rate VARCHAR(9) default NULL,
CPM VARCHAR(9) default NULL,
Earnings VARCHAR(9) default NULL
)
*/
$DB_HOSTNAME="localhost"; $DB_DATABASE="serverusername_dbname";
$DB_USERNAME="serverusername_dbusername"; $DB_PASSWORD="dbpassword";
$query = "INSERT INTO tblAdsenseIncome ".
" (Date,Impressions,Clicks,Rate,CPM,Earnings) ".
" VALUES (".
" '".date("Y-m-d H:i:s")."',".
" '".$Impressions."',".
" '".$Clicks."',".
" '".str_replace('%','',$Rate)."',".
" '".$CPM."',".
" '".$Earnings."')";
$link = @mysql_connect($DB_HOSTNAME,$DB_USERNAME,$DB_PASSWORD) or die("Unable to connect");
@mysql_select_db($DB_DATABASE) or die("Unable to select");
$result = @mysql_query ($query) or die ("Invalid query: $query");
@mysql_close($link);
