Google Hacks 2Nd Edition [Electronic resources] نسخه متنی

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.

Andof particular interest to us hacker typesit 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 handclicking
through the AdWords site, filling in forms, copying, and
pastingand then programmaticallyturning 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 (http://adwords.google.com) and click the
"Click to begin" button (Figure 7-1).

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.

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.

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 outfor perfectly understandable reasonsthat only Google can use the word Google (or Gooogle) in their AdWords program.

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.

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.

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.

For our purpose at hand, it's the average CPC
numbers that we were afterand 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.

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

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.

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

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

#!/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.
=cut
# 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
adwords_worth.pl keyword1 [..]
adwords_worth.pl < keywords.txt
=cut
# 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... ";
$agent->get('https://adwords.google.com/');
$agent->form_number(3);
$agent->click('start');
print STDERR "ok\n";
print STDERR "Visiting the Language and Targeting page... ";
# On Language and Targeting page.
# Defaults are okay for now.
$agent->click('save');
print STDERR "ok\n";
# On country selector.
# Right now default value is "All Countries".
print STDERR "Visiting the Country selector... ";
$agent->click('save');
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' );
$agent->click('save');
print STDERR "ok\n";
# On Keywords page.
print STDERR "Plugging in your keywords... ";
$agent->current_form->value( 'keywords', $keyword_string );
$agent->click('save');
print STDERR "ok\n";
# On Price Table Page, but no values are in the table.
print STDERR "Recalculating keyword values... ";
$agent->click('recalculate');
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.
s/<[^>]*>//g;
s/^\s+//;
s/\s+$//;
s/\&lt;/</g;
# Number of clicks contains commas, but we don't want those.
tr/,//d;
}
# 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>
=cut

7.7.2.2 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 adwords_worth.pl adword "another adword" "one more "

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

$ perl adwords_worth.pl < 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_worth.pl < adwords.txt > adwords.csv The script also keeps you apprised of just where it is in the process
with status messages printed to the screenlest you think
it's gone off into wilderness, never to return.

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

7.7.2.4 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 recalculationall
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.