AdWords Script: Find Your Best And Worst Search Queries Using N-Grams

A few months ago, we helped you mine your AdWords search queries by finding the performance of each word used in a query. Now, that script is back with some extra features.

The old AdWords script just looked at individual words. But words can have a lot of meanings, so sometimes you’re missing out by looking at them on their own; maybe searches containing “free” perform poorly in general, but “free delivery” gets conversions. Looking at short phrases within queries gives more insight into the searcher’s intent while still being general enough to reflect lots of different searches.

So the new script doesn’t just look at individual words; it also lets you split your queries into n-grams. An n-gram is a phrase made of n words: a 1-gram is a single word, a 2-gram is a phrase made of two words, and so on. For example, “this four word phrase” contains three 2-grams (“this four,” “four word,” “word phrase”) and two 3-grams (“this four-word” and “four word phrase”).

The script adds up the clicks, impressions, cost, converted clicks and conversion value of each query containing the n-grams — and calculates the click-through rate, cost per click, conversion rate, CPA and value/cost — so you can see their impact.

If the performance is good, you might want to make new ad groups around the n-gram; if the performance is bad, you might want to exclude the n-gram as a negative phrase-match keyword. The data is shown at ad group level, as well as campaign and account level, so you can look at whatever granularity you want.

Looking at longer phrases provides more insight, but it also means the phrase appears in fewer queries. So we’ve also included a “Query Count” column that shows how many queries each phrase appeared in. That means you can concentrate on commonly occurring phrases.

Lastly (as in the old script), there’s a word count analysis to tell you if longer or shorter queries work best for you.

At the top of the script, there are some options you will need to change, depending on your preferences and account setup:

• The startDate and endDate determine the date range of the data.
• currencySymbol is you currency symbol, used for formatting.
• campaignNameContains can be used to make the script only look at some campaigns. For example, if it’s set to “High Priority,” the script will only look at search queries in campaigns with “high priority” in the name. This is not case-sensitive.
• campaignNameDoesNotContain can be used to make the script ignore some campaigns. For example, if it’s set to “Competitor,” then the script would not look at any campaigns with “competitor” in their names. Again, this is not case-sensitive.
• If ignorePausedCampaigns and ignorePausedAdGroups are both true, then the script will only look at currently active campaigns and ad groups. Set them to false if you want to look at data from currently paused campaigns/groups.
• If checkNegatives is true, then the script will check queries against the current negative keywords, so any queries that would now be excluded are ignored. If this is set to false, then all queries will be used. If you have a lot of negatives, and the script keeps timing out, try setting this to false.
• spreadsheetUrl is the URL of a Google Doc spreadsheet, which the results will be copied into. Create a blank spreadsheet and put the URL in here. Do not ignore this step!
• minNGramLength and maxNGramLength let you say how big or small the phrases you are looking at should be. For example, if you set minNGramLength to 2 and maxNGramLength to 4, it will look at 2-grams, 3-grams and 4-grams. If you set both minNGramLength and maxNGramLength to 2, it will only look at two-word phrases. Set both to 1 to look at only single words.
• If clearSpreadsheet is true, any data already in the spreadsheet will be overwritten. If it’s false, then the script’s results will be added at the end of the sheets.

If you find the script keeps timing out, it may be that your account is too big. Try running the script multiple times using campaignNameContains and campaignNameDoesNotContain to look at different campaigns each time. Set clearSpreadsheet to false so you don’t overwrite the data from previous runs. (If you want the account-level data, you can copy all of the n-grams from campaign level into a new worksheet and then use a SUMIF function to find the totals.)

