Simple Perl Scripts for Google Adsense

Have you ever noticed that you can export Google Adsense reports in CSV format?

If not, look closely at any report you have asked for, whether in the old or the newer interface: somewhere you will see "Export to CSV". If you click that, a Comma Separated Value file will download to your computer.

Well, actually it isn't: it's a tab delimited file, but that's a technical detail that you really don't need to worry about. The point is that you'll end up with a "report.csv" file that contains everything your report has.

What can you do with that file? Well., you could open it with Excel or the Open Office.org spreadsheet or even Google Docs.

You could also write scripts to analyze the data in that report.

Scripting

I prefer to use Perl for scripting.

Omigod, start the car! He's talking about programming! Hurry, up get in the car - GO, GO, GO!

Please, calm down. Yes, scripting is programming, but it's only moderately difficult and you can do so much without knowing very much.

You can also take scripts like the ones I'm going to show you here and just use them without understanding them at all.

Still with me? OK, I will try not to make it painful.

Why Perl?

I like Perl first of all because it is almost ubiquitous. You will usually find it installed on any modern operating system with the notable exception of Microsoft Windows. That's because the folks at Microsoft can't imagine that you would ever want a really good scripting tool. However, you can get Perl for Windows very easily. Don't go look at that yet, though - it's a little geeky in places and I don't want to scare you.

The other reason I like Perl is because of TIMTOWDI. That stands for "There's More Than One Way To Do It", which is an idea strongly engraved into the philosophy of Perl scripting.

If you are a person who likes structure and rigid rules, Perl may not be for you, because Perl will let you do things that would horrify other scripting languages. Not only that, but Perl will quite often give you the results you expected when you do one of those verboten things like treating a string as a number or vice versa. Perl is the friendly old uncle or grandparent who sees nothing wrong with you riding backward on your bicycle.

Many professional programmers get very upset when they see someone riding backward on a Perl bicycle. They know that it's all too easy to end up with a broken neck. However, their complaints are most valid when we are talking about larger projects or projects where multiple people will be working on the writing of the script. This is just you, and you aren't going to doing a big project. You are just going to parse some Adsense CSV files.

I'm using a Mac

Oh, my, I used a Mac to do this.

I'm sorry, but it's just so much easier this way. Yes, you love your Windows, I know. I don't even have any major gripes about Windows 7 - it's a decent operating system. Mac is better, but Win 7 isn't truly awful like every one of the earlier versions of Windows were.

If you want to argue with me about that, fine. Let me put my creds on the table first:

I've been using and programming computers since 1967. I have worked on everything from IBM 360's to the machines of the current day. I have acquired certifications in both Windows and Unix. I can write scripts and programs in just about anything you can name and some languages you never heard of. I have been there, done that, and I have the gray hairs to prove it.

I mention all that just in case you want to tell me I'm some darn fool who doesn't know anything.  That's not going to cut it, because I really have forgotten more than many a Windows user will ever know. When I tell you that a Mac is better than Windows, you have reason to give some respect to that opinion.

OK? You want to keep using Windows, that's fine. Macs are better. Get over it.

But Perl is Perl

Perl is Perl. Most Perl programs will work as written on Windows, Mac or Linux and Unix. Yes, you have to sometimes fudge a few things on Windows, but those are very minor. Basically, it's all the same when you are working with scripts to tear apart files as we are here.

Unicode

One little oddity to deal with. On the Mac (and probably on Windows too), the downloaded CSV file will be UTF-16, but the Perl installed doesn't expect that by default. The transition from 8 bit ascii to Unicode is incomplete and will cause confusion until it is complete.

I don't mess with it. I just use the built-in "iconv" program to convert.

iconv -f utf-16 -t utf-8 ~/downloads/report.csv > all.csv

Now I don't have to concern myself with Unicode.

Earnings by Quarter

There are two ways we could calculate earnings by quarter.  You can get the information from a Payment report or from an "Account by Day" report.  

Let's try it with the Payment report first. If we look at the CSV file in a spreadsheet like Excel, you can see that it has what we want: Earnings.

The Equarter Script

This is the Perl script:

===

#!/usr/bin/perl
use POSIX qw(ceil);
open(I,"earnings.csv");
@all=;
close I;
$month=0;
$accum=0;
$quarter=0;
foreach ( @all) {
   next if not /Earnings/;
   s/"//g;
   s/,//g;
   @s=split /\t/;
   @y=split /\//,$s[0];
   if ($quarter and $quarter != ceil($y[0]/3)) {
    print "  Quarter $quarter $year $accum\n";
    $accum=0;
   }
   $month=$y[0];
   $quarter=ceil($month / 3);
   $year=$y[2];
   $accum+= $s[2];
   
 } 
print "  Quarter $quarter $year $accum\n";

===

