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.

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.

**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 $20.

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:

- The 1 n-gram participating on the highest number of conversions is word “buy”.
- The 2 n-gram participating on the highest number of conversions is word “buy tasty”.
- 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 $20.

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:**

- As you could probably guess based on the formula above, the tool checks up to 4th n-gram.
- N-Grams are made from search terms which consist of less than 13 words.
- 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 $20.

# 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.

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.

Hi Tom,

If you can, send me your search term report to mail@danzrust.cz and I will have a look.

Not sure what could be wrong.

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

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

Where do I need to go to view the formula with “List.Range..”?

Many thanks

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

Hi guys,

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

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 🙂

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?

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.