To use the script, just copy and paste the following into your account:

	/**
	*
	* 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.
	*
	* Version: 2.2
	* Updated 2015-09-17: replacing 'KeywordText' with 'Criteria'
	* Updated 2016-10-11: replacing 'ConvertedClicks' with 'Conversions'
	* Google AdWords Script maintained on brainlabsdigital.com
	*
	**/
	function main() {
	//////////////////////////////////////////////////////////////////////////////
	// Options
	var startDate = "2015-07-01";
	var endDate = "2015-07-30";
	// 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
	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<sharedSetCampaigns[setName].length; i++) {
	var campaignId = sharedSetCampaigns[setName][i];
	if (negativesByCampaign[campaignId] == undefined) {
	negativesByCampaign[campaignId] =
	[[sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]];
	} else {
	negativesByCampaign[campaignId].push([sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]);
	}
	}
	}
	}
	Logger.log("Finished finding negative keywords");
	}// end if
	//////////////////////////////////////////////////////////////////////////////
	// Define the statistics to download or calculate, and their formatting
	var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"];
	var calculatedStats = [["CTR","Clicks","Impressions"],
	["CPC","Cost","Clicks"],
	["Conv. Rate","Conversions","Clicks"],
	["Cost / conv.","Cost","Conversions"],
	["Conv. value/cost","ConversionValue","Cost"]]
	var currencyFormat = currencySymbol + "#,##0.00";
	var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
	//////////////////////////////////////////////////////////////////////////////
	// Go through the search query report, remove searches already excluded by negatives
	// record the performance of each word in each remaining query
	var queryReport = AdWordsApp.report(
	"SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, " + statColumns.join(", ") + " " +
	"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
	"WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus +
	"DURING " + dateRange);
	var numberOfWords = [];
	var campaignNGrams = {};
	var adGroupNGrams = {};
	var totalNGrams = [];
	for (var n=minNGramLength; n<maxNGramLength+1; n++) {
	totalNGrams[n] = {};
	}
	var queryRows = queryReport.rows();
	while (queryRows.hasNext()) {
	var queryRow = queryRows.next();
	if (checkNegatives) {
	var searchIsExcluded = false;
	// Checks if the query is excluded by an ad group level negative
	if (negativesByGroup[queryRow["AdGroupId"]] !== undefined) {
	for (var i=0; i<negativesByGroup[queryRow["AdGroupId"]].length; i++) {
	if ( (negativesByGroup[queryRow["AdGroupId"]][i][1] == "exact" &&
	queryRow["Query"] == negativesByGroup[queryRow["AdGroupId"]][i][0]) ||
	(negativesByGroup[queryRow["AdGroupId"]][i][1] != "exact" &&
	(" "+queryRow["Query"]+" ").indexOf(" "+negativesByGroup[queryRow["AdGroupId"]][i][0]+" ") > -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<negativesByCampaign[queryRow["CampaignId"]].length; i++) {
	if ( (negativesByCampaign[queryRow["CampaignId"]][i][1] == "exact" &&
	queryRow["Query"] == negativesByCampaign[queryRow["CampaignId"]][i][0]) ||
	(negativesByCampaign[queryRow["CampaignId"]][i][1]!= "exact" &&
	(" "+queryRow["Query"]+" ").indexOf(" "+negativesByCampaign[queryRow["CampaignId"]][i][0]+" ") > -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<maxNGramLength+1; n++) {
	campaignNGrams[queryRow["CampaignName"]][n] = {};
	}
	}
	if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
	adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
	for (var n=minNGramLength; n<maxNGramLength+1; n++) {
	adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n] = {};
	}
	}
	var stats = [];
	for (var i=0; i<statColumns.length; i++) {
	stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
	}
	var wordLength = currentWords.length;
	if (wordLength > 6) {
	wordLength = "7+";
	}
	if (numberOfWords[wordLength] == undefined) {
	numberOfWords[wordLength] = [];
	}
	for (var i=0; i<statColumns.length; i++) {
	if (numberOfWords[wordLength][statColumns[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<maxNGramLength+1; n++) {
	if (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<statColumns.length; i++) {
	if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[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<maxNGramLength+1; n++) {
	outputs[n] = {};
	outputs[n]['campaign'] = [];
	outputs[n]['adgroup'] = [];
	outputs[n]['total'] = [];
	formats[n] = {};
	formats[n]['campaign'] = [];
	formats[n]['adgroup'] = [];
	formats[n]['total'] = [];
	}
	// Create headers
	var calcStatNames = [];
	for (var s=0; s<calculatedStats.length; s++) {
	calcStatNames.push(calculatedStats[s][0]);
	}
	var statNames = statColumns.concat(calcStatNames);
	for (var n=minNGramLength; n<maxNGramLength+1; n++) {
	outputs[n]['campaign'].push(["Campaign","Phrase","Query Count"].concat(statNames));
	outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Query Count"].concat(statNames));
	outputs[n]['total'].push(["Phrase","Query Count"].concat(statNames));
	}
	wordLengthOutput.push(["Word count"].concat(statNames));
	// Organise the ad group level stats into an array for output
	for (var n=minNGramLength; n<maxNGramLength+1; n++) {
	for (var campaign in adGroupNGrams) {
	for (var adGroup in adGroupNGrams[campaign]) {
	for (var nGram in adGroupNGrams[campaign][adGroup][n]) {
	// skips nGrams under the thresholds
	if (adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
	if (adGroupNGrams[campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
	if (adGroupNGrams[campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
	if (adGroupNGrams[campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
	if (adGroupNGrams[campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
	var printline = [campaign, adGroup, nGram, adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"]];
	for (var s=0; s<statColumns.length; s++) {
	printline.push(adGroupNGrams[campaign][adGroup][n][nGram][statColumns[s]]);
	}
	for (var s=0; s<calculatedStats.length; s++) {
	var multiplier = calculatedStats[s][1];
	var divisor = calculatedStats[s][2];
	if (adGroupNGrams[campaign][adGroup][n][nGram][divisor] > 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<maxNGramLength+1; n++) {
	for (var campaign in campaignNGrams) {
	for (var nGram in campaignNGrams[campaign][n]) {
	// skips nGrams under the thresholds
	if (campaignNGrams[campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
	if (campaignNGrams[campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
	if (campaignNGrams[campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
	if (campaignNGrams[campaign][n][nGram]["Cost"] < costThreshold) {continue;}
	if (campaignNGrams[campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
	var printline = [campaign, nGram, campaignNGrams[campaign][n][nGram]["Query Count"]];
	for (var s=0; s<statColumns.length; s++) {
	printline.push(campaignNGrams[campaign][n][nGram][statColumns[s]]);
	}
	for (var s=0; s<calculatedStats.length; s++) {
	var multiplier = calculatedStats[s][1];
	var divisor = calculatedStats[s][2];
	if (campaignNGrams[campaign][n][nGram][divisor] > 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<maxNGramLength+1; n++) {
	for (var nGram in totalNGrams[n]) {
	// skips n-grams under the thresholds
	if (totalNGrams[n][nGram]["Query Count"] < queryCountThreshold) {continue;}
	if (totalNGrams[n][nGram]["Impressions"] < impressionThreshold) {continue;}
	if (totalNGrams[n][nGram]["Clicks"] < clickThreshold) {continue;}
	if (totalNGrams[n][nGram]["Cost"] < costThreshold) {continue;}
	if (totalNGrams[n][nGram]["Conversions"] < conversionThreshold) {continue;}
	var printline = [nGram, totalNGrams[n][nGram]["Query Count"]];
	for (var s=0; s<statColumns.length; s++) {
	printline.push(totalNGrams[n][nGram][statColumns[s]]);
	}
	for (var s=0; s<calculatedStats.length; s++) {
	var multiplier = calculatedStats[s][1];
	var divisor = calculatedStats[s][2];
	if (totalNGrams[n][nGram][divisor] > 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<statColumns.length; s++) {
	printline.push(numberOfWords[wordLength][statColumns[s]]);
	}
	for (var s=0; s<calculatedStats.length; s++) {
	var multiplier = calculatedStats[s][1];
	var divisor = calculatedStats[s][2];
	if (numberOfWords[wordLength][divisor] > 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<maxNGramLength+1; n++) {
	if (n==1) {
	campaignNGramName[n] = "Campaign Word Analysis";
	adGroupNGramName[n] = "Ad Group Word Analysis";
	totalNGramName[n] = "Account Word Analysis";
	} else {
	campaignNGramName[n] = "Campaign " + n + "-Gram Analysis";
	adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis";
	totalNGramName[n] = "Account " + n + "-Gram Analysis";
	}
	campaignNGramSheet[n] = spreadsheet.getSheetByName(campaignNGramName[n]);
	if (campaignNGramSheet[n] == null) {
	campaignNGramSheet[n] = spreadsheet.insertSheet(campaignNGramName[n]);
	}
	adGroupNGramSheet[n] = spreadsheet.getSheetByName(adGroupNGramName[n]);
	if (adGroupNGramSheet[n] == null) {
	adGroupNGramSheet[n] = spreadsheet.insertSheet(adGroupNGramName[n]);
	}
	totalNGramSheet[n] = spreadsheet.getSheetByName(totalNGramName[n]);
	if (totalNGramSheet[n] == null) {
	totalNGramSheet[n] = spreadsheet.insertSheet(totalNGramName[n]);
	}
	}
	var wordCountSheet = spreadsheet.getSheetByName("Word Count Analysis");
	if (wordCountSheet == null) {
	wordCountSheet = spreadsheet.insertSheet("Word Count Analysis");
	}
	// Write the output arrays to the spreadsheet
	for (var n=minNGramLength; n<maxNGramLength+1; n++) {
	var nGramName = n + "-Grams";
	if (n == 1) {
	nGramName = "Words";
	}
	writeOutput(outputs[n]['campaign'], formats[n]['campaign'], campaignNGramSheet[n], nGramName, "Campaign", filterText, clearSpreadsheet);
	writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
	writeOutput(outputs[n]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
	}
	writeOutput(wordLengthOutput, wordLengthFormat, wordCountSheet, "Word Count", "Account", filterText, clearSpreadsheet);
	Logger.log("Finished writing to spreadsheet.");
	} // end main function
	function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
	for (var i=0;i<5;i++) {
	try {
	if (clearSpreadsheet) {
	sheet.clear();
	}
	if (nGramName == "Word Count") {
	sheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Word Count");
	} else {
	sheet.getRange("R1C1").setValue("Analysis of " + nGramName + " in Search Query Report, By " + levelName);
	}
	sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText);
	var lastRow = sheet.getLastRow();
	if (formatArray.length == 0) {
	sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + nGramName.toLowerCase() + " found within the thresholds.");
	} else {
	sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray);
	sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray);
	var sortByColumns = [];
	if (outputArray[0][0] == "Campaign" || outputArray[0][0] == "Word count") {
	sortByColumns.push({column: 1, ascending: true});
	}
	if (outputArray[0][1] == "Ad Group") {
	sortByColumns.push({column: 2, ascending: true});
	}
	sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false});
	sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false});
	sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns);
	}
	break;
	} catch (e) {
	if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
	Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
	try {
	sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue("Not enough space to write the data - try again in an empty spreadsheet");
	} catch (e2) {
	Logger.log("Error writing 'not enough space' message: " + e2);
	}
	break;
	}
	if (i == 4) {
	Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
	}
	}
	}
	}

view raw Search Query Mining Tool.js hosted with ❤ by GitHub

Feel free to post any other comments or error messages below and we’ll try to help.

The post AdWords Script: Find Your Best And Worst Search Queries Using N-Grams appeared first on Search Engine Land.

from SEO Rank Video Blog http://ift.tt/1i1NTzZ
via IFTTT