A few things to notice: I had to strip out quotes (") and commas (,) from the values because I go way back to 2003 with Adsense and they used to report numbers that way ("1,215.00" rather than just 1215) and although they have cleaned up some of that, they have not done so entirely.

As I said, you really don't need to understand this script, but I hope you can see that it is short and non-threatening.

It's not an optimized script. It does some things that don't need to be done. I do that in hopes of making it easier to understand for people new to Perl. Let's just run through the general idea, shall we?

First, we open our csv file and read it into an array. That part isn't necessary - I could have read the file line by line and done this same task. However, I may expand upon this script and might want to have access to the lines again. If they are already in that array, I won't need to read the file again, so my habit is generally to read into an array.

===

use POSIX qw(ceil);
open(I,"earnings.csv");
@all=;
close I;
$month=0;
$accum=0;
$quarter=0;

===

That "use Posix" thing is just to give me access to a function that will convert 3.1 to the next highest integer: ceil(3.33) becomes 4 but celi(3) stays 3. We'll use that to figure out what quarter we are in. We also initialize three variables to zero ($month, $quarter, $accum).

I didn't need to initialize $month, but who knows - I might find that useful later, so I usually do. Perl can be told to force you to define variables so that you don't accidentally use "yare" when you meant "year", but I didn't bother to do that either.

===

foreach ( @all) {
   next if not /Earnings/;
   s/"//g;
   s/,//g;
   @s=split /\t/;
   @y=split /\//,$s[0];

===

Next, we start looking through the array. The "next if not /Earnings/;" does what you might think - if the line doesn't have "Earnings in it, we just forget it and go get the next line.

The s/"//g and the next line just strip out the junk that shouldn't be in the numbers.

The @s=split /\t/; is the first bit of Perl magic. It breaks the line into sections based on the TAB characters that are in the line. Excel did exactly the same thing to put the data in the right columns.

If you looked at the line using an editor that would show you the tabs, it might look something like this:

7/31/03^IEarnings (Jul 1 - Jul 31)^I"1,018.82"^I"1,018.82"

The ^I's are the tabs.

We do another split to break up the first section (a programmer would call that a "field"), which is the date. This time we split on "/".

That's a good example of TIMTOWTDI, by the way. I could have written that as @y=split ?/?,$s[0]; and it would have worked just as well.

===

   if ($quarter and $quarter != ceil($y[0]/3)) {
    print "  Quarter $quarter $year $accum\n";
    $accum=0;
   }

==

The next little section decides if we have reached a new quarter and, if we have, prints out what we have accumulated so far and resets the total in $accum.

On the very first line, $quarter will be 0, so "if not $quarter" will be true and nothing will happen.

===

   $month=$y[0];
   $quarter=ceil($month / 3);
   $year=$y[2];
   $accum+= $s[2];
  
 }

===

The code then decides what the month and quarter are for this line and starts accumulating money.

===

print "  Quarter $quarter $year $accum\n";

===

At the very end, we print out what's left and we are done.

Formatting

We could make the printouts a little neater by using

printf " Quarter $quarter $year %5.2f\n", $accum;

If you are fussy about such things, you should do that. We could even add commas if you are that compulsive.

Another report

We'll do a different program to look a a csv version of an "Account by Day" report. This has the dates slightly different, separated by "-" instead of "/" and the money field is in a different place.

However, the basic script is not much different:

===

#!/usr/bin/perl
use POSIX qw(ceil);
open(I,"all.csv");
@all=;
close I;
shift @all;
$month=0;
$accum=0;
$quarter=0;
foreach (reverse @all) {
   @s=split /\t/;
   @y=split /-/,$s[0];
   if ($quarter and $quarter != ceil($y[1]/3)) {
    print "  Quarter $quarter $year $accum\n";
    $accum=0;
   }
   $month=$y[1];
   $quarter=ceil($month / 3);
   $year=$y[0];
   $accum+= $s[6];
   
 } 
    print "  Quarter $quarter $year $accum\n";

===

Some results

I left out a bunch (as I said, my data goes back to 2003), but here's what it looks like:

$ ./quarter

  Quarter 3 2008 2190.21
  Quarter 4 2008 2097.22
  Quarter 1 2009 1905.2
  Quarter 2 2009 1796.76
  Quarter 3 2009 2439.62
  Quarter 4 2009 2341.72
  Quarter 1 2010 2187.02
  Quarter 2 2010 2157.54
  Quarter 3 2010 2574.19
  Quarter 4 2010 2337.37

$ ./equarter

  Quarter 3 08 2190.19
  Quarter 4 08 2097.04
  Quarter 1 09 1904.96
  Quarter 2 09 1796.46
  Quarter 3 09 2439.31
  Quarter 4 09 2341.48
  Quarter 1 10 2177.75
  Quarter 2 10 2148.12
  Quarter 3 10 2564.46
  Quarter 4 10 1833.44

Why different?

Well, the 4th quarter is different because I don't have earnings for December yet in the Payment report, but of course they are in the Account by Day.

But there are other variances: a few pennies, a few dollars. That's Google Adsense and you just have to accept it. The reports do not reconcile and it is as simple as that.

It mostly works out though - it's off a little one way here and the other way there. At the end of a year, the difference isn't much.

Too Geeky?

Yeah, I know: this stuff scares most people. But really, really, REALLY it isn't that hard and if you just take it slow you really can learn how to do very useful things with scripting.

Do you know someone who should be reading this? Click the Share button below to send it to them easily or to post it to Facebook or Twitter.