AdWords Script: Find Your Best And Worst Search Queries Using N-Grams
Former Googler Daniel Gilbert of Brainlabs shares a script to help you identify wasted AdWords spend using n-grams.
A few months ago, we helped you mine your AdWords search queries by finding the performance of each word used in a query. Now, that script is back with some extra features.
The old AdWords script just looked at individual words. But words can have a lot of meanings, so sometimes you’re missing out by looking at them on their own; maybe searches containing “free” perform poorly in general, but “free delivery” gets conversions. Looking at short phrases within queries gives more insight into the searcher’s intent while still being general enough to reflect lots of different searches.
So the new script doesn’t just look at individual words; it also lets you split your queries into n-grams. An n-gram is a phrase made of n words: a 1-gram is a single word, a 2-gram is a phrase made of two words, and so on. For example, “this four word phrase” contains three 2-grams (“this four,” “four word,” “word phrase”) and two 3-grams (“this four-word” and “four word phrase”).
The script adds up the clicks, impressions, cost, converted clicks and conversion value of each query containing the n-grams — and calculates the click-through rate, cost per click, conversion rate, CPA and value/cost — so you can see their impact.
If the performance is good, you might want to make new ad groups around the n-gram; if the performance is bad, you might want to exclude the n-gram as a negative phrase-match keyword. The data is shown at ad group level, as well as campaign and account level, so you can look at whatever granularity you want.
Looking at longer phrases provides more insight, but it also means the phrase appears in fewer queries. So we’ve also included a “Query Count” column that shows how many queries each phrase appeared in. That means you can concentrate on commonly occurring phrases.
Lastly (as in the old script), there’s a word count analysis to tell you if longer or shorter queries work best for you.
At the top of the script, there are some options you will need to change, depending on your preferences and account setup:
- The startDate and endDate determine the date range of the data.
- currencySymbol is you currency symbol, used for formatting.
- campaignNameContains can be used to make the script only look at some campaigns. For example, if it’s set to “High Priority,” the script will only look at search queries in campaigns with “high priority” in the name. This is not case-sensitive.
- campaignNameDoesNotContain can be used to make the script ignore some campaigns. For example, if it’s set to “Competitor,” then the script would not look at any campaigns with “competitor” in their names. Again, this is not case-sensitive.
- If ignorePausedCampaigns and ignorePausedAdGroups are both true, then the script will only look at currently active campaigns and ad groups. Set them to false if you want to look at data from currently paused campaigns/groups.
- If checkNegatives is true, then the script will check queries against the current negative keywords, so any queries that would now be excluded are ignored. If this is set to false, then all queries will be used. If you have a lot of negatives, and the script keeps timing out, try setting this to false.
- spreadsheetUrl is the URL of a Google Doc spreadsheet, which the results will be copied into. Create a blank spreadsheet and put the URL in here. Do not ignore this step!
- minNGramLength and maxNGramLength let you say how big or small the phrases you are looking at should be. For example, if you set minNGramLength to 2 and maxNGramLength to 4, it will look at 2-grams, 3-grams and 4-grams. If you set both minNGramLength and maxNGramLength to 2, it will only look at two-word phrases. Set both to 1 to look at only single words.
- If clearSpreadsheet is true, any data already in the spreadsheet will be overwritten. If it’s false, then the script’s results will be added at the end of the sheets.
If you find the script keeps timing out, it may be that your account is too big. Try running the script multiple times using campaignNameContains and campaignNameDoesNotContain to look at different campaigns each time. Set clearSpreadsheet to false so you don’t overwrite the data from previous runs. (If you want the account-level data, you can copy all of the n-grams from campaign level into a new worksheet and then use a SUMIF function to find the totals.)
To use the script, just copy and paste the following into your account:
Feel free to post any other comments or error messages below and we’ll try to help.
Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.