All in one Google Ads Script to Simplify Keyword Optimization
Analyzing search queries is an important task you should regularly do for any search campaign in your Google Ads account. How often you do this depends on how much traffic your campaign gets. You should do it once a month, every two weeks, once a week, or even a few times a week.
The simplest way to check your search terms is by looking at the report in the Google Ads platform. This can help you find new keywords and negative terms. However, it can be tricky and often requires a Google Ads script if you want to do a more detailed analysis.
In this article, I’ll show you one of my favorite methods for reviewing search query performance over time: n-grams.
Table of contents:
Part 1: Introducing N-grams for Google Ads
Part 2- What Are N-Grams and How Do They Help Analyze Search Queries?
Part 3- How to Perform N-Gram Analysis in Google Ads?
Part 4- Overview of N-Gram Analysis Reports
Part 5- Best practices for your n-gram Google Ads scripts
Part 1: Introducing N-grams for Google Ads
N-grams are a way to analyze data you can get from Google Ads using a script. If you haven’t used Google Ads scripts before, no problem! We’ll guide you through it with a simple, ready-to-use script you can copy and paste.
What are Google Ads Scripts and How Do They Work?
An n-gram is a way to analyze search term reports that goes beyond what you can do directly in the Google Ads interface. To use this type of analysis, you need a script. Before we dive into how to use n-gram analysis for your search term reports, let’s first take a moment to understand what Google Ads scripts are.
Understanding Google Ads Scripts
Google Ads scripts are small pieces of custom code that you can add to the platform to perform specific tasks that aren’t available by default. For example, one common use of Google Ads scripts is to automatically stop ads or keywords that aren’t performing well.
In this case, you could use a script to tell Google Ads to pause any ads or keywords that don’t meet a certain performance level. For larger accounts, scripts can be very helpful because they save you time by automating repetitive tasks.
In our case, Google Ads scripts can also be useful for creating more detailed reports, like the n-gram analysis.
You don’t need to be a coding expert (or even know anything about coding) to use Google Ads scripts. Google Ads offers some ready-made script templates right on the platform. You can also find many free scripts online that you can copy and paste (like the n-gram script we’re using today). Another option is to use an AI tool to help you create scripts to test out.
Part 2- What Are N-Grams and How Do They Help Analyze Search Queries?
Let’s begin with a simple explanation. N-grams are a way to analyze individual words within a search query. Instead of looking at the whole search query as one piece, n-grams let you break it down into separate words. This helps you see how each word is performing in your Google Ads account.
Here’s a simple example. In the account below, we’re trying to sell worker’s compensation insurance to local businesses. People might search for this type of insurance in many different ways. Some of the search queries are shown above. Each of these queries could be very valuable for us, but with n-grams, we can better understand how each word is performing.
Here, we have the data split by each term in the report. Without looking at it this way, it might not be clear that searches with the word “comp” have a higher cost per action (CPA) than those with the word “compensation.” This is the kind of useful information we want to find. Now, let’s start the process of running an n-gram analysis.
Part 3- How to Perform N-Gram Analysis in Google Ads?
At first glance, it might look like this kind of analysis requires a lot of technical knowledge, but that’s not the case. At least not for you and me. While you do need to use a Google Ads script, you don’t have to write the code yourself.
To make this process simpler, we've added a ready-made n-gram analysis script on our website. Before we can run the script in your account, there are a few steps to set it up.
First, go to the below section of the page and copy all the script into your Google Ads account, and you'll quickly get useful keyword information.
Step 1:
In the Google Ads tool, go to the section for bulk actions and select the option for scripts.
Step2:
Press the blue plus button to create a new script. Next, go to the main function window and paste the text you copied from the website.
Step 3:
Next, we need to let Google know where to send our information. Go to your Google Drive main page, make a new spreadsheet, and copy the link to your new spreadsheet from the address bar.
Then go back to the script manager and scroll down until you see the part that asks for the spreadsheet link. Paste the link to the spreadsheet you just made there.
Step 4:
Now, we need to pick the settings for our analysis. First, you should select a period to review the search queries. From what I've seen, an n-gram analysis usually covers a longer time frame, such as a month, three months, six months, or even a year, depending on how much activity your account has had.
Step 5:
You can adjust the script settings to include or exclude specific campaigns or to include or exclude campaigns and ad groups that are either paused or active. Most of these options are easy to understand, and you can probably figure them out. Just be careful not to change any of the punctuation marks around them, as this could cause the script to stop working.
Final Step:
The final step in the settings section is to choose how many words we want to analyze in our n-gram. In the example I showed earlier, we used an n-gram of one. This means we analyzed how single words performed across all search queries. However, we can also analyze longer word combinations.
By default, the minimum is set to one and the maximum to two. This means the report will only include single words or two-word phrases. I prefer to change this number to four. This way, I can see single words, two-word phrases, three-word phrases, and four-word phrases for my account. This is just my preference, though. You can adjust it to whatever works best for you.
After you finish those steps, you’ll be asked two times to allow the scripts to run. Once you do that, the script will start, and you can wait for your results.
Part 4- Overview of N-Gram Analysis Reports
You will now see several new tabs in your Google spreadsheet. For each level of n-gram you choose to analyze, there will be three tabs: one for the campaign, one for the ad group, and one for the account. For example, in my case, I ended up with 12 new tabs in my spreadsheet.
These included tabs for the campaign, ad group, and account levels, covering n-grams from one to four words.
In the image below, you can see what the report looks like. It shows the n-gram along with details like cost, clicks, impressions, conversions, cost per conversion, and a few other metrics. Now that this data is in a spreadsheet, we can explore it to better understand how these words or phrases performed.
Here are some simple filter ideas you can use for your N-gram reports:
- Queries with high costs but no conversions
- Queries with many impressions but few clicks
- Queries with a low cost per conversion
- Queries with a high conversion value
You can use any filter you’d normally use for your search terms report. Instead of just looking at individual queries, n-grams let you see how specific word combinations perform in your account.
N-grams in Action: A Client Story
Here’s a specific example that came from a client. They do take phone calls, but they’d rather have users fill out the form on their website because it’s easier for them to manage. So, they asked, “How often do people search for phone numbers, and do those searches lead to better or worse results compared to other search terms?” Time to look at the data!
This report makes it super easy. I can go to the account’s single-word section, filter for the word “phone,” and get the info I need. Simple as that!
But let's go a bit deeper. I can check the “two-word” section and look at “phone number” and other phrases that include the word “phone.” Then, I can see how these perform compared to our original phrase.
The results from this quick check will help us decide if we should start focusing on phone call terms and count phone calls as conversions, or if we should keep using the lead form as the main way to get conversions in the account.
As I said before, there are also tabs for n-grams at the campaign or ad group level. In my earlier example, I looked at the account level, but I could also check phone-related search terms by campaign to see if branded or non-branded campaigns, or specific campaigns, performed better
when it came to phone number searches. This is just a simple example, but it shows how useful it can be to understand how certain terms perform in your account. This is especially true if you analyze them at the account, campaign, and ad group levels to create more precise negative keywords.
Using N-grams to Find and Exclude Keywords
The great thing about n-Gram analysis is that it shows you which short phrases work well or poorly in your account. This helps you decide whether to use them as keywords or avoid them as negative keywords. That’s why I always analyze up to four-word phrases.
I’ve noticed that one and two-word n-grams are usually helpful for finding new negative keywords, while three and four-word n-grams often help me discover new keywords to target, either as exact or phrase matches.
In short, n-grams are a powerful tool for improving your search campaigns with more accuracy.
Part 5- Best practices for your n-gram Google Ads scripts
Here are some tips to remember when using this script:
First, the date range is fixed. This means you probably won’t set this script to run automatically on a schedule. Instead, every time you want to analyze data, you’ll need to manually update the date range to match the period you’re interested in.
Also, make sure to clear your filters after you finish analyzing the data. The script is set to replace the data on the spreadsheet tab, but I noticed that if filters are left on, the data doesn’t show up completely. Simply remove the filters, and everything should work fine.
Find your best search terms using the n-grams script for Google Ads
That’s all. I hope this explanation helps you understand what n-gram analysis is, how to do it, and how you can use the results in your account.
To summarize, here’s how you can create an n-gram script for your Google Ads search terms reports:
- In Google Ads, go to the automated rules section and select “Scripts.”
- Click the blue plus button to add a new script. Then, in the main function window, paste the text from this website.
- Next, open your Google Drive homepage, create a new spreadsheet, and copy the URL of that spreadsheet from the address bar.
- Go back to the Google Ads script manager, scroll down, and paste the URL of your spreadsheet in the “Spreadsheet URL” field.
- Choose a date range for your n-gram script analysis.
- If you don’t want to analyze your entire account, adjust the settings in the script code to focus on specific campaigns or ad groups.
- During this process, make sure not to change any of the punctuation marks around the text, as it could mess up the script.
- Finally, in the settings part of the n-gram script code, choose how many words you want to use for your n-gram analysis.
- Great job! Now, your Google Sheet will have data from Google Ads, and you can check each tab to see which search terms worked best (or worst). Based on this, you can update your keyword and negative keyword lists as needed.
To make things simpler and save you time, here's an easy-to-use n-gram script for your Google Ads search terms reports. Just copy and paste the script below, and you can start analyzing your data right away without any trouble!
/**
*
* Search Query Mining Tool
*
* This script calculates the contribution of each word or phrase found in the
* search query report and outputs a report into a Google Doc spreadsheet.
*
* Changed by Nils Rooijmans:
* Updated 2022-08-22: replaced AWQL query that throws error in new script environment with a compatible GAQL alternative
*
* Version: 2.2
* Updated 2015-09-17: replacing 'KeywordText' with 'Criteria'
* Updated 2016-10-11: replacing 'ConvertedClicks' with 'Conversions'
* Google AdWords Script maintained on managingseo.com
*
**/
function main() {
//////////////////////////////////////////////////////////////////////////////
// Options
var startDate = "2022-01-01";
var endDate = "2022-08-21";
// The start and end date of the date range for your search query data
// Format is yyyy-mm-dd
var currencySymbol = "€";
// The currency symbol used for formatting. For example "£", "$" or "€".
var campaignNameContains = "";
// Use this if you only want to look at some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
var campaignNameDoesNotContain = "";
// Use this if you want to exclude some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
var ignorePausedCampaigns = true;
// Set this to true to only look at currently active campaigns.
// Set to false to include campaigns that had impressions but are currently paused.
var ignorePausedAdGroups = true;
// Set this to true to only look at currently active ad groups.
// Set to false to include ad groups that had impressions but are currently paused.
var checkNegatives = true;
// Set this to true to remove queries that would be excluded by your negative keywords.
var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.
var minNGramLength = 1;
var maxNGramLength = 2;
// The word length of phrases to be checked.
// For example if minNGramLength is 1 and maxNGramLength is 3,
// phrases made of 1, 2 and 3 words will be checked.
// Change both min and max to 1 to just look at single words.
var clearSpreadsheet = true;
//////////////////////////////////////////////////////////////////////////////
// Thresholds
var queryCountThreshold = 0;
var impressionThreshold = 10;
var clickThreshold = 0;
var costThreshold = 0;
var conversionThreshold = 0;
// Words will be ignored if their statistics are lower than any of these thresholds
//////////////////////////////////////////////////////////////////////////////
// Check the spreadsheet has been entered, and that it works
if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
Logger.log("Problem with the spreadsheet URL: make sure you've replaces the default with a valid spreadsheet URL.");
return;
}
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
} catch (e) {
Logger.log("Problem with the spreadsheet URL: '" + e + "'");
return;
}
// Get the IDs of the campaigns to look at
var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
var activeCampaignIds = [];
var whereStatements = "";
if (campaignNameDoesNotContain != "") {
whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
}
if (ignorePausedCampaigns) {
whereStatements += "AND CampaignStatus = ENABLED ";
} else {
whereStatements += "AND CampaignStatus IN ['ENABLED','PAUSED'] ";
}
var campaignReport = AdWordsApp.report(
"SELECT CampaignName, CampaignId " +
"FROM CAMPAIGN_PERFORMANCE_REPORT " +
"WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
"AND Impressions > 0 " + whereStatements +
"DURING " + dateRange
);
var campaignRows = campaignReport.rows();
while (campaignRows.hasNext()) {
var campaignRow = campaignRows.next();
activeCampaignIds.push(campaignRow["CampaignId"]);
}//end while
if (activeCampaignIds.length == 0) {
Logger.log("Could not find any campaigns with impressions and the specified options.");
return;
}
var whereAdGroupStatus = "";
if (ignorePausedAdGroups) {
var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
} else {
whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
}
//////////////////////////////////////////////////////////////////////////////
// Find the negative keywords
var negativesByGroup = [];
var negativesByCampaign = [];
var sharedSetData = [];
var sharedSetNames = [];
var sharedSetCampaigns = [];
if (checkNegatives) {
// Gather ad group level negative keywords
var keywordReport = AdWordsApp.report(
"SELECT CampaignId, AdGroupId, Criteria, KeywordMatchType " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Status = ENABLED AND IsNegative = TRUE " + whereAdGroupStatus +
"AND CampaignId IN [" + activeCampaignIds.join(",") + "] " +
"DURING " + dateRange
);
var keywordRows = keywordReport.rows();
while (keywordRows.hasNext()) {
var keywordRow = keywordRows.next();
if (negativesByGroup[keywordRow["AdGroupId"]] == undefined) {
negativesByGroup[keywordRow["AdGroupId"]] =
[[keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]];
} else {
negativesByGroup[keywordRow["AdGroupId"]].push([keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]);
}
}
// Gather campaign level negative keywords
var campaignNegReport = AdWordsApp.report(
"SELECT CampaignId, Criteria, KeywordMatchType " +
"FROM CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " +
"WHERE IsNegative = TRUE " +
"AND CampaignId IN [" + activeCampaignIds.join(",") + "]"
);
var campaignNegativeRows = campaignNegReport.rows();
while (campaignNegativeRows.hasNext()) {
var campaignNegativeRow = campaignNegativeRows.next();
if (negativesByCampaign[campaignNegativeRow["CampaignId"]] == undefined) {
negativesByCampaign[campaignNegativeRow["CampaignId"]] = [[campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]];
} else {
negativesByCampaign[campaignNegativeRow["CampaignId"]].push([campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]);
}
}
// Find which campaigns use shared negative keyword sets
var campaignSharedReport = AdWordsApp.report(
"SELECT CampaignName, CampaignId, SharedSetName, SharedSetType, Status " +
"FROM CAMPAIGN_SHARED_SET_REPORT " +
"WHERE SharedSetType = NEGATIVE_KEYWORDS " +
"AND CampaignId IN [" + activeCampaignIds.join(",") + "]");
var campaignSharedRows = campaignSharedReport.rows();
while (campaignSharedRows.hasNext()) {
var campaignSharedRow = campaignSharedRows.next();
if (sharedSetCampaigns[campaignSharedRow["SharedSetName"]] == undefined) {
sharedSetCampaigns[campaignSharedRow["SharedSetName"]] = [campaignSharedRow["CampaignId"]];
} else {
sharedSetCampaigns[campaignSharedRow["SharedSetName"]].push(campaignSharedRow["CampaignId"]);
}
}
// Map the shared sets' IDs (used in the criteria report below)
// to their names (used in the campaign report above)
var sharedSetReport = AdWordsApp.report(
"SELECT Name, SharedSetId, MemberCount, ReferenceCount, Type " +
"FROM SHARED_SET_REPORT " +
"WHERE ReferenceCount > 0 AND Type = NEGATIVE_KEYWORDS ");
var sharedSetRows = sharedSetReport.rows();
while (sharedSetRows.hasNext()) {
var sharedSetRow = sharedSetRows.next();
sharedSetNames[sharedSetRow["SharedSetId"]] = sharedSetRow["Name"];
}
// Collect the negative keyword text from the sets,
// and record it as a campaign level negative in the campaigns that use the set
/* DEPERECATED CODE
var sharedSetReport = AdWordsApp.report(
"SELECT SharedSetId, KeywordMatchType, Criteria " +
"FROM SHARED_SET_CRITERIA_REPORT ");
var sharedSetRows = sharedSetReport.rows();
while (sharedSetRows.hasNext()) {
var sharedSetRow = sharedSetRows.next();
var setName = sharedSetNames[sharedSetRow["SharedSetId"]];
if (sharedSetCampaigns[setName] !== undefined) {
for (var i=0; i -1 )){
searchIsExcluded = true;
break;
}
}
}
// Checks if the query is excluded by a campaign level negative
if (!searchIsExcluded && negativesByCampaign[queryRow["CampaignId"]] !== undefined) {
for (var i=0; i -1 )){
searchIsExcluded = true;
break;
}
}
}
if (searchIsExcluded) {continue;}
}
var currentWords = queryRow["Query"].split(" ");
if (campaignNGrams[queryRow["CampaignName"]] == undefined) {
campaignNGrams[queryRow["CampaignName"]] = [];
adGroupNGrams[queryRow["CampaignName"]] = {};
for (var n=minNGramLength; n 6) {
wordLength = "7+";
}
if (numberOfWords[wordLength] == undefined) {
numberOfWords[wordLength] = [];
}
for (var i=0; i 0) {
numberOfWords[wordLength][statColumns[i]] += stats[i];
} else {
numberOfWords[wordLength][statColumns[i]] = stats[i];
}
}
// Splits the query into n-grams and records the stats for each
for (var n=minNGramLength; n currentWords.length) {
break;
}
var doneNGrams = [];
for (var w=0; w < currentWords.length - n + 1; w++) {
var currentNGram = '="' + currentWords.slice(w,w+n).join(" ") + '"';
if (doneNGrams.indexOf(currentNGram) < 0) {
if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram] == undefined) {
campaignNGrams[queryRow["CampaignName"]][n][currentNGram] = {};
campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
}
if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
}
if (totalNGrams[n][currentNGram] == undefined) {
totalNGrams[n][currentNGram] = {};
totalNGrams[n][currentNGram]["Query Count"] = 0;
}
campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
totalNGrams[n][currentNGram]["Query Count"]++;
for (var i=0; i 0) {
campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
} else {
campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
}
if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
} else {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
}
if (totalNGrams[n][currentNGram][statColumns[i]] > 0) {
totalNGrams[n][currentNGram][statColumns[i]] += stats[i];
} else {
totalNGrams[n][currentNGram][statColumns[i]] = stats[i];
}
}
doneNGrams.push(currentNGram);
}
}
}
}
Logger.log("Finished analysing queries.");
//////////////////////////////////////////////////////////////////////////////
// Output the data into the spreadsheet
var wordLengthOutput = [];
var wordLengthFormat = [];
var outputs = [];
var formats = [];
for (var n=minNGramLength; n 0) {
printline.push(adGroupNGrams[campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[campaign][adGroup][n][nGram][divisor]);
} else {
printline.push("-");
}
}
outputs[n]['adgroup'].push(printline);
formats[n]['adgroup'].push(["0","0","0"].concat(formatting));
}
}
}
}
// Organise the campaign level stats into an array for output
for (var n=minNGramLength; n 0) {
printline.push(campaignNGrams[campaign][n][nGram][multiplier] / campaignNGrams[campaign][n][nGram][divisor]);
} else {
printline.push("-");
}
}
outputs[n]['campaign'].push(printline);
formats[n]['campaign'].push(["0","0"].concat(formatting));
}
}
}
// Organise the account level stats into an array for output
for (var n=minNGramLength; n 0) {
printline.push(totalNGrams[n][nGram][multiplier] / totalNGrams[n][nGram][divisor]);
} else {
printline.push("-");
}
}
outputs[n]['total'].push(printline);
formats[n]['total'].push(["0"].concat(formatting));
}
}
// Organise the word count analysis into an array for output
for (var i = 1; i<8; i++) {
if (i < 7) {
var wordLength = i;
} else {
var wordLength = "7+";
}
var printline = [wordLength];
if (numberOfWords[wordLength] == undefined) {
printline.push([0,0,0,0,"-","-","-","-"]);
} else {
for (var s=0; s 0) {
printline.push(numberOfWords[wordLength][multiplier] / numberOfWords[wordLength][divisor]);
} else {
printline.push("-");
}
}
}
wordLengthOutput.push(printline);
wordLengthFormat.push(formatting);
}
var filterText = "";
if (ignorePausedAdGroups) {
filterText = "Active ad groups";
} else {
filterText = "All ad groups";
}
if (ignorePausedCampaigns) {
filterText += " in active campaigns";
} else {
filterText += " in all campaigns";
}
if (campaignNameContains != "") {
filterText += " containing '" + campaignNameContains + "'";
if (campaignNameDoesNotContain != "") {
filterText += " and not containing '" + campaignNameDoesNotContain + "'";
}
} else if (campaignNameDoesNotContain != "") {
filterText += " not containing '" + campaignNameDoesNotContain + "'";
}
// Find or create the required sheets
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var campaignNGramName = [];
var adGroupNGramName = [];
var totalNGramName = [];
var campaignNGramSheet = [];
var adGroupNGramSheet = [];
var totalNGramSheet = [];
for (var n=minNGramLength; n
Leave a Comment