Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mines the search query report to find phrases that appear in the same searches as user specified text
/**
*
* Associated Search Analysis
*
* This script takes in a list of pieces of text, and finds the words and
* phrases that appear in the same search queries as that text. The found
* phrases and the performance of the queries they appear in are then reported
* in a Google Doc spreadsheet.
*
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
//////////////////////////////////////////////////////////////////////////////
// Options
var startDate = "2017-08-01";
var endDate = "2017-08-31";
// 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 campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns.
// For example ["Display"] would ignore any campaigns with 'Display' in the name,
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or
// 'Shopping' in the name.
// Leave as [] to not exclude any campaigns.
var campaignNameContains = [];
// Use this if you only want to look at some campaigns.
// For example ["Brand"] would only look at campaigns with 'Brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic'
// in the name.
// Leave as [] to include all campaigns.
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 reportAdGroupLevel = true;
// Set this to true to report on phrases used in individual ad groups.
// Data is always shown at campaign and account level.
var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.
var textOfInterest = ["example one", "example two"];
// This is the text you are interested in
// The report will show what phrases appear in searches containing this text.
// Text should be in double quotes and comma separated, eg ["yes", "no"]
var minNGramLength = 1;
var maxNGramLength = 2;
// The word length of the phrases that appear alongside the text of interest.
// 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;
// Set this to true to clear any data already in the spreadsheet when the
// script is run.
// If this is set to false, then the script will instead append information to
// the bottom of the sheets. Use this if you are running the script multiple
// times on different parts of the account or looking for different text
//////////////////////////////////////////////////////////////////////////////
// Thresholds
var queryCountThreshold = 0;
var impressionThreshold = 100;
var clickThreshold = 0;
var costThreshold = 0;
var conversionThreshold = 0;
// Words will be ignored if their statistics are lower than any of these thresholds
//////////////////////////////////////////////////////////////////////////////
function main() {
// Check the spreadsheet has been entered, and that it works
var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");
// Get the IDs of the campaigns to look at
var activeCampaignIds = getCampaignIds();
var textOfInterestTreated = []
for (var i=0; i<textOfInterest.length; i++) {
textOfInterestTreated[i] = " " + textOfInterest[i].trim().toLowerCase() + " ";
}
//////////////////////////////////////////////////////////////////////////////
// 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%"];
//////////////////////////////////////////////////////////////////////////////
// Initialise objects to hold the data
var campaignNGrams = {};
var adGroupNGrams = {};
var totalNGrams = [];
initialiseContainer(totalNGrams, minNGramLength, maxNGramLength, textOfInterest);
//////////////////////////////////////////////////////////////////////////////
// Go through the search query report, remove searches already excluded by negatives
// record the performance of each word in each remaining query
var whereAdGroupStatus = "";
if (ignorePausedAdGroups) {
var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
} else {
whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
}
var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
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 queryRows = queryReport.rows();
while (queryRows.hasNext()) {
var queryRow = queryRows.next();
var query = " " + queryRow["Query"] + " ";
var includedWords = [];
var includedWordsSpaced = [];
for (var i=0; i<textOfInterest.length; i++) {
if (query.indexOf(textOfInterestTreated[i]) > -1) {
includedWords.push(textOfInterest[i]);
includedWordsSpaced.push(textOfInterestTreated[i]);
}
}
if (includedWords.length == 0) {
continue;
}
var currentWords = queryRow["Query"].split(" ");
if (campaignNGrams[queryRow["CampaignName"]] == undefined) {
campaignNGrams[queryRow["CampaignName"]] = [];
initialiseContainer(campaignNGrams[queryRow["CampaignName"]], minNGramLength, maxNGramLength, textOfInterest);
adGroupNGrams[queryRow["CampaignName"]] = {};
}
if (reportAdGroupLevel && adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
initialiseContainer(adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]], minNGramLength, maxNGramLength, textOfInterest);
}
var stats = [];
for (var i=0; i<statColumns.length; i++) {
stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
}
// 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 currentNGramRaw = currentWords.slice(w,w+n).join(" ");
var currentNGramSpaced = " " + currentNGramRaw + " ";
var currentNGram = '="' + currentNGramRaw + '"';
if (doneNGrams.indexOf(currentNGram) > -1) {
continue;
}
doneNGrams.push(currentNGram);
for (var q=0; q<includedWords.length; q++) {
if (currentNGramSpaced == includedWordsSpaced[q] || includedWordsSpaced[q].indexOf(currentNGramSpaced) > -1) {
continue;
}
if (campaignNGrams[queryRow["CampaignName"]][n][includedWords[q]][currentNGram] == undefined) {
campaignNGrams[queryRow["CampaignName"]][n][includedWords[q]][currentNGram] = {};
initialiseDataObject(campaignNGrams[queryRow["CampaignName"]][n][includedWords[q]][currentNGram], statColumns);
}
if (totalNGrams[n][includedWords[q]][currentNGram] == undefined) {
totalNGrams[n][includedWords[q]][currentNGram] = {};
initialiseDataObject(totalNGrams[n][includedWords[q]][currentNGram], statColumns);
}
campaignNGrams[queryRow["CampaignName"]][n][includedWords[q]][currentNGram]["Query Count"]++;
totalNGrams[n][includedWords[q]][currentNGram]["Query Count"]++;
for (var i=0; i<statColumns.length; i++) {
campaignNGrams[queryRow["CampaignName"]][n][includedWords[q]][currentNGram][statColumns[i]] += stats[i];
totalNGrams[n][includedWords[q]][currentNGram][statColumns[i]] += stats[i];
}
if (reportAdGroupLevel) {
if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][includedWords[q]][currentNGram] == undefined) {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][includedWords[q]][currentNGram] = {};
initialiseDataObject(adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][includedWords[q]][currentNGram], statColumns);
}
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][includedWords[q]][currentNGram]["Query Count"]++;
for (var i=0; i<statColumns.length; i++) {
adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][includedWords[q]][currentNGram][statColumns[i]] += stats[i];
}
}
}
}
}
}
Logger.log("Finished analysing queries.");
//////////////////////////////////////////////////////////////////////////////
// Output the data into the spreadsheet
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(["Word of Interest","Campaign","Phrase","Query Count"].concat(statNames));
outputs[n]['total'].push(["Word of Interest","Phrase","Query Count"].concat(statNames));
}
// Organise the ad group level stats into an array for output
if (reportAdGroupLevel) {
var adGroupLineFormatting = [0,0,0,0].concat(formatting);
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
outputs[n]['adgroup'].push(["Word of Interest", "Campaign","Ad Group","Phrase","Query Count"].concat(statNames));
for (var campaign in adGroupNGrams) {
for (var adGroup in adGroupNGrams[campaign]) {
for (var wordOfInterest in adGroupNGrams[campaign][adGroup][n]) {
for (var nGram in adGroupNGrams[campaign][adGroup][n][wordOfInterest]) {
var dataObject = adGroupNGrams[campaign][adGroup][n][wordOfInterest][nGram];
var printlineStart = [wordOfInterest, campaign, adGroup, nGram];
addToOutput(dataObject, printlineStart, outputs[n]['adgroup'], formats[n]['adgroup'], adGroupLineFormatting, statColumns, calculatedStats);
}
}
}
}
}
}
// Organise the campaign level stats into an array for output
var campaignLineFormatting = [0,0,0].concat(formatting);
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
for (var campaign in campaignNGrams) {
for (var wordOfInterest in campaignNGrams[campaign][n]) {
for (var nGram in campaignNGrams[campaign][n][wordOfInterest]) {
var dataObject = campaignNGrams[campaign][n][wordOfInterest][nGram];
var printlineStart = [wordOfInterest, campaign, nGram];
addToOutput(dataObject, printlineStart, outputs[n]['campaign'], formats[n]['campaign'], campaignLineFormatting, statColumns, calculatedStats);
}
}
}
}
// Organise the account level stats into an array for output
var totalLineFormatting = [0,0].concat(formatting);
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
for (var wordOfInterest in totalNGrams[n]) {
for (var nGram in totalNGrams[n][wordOfInterest]) {
var dataObject = totalNGrams[n][wordOfInterest][nGram];
var printlineStart = [wordOfInterest, nGram];
addToOutput(dataObject, printlineStart, outputs[n]['total'], formats[n]['total'], totalLineFormatting, statColumns, calculatedStats);
}
}
}
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]);
}
if (reportAdGroupLevel) {
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]);
}
}
// 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]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
if (reportAdGroupLevel) {
writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
}
}
Logger.log("Finished writing to spreadsheet.");
} // end main function
// Check the spreadsheet URL has been entered, and that it works
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL.");
}
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
// Checks if you can edit the spreadsheet
var sheet = spreadsheet.getSheets()[0];
var sheetName = sheet.getName();
sheet.setName(sheetName);
return spreadsheet;
} catch (e) {
throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
}
}
// Get the IDs of campaigns which match the given options
function getCampaignIds() {
var whereStatement = "WHERE ";
var whereStatementsArray = [];
var campaignIds = [];
if (ignorePausedCampaigns) {
whereStatement += "CampaignStatus = ENABLED ";
} else {
whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] ";
}
for (var i=0; i<campaignNameDoesNotContain.length; i++) {
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' ";
}
if (campaignNameContains.length == 0) {
whereStatementsArray = [whereStatement];
} else {
for (var i=0; i<campaignNameContains.length; i++) {
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" ');
}
}
for (var i=0; i<whereStatementsArray.length; i++) {
var campaignReport = AdWordsApp.report(
"SELECT CampaignId " +
"FROM CAMPAIGN_PERFORMANCE_REPORT " +
whereStatementsArray[i] +
"DURING LAST_30_DAYS");
var rows = campaignReport.rows();
while (rows.hasNext()) {
var row = rows.next();
campaignIds.push(row['CampaignId']);
}
}
if (campaignIds.length == 0) {
throw("No campaigns found with the given settings.");
}
Logger.log(campaignIds.length + " campaigns found");
return campaignIds;
}
function initialiseContainer(containerObject, minNGramLength, maxNGramLength, textOfInterest) {
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
containerObject[n] = {};
for (var i=0; i<textOfInterest.length; i++) {
containerObject[n][textOfInterest[i]] = {};
}
}
}
function initialiseDataObject(dataObject, statColumns) {
dataObject["Query Count"] = 0;
for (var i=0; i<statColumns.length; i++) {
dataObject[statColumns[i]] = 0;
}
}
function addToOutput(dataObject, printlineStart, output, format, formatting, statColumns, calculatedStats) {
// skips nGrams under the thresholds
if (dataObject["Query Count"] < queryCountThreshold) {return;}
if (dataObject["Impressions"] < impressionThreshold) {return;}
if (dataObject["Clicks"] < clickThreshold) {return;}
if (dataObject["Cost"] < costThreshold) {return;}
if (dataObject["Conversions"] < conversionThreshold) {return;}
var printline = printlineStart.slice();
printline.push(dataObject["Query Count"]);
for (var s=0; s<statColumns.length; s++) {
printline.push(dataObject[statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (dataObject[divisor] > 0) {
printline.push(dataObject[multiplier] / dataObject[divisor]);
} else {
printline.push("-");
}
}
output.push(printline);
format.push(formatting);
}
function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
for (var i=0;i<5;i++) {
try {
if (clearSpreadsheet) {
sheet.clear();
}
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][1] == "Campaign") {
sortByColumns.push({column: 2, ascending: true});
}
if (outputArray[0][2] == "Ad Group") {
sortByColumns.push({column: 3, ascending: true});
}
sortByColumns.push({column: 1, 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 + "'");
}
}
}
}
@bpstreit62

This comment has been minimized.

Copy link

bpstreit62 commented Oct 4, 2017

I'm getting "Parsing error. Please check your selector. (line 132)", I'm not well versed enough in js to see what the issue could be. Could you double check the code?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.