Thursday, 21 January 2010

Using Excel as Adwords keyword phrase generator

If you use keyword phrases of three or more words in Google Adwords you'll quickly find the number of phrases (combinations of words) becomes unmanageably large, like this:

hotel booking software
hotel booking diary
hotel booking system
inn booking software
inn booking diary
inn booking system
B&B booking software
etc.

Using, say, all combinations of these words to describe 'hotel booking software':

hotel / inn / guesthouse / villa / B&B / motel / campsite / campground
booking / reservation / management / front desk
software / system / tool / calendar / diary

would give you 8 x 4 x 5 = 160 phrases.

Manually generating each phrase would take you an age. But, as with many things in life, Excel can help you.

I'll show you how.

Set up three columns, one for each word in your phrase. If you were a plumber, you might want to bid on phrases like 'fix broken sink', 'mend dripping pipe' and so on. You would lay them out in Excel with all the verbs (fix, repair, mend, etc.) in Column A, all the descriptions (broken, leaking, dripping, etc.) in Column B, and all the things (sink, plumbing, pipe, etc.) in Column C. Like this:



Then paste this monster formula into a cell in a blank column: (Don't paste it in any of the columns you are using to store your words, that'll mess up the calculations.)

=INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98)),0))&" "&INDIRECT("B"&MOD(ROUNDUP((ROW())/COUNTA($C$1:$C$98),0)-1,COUNTA($B$1:$B$98))+1)&" "&INDIRECT("C"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($C$1:$C$98))+1)

Like this:




Then copy the formula down the requisite number of rows. You could calculate that yourself by multiplying the number of verbs x number of descriptions x number of things, or use this formula:

=PRODUCT(COUNTA(A1:A999),COUNTA(B1:B999),COUNTA(C1:C999))

Once you have copied down the first formula, you'll have something like this:



all the way down to row 144 where you'd see you final combination of 'replace blocked toilet':




This formula works by building up a different series of cell references for each row in which it appears.

So in the first row, it brings together the contents of cells A1, B1, and C1 ("fix broken sink"). Then in row 2 it brings together the contents of cells A1, B1 and C2 ("fix broken plumbing") and so.

There are two important functions used:

=INDIRECT - this allows you to use other functions to create a cell reference. So INDIRECT("A1") reports back the contents of cell A1, and you can use other functions to create that string "A1".

=COUNTA - counts the number of non-blank cells in a range, so you can count how many words you have in each column.

Plus MOD, PRODUCT and ROUNDUP to calculate the correct row to insert into the INDIRECT function

And finally, the string &" "& to join the three words into one string, with a space after each word.

Let's look at the section before the first &" "&. This determines whether to use fix, repair, mend or replace as the first word in the phrase:

INDIRECT("A"&ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98)),0))

All this is is doing is really:

INDIRECT("A"&[calculate which row to look in])

We are only looking in the first column, so "A" is fixed. Only the row changes, and that is calculated by this section:

ROUNDUP((ROW())/PRODUCT(COUNTA($B$1:$B$98),COUNTA($C$1:$C$98)),0)

which takes the current row (ROW()), divided by total number of times each verb needs to appear (once for every description-thing combination, so the number of descriptions x number of things) and rounds it up.

There are 4 descriptions and 9 things, so each verb needs to appear 4 x 9 = 36 times.

When that formula is pasted into row 1, that section will return:

1/36 = 0.28, rounded up = 1

And it'll do the same when it is in row 2:

2/36 = 0.56, rounded up = 1

And all the way until row 37:

37/36 = 1.028, rounded up = 2

So the first 36 rows have the first verb in them ("fix"). The next 36 rows have the second verb in them ("repair"), and so on.

Similar calculations are done in the next section of the formula, after the first &" "&:

INDIRECT("B"&MOD(ROUNDUP((ROW())/COUNTA($C$1:$C$98),0)-1,COUNTA($B$1:$B$98))+1)

Same kind of thing happening here: given the row we are in, which word in column B should I pull in to the phrase?

And finally, for the third word in the phrase:

INDIRECT("C"&MOD(ROUNDUP((ROW()),0)-1,COUNTA($C$1:$C$98))+1)

Once you copy the whole formula all the way down, you get all possible combinations of verb-description-thing, which you can then paste into your campaign in Adwords.

I think the rounding up and counting could be simplified. To be honest after getting most of the way there, I just fiddled around with increments and roundings until it worked, if my algebra was less rusty I think I could probably simplify the formula further.

Hope you find it useful.

******
Still using a desk diary to manage your guesthouse reservations?
Try
KeepMeBooked: simple web-based reservation management

11 comments:

markability . blogspot . com said...

Thanks Bruce, works well and saved me writing something.

It works in both Excel and OpenOffice Calc (free from Sun) by the way - I'm trialing OpenOffice as a Microsoft Office replacement.

Mark
Twitter: @webalyst

Cheap GHD Straighteners said...
This comment has been removed by the author.
M Fieldhouse said...

I've just started using this tool, it's really useful thanks.

Do you know how I could modify the formula so that it would work with just 2 rows of keywords, or 4 rows, 5, etc... ?

Anonymous said...

Thank you so much - I have been looking for this information for AGES - and now you have saved me hours of work.

Anonymous said...

Perfect, thank you.

Anonymous said...

Let me add a German Version to this great post

=INDIREKT("A"&AUFRUNDEN((ZEILE())/PRODUKT(ANZAHL2($B$1:$B$98);ANZAHL2($C$1:$C$98));0))&" "&INDIREKT("B"&REST(AUFRUNDEN((ZEILE())/ANZAHL2($C$1:$C$98);0)-1;ANZAHL2($B$1:$B$98))+1)&" "&INDIREKT("C"&REST(RUNDEN((ZEILE());0)-1;ANZAHL2($C$1:$C$98))+1)

Danke!

sunrmd said...

I've got syntax error. Can anybody send me a working example of the generator?

email: sunrmd @ gmail.com

thanks!

Anonymous said...

is there a way to include quotation marks to make the generated keywords into phrase match type?

Ivan Muljevski said...

Try http://mergewords.com/ to make a list of all possible combinations with 3 words.

Tweed Echidna said...

I can also confirm it works with Google Spreadsheets. Thank you

Trang suc said...

In the last of each list, you will want to put in a space cel like " " to make it generate all possible results.

Eg. if I have 3 columns like this
A B C
A1 B1 C1
A2 B2 C2

your code will only generate 27 unique keywords, in fact there are 63 keywords.

These keywords are not generated: AB, AC, BA, BC, CA, CB, A, B, C..

Anw, your codes save my life! Thank you very much.