Excel N-Gram Analyzer for PPC Search Terms

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? You can schedule a consultation session with us. Check out our offering here.***

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 “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 easily done in Power Query in Excel.

Free Excel N-Gram Analyzer

My sample search 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 first version of the tool from here. (click the download arrow in the top right corner on the next page). 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:

What’s the Main Trick in Power Query?

This tool is actually very simple and took about 15 minutes to make. There is only critical step in the query with this formula:

{
List.Range(Text.Split([Search term],” “),0,1),
List.Range(Text.Split([Search term],” “),1,1),
List.Range(Text.Split([Search term],” “),2,1),
List.Range(Text.Split([Search term],” “),3,1),
List.Range(Text.Split([Search term],” “),4,1),
List.Range(Text.Split([Search term],” “),5,1),
List.Range(Text.Split([Search term],” “),6,1),
List.Range(Text.Split([Search term],” “),7,1),
List.Range(Text.Split([Search term],” “),8,1),
List.Range(Text.Split([Search term],” “),9,1),
List.Range(Text.Split([Search term],” “),10,1),
List.Range(Text.Split([Search term],” “),11,1),

List.Range(Text.Split([Search term],” “),0,2),
List.Range(Text.Split([Search term],” “),1,2),
List.Range(Text.Split([Search term],” “),2,2),
List.Range(Text.Split([Search term],” “),3,2),
List.Range(Text.Split([Search term],” “),4,2),
List.Range(Text.Split([Search term],” “),5,2),
List.Range(Text.Split([Search term],” “),6,2),
List.Range(Text.Split([Search term],” “),7,2),
List.Range(Text.Split([Search term],” “),8,2),
List.Range(Text.Split([Search term],” “),9,2),
List.Range(Text.Split([Search term],” “),10,2),
List.Range(Text.Split([Search term],” “),11,2),

List.Range(Text.Split([Search term],” “),0,3),
List.Range(Text.Split([Search term],” “),1,3),
List.Range(Text.Split([Search term],” “),2,3),
List.Range(Text.Split([Search term],” “),3,3),
List.Range(Text.Split([Search term],” “),4,3),
List.Range(Text.Split([Search term],” “),5,3),
List.Range(Text.Split([Search term],” “),6,3),
List.Range(Text.Split([Search term],” “),7,3),
List.Range(Text.Split([Search term],” “),8,3),
List.Range(Text.Split([Search term],” “),9,3),
List.Range(Text.Split([Search term],” “),10,3),
List.Range(Text.Split([Search term],” “),11,3),

List.Range(Text.Split([Search term],” “),0,4),
List.Range(Text.Split([Search term],” “),1,4),
List.Range(Text.Split([Search term],” “),2,4),
List.Range(Text.Split([Search term],” “),3,4),
List.Range(Text.Split([Search term],” “),4,4),
List.Range(Text.Split([Search term],” “),5,4),
List.Range(Text.Split([Search term],” “),6,4),
List.Range(Text.Split([Search term],” “),7,4),
List.Range(Text.Split([Search term],” “),8,4),
List.Range(Text.Split([Search term],” “),9,4),
List.Range(Text.Split([Search term],” “),10,4),
List.Range(Text.Split([Search term],” “),11,4)
}

This formula creates the list of various n-gram types from search term in every row. The rest of the query is only about some filtering and deduplication.

Limits:

  1. As you could probably guess based on the formula above, 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.

Conclusion

N-Gram analysis can be done in many ways. If you don’t feel like using AdWords Scripts, this simple Excel utility could be way to go. 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.

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? You can schedule a consultation session with us. Check out our offering here.***

6 thoughts on “Excel 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

Leave a Reply

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