N-Gram Analyzer for PPC Search Terms

Search term report is a useful source of information – based on search terms, you can add new positive KWs into your account or you can add new negatives. We all know that. There is one more thing you can do with your search terms (or keywords). You can create an “N-Gram” report to see how various single words or word combinations participate on conversions.

N-Grams Explained

The definition of n-gram is quite simple – it’s a contiguous sequence of n items from a given sequence of text. Notice the “contiguous” word, that’s important. However, since people do not often like definitions, let’s have a look on a simple PPC example.

We have 4 search terms:

  • i want to buy bananas (1 conversions)
  • buy bananas (0 conversions)
  • bananas (2 conversions)

1 n-gram sequences from “i want to buy bananas” would be: I, want, to, buy, bananas.
2 n-gram sequences would be: I want, want to, to buy, buy bananas.
And so on.

The aim is to figure out how is every single n-gram contributing to conversion counts based on search terms in your account.

There are many ways how to complete this task. There is for example an AdWords script from BrainLabs. There are also various N-Gram analyzers online, not designed solely for PPC. And as always, the n-gram analysis can be done in Google Sheets and in Power Query in Excel.

Google Sheets N-Gram Analyzer

You’ve asked for it so I finally made N-Gram Analyzer in Google Sheets (April 2022) even though I did not originally think it was possible. But it is. The tool is based on a long G Apps script.

I’ve prepared a video which explains how the product works:

Given the efforts and some pretty serious coding involved, I am not going to provide a free version. You can buy the product here for $100 (one time):

BUY GOOGLE SHEETS VERSION

Limitations:

  1. Based on my heavy testing after shooting the video above, the tool can handle around 20,000 search terms consisting of 4 words in one run which will take about 30 minutes to process if you run a Google Workspace account (=email on a custom domain).  If you run just a free @gmail account, the script runtime is limited to 6 minutes which translates to the max of 3,300 search terms consisting of 4 words. The limits come from Google, not from me. The less words your search terms contain, the faster the script will run and more search terms can be processed. Of course, you can analyze search terms with more than 4 words, I am just using the 4-word search term as a benchmark for testing.
  2. A 3-word search term will translate to 5 new rows. A 4-word search term will translate to 9 new rows. A 6-word search term will translate to 22 new rows. The more words your search terms contain, the more rows you’ll have at the end. Note that your spreadsheet cannot go over 10M cells (not rows!!!). If you have lots of other stuff in the spreadsheet, the n-grams may not fit when being created.
  3. You always need to have the search term column on your search_terms_input_sheet. All other columns are optional, however, I recommend to keep the default set of columns as prepared and you can just leave columns which you don’t need blank.
  4. The first row must be always headers, performance data needs to start from the second row.
  5. The tool creates from 1-word to 7-word n-grams.

Excel N-Gram Analyzer for Windows

I strongly recommend you to always use the latest version of Excel on Windows and install all the updates before you run this tool. Older versions may throw an error.

Aside from the Google Sheets edition, you can also run the n-gram analysis in Excel via Power Query. The benefit is that Excel is more powerful than Google Sheets. The downside is that it’s not easily sharable and you are also out of luck if you use Mac.

Let’s look at the Excel tool now. My sample search term report looks like this:

And I want to see how various n-grams perform. The result is:

We can see that:

  1. The 1 n-gram participating on the highest number of conversions is word “buy”.
  2. The 2 n-gram participating on the highest number of conversions is word “buy tasty”.
  3. And so on.

You can download the tool below:
GET FREE VERSION  GET FULL VERSION
Free version is limited to 100 terms and does not calculate ROI.
Full version is unlimited and calculates ROI. The price is $30.

All you need to do is to copy and paste you search terms to “Add Your Search Terms Here” sheet and then refresh the pivot tables on “N Grams” sheet:

Limitations:

  1. The tool checks up to 4th n-gram.
  2. N-Grams are made from search terms which consist of less than 13 words.
  3. There is also a limit in Excel pivot tables – if this analysis produces more than 1,000,000 and something unique n-grams, you will not be able to use N-Gram field in the pivot table. This can be an issue with very large accounts (I have hit this limit already, so I know). However, most of the people will be just fine.

Download N-Gram Analyzer now:
GET FREE VERSION  GET FULL VERSION
Free version is limited to 100 terms and does not calculate ROI.
Full version is unlimited and calculates ROI. The price is $30.

Conclusion

N-Gram analysis can be done in many ways. If you don’t feel like using AdWords Scripts, you can go either with Google Sheets and G Apps Scripts or with my simple Excel utility. On the other hand, if you are already using Power Query for PPC, you probably know this n-gram trickery can be used as one step inside a bigger project.

Let know your thoughts in the comment section below.

16 thoughts on “N-Gram Analyzer for PPC Search Terms

  1. Hi Dan,

    Trying to use your tool but unfortunately it is failing to initialize the data source. Any tips on how to get it running?

    Thank you!

    -Tom S.

  2. Hi Dan,

    Thank you for replying! I actually figured it out – I hadn’t installed PowerQuery. Everything is working fine now. Thank you so much for your response, and making such a useful tool!

    Best,
    Tom

    1. OK 🙂 You kind of need PQ installed for these tricks… PQ comes as standard in Excel 2016 so you probably have some older version.

      DZ

    1. You need to open the Query Editor in the downloaded Excel.

      In Excel 2016, go to Data tab > Get & Transform section > New Query > Combine Queries > Launch Query Editor and find that step there. It’s all named.

      DZ

  3. Hi guys,

    Awesome content here. Got a quetion to see if anyone might know. Any way I can expand the 13 word limit?

    1. Hi Sean, yeah, it’s expandable. How many words do you need?

      You can edit the code directly in the sample file or I can do it.

      That I am not sure how many people use more than 13 words to search for something 🙂

  4. Hello, please help, i’ve added my data into the search terms tab and i’ve checked the For Pivots connection and it contains my data, however the pivots in the spreadsheet are still showing the kiwi and banana data and it isn’t refreshing to my data – please help?

    1. Aah, I see the problem. There has been an update in Excel and it broke the thing completely. I will update the article in near future.

  5. Does this work for one account or could I pull search terms from multiple add accounts for review. For example, I have 50 accounts to hold the campaign/strategy for one massive budget, would I be able to just drop in all search terms reports?

    If so, I’m ready to buy! 🙂

    1. Hi Jordan,
      yes, there is account column available. However, depending on how big your accounts are, you may run into limitations of Excel – e.g. if you have more than 1M search terms, you won’t be able to insert them all.

      Nevertheless, just go ahead and buy the paid version, I will refund you if it does work for you.

      DZ

  6. Hello

    I bought the script and I am wondering if it will integrate with Google scripts? I opened scripts and clicked on power app and I saw your script in and tried to run and it came back with some type of syntax error in Google script. I am not a code guy and have no education around this but would love to be able to run your script in Google script is that possible?

    1. Summary, no need to have it broken by date. THe tool does not work with time dimension.

Leave a Reply

Your email address will not be published. Required fields are marked *