-
-
Save siliconvallaeys/2c9edacc04c770bbc28451b828ff721d to your computer and use it in GitHub Desktop.
/** | |
* | |
* 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 + "'"); | |
} | |
} | |
} | |
} |
This is already an AdWords (Google Ads) script so when you install it in your Google Ads account, it will ask you to 'authorize' access to your ads data. If you haven't worked with Google Ads scripts before, this should help: https://developers.google.com/google-ads/scripts/docs/your-first-script
For whatever reason when I run this script I keep getting "Could not find any campaigns with impressions and the specified options.
" Any suggestions on what it may be?
For whatever reason when I run this script I keep getting "Could not find any campaigns with impressions and the specified options.
" Any suggestions on what it may be?
I encounter the same issue. No solution yet. Some one Twitter mentioned that it probably had to do with the timeframe not working anymore.
@CaptainKayo & @WalterNL, any news on that matter? I'm facing the same issue, thanks in advance!
this code is working for me:
font: https://searchengineland.com/brainlabs-script-find-best-worst-search-queries-using-n-grams-228379
I just set var campaignNameContains = " ";
this code is working for me:
font: https://searchengineland.com/brainlabs-script-find-best-worst-search-queries-using-n-grams-228379
I just set
var campaignNameContains = " ";
I use it too, but that script performs a different function. The one here is supposed to detect adcopy performance.
@CaptainKayo thank you for the heads up, I didn't realize that! Sorry!
For this one, did you found any solution?
Kind regards
@caioricciuti
I added a space and now it works again. Gotta love coding!
@caioricciuti
I added a space and now it works again. Gotta love coding!
I can confirm it works at my end! Thanks for diving in to it!
How we can optimize this script if we want to set it up for a single campaign?
Can I run it from the Spreadsheet itself, by eanbling the service API? Which one? AdSense? Analytics? Thanks for sharing such a great content!
@santosonit Not sure what you mean but since it is an Ad related script I would try enable Ads api's, the script will get all info from your campaigns so Ads api's is the way to go.
You're right. After I made the question, I read/understood that by enabling the API I could potentially use this script (with some modifications) in a script editor bounded to a spreadsheet. Thanks, @caioricciuti
Only me not geting Headlines anymore? Just Descriptions that is being populated with data
I am interested in doing this analysis for ad words. Could you provide instructions on how to set this up. I can see the url part. How can I add link this with my google adwords account.Thank you