I am a statistics freak. I can’t say anything else :-)
But I guess that most of you, my fellow webmasters love to check your adsense stats every now and then every every day, and probably every hour also…

Why not let your own script fetch the statistics directly from google adsense reporting site? It’s always much easier to have the data locally in your own database, and then create scripts to display the stats in the way you are interested in.

I will now – totally free, no strings attached- give you the code that collects the present stats from adsense, for any channel you specify. Because I also love to work with databases, we will store the results in mysql.

The basics behind the script is that you choose what channels you want to fetch stats for, and then you let your script do that as often as you which. You can schedule this script with crontab or something similar as often you which. Don’t do it more often than every 15 minutes, because I have heared something from google that you are not allowed to script things mroe often against adsense…

Well,no more talking … lets get to work!

Here’s what to do:

  1. Login to adsense and click on “advanced reports”. Create channels if you don’t have any yet, because this script shows stats for separate channels.
  2. This part might be the most complicated, but still very easy. “View source” for the page and search for select name=”reportId”. Scroll further out on that row in the source and look for your channel names. You will find something similar to this Cholesterol-week . Write down the number and the name on a paper or somewhere. Do this for every channel you want to track.
  3. Create a database and assign a user with a password for that.
  4. Run this sql code: CREATE TABLE `adsense` (
    `id` int(11) NOT NULL auto_increment,
    `channel` varchar(50) NOT NULL,
    `reportedWhen` datetime NOT NULL,
    `impressions` int(11) NOT NULL,
    `clicks` int(11) NOT NULL,
    `rate` decimal(10,3) NOT NULL,
    `cpm` decimal(10,3) NOT NULL,
    `earnings` decimal(10,3) NOT NULL,
    PRIMARY KEY (`id`)
    )
  5. Save the file you see in the blue box below as adsense.php or similar. Look at the first lines where you need to fill in your details. Both for your adsense account and your db details.
  6. In the file you need to enter all channelId’s and channelNames in the array. Just increase that array if you have more channels!

I have not created any script to display the stats yet, but that might come in the future :-) For now, enjoy the script and let me know if you like it!

Here is the script:

<?php
// Change things from here!!!
$adsenseUsername    = "yourname@yourcompany.se";
$adsensePassword    = "yourpassword";

$dbHostname    = "localhost";
$dbUsername    = "dbUsername";
$dbPassword    = "dbPasswd";
$dbName            = "theDbName";

$reports    = Array("1234"        =&gt; "channel1",
"56215"        =&gt; "channel4",
"35507"        =&gt; "channel3");
// Stop changing things here!!!

$adsenseCookie        =    "adsenseCookie.txt";

// Google time
putenv('TZ=US/Pacific');
$now    =    date("Y-m-d H:i:s");

$dbLink = mysql_pconnect($dbHostname, $dbUsername, $dbPassword) or die('Could not connect to database: '.$dbHostname);
mysql_select_db($dbName, $dbLink) or die('Could not find database: '.$dbName);

// Handle every report separately
foreach($reports as $id =&gt; $name)
{
$result    = getAdsenseReport($id);
logChannel($name, $result, $now);
}

// Only functions below, to do all the magic.

// This function takes care of the sql part, that stores channel information.
function logChannel($channelName, $data, $time)
{
global $dbLink;

$sql    = "INSERT INTO adsense(    id,
channel,
reportedWhen,
impressions,
clicks,
rate,
cpm,
earnings)
VALUES                        (    NULL,
'$channelName',
'$time',
'$data[impressions]',
'$data[clicks]',
'$data[rate]',
'$data[cpm]',
'$data[earnings]')";

mysql_query($sql, $dbLink);
}

// Gets the report for one channel (reportId).
// Formatting the respons in a pretty way, so we can work with it easily.
function getAdsenseReport($reportId)
{
$reportUrl    = "/adsense/report/view-custom.do?reportId=";
$result            = getAdsenseInfo($reportUrl . $reportId);

// the next two rows must be on one row!!!
preg_match('/\&lt;tr class\="totals"\&gt;.*\&lt;td.*\&gt;.*\&lt;\/td\&gt;.*\&lt;td.*\&gt;(.*)\&lt;\/td\&gt;.*\&lt;td.*\&gt;(.*)\&lt;\/td\&gt;
.*\&lt;td.*\&gt;(.*)\&lt;\/td\&gt;.*\&lt;td.*\&gt;(.*)\&lt;\/td\&gt;.*\&lt;td.*\&gt;(.*)\&lt;\/td\&gt;.*&lt;\/tr&gt;/simU', $result, $array);

foreach ($array as $key =&gt; $value)
{
$array[$key] = str_replace(Array("USD", "%", "."), "", $value);
$array[$key] = str_replace(",", ".", $array[$key]);
}

$info    = Array(    "impressions"    =&gt;    $array["1"],
"clicks"            =&gt;    $array["2"],
"rate"                =&gt;    $array["3"],
"cpm"                    =&gt;    $array["4"],
"earnings"        =&gt;    $array["5"]);

return $info;
}

// Accesses adsense to get the information.
// This is where all the magic happens, login and requesting the asked report.
function getAdsenseInfo($destination)
{
global $adsenseUsername, $adsensePassword, $adsenseCookie;

// Add login information to the url
$postdata    = "destination=" . urlencode($destination) . "&amp;username=" . urlencode($adsenseUsername) . "&amp;password=" . urlencode($adsensePassword) . "&amp;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);

return $result;
}
?>

That’s all for today… Let me know if you find it useful, because I love the result :-) If people are interested I will consider to create a function that displays the stats also?!?

Similar Posts:

  • Share/Bookmark