Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get an n-gram analysis of Google Ads ad texts
/**
*
* Ad Text N-Gram Mining Tool
*
* Get aggregated metrics for when the same word sequence is used in ads across your account.
* Discover better performing phrases from all your Google Ads.
*
* Based on a script by Daniel Gilbert and BrainLabsDigital.com (https://searchengineland.com/brainlabs-script-find-best-worst-search-queries-using-n-grams-228379)
*
* Adapted by Fred Vallaeys and Optmyzr.com to work with expanded ad text rather than search terms data
*
**/
function main() {
//////////////////////////////////////////////////////////////////////////////
// Options
var startDate = "2019-01-01";
var endDate = "2019-02-10";
// The start and end date of the date range for your 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 = false;
// 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 = false;
// 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 includeHeadline = true;
// Set this to true if you want to do analysis of words in Ad Headlines
var includeDescription = true;
// Set this to true if you want to do analysis of words in Ad Description Lines
var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/xxx-yyy/edit#gid=0"; // <--- Put your own Google Sheet URL here!!!
// The URL of the Google Doc the results will be put into.
var minNGramLength = 2;
var maxNGramLength = 6;
// 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'] ";
}
//////////////////////////////////////////////////////////////////////////////
// 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%"];
var adColumns = ['HeadlinePart1', 'HeadlinePart2', 'ExpandedTextAdHeadlinePart3', 'Description', 'ExpandedTextAdDescription2'];
//////////////////////////////////////////////////////////////////////////////
// Go through the search query report, remove searches already excluded by negatives
// record the performance of each word in each remaining query
var query = [
"SELECT CampaignName, CampaignId, AdGroupId, AdGroupName,",
adColumns.join(", ") + ', ',
statColumns.join(", "),
"FROM AD_PERFORMANCE_REPORT",
"WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus,
"DURING " + dateRange
].join(' ');
var queryReport = AdWordsApp.report(query, {apiVersion: 'v201809'});
var numberOfWords = {}, totalNGrams = {};
var campaignNGrams = {};
var adGroupNGrams = {};
if(includeHeadline) {
numberOfWords['Headline'] = [];
totalNGrams['Headline'] = [];
campaignNGrams['Headline'] = {};
adGroupNGrams['Headline'] = {};
}
if(includeDescription) {
numberOfWords['Description'] = [];
totalNGrams['Description'] = [];
campaignNGrams['Description'] = {};
adGroupNGrams['Description'] = {};
}
for(var type in totalNGrams) {
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
totalNGrams[type][n] = {};
}
}
var queryRows = queryReport.rows();
while (queryRows.hasNext()) {
var queryRow = queryRows.next();
var headline = [queryRow['HeadlinePart1'], queryRow['HeadlinePart2'], queryRow['ExpandedTextAdHeadlinePart3']].join(' ').replace(/[\.\!\?]/g,'');
var desc = [queryRow['Description'], queryRow['ExpandedTextAdDescription2']].join(' ').replace(/[\.\!\?]/g,'');
var toCheck = {};
if(includeHeadline) {
toCheck['Headline'] = headline;
}
if(includeDescription) {
toCheck['Description'] = desc;
}
for(var type in toCheck) {
var currentWords = toCheck[type].split(" ");
if (campaignNGrams[type][queryRow["CampaignName"]] == undefined) {
campaignNGrams[type][queryRow["CampaignName"]] = [];
adGroupNGrams[type][queryRow["CampaignName"]] = {};
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
campaignNGrams[type][queryRow["CampaignName"]][n] = {};
}
}
if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
for (var n=minNGramLength; n<maxNGramLength+1; n++) {
adGroupNGrams[type][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[type][wordLength] == undefined) {
numberOfWords[type][wordLength] = [];
}
for (var i=0; i<statColumns.length; i++) {
if (numberOfWords[type][wordLength][statColumns[i]] > 0) {
numberOfWords[type][wordLength][statColumns[i]] += stats[i];
} else {
numberOfWords[type][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[type][queryRow["CampaignName"]][n][currentNGram] == undefined) {
campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram] = {};
campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
}
if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
}
if (totalNGrams[type][n][currentNGram] == undefined) {
totalNGrams[type][n][currentNGram] = {};
totalNGrams[type][n][currentNGram]["Query Count"] = 0;
}
campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
totalNGrams[type][n][currentNGram]["Query Count"]++;
for (var i=0; i<statColumns.length; i++) {
if (campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] > 0) {
campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
} else {
campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
}
if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
} else {
adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
}
if (totalNGrams[type][n][currentNGram][statColumns[i]] > 0) {
totalNGrams[type][n][currentNGram][statColumns[i]] += stats[i];
} else {
totalNGrams[type][n][currentNGram][statColumns[i]] = stats[i];
}
}
doneNGrams.push(currentNGram);
}
}
}
}
}
Logger.log("Finished analysing queries.");
//////////////////////////////////////////////////////////////////////////////
// Output the data into the spreadsheet
for(var type in totalNGrams) {
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","Word Count"].concat(statNames));
outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Word Count"].concat(statNames));
outputs[n]['total'].push(["Phrase","Word 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[type]) {
for (var adGroup in adGroupNGrams[type][campaign]) {
for (var nGram in adGroupNGrams[type][campaign][adGroup][n]) {
// skips nGrams under the thresholds
if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
var printline = [campaign, adGroup, nGram, adGroupNGrams[type][campaign][adGroup][n][nGram]["Query Count"]];
for (var s=0; s<statColumns.length; s++) {
printline.push(adGroupNGrams[type][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[type][campaign][adGroup][n][nGram][divisor] > 0) {
printline.push(adGroupNGrams[type][campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[type][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[type]) {
for (var nGram in campaignNGrams[type][campaign][n]) {
// skips nGrams under the thresholds
if (campaignNGrams[type][campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
if (campaignNGrams[type][campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
if (campaignNGrams[type][campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
if (campaignNGrams[type][campaign][n][nGram]["Cost"] < costThreshold) {continue;}
if (campaignNGrams[type][campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
var printline = [campaign, nGram, campaignNGrams[type][campaign][n][nGram]["Query Count"]];
for (var s=0; s<statColumns.length; s++) {
printline.push(campaignNGrams[type][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[type][campaign][n][nGram][divisor] > 0) {
printline.push(campaignNGrams[type][campaign][n][nGram][multiplier] / campaignNGrams[type][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[type][n]) {
// skips n-grams under the thresholds
if (totalNGrams[type][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
if (totalNGrams[type][n][nGram]["Impressions"] < impressionThreshold) {continue;}
if (totalNGrams[type][n][nGram]["Clicks"] < clickThreshold) {continue;}
if (totalNGrams[type][n][nGram]["Cost"] < costThreshold) {continue;}
if (totalNGrams[type][n][nGram]["Conversions"] < conversionThreshold) {continue;}
var printline = [nGram, totalNGrams[type][n][nGram]["Query Count"]];
for (var s=0; s<statColumns.length; s++) {
printline.push(totalNGrams[type][n][nGram][statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (totalNGrams[type][n][nGram][divisor] > 0) {
printline.push(totalNGrams[type][n][nGram][multiplier] / totalNGrams[type][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[type][wordLength] == undefined) {
printline.push(0,0,0,0,0,"-","-","-","-","-");
} else {
for (var s=0; s<statColumns.length; s++) {
printline.push(numberOfWords[type][wordLength][statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (numberOfWords[type][wordLength][divisor] > 0) {
printline.push(numberOfWords[type][wordLength][multiplier] / numberOfWords[type][wordLength][divisor]);
} else {
printline.push("-");
}
}
}
// Logger.log(printline);
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 (" + type + ")" ;
adGroupNGramName[n] = "Ad Group Word Analysis (" + type + ")" ;
totalNGramName[n] = "Account Word Analysis (" + type + ")" ;
} else {
campaignNGramName[n] = "Campaign " + n + "-Gram Analysis (" + type + ")" ;
adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis (" + type + ")" ;
totalNGramName[n] = "Account " + n + "-Gram Analysis (" + type + ")" ;
}
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 (" + type + ")");
if (wordCountSheet == null) {
wordCountSheet = spreadsheet.insertSheet("Word Count Analysis (" + type + ")");
}*/
// 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 + "'");
}
}
}
}
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.