Remove Wasted PPC Spend with N-gram Analysis

Now more than ever, every dollar matters. Businesses cannot afford waste, whether that is in unutilized software, fancy office perks, or wasted advertising spend. And yet, most businesses are wasting a large percentage of their PPC budgets on keywords that don’t convert. 

Across the hundreds of search campaigns we have analyzed and managed over the years, we have never analyzed an account that didn’t have at least 5% waste. Most were over 15%. 

We have a process that we use to quickly and easily identify and remove wasted ad spend, and I’m going to show you can do it too. 

It requires no paid tools and takes less than an hour. 

The process is called N-gram analysis.

What is N-Gram Analysis?

An n-gram is a contiguous sequence of n items from a given sample of text. For our purposes, the items we are referring to are words. So a 1-gram would be any individual word, a 2-gram would be two word phrases, a 3-gram would be three word phrases, etc. 

Let’s take a look at the search term “affordable Brooklyn movers” as an example. 

• We have 3 1-grams with “affordable” “Brooklyn” and “movers”
• We have 2 2-grams with “affordable Brooklyn” and “Brooklyn movers”
• We have 1 3-gram with “affordable Brooklyn movers”

How does this help you find waste in your Google Ads account?

By running a script that collects all the data on your search terms and converts that data into a spreadsheet of n-grams, you are able to identify n-grams that drive clicks but not conversions and turn them into negative keywords easily. This will make your Google Ads campaigns more efficient and profitable. 

Before we start you will need a few things:

1. An empty Google Sheet
2. A Google Ads account with 3+ months of search campaign data
3. The n-gram Analysis script

Setting up the Script in your Ads Account

The first thing we need to do is set up the n-gram analysis script. I unfortunately cannot take credit for the creation of the script, that credit goes to the great team over at Brainlabs. It is important to note that you do not need any programming experience to run this n-gram script! 

All you need is some copy and paste skills. You can grab the script here.

Pasting the Script

The first step we need to do is take the code above and paste it into the script section of our Google Ads account. 

So go to tools & settings and under bulk actions choose the scripts option.

Next we need to click on the big plus sign, copy and paste the script into the script area and replace what is there, and then give it a name like “n-gram analysis.” 

Modifying the Script

In order to run the n-gram script, we will need to make a few small changes to some variables. This may sound daunting, but it isn’t. There are really only 2 changes that we need to make to get our script to run. 

1. Change the start & end date.

The first thing we want to do is change the start date and the end date for our data analysis. The script does not have a default time frame, so you will need to input your own.

Generally, the longer the time frame you input the better it will be because you will have a larger data set. I recommend at least 3 months, but 6 months or 12 months are even better. I don’t think it’s necessary to go longer than that. 

2. Add your Google Sheets URL

The only other change you need to make is to add your Google sheet URL so your data is sent to a URL. Let’s scroll down to line 48 in the code–you will see the code line to the left–and we’ll see the input for the variable. 

Now all we have to do is replace the placeholder with the editable Google Sheet URL. It is important to ensure that the sharing permissions are set to “anyone with the link can edit.”

Another important thing to remember is to only update the data within the quotation marks. If you remove the quotation marks from either the dates or the spreadsheet URL, the script will not work.

Click the save button at the bottom to update the information in the script. We’re almost ready to run!

Authorizing the Script

The final step before we are able to run the script is to authorize it. At the top of the screen you will see a yellow bar telling you that you need to authorize the script in order for it to run.

Click authorize.

You will be presented with the popup screen below, asking you to choose an account. Choose the account you’re using and continue.

On the next screen you will see Google telling you what the script is trying to access like in the screenshot below.

If you do not see both spreadsheets in Google Drive AND manage Google Adwords campaigns, that means you will need to do one more step. In either case, click allow.

If you only saw one bullet point for manage Google Adwords campaigns, you will get a prompt saying the authorization failed. 

If this happens, close the script, open it again, and try the authorization process again. I don’t know why this situation happens, but it is common and is almost always resolved after closing the script and then opening it back up and trying again.

Once you have successfully authorized the script, we’re ready to run!

Running The Script

The next step is running the script. This is easy. Just go to actions and choose run. 

Depending on the size of your account the script should take anywhere from 3 to 30 minutes to run. If you have a really big account with lots of data, go ahead and wait. 

You can see the status of your script by setting your date range to today in the script history

When it says complete, that means you’re ready to go through your data and start analyzing!

Analyzing the Data

Now that our script has run, You need to clean up your data and analyze it. While you can do your analysis from within Google Sheets, I prefer to work in Excel. So the first thing you will want to do is download the Google Sheet as an .xlsx file.

Cleaning up the data

There’s a little bit of data cleanup that you need to do to easily find n-grams that you will add to your negative keywords list.

First, you should delete the first sheet. This sheet contains no data. 

Next, you should go through each sheet and delete the first three rows of data. This data is unimportant.

Lastly, after deleting those rows,you need to turn each sheet of data into a table with headers. This allows you to sort through the data and find wasted ad spend easily. 

Zero Conversion Words

The first thing that I look at when clearing out waste is 1-grams that had zero conversions in the last year. Depending on the complexity of your account, you will want to do this either on a campaign basis or an account basis. So you can choose either the account word analysis tab or the campaign word analysis tab. 

So let’s go to the Cost / Conv. header and sort from largest to smallest. This will default to showing null at the top.

Every entry that has no cost per conversion are words that generated zero conversions at all. 

This is all waste. 

Go through these words and create a list of them. These are keywords that you will add to your negative keyword list. 

If you find keywords that are relevant to your business but have a low volume (10 clicks or less) you can leave them off your list. Sometimes a small sample size will result in zero conversions. 

High Cost per Conversion Words

Once you get past all your never-converting words, you’ll reach your very poorly converting words.

These are keywords that you should also be adding to your negative keyword list. If the Cost per Conversion is much higher than your target CPA, these are good options.

For this campaign the target CPA is $40. For the three words shown the CPAs are $310, $207, and $170. I will add these to the negative keyword list. 

Additional Removal

After adding the individual words that are wasted spend to your list, you need to repeat this process with 2-grams as well. 

Make sure that when you add these 2-grams to your list, you put them in quotation marks so that they are phrase matched. 

Updating Your Account

With your list of negative keywords compiled, you need to create a new negative keyword list in your Google Ads account and copy and paste all of these keywords over. 

I give the list a name like “N-gram Analysis Negatives” or something like that. If you already have negative keyword lists and would rather sort these terms into separate lists, that is also a good strategy. 

Add your negative keyword list to the campaigns you want to improve, and you’re done! 

After completing this process, you will likely see a big improvement in your campaign efficiency. Depending on your use of negative keywords historically, I have found that the improvement can range anywhere from 5% to 30%!

Ongoing N-gram Analysis

I also strongly recommend continuing to periodically run an n-gram analysis on your account. For our clients, we perform an n-gram analysis every quarter, which we do on top of our standard negative keyword optimization. 

If this is something that sounds too complex for you, get in touch with us, and we’ll be happy to perform an n-gram analysis for you!