Previous Section  < Day Day Up >  Next Section

Hack 84. Determine the Worth of AdWords Words

Harness the Google AdWords marketplace to guesstimate the value of a keyword or phrase in online advertising.

Google AdWords create what can only be termed an advertising marketplace.

You can choose to pay more to acquire more prominence on a results page, or pay less and still see your fair share of eyeballs and clicks until your daily budget is spent. By offering to pay a little more for each click-through, you'll move yourself up a spot or two in that list of 8 to 10 Sponsored Links on Google results pages.

Surely, then, you could just be sure to pay enough to keep your ad in the top spot? Not so, actually.

In addition to the cost-per-click (CPC) you're prepared to pay, AdWords pays close attention to the click-through-rate (CTR), the proportion of how many times your ad is shown versus how many times people actually click on it. By carefully balancing the price and effectiveness of ads, Google makes sure the top spots belong to the most relevant and targeted ads, not just those with the deepest pockets.

Now, this might appear at first blush to be a game of poker, and to a certain extent it is. That said, the AdWords Traffic Estimator (see Figure 7-7, later in this hack) is like a silent partner, advising you on your bets (sorry, bids) and guestimating your average position (1.0 is the top spot, 2.0 is the second, and so forth) in that list of ads.

The AdWords Traffic Estimator is an incredibly useful tool for experienced online advertisers and newbies alike.

And—of particular interest to us hacker types—it has the nice side effect of harnessing the Google AdWords marketplace to place a very real (albeit estimated) price on the relative value of individual words or phrases. It is on this alternate use of the estimator that this hack is focused.

We'll do this two ways: by hand—clicking through the AdWords site, filling in forms, copying, and pasting—and then programmatically—turning keywords or phrases into a comma-separated (CSV) file suitable for import into just about any spreadsheet or database application that you might be running.

Why do this manually when you can have your computer do all the work for you? First, the whole AdWords process is rather well done and educative. Second, the script automates what is supposed to be the actions of a human on his way to signing up for AdWords and so employs a set of hacks and scrapes and, as such, is brittle and could well not work by the time you read this.

7.7.1. By Hand

It's quite a journey from the AdWords home page to the AdWords Traffic Generator, so we'll walk you through how to get there with the bare minimum of work.

