**Update on 2019-04-30:** Recent Excel updates caused the version 1 of the tool stop to work. I uploaded a new version **here** but I am not sure if it’s going to work for everybody. I recommend you use Excel 2016 **on Windows** and install all the updates. If you still experience issues, let me know and I will try to troubleshoot.

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.

*****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? We can help you with PPC Automation** **and/or with your reporting under our new project MythicalReports.com here.*****

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 first version of the tool from **here (this is the latest version of the tool).** *(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:**

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

# 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? We can help you with PPC Automation** **and/or with your reporting under our new project MythicalReports.com here.*****

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.