O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  

Determine the Worth of AdWords Words
Harness the Google AdWords marketplace to guesstimate the value of a keyword or phrase in online advertising
[Discuss (1) | Link to this hack]

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 , 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.

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 (http://adwords.google.com) and click the "Click to begin" button ().

Figure 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 , by clicking the Save & Continue button.

Figure 2. Choose your language and geo-targeting

You do need to pick a country or set of countries on the page shown in . 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 3. Choose your countries

On the "Create ads" page shown in , 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 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 and click the Save Keywords button.

Figure 5. Choose your keywords or phrases

AdWords drops your keywords into a pretty table, shown in , 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 6. Calculate estimates

Finally, we get to the payoff, shown in : 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. 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 .

You'll find another handy tool behind the "find alternatives" links associated with each keyword. As shown in , 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 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 , and copy them as you would any other text (Control-C on Windows, Command-C on Macintosh).

Figure 9. Copy estimated Cost-Per-Clicks

Paste what you've copied into a text file or spreadsheet () 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 10. Paste estimates into Excel


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 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 (http://aspn.activestate.com/ASPN/Downloads/ActivePerl/PPM/Repository) for details and alternate installation instructions.

Save the following code as adwords_worth.pl:

#!/usr/bin/perl -w

# adwords_worth.pl
# Automate gleaning Google AdWords estimated cost-per-clicks (CPCs)
# Usage: perl adwords_worth.pl <keyword1> <keyword2> [..]
#        perl adwords_worth.pl < 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+/;


adwords_worth.pl keyword1 [..]
adwords_worth.pl < 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', 'a.com' );
$agent->current_form->value( 'creative.destUrl',    'a.com' );
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 <easyasy2k@gmail.com>


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.