Here’s An AdWords Script To Mine Your Search Query Reports


Paid search revolves around the search query. Most of the time, we think about keywords, as we have direct control over them. But the search query is what the searcher actually wants to find — and this can be different than what you had in mind (especially if broad match or ambiguous words are in play).

That means the search query report is a vital resource for your AdWords account, showing you where you could expand your keywords and where you should exclude irrelevant searches. And if something is important, that means we want to make it easier to work with!

This script takes all the words in your search query report and works out the performance of a word by adding up the performance of all the queries in a campaign that contain that word. It ignores paused campaigns, paused ad groups and search queries already excluded by negative keywords.

For example, if you had the search queries:

Screen Shot 2015-05-01 at 16.44.53

Then the script would say:

Screen Shot 2015-05-01 at 16.44.08

This gives you an idea of how much money you could have saved if you had that word as a campaign-level broad match negative, and if you’d have missed out on any conversions.  In this example, you could add “cheap” as a negative keyword, or look more closely into the highest cost/low conversion words (like “sale”) to see if they should be negatives (or if phrases containing them could be negatives). Of course, you have to use your judgement: “for” hasn’t performed well, but it’s a generic word so you may not want to have it as a negative keyword on its own.

The script also analyses the performance of search queries by the number of words they contain. (This isn’t an exact measure of how long tail a search is — the phrase “how i met your mother dvds” is no more long tail than “friends dvds,” despite the four extra words — but it is generally a good approximation and is much easier to measure.)

If you find longer searches perform better for you, you may want to focus on your long-tail strategy. At Brainlabs (my employer) we’ve found this report to be particularly useful for Shopping campaigns, where longer queries tend to perform better — probably because they are more specific. Here’s an example which allowed us to revamp our Shopping campaign to focus on the queries driving more profit:

revcost image

To use the script:

Go to Google Drive and make a blank spreadsheet. Make a note of its URL.

Then, go to your AdWords account, into “Bulk operations” and then into “Scripts.” Make a new Script and paste the code in.

At the start of the code, there are a few variables to set: Put your spreadsheet’s URL into “spreadsheetUrl” and your currency symbol into “currencySymbol.” Change the “startDate” and “endDate” according to what date range you want to look at; if you have low traffic, you may need to look at a longer period. And “campaignNameContains” can be used to look at particular campaigns (like just campaigns with “Shopping” in their name), but you can leave it blank.

If you want to, you can change the thresholds — the default is for the script to not report on words that have fewer than 10 impressions, but you could change these to only look at high impression or high cost words.

Then, save and run the script (giving authorization when asked).

The results should appear in new sheets (“Campaign Word Analysis,” “Total Word Analysis” and “Word Count Analysis”) in the Google Doc.

For the campaign and total word analysis, you can use the Google Spreadsheet filter (or export into Excel and use Excel’s filter) to find the words that are performing badly or that look out of place. (Remember, you can cross-reference the search query report in AdWords if you’re confused about why a particular word performs so badly — it may be that the problem is a phrase containing the word, rather than the word itself.)

For the word count analysis you can see what number of words performs best. If you want to add conditional formatting to the cost/conv, to make the variation easier to see, then click the “Format” menu, then ‘Conditional formatting…’ and in the sidebar select ‘Colour scale’.

Note: If you’ve got a big account, the script might time out before it finishes — if that happens, try using “campaignNameContains” to only look at a few campaigns at a time.

The post Here’s An AdWords Script To Mine Your Search Query Reports appeared first on Search Engine Land.