Central Perk: Excel Hints for PPC

vineri, 17 februarie 2012

Excel Hints for PPC

Posted: 16 Feb 2012 05:05 AM PST

Excel is one of the best tools for PPC. Downloading your data into Excel gives much more scope for analysis and complex change than using a browser interface or AdWords Editor.

You may have already read Distilled's Excel for SEO or some of PPCHero's Excel tips, but here are my own hints.

Concatenation

If there's one thing you can put into practice from this post, it is this: you can use ampersands instead of CONCATENATE().

A1&A2 is the same as CONCATENATE(A1,A2), except for being far fewer characters and not adding to the oft inevitable nightmare of nested brackets.

Wildcards

Excel has three wildcard characters:

A question mark (?) can match a single character, but it can be any character. So ‘ca?’ will match ‘cat’ and ‘cab’ but not ‘ca’ or ‘cattle’.
Tilde is the escape character – if you actually want to search for asterisks, question marks or tildes you have to add a tilde (~) before them. So ‘Where am I?’ will match ‘Where am Id’, while ‘Where am I~?’ will match ‘Where am I?’ and nothing else.
An asterisk, *, can represent zero or any number of any characters. ‘*’ will match anything. ‘*where’ will match ‘where’, ‘somewhere’ and ‘thwsohfkjsgnsmnglsnwhere’ but not ‘where am I’.

Main takeaway: ‘*where*’ will match anything with 'where' in it. You can use this to check text for a single word.

Wildcards do not work in all functions (you can't use them in SUBSTITUTE(), for example) but will work in SEARCH(), SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS() and VLOOKUP(). You can use also wildcards in Find and Replace.

Checking Ads

Pretty basic tip: You can check that all the bits of your ads are the right length using LEN(), and then having conditional formatting to highlight where text is too long.

More advanced: what if you use dynamic keyword insertion? Then your ad text's actual length can be over 25, as AdWords won't count the '{KeyWord:}' when counting the characters. You can get around this by using:

=LEN(A2)-10*COUNTIF(A2,”*{KeyWord:*}*”)

You usually would use COUNTIF() on a range of cells, but you can also use it to check just one cell – if A2 uses DKI, then COUNTIF() will return 1, and if it doesn't COUNTIF() will return 0. There are 10 characters in '{KeyWord:}', so if COUNTIF() is 1 the formula gives then length of A2 minus 10.

COUNTIF() is case insensitive, so it won't matter if your ads use '{keyword:', '{Keyword:' or '{KeyWord:'.

Checking Search Query Reports

Search query reports are great providers of negative and positive keywords. But it can be difficult to spot trends if people phrase their queries slightly differently. You can use SUMIF() and wildcards to see the performance of all search queries that have an individual word in them.

First, download your search query report into Excel – here's an entirely made-up example:

Then add a second worksheet, and set up these headings:

The 'Word' column is for the word or phrase you're searching the search terms for. Add some words that recur in the search terms.

In cell B2, we want to add up all the clicks of search terms containing whatever's in A2. So use the formula

=SUMIF(‘Search term report’!A:A,”*”&A2&”*”,’Search term report’!E:E)

There are three parameters inside SUMIF():

the first is the range of cells to test
the second is the criteria to test those cells
the third gives the range of cells to add up if the tested cell fulfils the criteria (This argument is optional – leave it blank if you're adding up the cells you're testing)

So the SUMIF() will look at ‘Search term report’!A:A (which is the column of search terms) and see if any match “*”&A2&”*”. So if A2 is 'cheap' then the function will look for search terms that match "*cheap*" – which means any search term that uses the word 'cheap'. It then sums the numbers in ‘Search term report’!E:E (the Clicks column).

The formula for Impressions is

=SUMIF(‘Search term report’!A:A,”*”&A2&”*”,’Search term report’!F:F)

which is the same except that the third parameter is now the Impressions column.

In the Cost column the third parameter should be ‘Search term report’!I:I, and in the Conv column the third parameter should be Search term report’!K:K.

The CTR, CPC, Cost/conv and Conv rate columns can't be filled in using SUMIF(), as they aren't sums – calculate these from the other columns.

Then copy the formulae in Row 2 and paste them downwards.

In this example, searches with the word 'cheap' get good CTR and bad cost/conv, suggesting that 'cheap' should either be added as a negative keyword or effort needs to be spend improving their performance. It also shows there's a lot of traffic for 'blue' which converts well, so separating 'blue widgets' into their own ad group might web a good idea.

What words should you check? Look at your search queries and see what keeps coming up. You might want to check for words suggesting an informational search (like 'why', 'how', 'what', 'where' or 'which'). Or you can check for people searching for websites with words like 'www' or 'com'.

Note that the CTR calculated is likely to be higher than the actual CTR of all searches containing your word – some queries are collated under 'Other Search Queries', and if a search query had a click it's less likely to be one of those. So it may be better to focus on the cost and conversion metrics.

Download the Sumif Example sheet here

Any More?

I hope you've found these tips useful – there's so much depth to Excel that it's easy to miss the things you can do with it. Please share your own tips in the comments!