Point your Web browser at the home page ( and click the "Click to begin" button (Figure 7-1).

Figure 7-1. Click to begin

Unless you're actually interested in specifying language or location targeting, go ahead and skip on past the "Choose your language and location targeting" page, shown in Figure 7-2, by clicking the Save & Continue button.

Figure 7-2. Choose your language and geo-targeting

You do need to pick a country or set of countries on the page shown in Figure 7-3. Again, unless you're actually interested in choosing specific countries, just click the Add button to select All Countries and the Save & Continue button to move on.

Figure 7-3. Choose your countries

On the "Create ads" page shown in Figure 7-4, you need to create a placeholder ad. Fill in the form and click the Continue button when you're done.

As an aside, notice that I've spelled Google with two too many "o"s: Goooogle. It turns out—for perfectly understandable reasons—that only Google can use the word Google (or Gooogle) in their AdWords program.

Figure 7-4. Create an ad, any ad

Now for the interesting part: choosing keywords or phrases to evaluate. Type in some number of keywords or phrases into the box shown in Figure 7-5 and click the Save Keywords button.

Figure 7-5. Choose your keywords or phrases

AdWords drops your keywords into a pretty table, shown in Figure 7-6, and gives you the chance to specify your preferred currency and how much you'd pay for a single click. Adjust these if you wish and, when you're ready, click the Calculate Estimates button.

Figure 7-6. Calculate estimates

Finally, we get to the payoff, shown in Figure 7-7: the table is filled in with reasonable guesstimates of average CPC, cost per day if you adjust for the number of clicks per day shown, and expected average position of your ad in the list of Sponsored Links on a results page in which your ad appears.

Figure 7-7. Average Cost-Per-Click (CPC)

For our purpose at hand, it's the average CPC numbers that we were after—and here you have them.

At any time, you can change the currency and maximum cost you'd be willing to consider paying per click. Click the Recalculate Estimates button to update the table. You can also change keywords by clicking the Change Keywords button and altering the keywords and phrases that you entered in Figure 7-5.

You'll find another handy tool behind the "find alternatives" links associated with each keyword. As shown in Figure 7-8, AdWords is remarkably good at finding related keywords and phrases for you. Select any number by clicking their checkboxes and click the "Ad these keywords" button at the bottom of the screen. Otherwise, click the Cancel button to leave things as they are.

Figure 7-8. Find alternative keywords

Now that you have your keywords or phrases and their respective worth in tabular format, you can simply highlight them with your mouse, as shown in Figure 7-9, and copy them as you would any other text (Control-C on Windows, Command-C on Macintosh).

Figure 7-9. Copy estimated Cost-Per-Clicks

Paste what you've copied into a text file or spreadsheet (Figure 7-10) and rearrange and clean up as you see fit. At this point you have data you can work with, save as a CSV and import into your preferred database for further analysis, or paste into an email message as a gentle nudge to your marketing department.

Figure 7-10. Paste estimates into Excel

7.7.2. Programmatically

Doing things by hand is certainly good enough if you only do so once in a while. Let's say, however, that you want to keep an eye on the relative costs of AdWords words that you're interested in buying into at some point—much as you would monitor individual stocks by building a portfolio and checking in on a regular basis or having your online brokerage email you alerts. Google, at least at the time of this writing, offers no such service: the focus of their interface is on the ads you're building right now and those you're running and maintaining on an ongoing basis.

So, let's build such a service on our own. The code

This script mimics the activity of someone manually going through the AdWords site, filling in forms, clicking buttons, and eventually copying CPC estimates and pasting them into to the screen or a CSV file. In other words, the code does just what we did by hand a scant moment ago.

You'll need to pick up and install a few prerequisite Perl modules along the way: Crypt::SSLeay for talking to the Google AdWords site on a secure channel (required by AdWords), WWW::Mechanize for automating your interaction with the Google AdWords site, HTML::TableContentParser for gleaning results from HTML tables, and Text::CSV for spitting out results in CSV format. (See [Hack #92] for guidance on installing Perl modules.)

The only one of these prerequisites that might cause you a little extra work is Crypt::SSLeay on Windows. ActiveState, makers of Active Perl for Windows, does not (at least not at the time of this writing) have permission to distribute the module as a PPM due to Canadian laws around cryptographic software. Check their "Status of the ActiveState PPM Repositories" page ( for details and alternate installation instructions.

Save the following code as

#!/usr/bin/perl -w


# Automate gleaning Google AdWords estimated cost-per-clicks (CPCs)

# Usage: perl <keyword1> <keyword2> [..]

#        perl < keywords.txt

use strict;

use WWW::Mechanize;

use HTML::TableContentParser;

use Text::CSV;

=head1 NAME

adwords_worth - Returns estimated Google AdWords cost-per-clicks (CPCs) 

of provided keywords in comma-separated (CSV) format.


# Fill up keywords.

my $keyword_string;

if( not @ARGV ) {

    # You piped in a file

    local $/ = undef;

    $keyword_string = <STDIN>;

} else { 

    # Keywords are specified on command line

    $keyword_string = join( "\n", @ARGV );


$keyword_string =~ s/\s+/\n/g;

die "No keywords specified!" unless $keyword_string =~ /\w+/;

=head1 SYNOPSIS keyword1 [..] < keywords.txt


# Set up WWW::Mechanize to die on errors.

my $agent = WWW::Mechanize->new( autocheck => 1 );

# Get initial page.

print STDERR "Fetching the Adwords initial page... ";




print STDERR "ok\n";

print STDERR "Visiting the Language and Targeting page... ";

# On Language and Targeting page.

# Defaults are okay for now.


print STDERR "ok\n";

# On country selector.

# Right now default value is "All Countries".

print STDERR "Visiting the Country selector... ";


print STDERR "ok\n";

# On Create Ad page.

# Fill in placeholder values, since it doesn't matter.

# CAVEAT: All creative lines must be spelled correctly.

# See: Adwords editorial guidelines.

print STDERR "Creating a placeholder ad... ";

$agent->current_form->value( 'adGroupName',         'groupname' );

$agent->current_form->value( 'creative.line1',      'Spelling' );

$agent->current_form->value( 'creative.line2',      'Spelling' );

$agent->current_form->value( 'creative.line3',      'Spelling' );

$agent->current_form->value( 'creative.visibleUrl', '' );

$agent->current_form->value( 'creative.destUrl',    '' );


print STDERR "ok\n";

# On Keywords page.

print STDERR "Plugging in your keywords... ";

$agent->current_form->value( 'keywords', $keyword_string );


print STDERR "ok\n";

# On Price Table Page, but no values are in the table.

print STDERR "Recalculating keyword values... ";


print STDERR "ok\n";

# Now on Price Table page, and the table now has values.

print STDERR "Gleaning keyword values and building you a CSV...\n\n";

my $p      = HTML::TableContentParser->new( );

my $tables = $p->parse( $agent->content( ) );

# Table with the values is has its class attribute set to report.

my @report_tables =

  grep { exists $_->{class} and $_->{class} eq 'report' } @$tables;

# Assuming that Google on has on report table per page.

my $table = $report_tables[0];

# Make CSV object out here instead of having loop make X of them.

my $csv = Text::CSV->new( );

# Get the rows of cells out of $table's convoluted structure.

# TODO naming of variables here is odd, but check out Dumper(\@row_cell_objs).

my @row_cell_objs = grep { $_->{cells} } @{ $table->{rows} };

my @data_cells    = map  { $_->{cells} } @row_cell_objs;

foreach my $row ( $table->{headers}, @data_cells ) {

    # Just being safe here with references.

    if( ref $row eq 'ARRAY' ) {

        # Eliminate title cell and cells in rightmost column.

        # They contain only links.

        my @table_cells =

          grep { not exists $_->{class} or $_->{class} ne 'rightcolumn' } @$row;

        my @data = map { $_->{data} } @table_cells;

        foreach (@data) {

            # Remove HTML tags and surrounding whitespace.





            # Number of clicks contains commas, but we don't want those.



        # Make a CSV line and print it.

        if( $csv->combine(@data) ) {

            print $csv->string, "\n";

        } else {

            my $err = $csv->error_input;

            print "combine( ) failed on argument: ", $err, "\n";


    } else {

        print "Row is not an array of cells!\n";



print STDERR "Done.\n";

=head1 AUTHOR

Leland Johnson <>

=cut Running the hack

You can invoke the script in two ways. The first is to pass keywords or phrases on the command line ["How to Run the Hacks" in the Preface] to be passed onto and priced by AdWords:

$ perl  adword "another adword" "one more"

Be sure to wrap phrases in quotes; otherwise, they'll be seen as individual words.

The second way is to maintain a text file of keywords and phrases, one per line (without any enclosing quotes), and feed that file to the script:

$ perl <  adwords.txt

Both of these invocations produce output as CSV, printing them to the screen. To capture them in a .csv file, redirect the output like so:

$ perl <  adwords.txt  >  adwords.csv

The script also keeps you apprised of just where it is in the process with status messages printed to the screen—lest you think it's gone off into wilderness, never to return. The results

Here's a sample run using the same keywords/phrases used in the "By Hand" walkthrough.

The estimated worth may well have changed between the versions—and, indeed, any two invocations of the script. We told you AdWords was a real market; and if it weren't, this little experiment wouldn't be anywhere near as interesting.

$ perl "George Bush" "John Kerry" "Ralph Nader" "Someone Else" 

Fetching the Adwords initial page... ok

Visiting the Language and Targeting page... ok

Visiting the Country selector... ok

Creating a placeholder ad... ok

Plugging in your keywords... ok

Recalculating keyword values... ok

Gleaning keyword values and building you a CSV...

"Keyword","Clicks /Day","AverageCost-Per-Click","Cost /Day","AveragePosition     [?]"

"George Bush","910.0","$0.21","$189.88","1.3"

"John Kerry","810.0","$0.18","$138.36","1.4"

"Ralph Nader","< 0.1","$0.00","$0.00","-"

"Someone Else","33.0","$0.09","$2.67","1.2"

"Overall","1771.0","$0.19","$334.62","1.3" Hacking the hack

There are any number of ways you could slice and dice this hack:

  • Add functionality to set your preferred maximum CPC by adding $agent->current_form->value( 'price', 1.25 ); just before $agent->click(`recalculate'); on the pricing page. Or further alter the script to take this value on the command line.

  • Make the script more interactive, allowing you to retrieve estimated CPC, alter your keyword list, and go back for recalculation—all without the overhead of logging back in and meandering through the same screens each time.

  • Automate things still further such that you can set up campaigns and ads right from the command line or another application. (This is something we weren't inclined to get into, but thought a mention worthwhile.)

Automating and scraping is a brittle process ["A Note on Spidering and Scraping" in Chapter 9] and, as such, is subject to breakage, lockouts, or simply being asked to stop.

Leland Johnson and Rael Dornfest

    Previous Section  < Day Day Up >  Next Section