Google Ads scripts are here to stay. Specific scripts for PPC tasks should be at the top of your to-do list.
Search Engine Journal, Frederick Vallaeys
If you’re an Ads manager, you most certainly know that monitoring tasks and daily optimization of your campaigns can become a long and tedious task. To get rid of this tension, there are already certain measures to automate processes like implementing an automatic rule. However, there are times when automatic rules aren’t sufficient and you need an advanced solution, Google Ads Scripts.
Google Ads scripts help you automate repetitive, mundane tasks while providing you time to think deeply into bigger things like overall marketing strategy and budget.
In this post, I’m going to provide you with the best Google Ads Scripts that can help you improve your PPC efforts.
Account Management Scripts
View and change all your budgets on Google Sheet
Problem
Keeping properly up with the expenses and budget while managing multiple Ads account can be tricky.
With this script by Daniel Gilbert, you can modify and download all your budgets across multiple Google Ads accounts in a single Google Sheet.
The script first presents a complete list of all budgets for every account. You’ll have chance to analyze both shared and campaign-specific budgets. The best part is that you can also make changes from Google Sheet without doing the same on your Ads account.
// ID: 8998c4c7e914892930e4f9554e4fe006
/**
*
* Budget Manager
*
* This script allows Google Ads MCC Accounts to monitor the performance
* of various budgets on child accounts based on defined
* metrics.
*
* Version: 1.0
* Google Ads Script maintained on brainlabsdigital.com
*
**/
//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////
//Options
//Run Type
// Enter 'DOWNLOAD' to download budgets, or 'UPDATE' to update budgets once set new values on the sheet.
// Include the quotation marks.
var RUN_TYPE = 'SET_RUN_TYPE_HERE';
//Spreadsheet URL
var SPREADSHEET_URL = 'YOUR-SPREADSHEET-URL-HERE';
//Ignore Paused Campaigns
// Set to 'false' to include paused campaigns in data.
var ignorePausedCampaigns = true;
//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////
// Indices
var CONFIG_HEADER_ROW = 1;
var CONFIG_FIRST_DATA_ROW = 3;
var DASHBOARD_HEADER_ROW = 6;
var DASHBOARD_FIRST_DATA_ROW = 7;
//////////////////////////////////////////////////////////////////////////////
// Functions
function main() {
var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
var runType = RUN_TYPE;
if (runType.toLowerCase() == "download") {
download(spreadsheet);
} else if (runType.toLowerCase() == "update") {
update(spreadsheet);
} else {
throw ("input for RUN_TYPE variable \"" + runType +
"\" not recognised. Please enter either 'DOWNLOAD' or 'UPDATE' (including quotation marks)");
}
}
function update(spreadsheet) {
var dashboardSheet = spreadsheet.getSheetByName("Budget Dashboard");
var budgetsToChange = getBudgetsToChange(dashboardSheet);
Logger.log("Updating budgets")
for (var i = 0; i < budgetsToChange.length; i++) {
budgetToChange = budgetsToChange[i];
updateBudgetOnGoogleAds(budgetToChange);
}
Logger.log("Clearing sheet");
clearSheet(dashboardSheet);
Logger.log("Re-downloading budgets");
download(spreadsheet);
Logger.log("Success");
}
function download(spreadsheet) {
var configSheet = spreadsheet.getSheetByName("Configuration");
var dashboardSheet = spreadsheet.getSheetByName("Budget Dashboard");
var tz = AdsApp.currentAccount().getTimeZone();
//Store Sheet Headers and Indices
var configHeaders = configSheet.getRange(CONFIG_HEADER_ROW + ":" + CONFIG_HEADER_ROW).getValues()[0];
var statusIndex = configHeaders.indexOf("Status");
var accountIDIndex = configHeaders.indexOf("Account ID");
var accountNameIndex = configHeaders.indexOf("Account Name");
var campaignNameContainsIndex = configHeaders.indexOf("Campaign Name Contains");
var campaignNameDoesNotContainIndex = configHeaders.indexOf("Campaign Name Doesn't Contain");
//Get all rows of data.
var allData = configSheet.getRange(CONFIG_FIRST_DATA_ROW, 1, configSheet.getLastRow() - (CONFIG_HEADER_ROW + 1), configSheet.getLastColumn()).getValues();
//For each row of data:
Logger.log("Verifying each row of data...")
for (var i = 0; i < allData.length; i++) {
var row = allData[i];
if (row[statusIndex] == "Paused") {
continue;
};
var childAccount = getAccountId(row[accountIDIndex]);
AdsManagerApp.select(childAccount);
var combinedQueries = makeQueries(row[campaignNameContainsIndex], row[campaignNameDoesNotContainIndex])
var budgetData = getBudgetData(combinedQueries, row[accountNameIndex]);
var accountCurrencyCode = getAccountCurrencyCode();
var accountDataRow = [row[accountNameIndex], row[accountIDIndex]]
var dashboardRows = budgetData.map(function (budgetDataRow) {
return accountDataRow.concat(budgetDataRow.map(function (field) {
return field.value;
})).concat([accountCurrencyCode])
});
Logger.log(dashboardRows)
writeRowsOntoSheet(dashboardSheet, dashboardRows);
}
setDate(dashboardSheet, tz);
Logger.log("Success.")
}
function getBudgetsToChange(sheet) {
var dataRange = sheet.getRange(DASHBOARD_HEADER_ROW, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
var headers = dataRange.shift();
var allBudgets = dataRange.map(function (budgetData) {
return headers.reduce(function (budgetRow, header, index) {
budgetRow[header] = budgetData[index];
return budgetRow;
}, {});
});
budgetsToChange = [];
for (var i = 0; i < allBudgets.length; i++) {
var budgetRow = allBudgets[i];
var updateBudgetCol = budgetRow["Set New Budget"];
if (updateBudgetCol.length !== 0) {
budgetsToChange.push(budgetRow);
}
}
return budgetsToChange;
}
function updateBudgetOnGoogleAds(budgetToChange) {
var childAccount = getAccountId(budgetToChange["Account ID"], budgetToChange["Account Name"])
AdsManagerApp.select(childAccount);
var budgetIterator = AdsApp.budgets().withCondition("BudgetId = " + budgetToChange["Budget ID"]).get();
if (budgetIterator.hasNext()) {
var budget = budgetIterator.next();
try {
budget.setAmount(budgetToChange["Set New Budget"]);
} catch (e) {
throw ("Unable to update budgets: " + e)
}
}
}
function getSpreadsheet(spreadsheetUrl) {
Logger.log('Checking spreadsheet: ' + spreadsheetUrl + ' is valid.');
if (spreadsheetUrl.replace(/[AEIOU]/g, "X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
throw ("Problem with " + SPREADSHEET_URL +
" URL: make sure you've replaced the default with a valid spreadsheet URL."
);
}
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
var sheet = spreadsheet.getSheets()[0];
var sheetName = sheet.getName();
sheet.setName(sheetName);
return spreadsheet;
} catch (e) {
throw ("Problem with " + spreadsheetUrl + " URL: '" + e + "'. You may not have edit access");
}
}
function getAccountId(accountId) {
var childAccount = AdsManagerApp.accounts().withIds([accountId]).get();
if (childAccount.hasNext()) {
return childAccount.next();
} else {
throw ("Could not find account with ID: " + accountId + ". Check you have entered a correct account ID (MCC IDs not valid)");
}
}
function clearSheet(sheet) {
sheet.getRange(DASHBOARD_FIRST_DATA_ROW, 1, sheet.getLastRow(), sheet.getLastColumn()).clear({
contentsOnly: true
});
}
function makeQueries(campaignNameContains, campaignNameDoesNotContain) {
var campaignNameContains = campaignNameContains.split(',').map(function (item) {
return item.trim();
});
var campaignNameDoesNotContain = campaignNameDoesNotContain.split(',').map(function (item) {
return item.trim();
});
var campaignFilterStatements = makeCampaignFilterStatements(campaignNameContains, campaignNameDoesNotContain, ignorePausedCampaigns);
var queries = addDateToStatements(campaignFilterStatements);
return queries;
}
function makeCampaignFilterStatements(campaignNameContains, campaignNameDoesNotContain, ignorePausedCampaigns) {
var whereStatement = "WHERE BudgetStatus != 'REMOVED' ";
var whereStatementsArray = [];
if (ignorePausedCampaigns) {
whereStatement += "AND AssociatedCampaignStatus = 'ENABLED' ";
} else {
whereStatement += "AND AssociatedCampaignStatus IN ['ENABLED','PAUSED'] ";
}
for (var i = 0; i < campaignNameDoesNotContain.length; i++) {
if (campaignNameDoesNotContain == "") {
break;
} else {
whereStatement += "AND AssociatedCampaignName 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 AssociatedCampaignName CONTAINS_IGNORE_CASE "' +
campaignNameContains[i].replace(/"/g, '\\\"') + '" '
);
}
}
return whereStatementsArray;
}
function addDateToStatements(campaignFilterQueries) {
var combinedQueries = []
for (var i = 0; i < campaignFilterQueries.length; i++) {
combinedQueries.push(campaignFilterQueries[i]
.concat(" DURING TODAY"));
}
return combinedQueries;
}
function getAccountCurrencyCode() {
var report = AdsApp.report("SELECT AccountCurrencyCode FROM ACCOUNT_PERFORMANCE_REPORT");
var reportRow = report.rows().next();
return reportRow["AccountCurrencyCode"]
}
function getBudgetData(queries, accountName) {
dataRows = []
var fields = ["BudgetName", "BudgetId", "BudgetReferenceCount", "Amount"]
for (var i = 0; i < queries.length; i++) {
var report = AdsApp.report(
"SELECT " + fields.map(function (field) {
return field;
}).join(',') + " FROM BUDGET_PERFORMANCE_REPORT " + queries[i]
);
var budgetIds = [];
var reportRows = report.rows();
if (reportRows.hasNext() === false) {
throw ("Bid Strategy Performance Monitor: error with account " + accountName +
": no campaigns found with the given settings: " + queries[i]
)
};
while (reportRows.hasNext()) {
var reportRow = reportRows.next();
if (budgetIds.indexOf(reportRow["BudgetId"]) == -1) {
budgetIds.push(reportRow["BudgetId"]);
var dataRow = fields.map(function (field) {
return {
name: field,
value: reportRow[field] || "N/A"
};
});
dataRows.push(dataRow)
}
}
}
return dataRows;
}
function writeRowsOntoSheet(sheet, rows) {
for (var i = 0; i < rows.length; i++) {
row = rows[i];
sheet.getRange((sheet.getLastRow() + 1), 1, 1, row.length).setValues([row]);
}
}
function setDate(sheet, tz) {
var now = Utilities.formatDate(new Date(), tz, 'dd/MM/yyyy HH:mm:ss');
sheet.getRange("H2").setValue(now);
}
Budget tracking script
Problem
Spending unknowingly more than the reserved Ads budget is every marketeer’s nightmare.
With this script by Wesley Parker, you’ll be able to track media spend by getting alerts when you are close to budget.
This advanced script not only lets to break down your budget for different campaigns and labels, it also provides you with budget tracking through alerts. You’ll also get daily emails showing the status of your budget.
* Version: 1.0
* maintained by Clicteq
*
**/
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options
//Specify sheet URL
URL = ''
//Specify the email for notifications
EMAIL = ''
//Specify the email's subject line
EMAILSUBJECT = ''
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function getLabelId(name)
{
return AdWordsApp.labels().withCondition('LabelName = ' + '"' + name + '"').get().next().getId()
}
function getSettings(sheet, tabName, headerRange, valuesRange, stopEmpty)
{
var tab = sheet.getSheetByName(tabName)
var header = tab.getRange(headerRange).getValues()
var values = tab.getRange(valuesRange).getValues()
var parsed = []
for(var row in values)
{
var temp = {}
for(var value in values[row])
{
var field = header[0][value]
if( values[row][value] == '')
{
temp[field] = ''
}
else
{
if(field == 'Campaign')
{
temp[field] = values[row][value][0] != '-' ? 'CampaignName CONTAINS_IGNORE_CASE ' + '"'+values[row][value]+'"' : 'CampaignName DOES_NOT_CONTAIN_IGNORE_CASE ' + '"'+values[row][value].slice(1,values[row][value].length+'"')
}
else if(field == 'Label')
{
temp[field] = values[row][value][0] != '-' ? 'LabelIds CONTAINS_ANY ' + '[' + getLabelId(values[row][value]) + ']' : 'LabelIds CONTAINS_NONE ' + '[' + getLabelId(values[row][value].slice(1,values[row][value].length)) + ']'
}
else if(field == 'Channel')
{
temp[field] = values[row][value][0] != '-' ? 'AdvertisingChannelType = ' + values[row][value] : 'AdvertisingChannelType != ' + values[row][value].slice(1,values[row][value].length)
}
else if(field == 'Tab Name')
{
temp[field] = values[row][value]
}
else
{
temp[field] = values[row][value]
}
}
}
if(stopEmpty && values[row][0] == '')
{break}
parsed.push(temp)
}
return parsed
}
function createTabs(settings,budgets,sheet,days)
{
for(var setting in settings)
{
try
{
var tabName = settings[setting]['Tab Name']
sheet.insertSheet(tabName)
createGraph(sheet,settings[setting])
}
catch(e)
{
continue
}
var newTab = sheet.getSheetByName(tabName)
newTab.getRange('A1:A1').setValue(tabName)
newTab.getRange('A2:A2').setValue('Total Budget')
newTab.getRange('B2:B2').setValue(budgets[tabName])
newTab.getRange('A4:A4').setValue('Daily Budget')
newTab.getRange('A5:F5').setValues([['Daily','Predicted','Actual','Projected at Current Spend', 'How to meet Target', 'Budget']])
for(var i = 6; i <= days + 5; i++)
{
newTab.getRange('A' + i + ':' + 'A' + i).setValue(i-5)
newTab.getRange('F' + i + ':' + 'F' + i).setValue(budgets[tabName])
}
}
}
function formatDate(date)
{
return Utilities.formatDate(date, 'GMT', 'YYYYMMdd')
}
function parseDate(sheet)
{
var oneDay = 24*60*60*1000
var userDate = sheet.getRange('B3:B3').getValue()
var yesterday = formatDate(new Date(new Date() - oneDay))
var quarters = ['20190101','20190401','20190701','20191001']
var ends = ['20190331', '20190630', '20190931', '20191231']
if(userDate == 'Monthly')
{
return ['THIS_MONTH','THIS_MONTH']
}
else if (userDate == 'Yearly')
{
var currentYear = (new Date()).getYear()
var startDate = currentYear + '0101'
return [startDate + ',' + yesterday, startDate + ',' + currentYear + '1231']
}
else if (userDate == 'Quartely')
{
var currentMonth = (new Date()).getMonth() + 1
var currentQuarter = parseInt(currentMonth / 3)
var startDate = quarters[currentQuarter]
return [startDate + ',' + yesterday, startDate + ',' + ends[currentQuarter]]
}
else
{
return [userDate + ',' + yesterday,userDate + ',' + yesterday]
}
}
function getReport(settings, date)
{
var fields = ['Campaign','Label','Channel']
var conditions = []
for(var i in fields)
{
if(settings[fields[i]] != '')
{
conditions.push(settings[fields[i]])
}
}
var fullCondition = 'WHERE ' + conditions.join(' AND ') + ' DURING ' + date
if(conditions.length == 0)
{fullCondition = fullCondition.replace('WHERE','')}
var query = 'SELECT Date, Cost from CAMPAIGN_PERFORMANCE_REPORT ' + fullCondition
var report = AdWordsApp.report(query).rows()
var data = {}
var dataArray = []
var parsed = []
while(report.hasNext())
{
var row = report.next()
data[row.Date] = Object.keys(data).indexOf(row.Date) != -1 ? data[row.Date] + parseFloat(row.Cost.replace(',')) : parseFloat(row.Cost.replace(','))
}
var keys = Object.keys(data)
for(var key in keys)
{
dataArray.push([keys[key], data[keys[key]]])
}
dataArray.sort(function(a,b) {return a[0] > b[0] ? 1 : -1})
for(var i in dataArray)
{
var total = 0
for(var j = 0; j <= i; j++)
{
total = total + dataArray[j][1]
}
parsed.push([total])
}
return date != 'THIS_MONTH' ? parsed : parsed.slice(0, parsed.length - 1)
}
function putActual(sheet,settings,actuals)
{
var days = actuals.length + 5
var range = 'C6:C' + days
var tab = sheet.getSheetByName(settings['Tab Name'])
tab.getRange(range).setValues(actuals)
}
function putPredicted(sheet,settings,actuals, days)
{
var predicted = []
var lastDay = actuals[actuals.length - 1]
var mean = lastDay/actuals.length
for(var i = 0; i < days; i++)
{
if(i < actuals.length)
{
predicted.push([parseFloat(actuals[i]).toFixed(2)])
}
else
{
var value = parseFloat(lastDay) + (mean * (i - actuals.length))
predicted.push([value.toFixed(2)])
}
}
var range = 'D6:D' + (days + 5)
sheet.getSheetByName(settings['Tab Name']).getRange(range).setValues(predicted)
}
function putPredictedInput(sheet,settings, days, dailyBudgets)
{
var values = []
for(var i= 0; i < days; i++)
{
var total = 0
for(var j = 0; j <= i; j++)
{
total = total + dailyBudgets[j][settings['Tab Name']]
}
values.push([total.toFixed(2)])
}
var range = 'B6:B' + (days + 5)
sheet.getSheetByName(settings['Tab Name']).getRange(range).setValues(values)
}
function putHowToMeet(sheet,settings, actuals, days)
{
var values = []
for(var i = 0; i < days; i++)
{
if(i < actuals.length)
{
values.push([parseFloat(actuals[i]).toFixed(2)])
}
else
{
var formula = '=round(E' + (i+5) + '+(($B$2-$E$' + (actuals.length+5) + ')/($A$' + (days+5) + '-$A$' + (actuals.length+5) + ')),2)'
values.push([formula])
}
}
var range = 'E6:E' + (days + 5)
sheet.getSheetByName(settings['Tab Name']).getRange(range).setValues(values)
}
function createGraph(sheet,settings)
{
var tab = sheet.getSheetByName(settings['Tab Name'])
var yAxis1 = tab.getRange('B5:B1000')
var yAxis2 = tab.getRange('D5:F1000')
var chart = tab.newChart().asLineChart()
.addRange(yAxis1)
.addRange(yAxis2)
.setPosition(8,8,1,1)
.setNumHeaders(1)
.setOption('height', 510)
.setOption('width', 825)
.setOption('legend', {'position':'top'})
.build()
tab.insertChart(chart)
}
function getDifference(date)
{
if(date == 'THIS_MONTH')
{
var now = new Date();
var firstDay = new Date(now.getFullYear(), now.getMonth(), 1);
var lastDay = new Date(now.getFullYear(), now.getMonth() + 1, 0);
}
else
{
var day_1 = date.slice(6,8)
var month_1 = date.slice(4,6)
var year_1 = date.slice(0,4)
var day_2 = date.slice(15,17)
var month_2 = date.slice(13,15)
var year_2 = date.slice(9,13)
var firstDay = new Date(year_1, month_1, day_1);
var lastDay = new Date(year_2,month_2, day_2);
}
var timeDiff = Math.abs(firstDay.getTime() - lastDay.getTime());
return Math.ceil(timeDiff / (1000 * 3600 * 24)) + 1;
}
function main()
{
var sheet = SpreadsheetApp.openByUrl(URL)
var settings = getSettings(sheet, 'Inputs','B5:E5', 'B6:E', true)
var dailyBudgets = getSettings(sheet, 'Daily budget inputs', 'B3:E3', 'B7:E', true)
var periodLength = dailyBudgets.length
var date = parseDate(sheet)[0]
var difference = getDifference(parseDate(sheet)[1])
if(difference != dailyBudgets.length)
{
Logger.log('Wrong number of days in the Daily budget inputs tab')
Logger.log('Input: ' + dailyBudgets.length +' days')
Logger.log('Specified time range: ' + difference +' days')
return
}
var accountName = AdWordsApp.currentAccount().getName()
var budgets = getSettings(sheet, 'Daily budget inputs', 'B3:E3', 'B4:E4', true)
var body = 'Budget summary for '+ accountName + ', % of budget spent: \n\n'
Logger.log('Creating tabs')
for(var budget in budgets)
{
createTabs(settings,budgets[budget],sheet,periodLength)
}
for(var setting in settings)
{
var single = settings[setting]
var actuals = getReport(single, date)
Logger.log('Processing ' + single['Tab Name'])
putActual(sheet,single,actuals)
putPredicted(sheet,single,actuals,periodLength)
putPredictedInput(sheet,single,periodLength,dailyBudgets)
putHowToMeet(sheet,single, actuals, periodLength)
var percentage = (100*(actuals[actuals.length - 1]/budgets[0][single['Tab Name']])).toFixed(2)
body = body + single['Tab Name'] + ' ' + percentage + '%\n'
}
MailApp.sendEmail(EMAIL, '', EMAILSUBJECT, body)
Logger.log('Finished')
}
Pause ads with low CTR
Problem
Ads with low CTR negatively impact your overall Quality Score.
With this script by Russ Savage, you’ll be able to pause or delete overlooked bad performing ads in your account.
The script below lets you find the worst performing ads in your ad groups. You need to get rid of poor CTR ads to protect your Ads Quality Score.
//-----------------------------------
// Pause Ads with Low CTR
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
// Let's start by getting all of the adGroups that are active
var ag_iter = AdWordsApp.adGroups()
.withCondition("Status = ENABLED")
.get();
// Then we will go through each one
while (ag_iter.hasNext()) {
var ag = ag_iter.next();
var ad_iter = ag.ads()
.withCondition("Status = ENABLED")
.forDateRange("ALL_TIME")
.orderBy("Ctr DESC")
.get();
var ad_array = new Array();
while(ad_iter.hasNext()) {
ad_array.push(ad_iter.next());
}
if(ad_array.length > 1) {
for(var i = 1; i < ad_array.length; i++) {
ad_array[i].pause(); //or .remove(); to delete them
}
}
}
}
Alert Scripts
Account anomaly detector
Problem
There could be always anomalies with your account due to little forgotten details or technical issues. Detecting them manually can be a time consuming problem.
With this Ads script by Google, you’ll be able to determine and track instant or unexpected anomalies in your account.
The script sends you an alerting email whenever an anomaly is detected in your account. This issue could be a drastically decreased quality score, skyrocketed spend, or low impressions than the usual.
// Copyright 2017, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
/**
* @name Account Anomaly Detector
*
* @fileoverview The Account Anomaly Detector alerts the advertiser whenever an
* advertiser account is suddenly behaving too differently from what's
* historically observed. See
* https://developers.google.com/google-ads/scripts/docs/solutions/account-anomaly-detector
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.1
*
* @changelog
* - version 1.1.1
* - Fixed bug in handling of reports with 0 rows.
* - version 1.1
* - Added conversions to tracked statistics.
* - version 1.0.3
* - Improved code readability and comments.
* - version 1.0.2
* - Added validation for external spreadsheet setup.
* - Updated to use report version v201609.
* - version 1.0.1
* - Improvements to time zone handling.
* - version 1.0
* - Released initial version.
*/
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
var DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday', 'Sunday'];
/**
* Configuration to be used for running reports.
*/
var REPORTING_OPTIONS = {
// Comment out the following line to default to the latest reporting version.
apiVersion: 'v201809'
};
function main() {
Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
var impressionsThreshold = parseField(spreadsheet.
getRangeByName('impressions').getValue());
var clicksThreshold = parseField(spreadsheet.getRangeByName('clicks').
getValue());
var conversionsThreshold =
parseField(spreadsheet.getRangeByName('conversions').getValue());
var costThreshold = parseField(spreadsheet.getRangeByName('cost').getValue());
var weeksStr = spreadsheet.getRangeByName('weeks').getValue();
var weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')));
var email = spreadsheet.getRangeByName('email').getValue();
var now = new Date();
// Basic reporting statistics are usually available with no more than a 3-hour
// delay.
var upTo = new Date(now.getTime() - 3 * 3600 * 1000);
var upToHour = parseInt(getDateStringInTimeZone('h', upTo));
if (upToHour == 1) {
// first run for the day, kill existing alerts
spreadsheet.getRangeByName('clicks_alert').clearContent();
spreadsheet.getRangeByName('impressions_alert').clearContent();
spreadsheet.getRangeByName('conversions_alert').clearContent();
spreadsheet.getRangeByName('cost_alert').clearContent();
}
var dateRangeToCheck = getDateStringInPast(0, upTo);
var dateRangeToEnd = getDateStringInPast(1, upTo);
var dateRangeToStart = getDateStringInPast(1 + weeks * 7, upTo);
var fields = 'HourOfDay,DayOfWeek,Clicks,Impressions,Conversions,Cost';
var todayQuery = 'SELECT ' + fields +
' FROM ACCOUNT_PERFORMANCE_REPORT DURING ' + dateRangeToCheck + ',' +
dateRangeToCheck;
var pastQuery = 'SELECT ' + fields +
' FROM ACCOUNT_PERFORMANCE_REPORT WHERE DayOfWeek=' +
DAYS[getDateStringInTimeZone('u', now)].toUpperCase() +
' DURING ' + dateRangeToStart + ',' + dateRangeToEnd;
var todayStats = getReportStats(todayQuery, upToHour, 1);
var pastStats = getReportStats(pastQuery, upToHour, weeks);
var statsExist = true;
if (typeof todayStats === 'undefined' || typeof pastStats === 'undefined') {
statsExist = false;
}
var alertText = [];
if (statsExist && impressionsThreshold &&
todayStats.impressions < pastStats.impressions * impressionsThreshold) {
var ImpressionsAlert = ' Impressions are too low: ' +
todayStats.impressions + ' impressions by ' + upToHour +
':00, expecting at least ' +
parseInt(pastStats.impressions * impressionsThreshold);
writeAlert(spreadsheet, 'impressions_alert', alertText, ImpressionsAlert,
upToHour);
}
if (statsExist && clicksThreshold &&
todayStats.clicks < pastStats.clicks * clicksThreshold) {
var clickAlert = ' Clicks are too low: ' + todayStats.clicks +
' clicks by ' + upToHour + ':00, expecting at least ' +
(pastStats.clicks * clicksThreshold).toFixed(1);
writeAlert(spreadsheet, 'clicks_alert', alertText, clickAlert, upToHour);
}
if (statsExist && conversionsThreshold &&
todayStats.conversions < pastStats.conversions * conversionsThreshold) {
var conversionsAlert =
' Conversions are too low: ' + todayStats.conversions +
' conversions by ' + upToHour + ':00, expecting at least ' +
(pastStats.conversions * conversionsThreshold).toFixed(1);
writeAlert(
spreadsheet, 'conversions_alert', alertText, conversionsAlert,
upToHour);
}
if (statsExist && costThreshold &&
todayStats.cost > pastStats.cost * costThreshold) {
var costAlert = ' Cost is too high: ' + todayStats.cost + ' ' +
AdsApp.currentAccount().getCurrencyCode() + ' by ' + upToHour +
':00, expecting at most ' +
(pastStats.cost * costThreshold).toFixed(2);
writeAlert(spreadsheet, 'cost_alert', alertText, costAlert, upToHour);
}
if (alertText.length > 0 && email && email.length > 0) {
MailApp.sendEmail(email,
'Google Ads Account ' + AdsApp.currentAccount().getCustomerId() +
' misbehaved.',
'Your account ' + AdsApp.currentAccount().getCustomerId() +
' is not performing as expected today: \n\n' + alertText.join('\n') +
'\n\nLog into Google Ads and take a look.\n\nAlerts dashboard: ' +
SPREADSHEET_URL);
}
writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats, pastStats,
AdsApp.currentAccount().getCustomerId());
}
function toFloat(value) {
value = value.toString().replace(/,/g, '');
return parseFloat(value);
}
function parseField(value) {
if (value == 'No alert') {
return null;
} else {
return toFloat(value);
}
}
/**
* Runs a Google Ads report query for a number of weeks and return the average
* values for the stats.
*
* @param {string} query The formatted report query.
* @param {int} hours The limit hour of day for considering the report rows.
* @param {int} weeks The number of weeks for the past stats.
* @return {Object} An object containing the average values for the stats.
*/
function getReportStats(query, hours, weeks) {
var reportRows = [];
var report = AdsApp.report(query, REPORTING_OPTIONS);
var rows = report.rows();
while (rows.hasNext()) {
reportRows.push(rows.next());
}
return accumulateRows(reportRows, hours, weeks);
}
function accumulateRows(rows, hours, weeks) {
var result = {clicks: 0, impressions: 0, conversions: 0, cost: 0};
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
var hour = row['HourOfDay'];
if (hour < hours) {
result = addRow(row, result, 1 / weeks);
}
}
return result;
}
function addRow(row, previous, coefficient) {
if (!coefficient) {
coefficient = 1;
}
if (row == null) {
row = {Clicks: 0, Impressions: 0, Conversions: 0, Cost: 0};
}
if (!previous) {
return {
clicks: parseInt(row['Clicks']) * coefficient,
impressions: parseInt(row['Impressions']) * coefficient,
conversions: parseInt(row['Conversions']) * coefficient,
cost: toFloat(row['Cost']) * coefficient
};
} else {
return {
clicks: parseInt(row['Clicks']) * coefficient + previous.clicks,
impressions:
parseInt(row['Impressions']) * coefficient + previous.impressions,
conversions:
parseInt(row['Conversions']) * coefficient + previous.conversions,
cost: toFloat(row['Cost']) * coefficient + previous.cost
};
}
}
/**
* Produces a formatted string representing a date in the past of a given date.
*
* @param {number} numDays The number of days in the past.
* @param {date} date A date object. Defaults to the current date.
* @return {string} A formatted string in the past of the given date.
*/
function getDateStringInPast(numDays, date) {
date = date || new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var past = new Date(date.getTime() - numDays * MILLIS_PER_DAY);
return getDateStringInTimeZone('yyyyMMdd', past);
}
/**
* Produces a formatted string representing a given date in a given time zone.
*
* @param {string} format A format specifier for the string to be produced.
* @param {date} date A date object. Defaults to the current date.
* @param {string} timeZone A time zone. Defaults to the account's time zone.
* @return {string} A formatted string of the given date in the given time zone.
*/
function getDateStringInTimeZone(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* Validates the provided spreadsheet URL and email address
* to make sure that they're set up properly. Throws a descriptive error message
* if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL or email hasn't been set
*/
function validateAndGetSpreadsheet(spreadsheeturl) {
if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
var email = spreadsheet.getRangeByName('email').getValue();
if ('foo@example.com' == email) {
throw new Error('Please either set a custom email address in the' +
' spreadsheet, or set the email field in the spreadsheet to blank' +
' to send no email.');
}
return spreadsheet;
}
/**
* Writes the alert time in the spreadsheet and push the alert message to the
* list of messages.
*
* @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
* @param {string} rangeName The named range in the spreadsheet.
* @param {Array<string>} alertText The list of alert messages.
* @param {string} alertMessage The alert message.
* @param {int} hour The limit hour used to get the stats.
*/
function writeAlert(spreadsheet, rangeName, alertText, alertMessage, hour) {
var range = spreadsheet.getRangeByName(rangeName);
if (!range.getValue() || range.getValue().length == 0) {
alertText.push(alertMessage);
range.setValue('Alerting ' + hour + ':00');
}
}
/**
* Writes the data to the spreadsheet.
*
* @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
* @param {Date} now The date corresponding to the running time of the script.
* @param {boolean} statsExist A boolean that indicates the existence of stats.
* @param {Object} todayStats The stats for today.
* @param {Object} pastStats The past stats for the period defined in the
* spreadsheet.
* @param {string} accountId The account ID.
*/
function writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats,
pastStats, accountId) {
spreadsheet.getRangeByName('date').setValue(now);
spreadsheet.getRangeByName('account_id').setValue(accountId);
spreadsheet.getRangeByName('timestamp').setValue(
getDateStringInTimeZone('E HH:mm:ss z', now));
if (statsExist) {
var dataRows = [
[todayStats.impressions, pastStats.impressions.toFixed(0)],
[todayStats.clicks, pastStats.clicks.toFixed(1)],
[todayStats.conversions, pastStats.conversions.toFixed(1)],
[todayStats.cost, pastStats.cost.toFixed(2)]
];
spreadsheet.getRangeByName('data').setValues(dataRows);
}
}
Link checker for single account
Problem
404 pages are always a big problem when it comes to Ads as they’re likely to result in wasted ad spend.
With this script by Google, you’ll be able to detect 404 pages automatically and your ads won’t be driving to 404s.
Your campaigns will be in sync with your Ads account and you won’t have wasted spend or 404 pages linked to your ads.
// Copyright 2016, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
/**
* @name Link Checker
*
* @overview The Link Checker script iterates through the ads, keywords, and
* sitelinks in your account and makes sure their URLs do not produce "Page
* not found" or other types of error responses. See
* https://developers.google.com/google-ads/scripts/docs/solutions/link-checker
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.2
*
* @changelog
* - version 2.2
* - Added support for failure strings and custom validation functions.
* - version 2.1
* - Added expansion of conditional ValueTrack parameters (e.g. ifmobile).
* - Added expanded text ad and other ad format support.
* - version 2.0.3
* - Added validation for external spreadsheet setup.
* - version 2.0.2
* - Allow the custom tracking label to include spaces.
* - version 2.0.1
* - Catch and output all UrlFetchApp exceptions.
* - version 2.0
* - Completely revised the script to work on larger accounts.
* - Check URLs in campaign and ad group sitelinks.
* - version 1.2
* - Released initial version.
*/
var CONFIG = {
// URL of the spreadsheet template.
// This should be a copy of https://docs.google.com/spreadsheets/d/1iO1iEGwlbe510qo3Li-j4KgyCeVSmodxU6J7M756ppk/copy.
SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',
// Array of addresses to be alerted via email if issues are found.
RECIPIENT_EMAILS: [
'YOUR_EMAIL_HERE'
],
// Label to use when a link has been checked.
LABEL: 'LinkChecker_Done',
// Number of milliseconds to sleep after each URL request. If your URLs are
// all on one or a few domains, use this throttle to reduce the load that the
// script imposes on your web server(s).
THROTTLE: 0,
// Number of seconds before timeout that the script should stop checking URLs
// to make sure it has time to output its findings.
TIMEOUT_BUFFER: 120
};
/**
* Performs custom validation on a URL, with access to details such as the URL,
* the response from the server, configuration options and entity Details.
*
* To use, the "Use Custom Validation" option in the configuration spreadsheet
* must be set to "Yes", and your custom validation code implemented within the
* below function.
*
* See the documentation for this solution for further details.
*
* @param {string} url The URL being checked.
* @param {!HTTPResponse} response The response object for the request.
* @param {!Object} options Configuration options.
* @param {!Object} entityDetails Details of the associated Ad / Keywords etc.
* @return {boolean} Return true if the URL and response are deemed valid.
*/
function isValidResponse(url, response, options, entityDetails) {
/*
Some examples of data that can be used in determining the validity of this
URL. This is not exhaustive and there are further properties available.
*/
// The HTTP status code, e.g. 200, 404
// var responseCode = response.getResponseCode();
// The HTTP response body, e.g. HTML for web pages:
// var responseText = response.getContentText();
// The failure strings from the configuration spreadsheet, as an array:
// var failureStrings = options.failureStrings;
// The type of the entity associated with the URL, e.g. Ad, Keyword, Sitelink.
// var entityType = entityDetails.entityType;
// The campaign name
// var campaignName = entityDetails.campaign;
// The ad group name, if applicable
// var adGroupName = entityDetails.adGroup;
// The ad text, if applicable
// var adText = entityDetails.ad;
// The keyword text, if applicable
// var keywordText = entityDetails.keyword;
// The sitelink link text, if applicable
// var sitelinkText = entityDetails.sitelink;
/*
Remove comments and insert custom logic to determine whether this URL and
response are valid, using the data obtained above.
If valid, return true. If invalid, return false.
*/
// Placeholder implementation treats all URLs as valid
return true;
}
/**
* Parameters controlling the script's behavior after hitting a UrlFetchApp
* QPS quota limit.
*/
var QUOTA_CONFIG = {
INIT_SLEEP_TIME: 250,
BACKOFF_FACTOR: 2,
MAX_TRIES: 5
};
/**
* Exceptions that prevent the script from finishing checking all URLs in an
* account but allow it to resume next time.
*/
var EXCEPTIONS = {
QPS: 'Reached UrlFetchApp QPS limit',
LIMIT: 'Reached UrlFetchApp daily quota',
TIMEOUT: 'Approached script execution time limit'
};
/**
* Named ranges in the spreadsheet.
*/
var NAMES = {
CHECK_AD_URLS: 'checkAdUrls',
CHECK_KEYWORD_URLS: 'checkKeywordUrls',
CHECK_SITELINK_URLS: 'checkSitelinkUrls',
CHECK_PAUSED_ADS: 'checkPausedAds',
CHECK_PAUSED_KEYWORDS: 'checkPausedKeywords',
CHECK_PAUSED_SITELINKS: 'checkPausedSitelinks',
VALID_CODES: 'validCodes',
EMAIL_EACH_RUN: 'emailEachRun',
EMAIL_NON_ERRORS: 'emailNonErrors',
EMAIL_ON_COMPLETION: 'emailOnCompletion',
FAILURE_STRINGS: 'failureStrings',
SAVE_ALL_URLS: 'saveAllUrls',
FREQUENCY: 'frequency',
DATE_STARTED: 'dateStarted',
DATE_COMPLETED: 'dateCompleted',
DATE_EMAILED: 'dateEmailed',
NUM_ERRORS: 'numErrors',
RESULT_HEADERS: 'resultHeaders',
ARCHIVE_HEADERS: 'archiveHeaders',
USE_SIMPLE_FAILURE_STRINGS: 'useSimpleFailureStrings',
USE_CUSTOM_VALIDATION: 'useCustomValidation'
};
function main() {
var spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
validateEmailAddresses(CONFIG.RECIPIENT_EMAILS);
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
var options = loadOptions(spreadsheet);
var status = loadStatus(spreadsheet);
if (!status.dateStarted) {
// This is the very first execution of the script.
startNewAnalysis(spreadsheet);
} else if (status.dateStarted > status.dateCompleted) {
Logger.log('Resuming work from a previous execution.');
} else if (dayDifference(status.dateStarted, new Date()) <
options.frequency) {
Logger.log('Waiting until ' + options.frequency +
' days have elapsed since the start of the last analysis.');
return;
} else {
// Enough time has passed since the last analysis to start a new one.
removeLabels([CONFIG.LABEL]);
startNewAnalysis(spreadsheet);
}
var results = analyzeAccount(options);
outputResults(results, options);
}
/**
* Checks as many new URLs as possible that have not previously been checked,
* subject to quota and time limits.
*
* @param {Object} options Dictionary of options.
* @return {!Object} An object with fields for the URLs checked and an
* indication if the analysis was completed (no remaining URLs to check).
*/
function analyzeAccount(options) {
// Ensure the label exists before attempting to retrieve already checked URLs.
ensureLabels([CONFIG.LABEL]);
var checkedUrls = getAlreadyCheckedUrls(options);
var urlChecks = [];
var didComplete = false;
try {
// If the script throws an exception, didComplete will remain false.
didComplete = checkUrls(checkedUrls, urlChecks, options);
} catch(e) {
if (e == EXCEPTIONS.QPS ||
e == EXCEPTIONS.LIMIT ||
e == EXCEPTIONS.TIMEOUT) {
Logger.log('Stopped checking URLs early because: ' + e);
Logger.log('Checked URLs will still be output.');
} else {
throw e;
}
}
return {
urlChecks: urlChecks,
didComplete: didComplete
};
}
/**
* Outputs the results to a spreadsheet and sends emails if appropriate.
*
* @param {Object} results An object with fields for the URLs checked and an
* indication if the analysis was completed (no remaining URLs to check).
* @param {Object} options Dictionary of options.
*/
function outputResults(results, options) {
var spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
var numErrors = countErrors(results.urlChecks, options);
Logger.log('Found ' + numErrors + ' this execution.');
saveUrlsToSpreadsheet(spreadsheet, results.urlChecks, options);
// Reload the status to get the total number of errors for the entire
// analysis, which is calculated by the spreadsheet.
status = loadStatus(spreadsheet);
if (results.didComplete) {
spreadsheet.getRangeByName(NAMES.DATE_COMPLETED).setValue(new Date());
Logger.log('Found ' + status.numErrors + ' across the entire analysis.');
}
if (CONFIG.RECIPIENT_EMAILS) {
if (!results.didComplete && options.emailEachRun &&
(options.emailNonErrors || numErrors > 0)) {
sendIntermediateEmail(spreadsheet, numErrors);
}
if (results.didComplete &&
(options.emailEachRun || options.emailOnCompletion) &&
(options.emailNonErrors || status.numErrors > 0)) {
sendFinalEmail(spreadsheet, status.numErrors);
}
}
}
/**
* Loads data from a spreadsheet based on named ranges. Strings 'Yes' and 'No'
* are converted to booleans. One-dimensional ranges are converted to arrays
* with blank cells omitted. Assumes each named range exists.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {Array.<string>} names A list of named ranges that should be loaded.
* @return {!Object} A dictionary with the names as keys and the values
* as the cell values from the spreadsheet.
*/
function loadDatabyName(spreadsheet, names) {
var data = {};
for (var i = 0; i < names.length; i++) {
var name = names[i];
var range = spreadsheet.getRangeByName(name);
if (range.getNumRows() > 1 && range.getNumColumns() > 1) {
// Name refers to a 2d range, so load it as a 2d array.
data[name] = range.getValues();
} else if (range.getNumRows() == 1 && range.getNumColumns() == 1) {
// Name refers to a single cell, so load it as a value and replace
// Yes/No with boolean true/false.
data[name] = range.getValue();
data[name] = data[name] === 'Yes' ? true : data[name];
data[name] = data[name] === 'No' ? false : data[name];
} else {
// Name refers to a 1d range, so load it as an array (regardless of
// whether the 1d range is oriented horizontally or vertically).
var isByRow = range.getNumRows() > 1;
var limit = isByRow ? range.getNumRows() : range.getNumColumns();
var cellValues = range.getValues();
data[name] = [];
for (var j = 0; j < limit; j++) {
var cellValue = isByRow ? cellValues[j][0] : cellValues[0][j];
if (cellValue) {
data[name].push(cellValue);
}
}
}
}
return data;
}
/**
* Loads options from the spreadsheet.
*
* @param {Object} spreadsheet The spreadsheet object.
* @return {!Object} A dictionary of options.
*/
function loadOptions(spreadsheet) {
return loadDatabyName(spreadsheet,
[NAMES.CHECK_AD_URLS, NAMES.CHECK_KEYWORD_URLS,
NAMES.CHECK_SITELINK_URLS, NAMES.CHECK_PAUSED_ADS,
NAMES.CHECK_PAUSED_KEYWORDS, NAMES.CHECK_PAUSED_SITELINKS,
NAMES.VALID_CODES, NAMES.EMAIL_EACH_RUN,
NAMES.EMAIL_NON_ERRORS, NAMES.EMAIL_ON_COMPLETION,
NAMES.SAVE_ALL_URLS, NAMES.FREQUENCY,
NAMES.FAILURE_STRINGS, NAMES.USE_SIMPLE_FAILURE_STRINGS,
NAMES.USE_CUSTOM_VALIDATION]);
}
/**
* Loads state information from the spreadsheet.
*
* @param {Object} spreadsheet The spreadsheet object.
* @return {!Object} A dictionary of status information.
*/
function loadStatus(spreadsheet) {
return loadDatabyName(spreadsheet,
[NAMES.DATE_STARTED, NAMES.DATE_COMPLETED,
NAMES.DATE_EMAILED, NAMES.NUM_ERRORS]);
}
/**
* Saves the start date to the spreadsheet and archives results of the last
* analysis to a separate sheet.
*
* @param {Object} spreadsheet The spreadsheet object.
*/
function startNewAnalysis(spreadsheet) {
Logger.log('Starting a new analysis.');
spreadsheet.getRangeByName(NAMES.DATE_STARTED).setValue(new Date());
// Helper method to get the output area on the results or archive sheets.
var getOutputRange = function(rangeName) {
var headers = spreadsheet.getRangeByName(rangeName);
return headers.offset(1, 0, headers.getSheet().getDataRange().getLastRow());
};
getOutputRange(NAMES.ARCHIVE_HEADERS).clearContent();
var results = getOutputRange(NAMES.RESULT_HEADERS);
results.copyTo(getOutputRange(NAMES.ARCHIVE_HEADERS));
getOutputRange(NAMES.RESULT_HEADERS).clearContent();
}
/**
* Counts the number of errors in the results.
*
* @param {Array.<Object>} urlChecks A list of URL check results.
* @param {Object} options Dictionary of options.
* @return {number} The number of errors in the results.
*/
function countErrors(urlChecks, options) {
var numErrors = 0;
for (var i = 0; i < urlChecks.length; i++) {
if (options.validCodes.indexOf(urlChecks[i].responseCode) == -1) {
numErrors++;
}
}
return numErrors;
}
/**
* Saves URLs for a particular account to the spreadsheet starting at the first
* unused row.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {Array.<Object>} urlChecks A list of URL check results.
* @param {Object} options Dictionary of options.
*/
function saveUrlsToSpreadsheet(spreadsheet, urlChecks, options) {
// Build each row of output values in the order of the columns.
var outputValues = [];
for (var i = 0; i < urlChecks.length; i++) {
var urlCheck = urlChecks[i];
if (options.saveAllUrls ||
options.validCodes.indexOf(urlCheck.responseCode) == -1) {
outputValues.push([
urlCheck.customerId,
new Date(urlCheck.timestamp),
urlCheck.url,
urlCheck.responseCode,
urlCheck.entityType,
urlCheck.campaign,
urlCheck.adGroup,
urlCheck.ad,
urlCheck.keyword,
urlCheck.sitelink
]);
}
}
if (outputValues.length > 0) {
// Find the first open row on the Results tab below the headers and create a
// range large enough to hold all of the output, one per row.
var headers = spreadsheet.getRangeByName(NAMES.RESULT_HEADERS);
var lastRow = headers.getSheet().getDataRange().getLastRow();
var outputRange = headers.offset(lastRow - headers.getRow() + 1,
0, outputValues.length);
outputRange.setValues(outputValues);
}
for (var i = 0; i < CONFIG.RECIPIENT_EMAILS.length; i++) {
spreadsheet.addEditor(CONFIG.RECIPIENT_EMAILS[i]);
}
}
/**
* Sends an email to a list of email addresses with a link to the spreadsheet
* and the results of this execution of the script.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {boolean} numErrors The number of errors found in this execution.
*/
function sendIntermediateEmail(spreadsheet, numErrors) {
spreadsheet.getRangeByName(NAMES.DATE_EMAILED).setValue(new Date());
MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
'Link Checker Results',
'The Link Checker script found ' + numErrors + ' URLs with errors in ' +
'an execution that just finished. See ' +
spreadsheet.getUrl() + ' for details.');
}
/**
* Sends an email to a list of email addresses with a link to the spreadsheet
* and the results across the entire account.
*
* @param {Object} spreadsheet The spreadsheet object.
* @param {boolean} numErrors The number of errors found in the entire account.
*/
function sendFinalEmail(spreadsheet, numErrors) {
spreadsheet.getRangeByName(NAMES.DATE_EMAILED).setValue(new Date());
MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
'Link Checker Results',
'The Link Checker script found ' + numErrors + ' URLs with errors ' +
'across its entire analysis. See ' +
spreadsheet.getUrl() + ' for details.');
}
/**
* Retrieves all final URLs and mobile final URLs in the account across ads,
* keywords, and sitelinks that were checked in a previous run, as indicated by
* them having been labeled.
*
* @param {Object} options Dictionary of options.
* @return {!Object} A map of previously checked URLs with the URL as the key.
*/
function getAlreadyCheckedUrls(options) {
var urlMap = {};
var addToMap = function(items) {
for (var i = 0; i < items.length; i++) {
var urls = expandUrlModifiers(items[i]);
urls.forEach(function(url) {
urlMap[url] = true;
});
}
};
if (options.checkAdUrls) {
addToMap(getUrlsBySelector(AdsApp.ads().
withCondition(labelCondition(true))));
}
if (options.checkKeywordUrls) {
addToMap(getUrlsBySelector(AdsApp.keywords().
withCondition(labelCondition(true))));
}
if (options.checkSitelinkUrls) {
addToMap(getAlreadyCheckedSitelinkUrls());
}
return urlMap;
}
/**
* Retrieves all final URLs and mobile final URLs for campaign and ad group
* sitelinks.
*
* @return {Array.<string>} An array of URLs.
*/
function getAlreadyCheckedSitelinkUrls() {
var urls = [];
// Helper method to get campaign or ad group sitelink URLs.
var addSitelinkUrls = function(selector) {
var iterator = selector.withCondition(labelCondition(true)).get();
while (iterator.hasNext()) {
var entity = iterator.next();
var sitelinks = entity.extensions().sitelinks();
urls = urls.concat(getUrlsBySelector(sitelinks));
}
};
addSitelinkUrls(AdsApp.campaigns());
addSitelinkUrls(AdsApp.adGroups());
return urls;
}
/**
* Retrieves all URLs in the entities specified by a selector.
*
* @param {Object} selector The selector specifying the entities to use.
* The entities should be of a type that has a urls() method.
* @return {!Array.<string>} An array of URLs.
*/
function getUrlsBySelector(selector) {
var urls = [];
var entities = selector.get();
// Helper method to add the url to the list if it exists.
var addToList = function(url) {
if (url) {
urls.push(url);
}
};
while (entities.hasNext()) {
var entity = entities.next();
addToList(entity.urls().getFinalUrl());
addToList(entity.urls().getMobileFinalUrl());
}
return urls;
}
/**
* Retrieves all final URLs and mobile final URLs in the account across ads,
* keywords, and sitelinks, and checks their response code. Does not check
* previously checked URLs.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} options Dictionary of options.
* @return {boolean} True if all URLs were checked.
*/
function checkUrls(checkedUrls, urlChecks, options) {
var didComplete = true;
// Helper method to add common conditions to ad group and keyword selectors.
var addConditions = function(selector, includePaused) {
var statuses = ['ENABLED'];
if (includePaused) {
statuses.push('PAUSED');
}
var predicate = ' IN [' + statuses.join(',') + ']';
return selector.withCondition(labelCondition(false)).
withCondition('Status' + predicate).
withCondition('CampaignStatus' + predicate).
withCondition('AdGroupStatus' + predicate);
};
if (options.checkAdUrls) {
didComplete = didComplete && checkUrlsBySelector(checkedUrls, urlChecks,
addConditions(AdsApp.ads().withCondition('CreativeFinalUrls != ""'),
options.checkPausedAds), options);
}
if (options.checkKeywordUrls) {
didComplete = didComplete && checkUrlsBySelector(checkedUrls, urlChecks,
addConditions(AdsApp.keywords().withCondition('FinalUrls != ""'),
options.checkPausedKeywords), options);
}
if (options.checkSitelinkUrls) {
didComplete = didComplete &&
checkSitelinkUrls(checkedUrls, urlChecks, options);
}
return didComplete;
}
/**
* Retrieves all final URLs and mobile final URLs in a selector and checks them
* for a valid response code. Does not check previously checked URLs. Labels the
* entity that it was checked, if possible.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} selector The selector specifying the entities to use.
* The entities should be of a type that has a urls() method.
* @param {!Object} options Dictionary of options.
* @return {boolean} True if all URLs were checked.
*/
function checkUrlsBySelector(checkedUrls, urlChecks, selector, options) {
var customerId = AdsApp.currentAccount().getCustomerId();
var iterator = selector.get();
var entities = [];
// Helper method to check a URL.
var checkUrl = function(entity, url) {
if (!url) {
return;
}
var urlsToCheck = expandUrlModifiers(url);
for (var i = 0; i < urlsToCheck.length; i++) {
var expandedUrl = urlsToCheck[i];
if (checkedUrls[expandedUrl]) {
continue;
}
var entityType = entity.getEntityType();
var entityDetails = {
entityType: entityType,
campaign: entity.getCampaign ? entity.getCampaign().getName() : '',
adGroup: entity.getAdGroup ? entity.getAdGroup().getName() : '',
ad: entityType == 'Ad' ? getAdAsText(entity) : '',
keyword: entityType == 'Keyword' ? entity.getText() : '',
sitelink: entityType.indexOf('Sitelink') != -1 ?
entity.getLinkText() : ''
};
var responseCode = requestUrl(expandedUrl, options, entityDetails);
urlChecks.push({
customerId: customerId,
timestamp: new Date(),
url: expandedUrl,
responseCode: responseCode,
entityType: entityDetails.entityType,
campaign: entityDetails.campaign,
adGroup: entityDetails.adGroup,
ad: entityDetails.ad,
keyword: entityDetails.keyword,
sitelink: entityDetails.sitelink
});
checkedUrls[expandedUrl] = true;
}
};
while (iterator.hasNext()) {
entities.push(iterator.next());
}
for (var i = 0; i < entities.length; i++) {
var entity = entities[i];
checkUrl(entity, entity.urls().getFinalUrl());
checkUrl(entity, entity.urls().getMobileFinalUrl());
// Sitelinks do not have labels.
if (entity.applyLabel) {
entity.applyLabel(CONFIG.LABEL);
checkTimeout();
}
}
// True only if we did not breach an iterator limit.
return entities.length == iterator.totalNumEntities();
}
/**
* Retrieves a text representation of an ad, casting the ad to the appropriate
* type if necessary.
*
* @param {Ad} ad The ad object.
* @return {string} The text representation.
*/
function getAdAsText(ad) {
// There is no AdTypeSpace method for textAd
if (ad.getType() === 'TEXT_AD') {
return ad.getHeadline();
} else if (ad.isType().expandedTextAd()) {
var eta = ad.asType().expandedTextAd();
return eta.getHeadlinePart1() + ' - ' + eta.getHeadlinePart2();
} else if (ad.isType().gmailImageAd()) {
return ad.asType().gmailImageAd().getName();
} else if (ad.isType().gmailMultiProductAd()) {
return ad.asType().gmailMultiProductAd().getHeadline();
} else if (ad.isType().gmailSinglePromotionAd()) {
return ad.asType().gmailSinglePromotionAd().getHeadline();
} else if (ad.isType().html5Ad()) {
return ad.asType().html5Ad().getName();
} else if (ad.isType().imageAd()) {
return ad.asType().imageAd().getName();
} else if (ad.isType().responsiveDisplayAd()) {
return ad.asType().responsiveDisplayAd().getLongHeadline();
}
return 'N/A';
}
/**
* Retrieves all final URLs and mobile final URLs for campaign and ad group
* sitelinks and checks them for a valid response code. Does not check
* previously checked URLs. Labels the containing campaign or ad group that it
* has been checked.
*
* @param {Object} checkedUrls A map of previously checked URLs with the URL as
* the key.
* @param {Array.<Object>} urlChecks An array into which the results of each URL
* check will be inserted.
* @param {Object} options Dictionary of options.
* @return {boolean} True if all URLs were checked.
*/
function checkSitelinkUrls(checkedUrls, urlChecks, options) {
var didComplete = true;
// Helper method to check URLs for sitelinks in a campaign or ad group
// selector.
var checkSitelinkUrls = function(selector) {
var iterator = selector.withCondition(labelCondition(false)).get();
var entities = [];
while (iterator.hasNext()) {
entities.push(iterator.next());
}
for (var i = 0; i < entities.length; i++) {
var entity = entities[i];
var sitelinks = entity.extensions().sitelinks();
if (sitelinks.get().hasNext()) {
didComplete = didComplete &&
checkUrlsBySelector(checkedUrls, urlChecks, sitelinks, options);
entity.applyLabel(CONFIG.LABEL);
checkTimeout();
}
}
// True only if we did not breach an iterator limit.
didComplete = didComplete &&
entities.length == iterator.totalNumEntities();
};
var statuses = ['ENABLED'];
if (options.checkPausedSitelinks) {
statuses.push('PAUSED');
}
var predicate = ' IN [' + statuses.join(',') + ']';
checkSitelinkUrls(AdsApp.campaigns().
withCondition('Status' + predicate));
checkSitelinkUrls(AdsApp.adGroups().
withCondition('Status' + predicate).
withCondition('CampaignStatus' + predicate));
return didComplete;
}
/**
* Expands a URL that contains ValueTrack parameters such as {ifmobile:mobile}
* to all the combinations, and returns as an array. The following pairs of
* ValueTrack parameters are currently expanded:
* 1. {ifmobile:<...>} and {ifnotmobile:<...>} to produce URLs simulating
* clicks from either mobile or non-mobile devices.
* 2. {ifsearch:<...>} and {ifcontent:<...>} to produce URLs simulating
* clicks on either the search or display networks.
* Any other ValueTrack parameters or customer parameters are stripped out from
* the URL entirely.
*
* @param {string} url The URL which may contain ValueTrack parameters.
* @return {!Array.<string>} An array of one or more expanded URLs.
*/
function expandUrlModifiers(url) {
var ifRegex = /({(if\w+):([^}]+)})/gi;
var modifiers = {};
var matches;
while (matches = ifRegex.exec(url)) {
// Tags are case-insensitive, e.g. IfMobile is valid.
modifiers[matches[2].toLowerCase()] = {
substitute: matches[0],
replacement: matches[3]
};
}
if (Object.keys(modifiers).length) {
if (modifiers.ifmobile || modifiers.ifnotmobile) {
var mobileCombinations =
pairedUrlModifierReplace(modifiers, 'ifmobile', 'ifnotmobile', url);
} else {
var mobileCombinations = [url];
}
// Store in a map on the offchance that there are duplicates.
var combinations = {};
mobileCombinations.forEach(function(url) {
if (modifiers.ifsearch || modifiers.ifcontent) {
pairedUrlModifierReplace(modifiers, 'ifsearch', 'ifcontent', url)
.forEach(function(modifiedUrl) {
combinations[modifiedUrl] = true;
});
} else {
combinations[url] = true;
}
});
var modifiedUrls = Object.keys(combinations);
} else {
var modifiedUrls = [url];
}
// Remove any custom parameters
return modifiedUrls.map(function(url) {
return url.replace(/{[0-9a-zA-Z\_\+\:]+}/g, '');
});
}
/**
* Return a pair of URLs, where each of the two modifiers is mutually exclusive,
* one for each combination. e.g. Evaluating ifmobile and ifnotmobile for a
* mobile and a non-mobile scenario.
*
* @param {Object} modifiers A map of ValueTrack modifiers.
* @param {string} modifier1 The modifier to honour in the URL.
* @param {string} modifier2 The modifier to remove from the URL.
* @param {string} url The URL potentially containing ValueTrack parameters.
* @return {!Array.<string>} A pair of URLs, as a list.
*/
function pairedUrlModifierReplace(modifiers, modifier1, modifier2, url) {
return [
urlModifierReplace(modifiers, modifier1, modifier2, url),
urlModifierReplace(modifiers, modifier2, modifier1, url)
];
}
/**
* Produces a URL where the first {if...} modifier is set, and the second is
* deleted.
*
* @param {Object} mods A map of ValueTrack modifiers.
* @param {string} mod1 The modifier to honour in the URL.
* @param {string} mod2 The modifier to remove from the URL.
* @param {string} url The URL potentially containing ValueTrack parameters.
* @return {string} The resulting URL with substitions.
*/
function urlModifierReplace(mods, mod1, mod2, url) {
var modUrl = mods[mod1] ?
url.replace(mods[mod1].substitute, mods[mod1].replacement) :
url;
return mods[mod2] ? modUrl.replace(mods[mod2].substitute, '') : modUrl;
}
/**
* Requests a given URL. Retries if the UrlFetchApp QPS limit was reached,
* exponentially backing off on each retry. Throws an exception if it reaches
* the maximum number of retries. Throws an exception if the UrlFetchApp daily
* quota limit was reached.
*
* @param {string} url The URL to test.
* @param {!Object} options The options loaded from the configuration sheet.
* @param {!Object} entityDetails Details of the entity, e.g. type, name etc.
* @return {number|string} The response code received when requesting the URL,
* or an error message.
*/
function requestUrl(url, options, entityDetails) {
var responseCode;
var sleepTime = QUOTA_CONFIG.INIT_SLEEP_TIME;
var numTries = 0;
while (numTries < QUOTA_CONFIG.MAX_TRIES && !responseCode) {
try {
// If UrlFetchApp.fetch() throws an exception, responseCode will remain
// undefined.
var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
responseCode = response.getResponseCode();
if (options.validCodes.indexOf(responseCode) !== -1) {
if (options.useSimpleFailureStrings &&
bodyContainsFailureStrings(response, options.failureStrings)) {
responseCode = 'Failure string detected';
} else if (options.useCustomValidation && !isValidResponse(url,
response, options, entityDetails)) {
responseCode = "Custom validation failed";
}
}
if (CONFIG.THROTTLE > 0) {
Utilities.sleep(CONFIG.THROTTLE);
}
} catch(e) {
if (e.message.indexOf('Service invoked too many times in a short time:')
!= -1) {
Utilities.sleep(sleepTime);
sleepTime *= QUOTA_CONFIG.BACKOFF_FACTOR;
} else if (e.message.indexOf('Service invoked too many times:') != -1) {
throw EXCEPTIONS.LIMIT;
} else {
return e.message;
}
}
numTries++;
}
if (!responseCode) {
throw EXCEPTIONS.QPS;
} else {
return responseCode;
}
}
/**
* Searches the body of a HTTP response for any occurrence of a "failure string"
* as defined in the configuration spreadsheet. For example, "Out of stock".
*
* @param {!HTTPResponse} response The response from the UrlFetchApp request.
* @param {!Array.<string>} failureStrings A list of failure strings.
* @return {boolean} Returns true if at least one failure string found.
*/
function bodyContainsFailureStrings(response, failureStrings) {
var contentText = response.getContentText() || '';
// Whilst searching for each separate failure string across the body text
// separately may not be the most efficient, it is simple, and tests suggest
// it is not overly poor performance-wise.
return failureStrings.some(function(failureString) {
return contentText.indexOf(failureString) !== -1;
});
}
/**
* Throws an exception if the script is close to timing out.
*/
function checkTimeout() {
if (AdsApp.getExecutionInfo().getRemainingTime() <
CONFIG.TIMEOUT_BUFFER) {
throw EXCEPTIONS.TIMEOUT;
}
}
/**
* Returns the number of days between two dates.
*
* @param {Object} from The older Date object.
* @param {Object} to The newer (more recent) Date object.
* @return {number} The number of days between the given dates (possibly
* fractional).
*/
function dayDifference(from, to) {
return (to.getTime() - from.getTime()) / (24 * 3600 * 1000);
}
/**
* Builds a string to be used for withCondition() filtering for whether the
* label is present or not.
*
* @param {boolean} hasLabel True if the label should be present, false if the
* label should not be present.
* @return {string} A condition that can be used in withCondition().
*/
function labelCondition(hasLabel) {
return 'LabelNames ' + (hasLabel ? 'CONTAINS_ANY' : 'CONTAINS_NONE') +
' ["' + CONFIG.LABEL + '"]';
}
/**
* Retrieves an entity by name.
*
* @param {Object} selector A selector for an entity type with a Name field.
* @param {string} name The name to retrieve the entity by.
* @return {Object} The entity, if it exists, or null otherwise.
*/
function getEntityByName(selector, name) {
var entities = selector.withCondition('Name = "' + name + '"').get();
if (entities.hasNext()) {
return entities.next();
} else {
return null;
}
}
/**
* Retrieves a Label object by name.
*
* @param {string} labelName The label name to retrieve.
* @return {Object} The Label object, if it exists, or null otherwise.
*/
function getLabel(labelName) {
return getEntityByName(AdsApp.labels(), labelName);
}
/**
* Checks that the account has all provided labels and creates any that are
* missing. Since labels cannot be created in preview mode, throws an exception
* if a label is missing.
*
* @param {Array.<string>} labelNames An array of label names.
*/
function ensureLabels(labelNames) {
for (var i = 0; i < labelNames.length; i++) {
var labelName = labelNames[i];
var label = getLabel(labelName);
if (!label) {
if (!AdsApp.getExecutionInfo().isPreview()) {
AdsApp.createLabel(labelName);
} else {
throw 'Label ' + labelName + ' is missing and cannot be created in ' +
'preview mode. Please run the script or create the label manually.';
}
}
}
}
/**
* Removes all provided labels from the account. Since labels cannot be removed
* in preview mode, throws an exception in preview mode.
*
* @param {Array.<string>} labelNames An array of label names.
*/
function removeLabels(labelNames) {
if (AdsApp.getExecutionInfo().isPreview()) {
throw 'Cannot remove labels in preview mode. Please run the script or ' +
'remove the labels manually.';
}
for (var i = 0; i < labelNames.length; i++) {
var label = getLabel(labelNames[i]);
if (label) {
label.remove();
}
}
}
/**
* Validates the provided spreadsheet URL to make sure that it's set up
* properly. Throws a descriptive error message if validation fails.
*
* @param {string} spreadsheeturl The URL of the spreadsheet to open.
* @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
* @throws {Error} If the spreadsheet URL hasn't been set
*/
function validateAndGetSpreadsheet(spreadsheeturl) {
if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
throw new Error('Please specify a valid Spreadsheet URL. You can find' +
' a link to a template in the associated guide for this script.');
}
return SpreadsheetApp.openByUrl(spreadsheeturl);
}
/**
* Validates the provided email addresses to make sure it's not the default.
* Throws a descriptive error message if validation fails.
*
* @param {Array.<string>} recipientEmails The list of email adresses.
* @throws {Error} If the list of email addresses is still the default
*/
function validateEmailAddresses(recipientEmails) {
if (recipientEmails &&
recipientEmails[0] == 'YOUR_EMAIL_HERE') {
throw new Error('Please either specify a valid email address or clear' +
' the RECIPIENT_EMAILS field.');
}
}
Low quality score alert
Problem
Low quality score leads to wasted ad budgets, underperforming ads, and overall inefficient performance.
With this script by Daniel Gilbert, you’ll be able to get rid of low quality score keywords.
The script below finds the low quality score keywords in your account and sends you an email alert. Depending on your choice, you can also pause or label these keywords found by the script.
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options
var EMAIL_ADDRESSES = ["alice@example.com", "bob@example.co.uk"];
// The address or addresses that will be emailed a list of low QS keywords
// eg ["alice@example.com", "bob@example.co.uk"] or ["eve@example.org"]
var QS_THRESHOLD = 3;
// Keywords with quality score less than or equal to this number are
// considered 'low QS'
var LABEL_KEYWORDS = true;
// If this is true, low QS keywords will be automatically labelled
var LOW_QS_LABEL_NAME = "Low QS Keyword";
// The name of the label applied to low QS keywords
var PAUSE_KEYWORDS = false;
// If this is true, low QS keywords will be automatically paused
// Set to false if you want them to stay active.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions
function main() {
Logger.log("Pause Keywords: " + PAUSE_KEYWORDS);
Logger.log("Label Keywords: " + LABEL_KEYWORDS);
var keywords = findKeywordsWithQSBelow(QS_THRESHOLD);
Logger.log("Found " + keywords.length + " keywords with low quality score");
if (!labelExists(LOW_QS_LABEL_NAME)) {
Logger.log(Utilities.formatString('Creating label: "%s"', LOW_QS_LABEL_NAME));
AdWordsApp.createLabel(LOW_QS_LABEL_NAME, 'Automatically created by QS Alert', 'red');
}
var mutations = [
{
enabled: PAUSE_KEYWORDS,
callback: function (keyword) {
keyword.pause();
}
},
{
enabled: LABEL_KEYWORDS,
callback: function (keyword, currentLabels) {
if (currentLabels.indexOf(LOW_QS_LABEL_NAME) === -1) {
keyword.applyLabel(LOW_QS_LABEL_NAME);
}
}
}
];
var chunkSize = 10000;
var chunkedKeywords = chunkList(keywords, chunkSize);
Logger.log("Making changes to keywords..");
chunkedKeywords.forEach(function (keywordChunk) {
mutateKeywords(keywordChunk, mutations);
});
if (keywords.length > 0) {
sendEmail(keywords);
Logger.log("Email sent.");
} else {
Logger.log("No email to send.");
}
}
function findKeywordsWithQSBelow(threshold) {
var query = 'SELECT Id, AdGroupId, CampaignName, AdGroupName, Criteria, QualityScore, Labels'
+ ' FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = "ENABLED" AND CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED"'
+ ' AND HasQualityScore = "TRUE" AND QualityScore <= ' + threshold;
var report = AdWordsApp.report(query);
var rows = report.rows();
var lowQSKeywords = [];
while (rows.hasNext()) {
var row = rows.next();
var lowQSKeyword = {
campaignName: row['CampaignName'],
adGroupName: row['AdGroupName'],
keywordText: row['Criteria'],
labels: (row['Labels'].trim() === '--') ? [] : JSON.parse(row['Labels']),
uniqueId: [row['AdGroupId'], row['Id']],
qualityScore: row['QualityScore']
};
lowQSKeywords.push(lowQSKeyword);
}
return lowQSKeywords;
}
function labelExists(labelName) {
var condition = Utilities.formatString('LabelName = "%s"', labelName);
return AdWordsApp.labels().withCondition(condition).get().hasNext();
}
function chunkList(list, chunkSize) {
var chunks = [];
for (var i = 0; i < list.length; i += chunkSize) {
chunks.push(list.slice(i, i + chunkSize));
}
return chunks;
}
function mutateKeywords(keywords, mutations) {
var keywordIds = keywords.map(function (keyword) {
return keyword['uniqueId'];
});
var mutationsToApply = getMutationsToApply(mutations);
var adwordsKeywords = AdWordsApp.keywords().withIds(keywordIds).get();
var i = 0;
while (adwordsKeywords.hasNext()) {
var currentKeywordLabels = keywords[i]['labels'];
var adwordsKeyword = adwordsKeywords.next();
mutationsToApply.forEach(function(mutate) {
mutate(adwordsKeyword, currentKeywordLabels);
});
i++;
}
}
function getMutationsToApply(mutations) {
var enabledMutations = mutations.filter(function (mutation) {
return mutation['enabled'];
});
return enabledMutations.map(function (condition) {
return condition['callback'];
});
}
function sendEmail(keywords) {
var subject = "Low Quality Keywords Paused";
var htmlBody =
"<p>Keywords with a quality score of less than " + QS_THRESHOLD + "found.<p>"
+ "<p>Actions Taken:<p>"
+ "<ul>"
+ "<li><b>Paused</b>: " + PAUSE_KEYWORDS + "</li>"
+ "<li><b>Labelled</b> with <code>" + LOW_QS_LABEL_NAME + "</code>: " + LABEL_KEYWORDS + "</li>"
+ "</ul>"
+ renderTable(keywords);
MailApp.sendEmail({
to: EMAIL_ADDRESSES.join(","),
subject: subject,
htmlBody: htmlBody
});
}
function renderTable(keywords) {
var header = '<table border="2" cellspacing="0" cellpadding="6" rules="groups" frame="hsides">'
+ '<thead><tr>'
+ '<th>Campaign Name</th>'
+ '<th>Ad Group Name</th>'
+ '<th>Keyword Text</th>'
+ '<th>Quality Score</th>'
+ '</tr></thead><tbody>';
var rows = keywords.reduce(function(accumulator, keyword) {
return accumulator
+ '<tr><td>' + [
keyword['campaignName'],
keyword['adGroupName'],
keyword['keywordText'],
keyword['qualityScore']
].join('</td><td>')
+ '</td></tr>';
}, "");
var footer = '</tbody></table>';
var table = header + rows + footer;
return table;
}
Bid Management Scripts
Unique bidding rules for each campaign
Problem
Adjusting the bids for each campaign can be time consuming if you have a lot of active campaigns going on in your Ads account.
With this script by Russ Savage, you’ll be able to set up a script in order to automatically adjust the bids for each campaign based on a group of rules.
Automated bidding for your campaigns will save a lot of time and effort, as well as Ads performance.
//-----------------------------------
// Unique Bid Updates By Campaign
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
// this is the structure that holds all the bid information about your accounts.
var CAMP_LIST = [
{
'camp_name' : 'camp name 1',
'rules' : [
{
'cpv_min' : 0, 'cpv_max' : 10,
'avg_pos_min' : 2, 'avg_pos_max' : 6,
'bid_change_amt' : 1.1, 'bid_limit' : 10
},
{
'cpv_min' : 10, 'cpv_max' : 20,
'avg_pos_min' : 6, 'avg_pos_max' : 10,
'bid_change_amt' : 1.2, 'bid_limit' : 10
}
]
},
{
'camp_name' : 'camp name 2',
'rules' : [
{
'cpv_min' : 0, 'cpv_max' : 5,
'avg_pos_min' : 3, 'avg_pos_max' : 5,
'bid_change_amt' : .9, 'bid_limit' : .01
},
{
'cpv_min' : 5, 'cpv_max' : 20,
'avg_pos_min' : 5, 'avg_pos_max' : 8,
'bid_change_amt' : 1.2, 'bid_limit' : 10
}
]
}
];
var date_range = 'LAST_7_DAYS';
for (index in CAMP_LIST) {
var camp = CAMP_LIST[index];
var camp_name = camp.camp_name;
var rules = camp.rules;
var kw_iter = AdWordsApp.keywords()
.withCondition("CampaignName CONTAINS_IGNORE_CASE '" + camp_name + "'")
.get();
while(kw_iter.hasNext()) {
var kw = kw_iter.next();
var kw_stats = kw.getStatsFor(date_range);
var conv = kw_stats.getConversions();
if (conv == 0) { continue; } //skip anything with no conversions
var cost = kw_stats.getCost();
var cpv = cost/conv;
var avg_pos = kw_stats.getAveragePosition();
var max_cpc = kw.getMaxCpc();
for(i in rules) {
var r = rules[i];
if(cpv >= r.cpv_min &&
cpv < r.cpv_max &&
avg_pos >= r.avg_pos_min &&
avg_pos < r.avg_pos_max)
{
kw.setMaxCpc(calculate_bid(max_cpc,r.bid_change_amt,r.bid_limit));
break;
}
}
}
}
function calculate_bid(current_bid,perc_to_change,max_min_amt) {
if(perc_to_change >= 1) {
return (current_bid * perc_to_change > max_min_amt) ? max_min_amt : (current_bid * perc_to_change);
} else {
return (current_bid * perc_to_change < max_min_amt) ? max_min_amt : (current_bid * perc_to_change);
}
}
}
Real time position bidding
Problem
Yet again, manually bidding takes a lot of time, and there is high competition in many fields. Automation is necessary.
With this script by BrainLabs, you’ll be able to automatically change the bids of labelled keywords with the help of automation.
The script runs every hour, thus letting you appear in target positions. The changes are limited to 20% so that you feel safe with your budget and account.
// ID: fc0e24f9577d1f8f2279b1e5d4ee50f9
/**
*
* Average Position Bidding Tool
*
* This script changes keyword bids so that they target specified positions,
* based on recent performance.
*
* Version: 1.5
* Updated 2015-09-28 to correct for report column name changes
* Updated 2016-02-05 to correct label reading, add extra checks and
* be able to adjust maximum bid increases and decreases separately
* Updated 2016-08-30 to correct label reading from reports
* Updated 2016-09-14 to update keywords in batches
* Updated 2016-10-26 to avoid DriveApp bug
* Google AdWords Script maintained on brainlabsdigital.com
*
*/
// Options
var maxBid = 3.00;
// Bids will not be increased past this maximum.
var minBid = 0.15;
// Bids will not be decreased below this minimum.
var firstPageMaxBid = 0.90;
// The script avoids reducing a keyword's bid below its first page bid estimate. If you think
// Google's first page bid estimates are too high then use this to overrule them.
var dataFile = 'AveragePositionData.txt';
// This name is used to create a file in your Google Drive to store today's performance so far,
// for reference the next time the script is run.
var useFirstPageBidsOnKeywordsWithNoImpressions = false;
// If this is true, then if a keyword has had no impressions since the last time the script was run
// its bid will be increased to the first page bid estimate (or the firsPageMaxBid if that is smaller).
// If this is false, keywords with no recent impressions will be left alone.
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Advanced Options
var bidIncreaseProportion = 0.2;
var bidDecreaseProportion = 0.2;
var targetPositionTolerance = 0.3;
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function main() {
var fieldJoin = ',';
var lineJoin = '$';
var idJoin = '#';
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var files = DriveApp.getFilesByName(dataFile);
if (!files.hasNext()) {
var file = DriveApp.createFile(dataFile, '\n');
Logger.log("File '" + dataFile + "' has been created.");
} else {
var file = files.next();
if (files.hasNext()) {
Logger.log("Error - more than one file named '" + dataFile + "'");
return;
}
Logger.log("File '" + dataFile + "' has been read.");
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var labelIds = [];
var labelIterator = AdWordsApp.labels()
.withCondition('KeywordsCount > 0')
.withCondition("LabelName CONTAINS_IGNORE_CASE 'Position '")
.get();
while (labelIterator.hasNext()) {
var label = labelIterator.next();
if (label.getName().substr(0, 'position '.length).toLowerCase() == 'position ') {
labelIds.push(label.getId());
}
}
if (labelIds.length == 0) {
Logger.log('No position labels found.');
return;
}
Logger.log(labelIds.length + ' position labels have been found.');
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var keywordData = {
// UniqueId1: {LastHour: {Impressions: , AveragePosition: }, ThisHour: {Impressions: , AveragePosition: },
// CpcBid: , FirstPageCpc: , MaxBid, MinBid, FirstPageMaxBid, PositionTarget: , CurrentAveragePosition:,
// Criteria: }
};
var ids = [];
var uniqueIds = [];
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var report = AdWordsApp.report(
'SELECT Id, Criteria, AdGroupId, AdGroupName, CampaignName, Impressions, AveragePosition, CpcBid, FirstPageCpc, Labels, BiddingStrategyType '
+ 'FROM KEYWORDS_PERFORMANCE_REPORT '
+ 'WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignStatus = ENABLED '
+ 'AND LabelIds CONTAINS_ANY [' + labelIds.join(',') + '] '
+ 'AND AdNetworkType2 = SEARCH '
+ 'AND Device NOT_IN ["HIGH_END_MOBILE"] '
+ 'DURING TODAY'
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
if (row.BiddingStrategyType != 'cpc') {
if (row.BiddingStrategyType == 'Enhanced CPC'
|| row.BiddingStrategyType == 'Target search page location'
|| row.BiddingStrategyType == 'Target Outranking Share'
|| row.BiddingStrategyType == 'None'
|| row.BiddingStrategyType == 'unknown') {
Logger.log('Warning: keyword ' + row.Criteria + "' in campaign '" + row.CampaignName
+ "' uses '" + row.BiddingStrategyType + "' rather than manual CPC. This may overrule keyword bids and interfere with the script working.");
} else {
Logger.log('Warning: keyword ' + row.Criteria + "' in campaign '" + row.CampaignName
+ "' uses the bidding strategy '" + row.BiddingStrategyType + "' rather than manual CPC. This keyword will be skipped.");
continue;
}
}
var positionTarget = '';
if (row.Labels.trim() == '--') {
continue;
}
var labels = JSON.parse(row.Labels.toLowerCase()); // Labels are returned as a JSON formatted string
for (var i = 0; i < labels.length; i++) {
if (labels[i].substr(0, 'position '.length) == 'position ') {
var positionTarget = parseFloat(labels[i].substr('position '.length - 1).replace(/,/g, '.'), 10);
break;
}
}
if (positionTarget == '') {
continue;
}
if (integrityCheck(positionTarget) == -1) {
Logger.log("Invalid position target '" + positionTarget + "' for keyword '" + row.Criteria + "' in campaign '" + row.CampaignName + "'");
continue;
}
ids.push(parseFloat(row.Id, 10));
var uniqueId = row.AdGroupId + idJoin + row.Id;
uniqueIds.push(uniqueId);
keywordData[uniqueId] = {};
keywordData[uniqueId].Criteria = row.Criteria;
keywordData[uniqueId].ThisHour = {};
keywordData[uniqueId].ThisHour.Impressions = parseFloat(row.Impressions.replace(/,/g, ''), 10);
keywordData[uniqueId].ThisHour.AveragePosition = parseFloat(row.AveragePosition.replace(/,/g, ''), 10);
keywordData[uniqueId].CpcBid = parseFloat(row.CpcBid.replace(/,/g, ''), 10);
keywordData[uniqueId].FirstPageCpc = parseFloat(row.FirstPageCpc.replace(/,/g, ''), 10);
setPositionTargets(uniqueId, positionTarget);
}
Logger.log(uniqueIds.length + ' labelled keywords found');
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
setBidChange();
setMinMaxBids();
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
var currentHour = parseInt(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'HH'), 10);
if (currentHour != 0) {
var data = file.getBlob().getDataAsString();
var data = data.split(lineJoin);
for (var i = 0; i < data.length; i++) {
data[i] = data[i].split(fieldJoin);
var uniqueId = data[i][0];
if (keywordData.hasOwnProperty(uniqueId)) {
keywordData[uniqueId].LastHour = {};
keywordData[uniqueId].LastHour.Impressions = parseFloat(data[i][1], 10);
keywordData[uniqueId].LastHour.AveragePosition = parseFloat(data[i][2], 10);
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
findCurrentAveragePosition();
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Batch the keyword IDs, as the iterator can't take them all at once
var idBatches = [];
var batchSize = 5000;
for (var i = 0; i < uniqueIds.length; i += batchSize) {
idBatches.push(uniqueIds.slice(i, i + batchSize));
}
Logger.log('Updating keywords');
// Update each batch
for (var i = 0; i < idBatches.length; i++) {
try {
updateKeywords(idBatches[i]);
} catch (e) {
Logger.log('Error updating keywords: ' + e);
Logger.log('Retrying after one minute.');
Utilities.sleep(60000);
updateKeywords(idBatches[i]);
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
Logger.log('Writing file.');
var content = resultsString();
file.setContent(content);
Logger.log('Finished.');
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function integrityCheck(target) {
var n = parseFloat(target, 10);
if (!isNaN(n) && n >= 1) {
return n;
}
return -1;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function setPositionTargets(uniqueId, target) {
if (target !== -1) {
keywordData[uniqueId].HigherPositionTarget = Math.max(target - targetPositionTolerance, 1);
keywordData[uniqueId].LowerPositionTarget = target + targetPositionTolerance;
} else {
keywordData[uniqueId].HigherPositionTarget = -1;
keywordData[uniqueId].LowerPositionTarget = -1;
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function bidChange(uniqueId) {
var newBid = -1;
if (keywordData[uniqueId].HigherPositionTarget === -1) {
return newBid;
}
var cpcBid = keywordData[uniqueId].CpcBid;
var minBid = keywordData[uniqueId].MinBid;
var maxBid = keywordData[uniqueId].MaxBid;
if (isNaN(keywordData[uniqueId].FirstPageCpc)) {
Logger.log("Warning: first page CPC estimate is not a number for keyword '" + keywordData[uniqueId].Criteria + "'. This keyword will be skipped");
return -1;
}
var firstPageBid = Math.min(keywordData[uniqueId].FirstPageCpc, keywordData[uniqueId].FirstPageMaxBid, maxBid);
var currentPosition = keywordData[uniqueId].CurrentAveragePosition;
var higherPositionTarget = keywordData[uniqueId].HigherPositionTarget;
var lowerPositionTarget = keywordData[uniqueId].LowerPositionTarget;
var bidIncrease = keywordData[uniqueId].BidIncrease;
var bidDecrease = keywordData[uniqueId].BidDecrease;
if ((currentPosition > lowerPositionTarget) && (currentPosition !== 0)) {
var linearBidModel = Math.min(2 * bidIncrease, (2 * bidIncrease / lowerPositionTarget) * (currentPosition - lowerPositionTarget));
var newBid = Math.min((cpcBid + linearBidModel), maxBid);
}
if ((currentPosition < higherPositionTarget) && (currentPosition !== 0)) {
var linearBidModel = Math.min(2 * bidDecrease, ((-4) * bidDecrease / higherPositionTarget) * (currentPosition - higherPositionTarget));
var newBid = Math.max((cpcBid - linearBidModel), minBid);
if (cpcBid > firstPageBid) {
var newBid = Math.max(firstPageBid, newBid);
}
}
if ((currentPosition === 0) && useFirstPageBidsOnKeywordsWithNoImpressions && (cpcBid < firstPageBid)) {
var newBid = firstPageBid;
}
if (isNaN(newBid)) {
Logger.log("Warning: new bid is not a number for keyword '" + keywordData[uniqueId].Criteria + "'. This keyword will be skipped");
return -1;
}
return newBid;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function findCurrentAveragePosition() {
for (var x in keywordData) {
if (keywordData[x].hasOwnProperty('LastHour')) {
keywordData[x].CurrentAveragePosition = calculateAveragePosition(keywordData[x]);
} else {
keywordData[x].CurrentAveragePosition = keywordData[x].ThisHour.AveragePosition;
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function calculateAveragePosition(keywordDataElement) {
var lastHourImpressions = keywordDataElement.LastHour.Impressions;
var lastHourAveragePosition = keywordDataElement.LastHour.AveragePosition;
var thisHourImpressions = keywordDataElement.ThisHour.Impressions;
var thisHourAveragePosition = keywordDataElement.ThisHour.AveragePosition;
if (thisHourImpressions == lastHourImpressions) {
return 0;
}
var currentPosition = (thisHourImpressions * thisHourAveragePosition - lastHourImpressions * lastHourAveragePosition) / (thisHourImpressions - lastHourImpressions);
if (currentPosition < 1) {
return 0;
}
return currentPosition;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function keywordUniqueId(keyword) {
var id = keyword.getId();
var idsIndex = ids.indexOf(id);
if (idsIndex === ids.lastIndexOf(id)) {
return uniqueIds[idsIndex];
}
var adGroupId = keyword.getAdGroup().getId();
return adGroupId + idJoin + id;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function setMinMaxBids() {
for (var x in keywordData) {
keywordData[x].MinBid = minBid;
keywordData[x].MaxBid = maxBid;
keywordData[x].FirstPageMaxBid = firstPageMaxBid;
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function setBidChange() {
for (var x in keywordData) {
keywordData[x].BidIncrease = keywordData[x].CpcBid * bidIncreaseProportion / 2;
keywordData[x].BidDecrease = keywordData[x].CpcBid * bidDecreaseProportion / 2;
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function updateKeywords(idBatch) {
var keywordIterator = AdWordsApp.keywords()
.withIds(idBatch.map(function (str) {
return str.split(idJoin);
}))
.get();
while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var uniqueId = keywordUniqueId(keyword);
var newBid = bidChange(uniqueId);
if (newBid !== -1) {
keyword.setMaxCpc(newBid);
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function resultsString() {
var results = [];
for (var uniqueId in keywordData) {
var resultsRow = [uniqueId, keywordData[uniqueId].ThisHour.Impressions, keywordData[uniqueId].ThisHour.AveragePosition];
results.push(resultsRow.join(fieldJoin));
}
return results.join(lineJoin);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
}
Automate your in-market audience
Problem
You need to keep a close eye on in-market audiences as often performance can decrease with them.
With this script by Daniel Gilbert, you’ll be able to implement bid adjustments to your in-market audience.
“The script looks at your campaigns’ CPC over a given time range and sets bid modifiers to each of the campaign-level in-market audiences based on performance.”
/**
*
* In-market Audiences Bidding
*
* Automatically apply modifiers to your in-market audiences based on performance.
*
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
// Use this to determine the relevant date range for your data.
// See here for the possible options:
// https://developers.google.com/google-ads/scripts/docs/reference/adwordsapp/adwordsapp_campaignselector#forDateRange_1
var DATE_RANGE = 'LAST_30_DAYS';
// Use this to determine the minimum number of impressions a campaign or
// and ad group should have before being considered.
var MINIMUM_IMPRESSIONS = 0;
// Use this if you want to exclude some campaigns. Case insensitive.
// For example ["Brand"] would ignore any campaigns with 'brand' in the name,
// while ["Brand","Competitor"] would ignore any campaigns with 'brand' or
// 'competitor' in the name.
// Leave as [] to not exclude any campaigns.
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = [];
// Use this if you only want to look at some campaigns. Case insensitive.
// 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 CAMPAIGN_NAME_CONTAINS = [];
var AUDIENCE_MAPPING_CSV_DOWNLOAD_URL = 'https://developers.google.com/adwords/api/docs/appendix/in-market_categories.csv';
function main() {
Logger.log('Getting audience mapping');
var audienceMapping =
getInMarketAudienceMapping(AUDIENCE_MAPPING_CSV_DOWNLOAD_URL);
Logger.log('Getting campaign performance');
var campaignPerformance = getCampaignPerformance();
Logger.log('Getting ad group performance');
var adGroupPerformance = getAdGroupPerformance();
Logger.log('Making operations');
var operations = makeAllOperations(
audienceMapping,
campaignPerformance,
adGroupPerformance
);
Logger.log('Applying bids');
applyBids(operations);
}
function getInMarketAudienceMapping(downloadCsvUrl) {
var csv = Utilities.parseCsv(
UrlFetchApp.fetch(downloadCsvUrl).getContentText()
);
var headers = csv[0];
var indexOfId = headers.indexOf('Criterion ID');
var indexOfName = headers.indexOf('Category');
if ((indexOfId === -1) || (indexOfName === -1)) {
throw new Error('The audience CSV does not have the expected headers');
}
var mapping = {};
for (var i = 1; i < csv.length; i++) {
var row = csv[i];
mapping[row[indexOfId]] = row[indexOfName];
}
return mapping;
}
function getCampaignPerformance() {
return getEntityPerformance('CampaignId', 'CAMPAIGN_PERFORMANCE_REPORT');
}
function getAdGroupPerformance() {
return getEntityPerformance('AdGroupId', 'ADGROUP_PERFORMANCE_REPORT');
}
function getEntityPerformance(entityIdFieldName, reportName) {
var performance = {};
var query = "SELECT " + entityIdFieldName + ", CostPerAllConversion " +
"FROM " + reportName + " " +
"WHERE Impressions > " + String(MINIMUM_IMPRESSIONS) + " " +
"DURING " + DATE_RANGE;
var rows = AdsApp.report(query).rows();
while (rows.hasNext()) {
var row = rows.next();
performance[row[entityIdFieldName]] = row.CostPerAllConversion;
}
return performance;
}
function makeAllOperations(
audienceMapping,
campaignPerformance,
adGroupPerformance
) {
var operations = [];
var allCampaigns =
filterCampaignsBasedOnName(AdWordsApp.campaigns());
var filteredCampaigns =
filterEntitiesBasedOnDateAndImpressions(allCampaigns)
.get();
while (filteredCampaigns.hasNext()) {
var campaign = filteredCampaigns.next();
// Can't have both ad-group-level and campaign-level
// audiences on any given campaign.
if (campaignHasAnyCampaignLevelAudiences(campaign)) {
var operationsFromCampaign = makeOperationsFromEntity(
campaign,
campaignPerformance[campaign.getId()],
audienceMapping
);
operations = operations.concat(operationsFromCampaign);
} else {
var adGroups =
filterEntitiesBasedOnDateAndImpressions(campaign.adGroups())
.get();
while (adGroups.hasNext()) {
var adGroup = adGroups.next();
var operationsFromAdGroup = makeOperationsFromEntity(
adGroup,
adGroupPerformance[adGroup.getId()],
audienceMapping
);
operations = operations.concat(operationsFromAdGroup);
}
}
}
return operations;
}
function filterCampaignsBasedOnName(campaigns) {
CAMPAIGN_NAME_DOES_NOT_CONTAIN.forEach(function(part) {
campaigns = campaigns.withCondition(
"CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + part.replace(/"/g,'\\\"') + "'"
);
});
CAMPAIGN_NAME_CONTAINS.forEach(function(part) {
campaigns = campaigns.withCondition(
"CampaignName CONTAINS_IGNORE_CASE '" + part.replace(/"/g,'\\\"') + "'"
);
});
return campaigns;
}
function filterEntitiesBasedOnDateAndImpressions(selector) {
return selector
.forDateRange(DATE_RANGE)
.withCondition('Impressions > ' + String(MINIMUM_IMPRESSIONS));
}
function makeOperationsFromEntity(entity, entityCpa, audienceMapping) {
var entityAudiences = getAudiencesFromEntity(entity, audienceMapping);
return makeOperations(entityCpa, entityAudiences);
}
function getAudiencesFromEntity(entity, audienceMapping) {
var inMarketIds = Object.keys(audienceMapping);
var allAudiences = entity
.targeting()
.audiences()
.forDateRange(DATE_RANGE)
.withCondition('Impressions > ' + String(MINIMUM_IMPRESSIONS))
.get();
var inMarketAudiences = [];
while (allAudiences.hasNext()) {
var audience = allAudiences.next();
if (isAudienceInMarketAudience(audience, inMarketIds)) {
inMarketAudiences.push(audience);
}
}
return inMarketAudiences;
}
function isAudienceInMarketAudience(audience, inMarketIds) {
return inMarketIds.indexOf(audience.getAudienceId()) > -1;
}
function makeOperations(entityCpa, audiences) {
var operations = [];
audiences.forEach(function(audience) {
var stats = audience.getStatsFor(DATE_RANGE);
var conversions = stats.getConversions();
if (conversions > 0) {
var audienceCpa = stats.getCost() / stats.getConversions();
entityCpa = parseFloat(entityCpa);
var modifier = (entityCpa / audienceCpa);
var operation = {};
operation.audience = audience;
operation.modifier = modifier;
operations.push(operation);
}
});
return operations;
}
function campaignHasAnyCampaignLevelAudiences(campaign) {
var totalNumEntities = campaign
.targeting()
.audiences()
.get()
.totalNumEntities();
return totalNumEntities > 0;
}
function applyBids(operations) {
operations.forEach(function(operation) {
operation.audience.bidding().setBidModifier(operation.modifier);
});
}
Reporting Scripts
Automated keyword performance reports
Problem
Managing multiple Google Ads account is sometimes hard to keep up with, especially with reporting and tracking performance for each account.
With this script by Philip Jones, you’ll have Keyword Performance Report for each specified account in a Google Ads manager account.
For each account, the script will create a seperate tab on Google Sheets. You’ll get an email with the export link when the process is completed.
- Copy this code to MCC level script tab.
- In code line 5, add account lds to the config.
- In code line 6, type in your email address to get export link automatically.
//** Script Developed by PMJ Digital **//
// Allowed Date Ranges TODAY, YESTERDAY, LAST_7_DAYS, LAST_30_DAYS
// For Custom date ranges use date format : yyyymmdd, yyyymmdd
var config = {
'accountIDs': [''],
'email address': '',
'time frame' : "LAST_30_DAYS"
}
function main() {
var now = new Date();
var today = JSON.stringify(new Date(now.getTime()));
//creates spreadsheet
var spreadsheet = SpreadsheetApp.create("MCC Level Keyword Performance Reports " + today.substr(1, 10));
//Gets all the accounts from MCC with selected account IDs
var accountIterator = AdsManagerApp.accounts()
.withIds(config.accountIDs)
.get();
//Iterates through accounts
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var accountName = account.getName();
//Logs account name
Logger.log('Running Account Performance Report For ' + accountName)
var report = AdsApp.report("SELECT CampaignName, AdGroupName, Criteria, Impressions, Clicks, Ctr, Cost, AverageCpc, Conversions" +
" FROM KEYWORDS_PERFORMANCE_REPORT" +
' WHERE Impressions > 0' +
' DURING ' + config["time frame"]);
spreadsheet.insertSheet(accountName)
report.exportToSheet(spreadsheet.getActiveSheet());
}
var sheet = spreadsheet.getSheetByName('Sheet1');
spreadsheet.deleteSheet(sheet);
spreadsheet.addEditors([config["email address"]])
//Builds email
var emailHeader = [];
var emailFooter = [];
var emailBody = [];
emailHeader.push('<html>',
'<head></head>',
'<body>');
emailFooter.push('</body>',
'</html>');
emailBody.push("SQR Report available at " + spreadsheet.getUrl())
//sends email
MailApp.sendEmail({
to: config["email address"],
name: 'PMJ Digital Google Ads Scripts',
subject: "Mcc keyword performance report ready",
htmlBody: emailHeader.join('\n') + emailBody.join('\n') + emailFooter.join('\n')
});
Logger.log("Email sent with export")
}
Google Ads spellcheck script
Problem
Typo, misspelling, or other mistakes in your ads snippets can lead to low CTR or bad brand image.
With this script by Russell Savage, you’ll be able to automatically spellcheck your ad copies.
The script makes use of Bing spellcheck API (from Microsoft Word) to let you check any spelling mistake with your ad copies. All you need to do is register to get a key.
/******************************************
* Bing Spellchecker API v1.0
* By: Russ Savage (@russellsavage)
* Usage:
* // You will need a key from
* // https://www.microsoft.com/cognitive-services/en-us/bing-spell-check-api/documentation
* // to use this library.
* var bing = new BingSpellChecker({
* key : 'xxxxxxxxxxxxxxxxxxxxxxxxx',
* toIgnore : ['list','of','words','to','ignore'],
* enableCache : true // <- stores data in a file to reduce api calls
* });
* // Example usage:
* var hasSpellingIssues = bing.hasSpellingIssues('this is a speling error');
******************************************/
function BingSpellChecker(config) {
this.BASE_URL = 'https://api.cognitive.microsoft.com/bing/v5.0/spellcheck';
this.CACHE_FILE_NAME = 'spellcheck_cache.json';
this.key = config.key;
this.toIgnore = config.toIgnore;
this.cache = null;
this.previousText = null;
this.previousResult = null;
this.delay = (config.delay) ? config.delay : 60000/7;
this.timeOfLastCall = null;
this.hitQuota = false;
// Given a set of options, this function calls the API to check the spelling
// options:
// options.text : the text to check
// options.mode : the mode to use, defaults to 'proof'
// returns a list of misspelled words, or empty list if everything is good.
this.checkSpelling = function(options) {
if(this.toIgnore) {
options.text = options.text.replace(new RegExp(this.toIgnore.join('|'),'gi'), '');
}
options.text = options.text.replace(/{.+}/gi, '');
options.text = options.text.replace(/[^a-z ]/gi, '').trim();
if(options.text.trim()) {
if(options.text == this.previousText) {
Logger.log('INFO: Using previous response.');
return this.previousResult;
}
if(this.cache) {
var words = options.text.split(/ +/);
for(var i in words) {
Logger.log('INFO: checking cache: '+words[i]);
if(this.cache.incorrect[words[i]]) {
Logger.log('INFO: Using cached response.');
return [{"offset":1,"token":words[i],"type":"cacheHit","suggestions":[]}];
}
}
}
var url = this.BASE_URL;
var config = {
method : 'POST',
headers : {
'Ocp-Apim-Subscription-Key' : this.key,
'Content-Type' : 'application/x-www-form-urlencoded'
},
payload : 'Text='+encodeURIComponent(options.text),
muteHttpExceptions : true
};
if(options && options.mode) {
url += '?mode='+options.mode;
} else {
url += '?mode=proof';
}
if(this.timeOfLastCall) {
var now = Date.now();
if(now - this.timeOfLastCall < this.delay) {
Logger.log(Utilities.formatString('INFO: Sleeping for %s milliseconds',
this.delay - (now - this.timeOfLastCall)));
Utilities.sleep(this.delay - (now - this.timeOfLastCall));
}
}
var resp = UrlFetchApp.fetch(url, config);
this.timeOfLastCall = Date.now();
if(resp.getResponseCode() != 200) {
if(resp.getResponseCode() == 403) {
this.hitQuota = true;
}
throw JSON.parse(resp.getContentText()).message;
} else {
var jsonResp = JSON.parse(resp.getContentText());
this.previousText = options.text;
this.previousResult = jsonResp.flaggedTokens;
for(var i in jsonResp.flaggedTokens) {
this.cache.incorrect[jsonResp.flaggedTokens[i].token] = true;
}
return jsonResp.flaggedTokens;
}
} else {
return [];
}
};
// Returns true if there are spelling mistakes in the text toCheck
// toCheck : the phrase to spellcheck
// returns true if there are words misspelled, false otherwise.
this.hasSpellingIssues = function(toCheck) {
var issues = this.checkSpelling({ text : toCheck });
return (issues.length > 0);
};
// Loads the list of misspelled words from Google Drive.
// set config.enableCache to true to enable.
this.loadCache = function() {
var fileIter = DriveApp.getFilesByName(this.CACHE_FILE_NAME);
if(fileIter.hasNext()) {
this.cache = JSON.parse(fileIter.next().getBlob().getDataAsString());
} else {
this.cache = { incorrect : {} };
}
}
if(config.enableCache) {
this.loadCache();
}
// Called when you are finished with everything to store the data back to Google Drive
this.saveCache = function() {
var fileIter = DriveApp.getFilesByName(this.CACHE_FILE_NAME);
if(fileIter.hasNext()) {
fileIter.next().setContent(JSON.stringify(this.cache));
} else {
DriveApp.createFile(this.CACHE_FILE_NAME, JSON.stringify(this.cache));
}
}
}
Auction insights script
Problem
As Sun Tzu say, “thy your enemy.” Tracking competitors is quite necessary in Google Ads auction.
With this script by BrainLabs, you’ll be able to measure changes over time, filter the Google Ads report, and concentrate on your competitors.
Although there are many platforms focused on analyzing competitors for your PPC efforts, this script does the same job for free.
// ID: 49b262a518e68d401b6a69f62ddf5d05
/**
* Brainlabs Auctions Insights Report Generator
*
* This script will take data from an Auctions Insights reports and create reports
* and charts showing the changes over time for your your domain and selected
* competitors over time.
*
* Version: 3.0
* Google Apps Script maintained on brainlabsdigital.com
*/
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Information about the different columns of the Auctions Insights report
// (Will only be included if column names are given in English)
var subtitle = {};
subtitle['Impr. share'] = 'How often a participant received an impression, as a proportion of the auctions in which you were also competing.';
subtitle['Avg. position'] = 'The average position on the search results page for the participant’s ads when they received an impression.';
subtitle['Overlap rate'] = "How often another participant's ad received an impression when your ad also received an impression.";
subtitle['Position above rate'] = 'When you and another participant received an impression in the same auctions, % when participant’s ad was shown in a higher position.';
subtitle['Top of page rate'] = 'When a participant’s ads received impressions, how often it appeared at the top of the page above the search results.';
subtitle['Outranking share'] = "How often your ad ranked higher in the auction than another participant's ad, or your ad showed when theirs did not.";
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to keep the prefills in the setting sheet up-to-date
function onEdit() {
// Find the sheets that give settings and data
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
if (settingsSheet == null) {
return;
}
var totalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights');
var byDeviceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights By Device');
if (totalSheet != null) {
// Get the column names and copy to the settings sheet
var columnHeaders = getHeaders(totalSheet);
if (columnHeaders[0] != '') {
settingsSheet.getRange('ColumnNames').setValues([columnHeaders.concat(['', '', '', '', '', '']).slice(2, 8)]);
}
if (settingsSheet.getRange('competitorListAutoRefresh').getValue().toLowerCase() == 'yes') {
listCompetitors();
}
}
// Get the device names, and copy to the settings sheet
if (byDeviceSheet != null && byDeviceSheet.getLastRow() > 1) {
var columnHeaders = getHeaders(byDeviceSheet).map(function (a) {
return a.toLowerCase();
});
var deviceColumnName = settingsSheet.getRange('deviceColumnName').getValue().toLowerCase();
var deviceIndex = columnHeaders.indexOf(deviceColumnName);
if (deviceColumnName != '' && deviceIndex != -1) {
var deviceNames = [];
var deviceNamesInColumn = [];
var allDeviceNames = byDeviceSheet.getRange(3, deviceIndex + 1, 100, 1).getValues();
for (var d = 0; d < allDeviceNames.length; d++) {
if (allDeviceNames[d][0].toString() == '' || allDeviceNames[d][0].toString().toLowerCase() == deviceColumnName) {
continue;
}
if (deviceNames.indexOf(allDeviceNames[d][0]) == -1) {
deviceNames.push(allDeviceNames[d][0]);
deviceNamesInColumn.push([allDeviceNames[d][0]]);
}
Logger.log(allDeviceNames[d][0]);
if (deviceNamesInColumn.length >= 3) {
break;
}
}
Logger.log(deviceNamesInColumn);
settingsSheet.getRange('DeviceNames').setValues(deviceNamesInColumn.slice(0, 3));
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function takes the competitor names from the totals sheet,
// and lists them in the Settings sheet.
function listCompetitors() {
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
var totalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights');
if (settingsSheet == null || totalSheet == null) {
return;
}
// Get the column names and copy to the settings sheet
var columnHeaders = getHeaders(totalSheet).map(function (a) {
return a.toLowerCase();
});
var displayDomainColumnName = settingsSheet.getRange('displayDomainColumnName').getValue();
var domainIndex = columnHeaders.indexOf(displayDomainColumnName.toLowerCase());
if (displayDomainColumnName != '' && domainIndex != -1) {
var namesInColumn = getCompetitorsByMaxImprShare(settingsSheet, totalSheet, domainIndex);
settingsSheet.getRange('CompetitorNames').setValues(namesInColumn);
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function removes any existing report sheets
function deleteReports() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var nonReportSheetNames = ['Settings', 'Auction Insights', 'Auction Insights By Device', 'Performance Data'];
for (var i = 0; i < sheets.length; i++) {
if (nonReportSheetNames.indexOf(sheets[i].getName()) < 0) {
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheets[i]);
}
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function goes through the Settings sheet to find the reports to make,
// and makes them
function generateReports() {
// Find the sheets that give settings and data
var sheetNames = ['Settings', 'Auction Insights', 'Auction Insights By Device', 'Performance Data'];
var sheet = {};
for (var i = 0; i < sheetNames.length; i++) {
sheet[sheetNames[i]] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNames[i]);
if (sheet[sheetNames[i]] == null) {
Browser.msgBox('The ' + sheetNames[i] + ' sheet could not be found. Please check you have not deleted or renamed it.');
return;
}
}
var youName = sheet.Settings.getRange('youName').getValue();
var dateName = sheet.Settings.getRange('dateColumnName').getValue();
var domainName = sheet.Settings.getRange('displayDomainColumnName').getValue();
if (youName == '') {
Browser.msgBox('You name is blank. Please make sure you have entered the name that the report uses when giving your performance.');
return;
}
if (dateName == '') {
Browser.msgBox('Date column name is blank. Please make sure you have entered a name for the date column.');
return;
}
if (domainName == '') {
Browser.msgBox('Display URL Domain column name is blank. Please make sure you have entered a name for the Display URL Domain column.');
return;
}
// Get a list of competitors
var allCompetitorNames = sheet.Settings.getRange('CompetitorNames').getValues();
var competitorNameSelection = sheet.Settings.getRange('CompetitorNameSelection').getValues();
var competitors = [];
var orderedCompetitors = {};
for (var i = 0; i < allCompetitorNames.length; i++) {
if (competitorNameSelection[i][0] != '' && allCompetitorNames[i][0] != '') {
competitors.push(allCompetitorNames[i][0]);
if (!isNaN(competitorNameSelection[i][0])) {
orderedCompetitors[allCompetitorNames[i][0]] = competitorNameSelection[i][0];
}
}
}
var includeAllCompetitors = sheet.Settings.getRange('includeAllCompetitors').getValue().toLowerCase();
if (includeAllCompetitors == 'yes') {
competitors = getAllCompetitors(sheet['Auction Insights'], youName, domainName);
}
// Get list of stats
var statsToReport = [];
var statsInChart = [];
var statsToReportTable = sheet.Settings.getRange('StatsToReport').getValues();
for (var i = 0; i < statsToReportTable.length; i++) {
if (statsToReportTable[i][1].toLowerCase() == 'yes') {
statsToReport.push(statsToReportTable[i][0]);
}
if (statsToReportTable[i][2].toLowerCase() == 'yes' && statsInChart.length < 2) {
statsInChart.push(statsToReportTable[i][0]);
if (statsToReportTable[i][1].toLowerCase() != 'yes') {
statsToReport.push(statsToReportTable[i][0]);
}
}
}
if (statsToReport.length > 0) {
var statsReadable = sheet['Performance Data'].getLastColumn() > 0;
if (statsReadable) {
var statNames = [];
statNames.Clicks = sheet.Settings.getRange('clicksColumnName').getValue();
statNames.Impressions = sheet.Settings.getRange('impressionsColumnName').getValue();
statNames.Cost = sheet.Settings.getRange('costColumnName').getValue();
for (var statName in statNames) {
if (statNames[statName] == '') {
Browser.msgBox(statName + ' column name is blank. Please make sure you have entered a name for the ' + statName.toLowerCase() + ' column if you want stats to be reported.');
statsReadable = false;
break;
}
}
}
if (statsReadable) {
var statHeaders = getHeaders(sheet['Performance Data']).map(function (a) {
return a.toLowerCase();
});
var nonBlankHeaders = statHeaders.filter(function (a) {
return a != '';
});
if (nonBlankHeaders.length == 0) {
Browser.msgBox("No headers found in the 'Performance Data' sheet. Please make sure you have copied in your data if you want stats to be reported.");
statsReadable = false;
}
}
if (statsReadable) {
if (statHeaders.indexOf(statNames.Impressions.toLowerCase()) < 0
&& statHeaders.indexOf('impr.') >= 0) {
statNames.Impressions = 'impr.';
}
for (var statName in statNames) {
if (statHeaders.indexOf(statNames[statName].toLowerCase()) < 0) {
if (statName == 'Impressions' && getStatColumnIndex(sheet.Settings, statHeaders, 'impressionsColumnName') >= 0) {
continue;
} else if (statName == 'Clicks' && getStatColumnIndex(sheet.Settings, statHeaders, 'clicksColumnName') >= 0) {
continue;
}
Browser.msgBox('Could not find the ' + statName.toLowerCase() + " column '" + statNames[statName] + "'. Please check it is typed correctly if you want stats to be reported.");
statsReadable = false;
break;
}
}
}
if (!statsReadable) {
statsToReport = [];
statsInChart = [];
}
}
var deviceNames = sheet.Settings.getRange('DeviceNames').getValues();
var columnNames = sheet.Settings.getRange('ColumnNames').getValues()[0];
var reportsToBeMadeTable = sheet.Settings.getRange('ReportsToMake').getValues();
var reportCount = 0;
// Reports for 'Total'
var totalSheetChecked = false;
for (var j = 0; j < columnNames.length; j++) {
if (reportsToBeMadeTable[0][j].toLowerCase() == 'yes' && columnNames[j] != '') {
reportCount++;
if (!totalSheetChecked) {
var totalSheetFilledIn = checkSheetIsFilledIn(sheet.Settings, sheet['Auction Insights'], 'Auction Insights', dateName, domainName, youName);
if (totalSheetFilledIn) {
totalSheetChecked = true;
} else {
totalSheetChecked = null;
break;
}
}
makeReport(columnNames[j], 'Total', competitors, orderedCompetitors, statsToReport, statsInChart);
}
}
// Reports for each device
var deviceSheetChecked = false;
for (var i = 0; i < deviceNames.length; i++) {
for (var j = 0; j < columnNames.length; j++) {
if (reportsToBeMadeTable[i + 1][j].toLowerCase() == 'yes' && columnNames[j] != '') {
reportCount++;
if (!deviceSheetChecked) {
var deviceSheetFilledIn = checkSheetIsFilledIn(sheet.Settings, sheet['Auction Insights By Device'], 'Auction Insights By Device', dateName, domainName, youName);
if (deviceSheetFilledIn) {
deviceSheetChecked = true;
} else {
deviceSheetChecked = null;
break;
}
}
makeReport(columnNames[j], deviceNames[i][0], competitors, orderedCompetitors, statsToReport, statsInChart);
}
}
}
if (totalSheetChecked == null || deviceSheetChecked == null) {
return;
}
// 'Compare All Devices' reports
for (var j = 0; j < columnNames.length; j++) {
if (reportsToBeMadeTable[4][j].toLowerCase() == 'yes' && columnNames[j] != '') {
reportCount++;
if (!totalSheetChecked) {
var totalSheetFilledIn = checkSheetIsFilledIn(sheet.Settings, sheet['Auction Insights'], 'Auction Insights', dateName, domainName, youName);
if (totalSheetFilledIn) {
totalSheetChecked = true;
} else {
break;
}
}
if (!deviceSheetChecked) {
var deviceSheetFilledIn = checkSheetIsFilledIn(sheet.Settings, sheet['Auction Insights By Device'], 'Auction Insights By Device', dateName, domainName, youName);
if (deviceSheetFilledIn) {
deviceSheetChecked = true;
} else {
break;
}
}
makeAllDeviceReport(columnNames[j], statsToReport, statsInChart);
}
}
if (reportCount == 0) {
Browser.msgBox('No reports requested to be made.');
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to make reports comparing domains for a particular column header
// 'device' is a string indicating which device the report is for
// 'competitors' is an array of competitor names to include
// 'statsToReport' is an array of the stats to add to the data table
// 'statsInChart' is an array of the stats to add to the chart
function makeReport(columnHeader, device, competitors, orderedCompetitors, statsToReport, statsInChart) {
var displayColumnHeader = getDisplayName(columnHeader);
var displayDevice = getDisplayName(device);
// If the report's sheet exists, delete it
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(displayColumnHeader + ' - ' + displayDevice);
if (sheet != null) {
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);
}
// Create a new sheet for the report
var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(displayColumnHeader + ' - ' + displayDevice);
// Get the existing sheets for the settings and data
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
var performanceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Performance Data');
if (device == 'Total') {
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights');
} else {
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights By Device');
}
var youName = settingsSheet.getRange('youName').getValue();
var youNameLowerCase = youName.toLowerCase();
var data = getDataByDate(settingsSheet, dataSheet, columnHeader, device, competitors, youNameLowerCase, false);
var dates = data.dates;
var domains = data.data;
var domainNames = data.domainNames;
if (statsToReport.indexOf('Searches') != -1) {
var allColumnHeaders = settingsSheet.getRange('ColumnNames').getValues()[0];
var imprShareName = allColumnHeaders[0];
var imprShareData = getDataByDate(settingsSheet, dataSheet, imprShareName, device, [], youNameLowerCase, false).data;
} else {
var imprShareData = {};
}
var statData = getStatByDate(settingsSheet, performanceSheet, statsToReport, device);
// Get the total impressions, cost and clicks to calculate CPC, etc
dates.sort();
// Sorts the dates alphabetically - as they're in bigendian format, this means they are sorted oldest to newest
domainNames.sort(compareDomainNames);
// Sorts the domain names by their highest impression share, using the function below
function compareDomainNames(a, b) {
// user defined ordering has priority
var aHasOrder = orderedCompetitors[a] != undefined;
var bHasOrder = orderedCompetitors[b] != undefined;
if (aHasOrder && !bHasOrder) {
return -1;
} if (!aHasOrder && bHasOrder) {
return 1;
} if (aHasOrder && bHasOrder) {
return orderedCompetitors[a] - orderedCompetitors[b];
}
// otherwise use max impression share
var aIsString = typeof (domains[a]['Max Impr Share']) === 'string';
var bIsString = typeof (domains[b]['Max Impr Share']) === 'string';
if (aIsString && !bIsString) {
return 1;
} if (!aIsString && bIsString) {
return -1;
} if (!aIsString && !bIsString && domains[a]['Max Impr Share'] != domains[b]['Max Impr Share']) {
// If the max impression shares are different, the domain with the highest is put first
return domains[b]['Max Impr Share'] - domains[a]['Max Impr Share'];
}
// If both domains have the same max impression share, the one with data for the most dates is put first
return Object.keys(domains[b]).length - Object.keys(domains[a]).length;
}
var includeYou = false;
for (var i = 0; i < dates.length; i++) {
if (domains[youNameLowerCase] != undefined && domains[youNameLowerCase][dates[i]] != undefined && domains[youNameLowerCase][dates[i]] != '--') {
includeYou = true;
break;
}
}
domainNames.splice(domainNames.indexOf(youNameLowerCase), 1);
// Removes "You" from the array
if (includeYou) {
// If this graph is supposed to include 'You', then it's added to the start of the array
domainNames.unshift(youName);
}
// The first row of the report sheet is the column name
if (device == 'Total') {
sheet.getRange('A1').setValue(displayColumnHeader);
} else {
sheet.getRange('A1').setValue(displayColumnHeader + ' - ' + displayDevice);
}
sheet.getRange('A1').setFontWeight('bold');
// Check there is data
if (!includeYou && competitors.length == 0) {
sheet.getRange('A2').setValue('No competitors are selected, so there is no data to show.');
Browser.msgBox("No competitors are selected, so there is no data to show in the '" + displayColumnHeader + ' - ' + displayDevice + "' report.");
return;
}
if (domainNames.length == 0) {
sheet.getRange('A2').setValue('No data was found for this report.');
return;
}
// The second row of the report sheet is the headings
var outputHeaders = ['Date'];
for (var i = 0; i < statsToReport.length; i++) {
outputHeaders.push(getDisplayName(statsToReport[i]));
}
for (var d = 0; d < domainNames.length; d++) {
outputHeaders.push(getDisplayName(domainNames[d]));
}
sheet.getRange(2, 1, 1, outputHeaders.length).setValues([outputHeaders]);
sheet.getRange(2, 1, 1, outputHeaders.length).setFontWeight('bold');
// 'output' is a multi-dimensional array that will become the rest of the cells in the spreadsheet
var output = [];
// We loop though the dates to make their lines of output
// (the date, the CPC, then each domain's metric)
for (var i = 0; i < dates.length; i++) {
output[i] = [stringToDate(dates[i])];
for (var j = 0; j < statsToReport.length; j++) {
output[i].push(calculateStat(statsToReport[j], statData, dates[i], imprShareData[youNameLowerCase]));
}
for (var d = 0; d < domainNames.length; d++) {
if (domains[domainNames[d].toLowerCase()][dates[i]] === undefined || domains[domainNames[d].toLowerCase()][dates[i]] === '--') {
if (columnHeader.toLowerCase() == 'avg. position') {
output[i].push('');
} else {
output[i].push(0);
}
} else {
output[i].push(domains[domainNames[d].toLowerCase()][dates[i]]);
}
}
}
// Write the data to the sheet
sheet.getRange(3, 1, output.length, output[0].length).setValues(output);
// Format the tables
var dateFormat = settingsSheet.getRange('dateFormat').getValue();
var currencySymbol = settingsSheet.getRange('currencySymbol').getValue();
for (var i = 0; i < outputHeaders.length; i++) {
if (outputHeaders[i] == 'Date') {
sheet.getRange(3, i + 1, output.length).setNumberFormat(dateFormat);
} else if (outputHeaders[i] == 'CPC') {
sheet.getRange(3, i + 1, output.length).setNumberFormat(currencySymbol + '0.00');
} else if (outputHeaders[i] == 'Impressions' || outputHeaders[i] == 'Searches') {
sheet.getRange(3, i + 1, output.length).setNumberFormat('#,###,##0');
} else if (outputHeaders[i].substr(-3) == 'CTR') {
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.00%');
} else if (columnHeader == 'Avg. position' || sheet.getRange(4, i + 1).getValue() >= 1) {
// average position is not a percentage
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.0');
} else {
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.00%');
}
}
// Centralise the data
sheet.getRange(2, 1, output.length + 1, output[0].length).setHorizontalAlignment('center');
// Make the chart
// Get the width in pixels for the chart, so the chart is 11 columns wide
var width = 0;
for (var i = 1; i < 12; i++) {
width += sheet.getColumnWidth(i);
}
// Remove stat columns if there's no impressions, as that suggests there's
// a problem with the data (and the graph will be flat anyway)
if (statData.zeroImpressions) {
statsInChart = [];
}
// Creates the chart
var chartTitle = displayColumnHeader;
if (typeof subtitle[columnHeader] !== 'undefined') {
chartTitle = displayColumnHeader + ' - ' + subtitle[columnHeader];
}
var chartBuilder = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.setOption('chartArea', {
left: '10%',
top: '15%',
width: '80%',
height: '70%'
})
.setPosition(4 + output.length, 1, 0, 0)
.setOption('width', width)
.setOption('height', 500)
.setOption('title', chartTitle)
.setOption('legend', {
position: 'top'
});
var statFormat = {
CPC: 'currency',
CTR: 'percent',
Impressions: 'decimal',
Searches: 'decimal'
};
if (statsInChart.length == 0) {
chartBuilder.setOption('vAxes', {
// Adds titles to the axis.
0: {
title: displayColumnHeader
}
});
} else if (statsInChart.length == 1) {
chartBuilder.setOption('vAxes', {
// Adds titles to both axes.
0: {
title: displayColumnHeader
},
1: {
title: statsInChart[0],
format: statFormat[statsInChart[0]]
}
});
} else {
chartBuilder.setOption('vAxes', {
// Adds title to the first axis, blanks the others.
0: {
title: displayColumnHeader
},
1: {
format: statFormat[statsInChart[0]],
textPosition: 'in'
},
2: {
format: statFormat[statsInChart[1]],
textPosition: 'out'
},
3: {
textStyle: {
color: 'white'
}
}
});
}
var seriesOptions = [];
var statColours = ['#999999', '#a2c4c9']; // grey, grey-blue
var statDashStyles = [
[10, 5],
[3, 3]
]; // dashed, dotted
var regularColours = ['#3366cc', '#dc3912', '#ff9900', '#109618', '#994499', '#ea9999']; // blue, red, yellow, green, purple, pink
chartBuilder.addRange(sheet.getRange(2, 1, output.length + 1, 1)); // Date
for (var j = 0; j < statsToReport.length; j++) {
var statIndex = statsInChart.indexOf(statsToReport[j]);
if (statIndex != -1) {
chartBuilder.addRange(sheet.getRange(2, j + 2, output.length + 1, 1));
seriesOptions.push({
targetAxisIndex: statIndex + 1,
lineDashStyle: statDashStyles[statIndex],
color: statColours[statIndex]
});
}
}
if (domainNames.length < regularColours.length) {
var numberOfColumns = domainNames.length;
} else {
var numberOfColumns = regularColours.length;
}
if (includeYou) {
numberOfColumns++;
}
chartBuilder.addRange(sheet.getRange(2, statsToReport.length + 2, output.length + 1, numberOfColumns)); // You and Competitors
if (includeYou) {
// Format the 'You' line to be black and thicker
seriesOptions.push({
targetAxisIndex: 0,
color: '#000000',
lineWidth: 4
});
}
// Format the competitor lines
for (var i = 0; i < output[0].length - statsToReport.length - 2 && i < regularColours.length; i++) {
seriesOptions.push({
targetAxisIndex: 0,
color: regularColours[i]
});
}
chartBuilder.setOption('series', seriesOptions);
var chart = chartBuilder.build();
sheet.insertChart(chart);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to make reports comparing your performance on different devices for
// a particular column header
function makeAllDeviceReport(columnHeader, statsToReport, statsInChart) {
var displayColumnHeader = getDisplayName(columnHeader);
// If the report's sheet exists, delete it
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(displayColumnHeader + ' - All Devices');
if (sheet != null) {
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);
}
// Create a new sheet for the report
var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(displayColumnHeader + ' - All Devices');
// Find the sheets that give settings and data
var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
var totalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights');
var byDeviceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Auction Insights By Device');
var performanceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Performance Data');
var youName = settingsSheet.getRange('youName').getValue();
var youNameLowerCase = youName.toLowerCase();
var totalData = getDataByDate(settingsSheet, totalSheet, columnHeader, 'Total', [], youNameLowerCase, false);
var deviceData = getDataByDate(settingsSheet, byDeviceSheet, columnHeader, 'Total', [], youNameLowerCase, true);
var dates = totalData.dates;
for (var i = 0; i < deviceData.dates.length; i++) {
if (dates.indexOf(deviceData.dates[i]) == -1) {
dates.push(deviceData.dates[i]);
}
}
// Get the other stats from the performance sheet
var totalStats = getStatByDate(settingsSheet, performanceSheet, statsToReport, 'Total');
var deviceStats = {};
for (var d = 0; d < deviceData.domainNames.length && d < 6; d++) {
deviceStats[deviceData.domainNames[d]] = getStatByDate(settingsSheet, performanceSheet, statsToReport, deviceData.domainNames[d]);
}
if (statsToReport.indexOf('Searches') != -1) {
var totalImprShare = getDataByDate(settingsSheet, totalSheet, 'Impr. share', 'Total', [], youNameLowerCase, false).data;
var deviceImprShare = getDataByDate(settingsSheet, byDeviceSheet, 'Impr. share', 'Total', [], youNameLowerCase, true).data;
} else {
var deviceImprShare = {};
var totalImprShare = {};
}
dates.sort();
// Sorts the dates alphabetically - as they're in bigendian format, this means they are sorted oldest to newest
var deviceDisplayNames = [];
for (var d = 0; d < deviceData.domainNames.length; d++) {
deviceDisplayNames[d] = getDisplayName(deviceData.domainNames[d]);
}
var statDisplayNames = [];
for (var i = 0; i < statsToReport.length; i++) {
statDisplayNames[i] = getDisplayName(statsToReport[i]);
}
// The first row of the report sheet is the report name
sheet.getRange('A1').setValue(displayColumnHeader + ' - All Devices');
sheet.getRange('A1').setFontWeight('bold');
// The second row of the report sheet is the headings
var outputHeaders = ['Date'];
for (var i = 0; i < statDisplayNames.length; i++) {
outputHeaders.push('Total ' + statDisplayNames[i]);
for (var d = 0; d < deviceDisplayNames.length; d++) {
outputHeaders.push(deviceDisplayNames[d] + ' ' + statDisplayNames[i]);
}
}
outputHeaders.push('Total ' + columnHeader);
for (var d = 0; d < deviceDisplayNames.length; d++) {
outputHeaders.push(deviceDisplayNames[d] + ' ' + displayColumnHeader);
}
sheet.getRange(2, 1, 1, outputHeaders.length).setValues([outputHeaders]);
sheet.getRange(2, 1, 1, outputHeaders.length).setFontWeight('bold');
// 'output' is a multi-dimensional array that will become the rest of the cells in the spreadsheet
var output = [];
// We loop though the dates to make their lines of output
for (var i = 0; i < dates.length; i++) {
output[i] = [stringToDate(dates[i])];
for (var j = 0; j < statsToReport.length; j++) {
output[i].push(calculateStat(statsToReport[j], totalStats, dates[i], totalImprShare[youNameLowerCase]));
for (var d = 0; d < deviceData.domainNames.length; d++) {
output[i].push(calculateStat(statsToReport[j], deviceStats[deviceData.domainNames[d]], dates[i], deviceImprShare[deviceData.domainNames[d]]));
}
}
if (totalData.data[youNameLowerCase][dates[i]] === undefined || totalData.data[youNameLowerCase][dates[i]] == '--') {
if (columnHeader.toLowerCase() == 'avg. position') {
output[i].push('');
} else {
output[i].push(0);
}
} else {
output[i].push(totalData.data[youNameLowerCase][dates[i]]);
}
for (var d = 0; d < deviceData.domainNames.length; d++) {
if (deviceData.data[deviceData.domainNames[d]][dates[i]] === undefined || deviceData.data[deviceData.domainNames[d]][dates[i]] === '--') {
if (columnHeader.toLowerCase() == 'avg. position') {
output[i].push('');
} else {
output[i].push(0);
}
} else {
output[i].push(deviceData.data[deviceData.domainNames[d]][dates[i]]);
}
}
}
// Write the data to the sheet
sheet.getRange(3, 1, output.length, output[0].length).setValues(output);
// Format the tables
var dateFormat = settingsSheet.getRange('dateFormat').getValue();
var currencySymbol = settingsSheet.getRange('currencySymbol').getValue();
for (var i = 0; i < outputHeaders.length; i++) {
if (outputHeaders[i] == 'Date') {
sheet.getRange(3, i + 1, output.length).setNumberFormat(dateFormat);
} else if (outputHeaders[i].substr(-3) == 'CPC') {
sheet.getRange(3, i + 1, output.length).setNumberFormat(currencySymbol + '0.00');
} else if (outputHeaders[i].substr(-11) == 'Impressions' || outputHeaders[i].substr(-11) == 'Impr.' || outputHeaders[i].substr(-8) == 'Searches') {
sheet.getRange(3, i + 1, output.length).setNumberFormat('#,###,##0');
} else if (outputHeaders[i].substr(-3) == 'CTR') {
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.00%');
} else if (columnHeader == 'Avg. position' || sheet.getRange(4, i + 1).getValue() >= 1) {
// must be average position
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.0');
} else {
sheet.getRange(3, i + 1, output.length).setNumberFormat('0.00%');
}
}
// Centralise the data
sheet.getRange(3, 1, output.length, output[0].length).setHorizontalAlignment('center');
// Make the chart
// Get the width in pixels for the chart, so the chart is 11 columns wide
var width = 0;
for (var i = 1; i < 12; i++) {
width += sheet.getColumnWidth(i);
}
// Remove stat columns if there's no impressions, as that suggests there's
// a problem with the data (and the graph will be flat anyway)
if (totalStats.zeroImpressions) {
statsInChart = [];
}
// Creates the chart
var chartTitle = displayColumnHeader;
if (typeof subtitle[columnHeader] !== 'undefined') {
chartTitle = chartTitle + ' - ' + subtitle[columnHeader];
}
var chartBuilder = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.setOption('chartArea', {
left: '10%',
top: '15%',
width: '80%',
height: '70%'
})
.setPosition(4 + output.length, 1, 0, 0)
.setOption('width', width)
.setOption('height', 500)
.setOption('title', chartTitle)
.setOption('legend', {
position: 'top'
});
var statFormat = {
CPC: 'currency',
CTR: 'percentage',
Impressions: 'decimal',
Searches: 'decimal'
};
if (statsInChart.length == 0) {
chartBuilder.setOption('vAxes', {
// Adds titles to the axis.
0: {
title: displayColumnHeader
}
});
} else if (statsInChart.length == 1) {
chartBuilder.setOption('vAxes', {
// Adds titles to both axes.
0: {
title: displayColumnHeader
},
1: {
title: statDisplayNames[0],
format: statFormat[statsInChart[0]]
}
});
} else {
chartBuilder.setOption('vAxes', {
// Adds title to the first axis, blanks the others.
0: {
title: displayColumnHeader
},
1: {
textStyle: {
color: 'white'
}
},
2: {
title: statDisplayNames[1],
format: statFormat[statsInChart[1]]
},
3: {
textStyle: {
color: 'white'
}
}
});
}
var seriesOptions = [];
var colours = [];
colours.push(['#3366cc', '#dc3912', '#ff9900', '#109618']); // blue, red, yellow, green
colours.push(['#9fc5e8', '#ea9999', '#f9cb9c', '#b6d7a8']);
var lineDashStyles = [];
lineDashStyles.push([10, 5]);
lineDashStyles.push([3, 3]);
chartBuilder.addRange(sheet.getRange(2, 1, output.length + 1, 1)); // Date
for (var j = 0; j < statsToReport.length; j++) {
var statIndex = statsInChart.indexOf(statsToReport[j]);
if (statIndex != -1) {
chartBuilder.addRange(sheet.getRange(2, (4 * j) + 2, output.length + 1, 4));
for (var x = 0; x < 4; x++) {
seriesOptions.push({
targetAxisIndex: statIndex + 1,
lineDashStyle: lineDashStyles[statIndex],
color: colours[statIndex][x]
});
}
}
}
chartBuilder.addRange(sheet.getRange(2, (4 * statsToReport.length) + 2, output.length + 1, 4));
for (var x = 0; x < 4; x++) {
seriesOptions.push({
targetAxisIndex: 0,
color: colours[0][x]
});
}
chartBuilder.setOption('series', seriesOptions);
var chart = chartBuilder.build();
sheet.insertChart(chart);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function calculates a stat, or returns 0 if there is no data or that stat would
// involve dividing by zero
function calculateStat(stat, statData, date, imprShareData) {
switch (stat) {
case 'CPC':
if (statData.cost[date] == undefined || statData.clicks[date] == undefined || statData.clicks[date] == 0) {
return 0;
}
return statData.cost[date] / statData.clicks[date];
break;
case 'CTR':
if (statData.impr[date] == undefined || statData.clicks[date] == undefined || statData.impr[date] == 0) {
return 0;
}
return statData.clicks[date] / statData.impr[date];
break;
case 'Impressions':
if (statData.impr[date] == undefined) {
return 0;
}
return statData.impr[date];
break;
case 'Searches':
if (statData.impr[date] == undefined || imprShareData[date] == undefined || imprShareData[date] == 0) {
return 0;
}
return statData.impr[date] / imprShareData[date];
break;
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to take the data from the data sheet organised by date and by domain
// name or (if recordDeviceAsDomain is true) by device
function getDataByDate(settingsSheet, dataSheet, columnHeader, device, competitors, youName, recordDeviceAsDomain) {
// Dates are stored as bigendian date strings, then converted back to dates at the end
var bigendianDate = 'yyyy-MM-dd';
// The timezone is used to convert them back
var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
var domains = {};
var dates = [];
var domainNames = [];
// Headers are made lowercase so that the column headers can be case insensitive
var headers = getHeaders(dataSheet).map(function (a) {
return a.toLowerCase();
});
var dateIndex = headers.indexOf(settingsSheet.getRange('dateColumnName').getValue().toLowerCase());
var domainIndex = headers.indexOf(settingsSheet.getRange('displayDomainColumnName').getValue().toLowerCase());
var deviceIndex = headers.indexOf(settingsSheet.getRange('deviceColumnName').getValue().toLowerCase());
var under10Percent = settingsSheet.getRange('under10Percent').getValue();
if (settingsSheet.getRange('deviceColumnName') == '' || deviceIndex == -1) {
// If there is no device column, the impression share column is 2
var imprShareIndex = 2;
} else {
// If there *is* a device column, then the impr share column is bumped up to 3
var imprShareIndex = 3;
}
if (columnHeader == 'Impr. share') {
var columnIndex = imprShareIndex;
} else {
var columnIndex = headers.indexOf(columnHeader.toLowerCase());
// The index of the required stat
}
var dataTable = dataSheet.getRange(2, 1, dataSheet.getLastRow(), dataSheet.getLastColumn()).getValues();
if (dataTable[0][0].toString().toLowerCase() == headers[0]) {
// First row of data is the headers and can be removed
dataTable.shift();
}
// First we record the stats for each domain, by month
// and record each domain's highest impression share
for (var i = 0; i < dataTable.length; i++) {
// auditInsights is a multi-dimensional array containing the values of the auditInsights cells.
// So auditInsights[i] is a row on the Auction Insights report
// The loop starts at 2 as auditInsights[0] is the title and auditInsights[1] is the headers.
var date = dataTable[i][dateIndex];
if (!date) {
// If the date field is blank, there isn't data on this row
continue;
}
if (deviceIndex != -1 && device != 'Total' && dataTable[i][deviceIndex] != device) {
continue;
}
var domainName = dataTable[i][domainIndex].toLowerCase();
if (domainName != youName && competitors.indexOf(domainName) == -1) {
continue;
}
if (recordDeviceAsDomain) {
domainName = dataTable[i][deviceIndex];
}
if (typeof date !== 'string') {
// The date is converted into a string
date = Utilities.formatDate(date, timezone, bigendianDate);
}
if (Utilities.formatDate(stringToDate(date), 'UTC', bigendianDate) == '1970-01-01') {
// This means it isn't a proper date, so the row is skipped
continue;
}
if (dates.indexOf(date) < 0) {
// If the current row's date isn't in the dates array, it's added
dates.push(date);
}
var imprShare = dataTable[i][imprShareIndex]; // the impression share
if (imprShare == '< 10%') {
// If the impression share is "< 10%" (a string) it is changed to the value given
// in the Settings sheet, so it can be displayed in the graph.
imprShare = under10Percent;
}
if (domains[domainName] == undefined) {
// If the current row's domain name isn't in the domainNames array, it is added,
// and an entry for it is entered into the domains object.
domainNames.push(domainName);
domains[domainName] = {};
domains[domainName]['Max Impr Share'] = imprShare;
}
// If the stat is impression share, we recorded it
if (columnIndex == imprShareIndex) {
domains[domainName][date] = imprShare;
} else {
// Otherwise the value of the row with the right column header is recorded
domains[domainName][date] = dataTable[i][columnIndex];
}
if (typeof (domains[domainName]['Max Impr Share']) === 'string' || imprShare > domains[domainName]['Max Impr Share']) {
// If the current imprShare is bigger than the last recorded max impr share,
// the current one is recorded as being the max
domains[domainName]['Max Impr Share'] = imprShare;
}
} // end of for loop
return {
data: domains,
domainNames: domainNames,
dates: dates
};
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function takes the cost, clicks and impression data and organises it by date
// Can be filtered by device
function getStatByDate(settingsSheet, performanceSheet, statsToReport, device) {
var costTotals = [];
var clicksTotals = [];
var imprTotals = [];
var zeroImpressions = true;
// If there are data in the performance sheet, and extra metrics are wanted
if (statsToReport.length > 0) {
var headers = getHeaders(performanceSheet).map(function (a) {
return a.toLowerCase();
});
var deviceIndex = headers.indexOf(settingsSheet.getRange('deviceColumnName').getValue().toLowerCase());
if (deviceIndex > -1 || device == 'Total') {
var dateIndex = getStatColumnIndex(settingsSheet, headers, 'dateColumnName');
var costIndex = getStatColumnIndex(settingsSheet, headers, 'costColumnName');
var clicksIndex = getStatColumnIndex(settingsSheet, headers, 'clicksColumnName');
var imprIndex = getStatColumnIndex(settingsSheet, headers, 'impressionsColumnName');
// Dates are stored as bigendian date strings, then converted back to dates at the end
var bigendianDate = 'yyyy-MM-dd';
// The timezone is used to convert them back
var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
var performance = performanceSheet.getRange(2, 1, performanceSheet.getLastRow(), performanceSheet.getLastColumn()).getValues();
if (performance[0][0].toString().toLowerCase() == headers[0]) {
// First row of data is the headers and can be removed
performance.shift();
}
for (var i = 0; i < performance.length; i++) {
var date = performance[i][dateIndex];
Logger.log(date + ' - ' + performance[i][imprIndex] + ' - ' + performance[i][deviceIndex]);
if (!date) {
// If there's no date there's no data on this row, or (in reports
// from the new interface) this is the start of the totals rows.
if (i < 3) {
continue;
} else {
break;
}
}
if (device != 'Total' && performance[i][deviceIndex] != device) {
continue;
}
if (typeof date !== 'string') {
// If the date isn't a string, convert it into one
date = Utilities.formatDate(date, timezone, bigendianDate);
}
if (costTotals[date] == undefined) {
costTotals[date] = performance[i][costIndex];
clicksTotals[date] = performance[i][clicksIndex];
imprTotals[date] = performance[i][imprIndex];
} else {
costTotals[date] += performance[i][costIndex];
clicksTotals[date] += performance[i][clicksIndex];
imprTotals[date] += performance[i][imprIndex];
}
if (zeroImpressions && performance[i][imprIndex] > 0) {
zeroImpressions = false;
}
} // end of for loop
}
}
return {
cost: costTotals,
clicks: clicksTotals,
impr: imprTotals,
zeroImpressions: zeroImpressions
};
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function finds the index of stat columns in the Performance sheet. It tries
// alternative (English) names for impressions and clicks if the given name is not found.
// NB: If 'Interactions' is found instead of 'Clicks', this may include Engagements
// from shopping showcase ads as well as clicks
function getStatColumnIndex(settingsSheet, headers, columnRangeName) {
var possibleNames = [settingsSheet.getRange(columnRangeName).getValue().toLowerCase()];
switch (columnRangeName) {
case 'clicksColumnName':
possibleNames.push('interactions'); // Used when different interaction types are available
break;
case 'impressionsColumnName':
possibleNames.push('impr.'); // New interface
possibleNames.push('impressions'); // Old interface
break;
default:
break;
}
for (var i = 0; i < possibleNames.length; i++) {
var index = headers.indexOf(possibleNames[i]);
if (index >= 0) {
return index;
}
}
return -1;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function lists the top competitors in order of thier maximum impression share.
function getCompetitorsByMaxImprShare(settingsSheet, dataSheet, domainIndex) {
var competitorImprShare = {};
var competitorAppearances = {};
var youName = settingsSheet.getRange('youName').getValue().toLowerCase();
var columnHeaders = getHeaders(dataSheet).map(function (a) {
return a.toLowerCase();
});
var imprShareIndex = 2;
var dataTable = dataSheet.getRange(2, 1, dataSheet.getLastRow(), 9).getValues();
for (var i = 0; i < dataTable.length; i++) {
var cellValue = dataTable[i][0].toString().toLowerCase();
if (cellValue == columnHeaders[0] || cellValue == '') {
continue;
}
var competitor = dataTable[i][domainIndex].toLowerCase();
if (competitor == youName) {
continue;
}
var imprShare = dataTable[i][imprShareIndex];
if (imprShare == '< 10%') {
imprShare = 0.05;
}
if (competitorImprShare[competitor] == undefined) {
competitorImprShare[competitor] = imprShare;
competitorAppearances[competitor] = 0;
} else if (competitorImprShare[competitor] < imprShare) {
competitorImprShare[competitor] = imprShare;
}
competitorAppearances[competitor]++;
}
var competitorNames = Object.keys(competitorImprShare);
competitorNames.sort(function (a, b) {
if (competitorImprShare[a] != competitorImprShare[b]) {
return competitorImprShare[b] - competitorImprShare[a];
}
return competitorAppearances[b] - competitorAppearances[a];
});
var numberOfCompetitorsRequired = settingsSheet.getRange('CompetitorNames').getValues().length;
for (var i = competitorNames.length; i < numberOfCompetitorsRequired; i++) {
competitorNames.push(['']); // pads out the array
}
for (var i = 0; i < competitorNames.length; i++) {
competitorNames[i] = [competitorNames[i]];
}
return competitorNames.slice(0, numberOfCompetitorsRequired);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function lists all competitors
function getAllCompetitors(dataSheet, youName, domainName) {
var competitorsKeyed = {};
var columnHeaders = getHeaders(dataSheet).map(function (a) {
return a.toLowerCase();
});
var domainIndex = columnHeaders.indexOf(domainName.toLowerCase());
Logger.log(domainName);
Logger.log(columnHeaders);
Logger.log(2 + ', ' + domainIndex + 1 + ', ' + dataSheet.getLastRow() + ', ' + 1);
var dataTable = dataSheet.getRange(2, domainIndex + 1, dataSheet.getLastRow(), 1).getValues();
youName = youName.toLowerCase();
for (var i = 0; i < dataTable.length; i++) {
var competitor = dataTable[i][0].toString().toLowerCase();
if (competitor == columnHeaders[domainIndex] || competitor == '' || competitor == youName) {
continue;
}
competitorsKeyed[competitor] = true;
}
return Object.keys(competitorsKeyed);
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function converts date-strings back into dates
function stringToDate(string) {
var dateBits = string.split('-');
var date = new Date(dateBits[0], parseInt(dateBits[1], 10) - 1, parseInt(dateBits[2], 10), 1, 1, 1, 1);
return date;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function removes leading and trailing spaces, and line breaks
// (because the Computer device name tends to include a line break)
function getDisplayName(rawName) {
var displayName = rawName.trim();
displayName = displayName.replace(/(\n|\r)/g, '');
return displayName;
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Find the headers from the copied reports
// (which could be the first or second row depending how the data has been pasted in)
function getHeaders(sheet) {
if (sheet.getLastColumn() == 0) {
// no data in sheet yet
return [''];
}
var numberOfRowsToCheck = 3;
var topOfPageData = sheet.getRange(1, 1, numberOfRowsToCheck, sheet.getLastColumn()).getValues();
if (topOfPageData[1][0] == '') {
// no data in sheet yet
return [''];
}
for (var i = 0; i < topOfPageData.length; i++) {
if (topOfPageData[i][1] != '') {
// if this cell isn't blank, it should be a header
Logger.log('i is ' + i + ', cell 1 is ' + topOfPageData[i][1]);
return topOfPageData[i];
}
}
return [''];
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function checks that a data sheet is filled in and has the required headings
function checkSheetIsFilledIn(settingsSheet, dataSheet, sheetName, dateName, domainName, youName) {
var headers = getHeaders(dataSheet).map(function (a) {
return a.toLowerCase();
});
// Check the required column headers are not blank
var nonBlankHeaders = headers.filter(function (a) {
return a != '';
});
if (nonBlankHeaders.length == 0) {
Browser.msgBox("No headers found in the '" + sheetName + "' sheet. Please make sure you have copied in your data.");
return false;
}
if (sheetName == 'Auction Insights By Device') {
var deviceColumnName = settingsSheet.getRange('deviceColumnName').getValue();
if (deviceColumnName == '') {
Browser.msgBox('Device column name is blank. Please make sure you have entered a name for the device column.');
return false;
}
var deviceIndex = headers.indexOf(deviceColumnName.toLowerCase());
if (deviceIndex == -1) {
Browser.msgBox("Could not find the Device column '" + deviceColumnName + "' in the '" + sheetName + "' sheet. Please check it is typed correctly.");
return false;
}
}
var dateIndex = headers.indexOf(dateName.toLowerCase());
var domainIndex = headers.indexOf(domainName.toLowerCase());
if (dateIndex == -1) {
Browser.msgBox("Could not find the date column '" + dateName + "' in the '" + sheetName + "' sheet. Please check it is typed correctly.");
return false;
}
if (domainIndex == -1) {
Browser.msgBox("Could not find the Display URL Domain column '" + domainName + "' in the '" + sheetName + "' sheet. Please check it is typed correctly.");
return false;
}
// Check there are no dates which read "########"
var allDates = dataSheet.getRange(3, dateIndex + 1, dataSheet.getLastRow(), 1).getValues();
var badDates = allDates.filter(function (a) {
return typeof a[0] === 'string' && a[0].substr(0, 1) == '#';
});
if (badDates.length > 0) {
Browser.msgBox(badDates.length + " dates in the '" + sheetName + "' sheet contain #s. Please check they were copied correctly.");
return false;
}
// Check there is data for the given You name
var allDomains = dataSheet.getRange(3, domainIndex + 1, dataSheet.getLastRow(), 1).getValues();
var youNameLowerCase = youName.toLowerCase();
var badDomains = allDomains.filter(function (a) {
return a[0].toLowerCase() == youNameLowerCase;
});
if (badDomains.length == 0) {
Browser.msgBox("No rows found in the '" + sheetName + "' sheet with the domain '" + youName + "'. Please check you have entered the You name correctly.");
return false;
}
return true;
}
Keyword Scripts
Exclude queries with high CPA
Problem
Overpriced search terms can hurt your overall Ads budget that might be more useful when optimized and spent properly.
With this script developed by Tibbe van Asten, you’ll be able to ensure that overpriced search queries are automatically excluded from your campaigns.
The script below works with optimal CPA labels defined by you for your different campaigns. By applying the label to campaigns, the script finds too expensive search terms and lists for you to take action.
// Copyright 2019. Increase BV. All Rights Reserved.
//
// Created By: Tibbe van Asten
// for Increase B.V.
//
// Last update: 03-01-2019
//
// ABOUT THE SCRIPT
// With this script you can exclude search queries with
// a high CPA. Each search queries must have at least
// one conversion to be excluded. You can set the
// threshold multiplier yourself.
//
////////////////////////////////////////////////////////////////////
var config = {
LOG : false,
// Search queries will only be excluded when the CPA is at least [target CPA] * THRESHOLD_MULTIPLIER.
// For example: When your target CPA is €5 and you set the threshold multiplier to 1.5,
// the search query will only be excluded when the CPA is over €5 * 1.5 = €7.5
THRESHOLD_MULTIPLIER : 2
}
////////////////////////////////////////////////////////////////////
function main() {
var campaignIterator = AdsApp
.campaigns()
.withCondition("Status = ENABLED")
.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
findQueries(campaign);
}
var shoppingCampaignIterator = AdsApp
.shoppingCampaigns()
.withCondition("Status = ENABLED")
.get();
while (shoppingCampaignIterator.hasNext()) {
var campaign = shoppingCampaignIterator.next();
findQueries(campaign);
}
Logger.log("Thanks for using this custom script. Visit https://increase.nl for more information.");
} // function main()
////////////////////////////////////////////////////////////////////
function findQueries(campaign) {
var labelIterator = campaign
.labels()
.withCondition("Name CONTAINS 'CPA'")
.withCondition("Name DOES_NOT_CONTAIN '_'")
.get();
while (labelIterator.hasNext()) {
var label = labelIterator.next();
var targetCpa = label.getName().split(" ")[1];
var THRESHOLD_CPA = (Math.round((targetCpa * config.THRESHOLD_MULTIPLIER) * 100) / 100) * 1000000;
var report = AdsApp.report(
"SELECT Query, Conversions, CostPerConversion, KeywordTextMatchingQuery, AdGroupId, AdGroupName, KeywordId, Cost " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE CampaignStatus = ENABLED AND CampaignId = " + campaign.getId() +
" AND AdGroupStatus = ENABLED" +
" AND QueryTargetingStatus = NONE" +
" AND Cost > " + THRESHOLD_CPA
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
if(row["Conversions"] == 0 && row["Cost"] > (targetCpa * config.THRESHOLD_MULTIPLIER) || row["Conversions"] > 0.99 && row["CostPerConversion"] > (targetCpa * config.THRESHOLD_MULTIPLIER)) {
Logger.log("-----");
Logger.log(campaign.getName());
Logger.log("Target CPA: " + targetCpa);
Logger.log("-----");
var query = "";
if (row["Query"].split(" ").length < 10) {
query = "[" + row["Query"] + "]";
} else {
for (var i = 0; (i < row["Query"].split(" ").length) && (i < 10); i++){
query += row["Query"].split(" ")[i] + " ";
}
query = '"' + query.replace(/\s+$/,'') + '"';
}
if(config.LOG === true){
Logger.log("Query: " + row["Query"]);
Logger.log("Clean Query: " + query);
Logger.log("Keyword: " + row["KeywordTextMatchingQuery"]);
Logger.log("KeywordId: " + row["KeywordId"]);
}
if (row["Query"] == row["KeywordTextMatchingQuery"]) {
var ids = [];
var adgroupId = row["AdGroupId"];
var keywordId = row["KeywordId"];
ids.push([adgroupId, keywordId]);
var keywordIterator = AdsApp
.keywords()
.withIds(ids)
.withCondition("Status = ENABLED")
.get();
while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
keyword.pause();
Logger.log("Conversions: " + row["Conversions"]);
Logger.log("Cost: " + row["Cost"]);
Logger.log("CostPerConversion: " + row["CostPerConversion"]);
Logger.log("Keyword paused: " + row["KeywordTextMatchingQuery"]);
Logger.log(" ");
}
}
else{
var ids = [row["AdGroupId"]];
var adGroupIterator = AdsApp
.adGroups()
.withIds(ids)
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
adGroup.createNegativeKeyword(query);
Logger.log("Conversions: " + row["Conversions"]);
Logger.log("Cost: " + row["Cost"]);
Logger.log("CostPerConversion: " + row["CostPerConversion"]);
Logger.log("Query excluded: " + query + " in " + row["AdGroupName"]);
Logger.log(" ");
}
}
} // row selector
} // row iterator
} // label iterator
} // function findQueries(campaign)
Keyword performance script
Problem
Determining which keywords in your ads contribute to the overall performance and which keywords do not contribute at all can become a time-consuming task.
With this script by Team Impression, you’ll have an overview as to exactly which keywords are contributing to the most of your success and which ones are consuming your Ads budget.
The script will present you logs as highlights; distribution of cost, conversions, and revenue. You’ll be able to see a variety of metrics for each keywords including CTR, Avg. CPC, conversion rate, clicks, impressions, and cost per conversion. Plus, you’ll get a cumulative percentage stat for which keyword is responsible for how much of the overall success/failure.
* [KPR] Keyword Performance Report
* Author: Nathan Ifill (@nathanifill), Impression
*
* This script gives you the distribution of cost, conversions and revenue
* (if you are pulling conversion value into your account) amongst your keywords.
*
* v1.1
*
* Any suggestions? Email nathan.ifill@impression.co.uk
*
* Change History:
* v1.1
* - NI added option to select or exclude campaigns by name
*
*******************************************************************************/
// Want your report emailed to you every month? Enter your email address here.
// e.g. var yourEmailAddress = "james.jameson@impression.co.uk";
var yourEmailAddress = "";
var campaignNameContains = "";
// Use this if you only want to look at some campaigns such as campaigns with
// names containing 'Brand' or 'Remarketing'. Leave it as "" if you don't need
// it.
// e.g. var campaignNameContains = "Brand";
var campaignNameDoesNotContain = "";
// Use this if you want to exclude some campaigns such as campaigns with names
// containing 'Brand' or 'Remarketing'. Leave it as "" if you don't need it.
// e.g. var campaignNameDoesNotContain = "Remarketing";
/*******************************************************************************
//********* ONLY CERTIFIED NERDS SHOULD CHANGE THINGS BELOW THIS LINE **********
//*****************************************************************************/
var fullCleanLog = ""; // initialise fullCleanLog
var whereStatements = "";
if (campaignNameDoesNotContain != "") {
whereStatements += " AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '"
+ campaignNameDoesNotContain + "' ";
}
var query = "SELECT Criteria, AdGroupName, CampaignName, KeywordMatchType,"
+ " Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionValue,"
+ " ConversionRate FROM KEYWORDS_PERFORMANCE_REPORT WHERE Cost > 0"
+ " AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "'"
+ whereStatements
+ " DURING LAST_MONTH"
var report = AdsApp.report(query);
function main() {
var rows = report.rows();
var keywordSubtotal = 0;
var costSubtotal = 0;
var convSubtotal = 0;
var revSubtotal = 0;
var keywordArray = [];
var costData = [];
var convData = [];
var revData = [];
var skipRev = false; // flag to say whether to add revenue data or not
Logger.log("Keyword Performance Report - www.impression.co.uk");
Logger.log("-------------------------------------------------");
cleanLog("");
while (rows.hasNext()) {
var row = rows.next();
var criteria = row["Criteria"];
var adGroupName = row["AdGroupName"];
var campaignName = row["CampaignName"];
var keywordMatchType = row["KeywordMatchType"];
var impressions = +row["Impressions"];
var clicks = +row["Clicks"];
var ctr = row["Ctr"];
var averageCpc = +row["AverageCpc"];
var cost = +row["Cost"];
var conversions = +row["Conversions"];
var conversionValue = +row["ConversionValue"];
var conversionRate = row["ConversionRate"];
// Sets conversion value to 0 if NaN
if (isNaN(conversionValue)) {
conversionValue = 0;
}
// Sets ROAS if cost > 0
if (cost > 0) {
var roas = conversionValue / cost;
} else {
var roas = 0;
}
if (conversions > 0) {
var costPerConversion = (cost / conversions).toFixed(2);
} else {
var costPerConversion = 0;
}
keywordArray.push({
"criteria": criteria,
"ad group name": adGroupName,
"campaign name": campaignName,
"keyword match type": keywordMatchType,
"impressions": impressions,
"clicks": clicks,
"ctr": ctr,
"average cpc": averageCpc,
"cost": cost,
"conversions": conversions,
"cost per conversion": costPerConversion,
"conversion value": conversionValue,
"conversion rate": conversionRate,
"roas": roas
});
keywordSubtotal++;
costSubtotal += cost;
convSubtotal += conversions;
revSubtotal += conversionValue;
}
if (revSubtotal == 0) {
skipRev = true;
}
var costArray = keywordArray.sort(function (a,b) {
if ((b["cost"] - a["cost"]) < 0) {
return -1;
} else if ((b["cost"] - a["cost"]) > 0) {
return 1;
} else {
return 0;
}
});
var cumulativeCost = 0;
var maxCostDiff = 0;
var keywordPercentage = 0;
var costPercentage = 0;
var convPercentage = 0;
var revPercentage = 0;
costArray.forEach(function(item, index) {
cumulativeCost += item.cost;
item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
item["cost percentage"] = (100 * (item["cost"] / costSubtotal)).toFixed(2);
item["cum cost percentage"] = (100 * (cumulativeCost / costSubtotal)).toFixed(2);
item["cost diff"] = (item["cum cost percentage"] - item["keyword percentage"]).toFixed(2);
// sets maxCostDiff to item["cost diff"] if it's greater
if (+item["cost diff"] > +maxCostDiff) {
maxCostDiff = item["cost diff"];
keywordPercentage = item["keyword percentage"];
costPercentage = item["cum cost percentage"];
};
costData.push([item["criteria"],
item["ad group name"],
item["campaign name"],
item["keyword match type"],
item["cost percentage"] / 100,
item["cum cost percentage"] / 100,
item["impressions"],
item["clicks"],
item["ctr"],
item["average cpc"],
item["cost"],
item["conversions"],
item["cost per conversion"],
item["conversion value"],
item["conversion rate"],
item["roas"]]);
});
cleanLog(keywordPercentage + "% of your keywords are spending " + costPercentage + "% of your media spend.");
var expensiveKeywords = costArray.filter(function(item) { return +item["keyword percentage"] <= +keywordPercentage && +item["cost percentage"] <= +costPercentage});
var convArray = keywordArray.sort(function (a,b) {
if ((b["conversions"] - a["conversions"]) < 0) {
return -1;
} else if ((b["conversions"] - a["conversions"]) > 0) {
return 1;
} else {
return 0;
}
});
var cumulativeConv = 0;
var maxConvDiff = 0;
keywordPercentage = 0;
convPercentage = 0;
convArray.forEach(function(item, index) {
cumulativeConv += item.conversions;
item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
item["conv percentage"] = (100 * (item["conversions"] / convSubtotal)).toFixed(2);
item["cum conv percentage"] = (100 * (cumulativeConv / convSubtotal)).toFixed(2);
item["conv diff"] = (item["cum conv percentage"] - item["keyword percentage"]).toFixed(2);
// sets maxCostDiff to item["cost diff"] if it's greater
if (+item["conv diff"] > +maxConvDiff) {
maxConvDiff = item["conv diff"];
keywordPercentage = item["keyword percentage"];
convPercentage = item["cum conv percentage"];
};
convData.push([item["criteria"],
item["ad group name"],
item["campaign name"],
item["keyword match type"],
item["conv percentage"] / 100,
item["cum conv percentage"] / 100,
item["impressions"],
item["clicks"],
item["ctr"],
item["average cpc"],
item["cost"],
item["conversions"],
item["cost per conversion"],
item["conversion value"],
item["conversion rate"],
item["roas"]]);
});
cleanLog(keywordPercentage + "% of your keywords are driving " + convPercentage + "% of your conversions.");
var highConvertingKeywords = convArray.filter(
function(item) {
return +item["keyword percentage"] <= +keywordPercentage && +item["conv percentage"] <= +convPercentage
});
if (!skipRev) {
var revArray = keywordArray.sort(function (a,b) {
if ((b["conversion value"] - a["conversion value"]) < 0) {
return -1;
} else if ((b["conversion value"] - a["conversion value"]) > 0) {
return 1;
} else {
return 0;
}
});
// REVENUE BITS
var cumulativeRev = 0;
var maxRevDiff = 0;
keywordPercentage = 0;
revPercentage = 0;
revArray.forEach(function(item, index) {
cumulativeRev += item["conversion value"];
item["keyword percentage"] = (100 * ((index + 1) / keywordSubtotal)).toFixed(2);
item["rev percentage"] = (100 * (item["conversion value"] / revSubtotal)).toFixed(2);
item["cum rev percentage"] = (100 * (cumulativeRev / revSubtotal)).toFixed(2);
item["rev diff"] = (item["cum rev percentage"] - item["keyword percentage"]).toFixed(2);
// sets maxCostDiff to item["cost diff"] if it's greater
if (+item["rev diff"] > +maxRevDiff) {
maxRevDiff = item["rev diff"];
keywordPercentage = item["keyword percentage"];
revPercentage = item["cum rev percentage"];
};
// If rev percentage isn't a num, set it to '--'
isNaN(item["rev percentage"])? item["rev percentage"] = '--' : false;
isNaN(item["cum rev percentage"])? item["cum rev percentage"] = '--' : false;
revData.push([item["criteria"],
item["ad group name"],
item["campaign name"],
item["keyword match type"],
item["rev percentage"] / 100,
item["cum rev percentage"] / 100,
item["impressions"],
item["clicks"],
item["ctr"],
item["average cpc"],
item["cost"],
item["conversions"],
item["cost per conversion"],
item["conversion value"],
item["conversion rate"],
item["roas"]]);
});
cleanLog(keywordPercentage + "% of your keywords are driving " + revPercentage + "% of your revenue.");
var highRevKeywords = revArray.filter(
function(item) {
return +item["keyword percentage"] <= +keywordPercentage && +item["rev percentage"] <= +revPercentage
});
}
// SPREADSHEET BITS
// Headers for sheets
var costReportHeaders = [ "Keyword", "Ad Group", "Campaign",
"Match type", "% of Cost", "Cum. % of Cost", "Impr.", "Clicks", "CTR",
"Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value", "Conv. rate",
"Conv. value / cost"];
var convReportHeaders = [ "Keyword", "Ad Group", "Campaign",
"Match type", "% of Conversions", "Cum. % of Conversions", "Impr.", "Clicks",
"CTR", "Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value",
"Conv. rate", "Conv. value / cost"];
if (!skipRev) {
var revReportHeaders = [ "Keyword", "Ad Group", "Campaign",
"Match type", "% of Revenue", "Cum. % of Revenue", "Impr.", "Clicks", "CTR",
"Avg. CPC", "Cost", "Conv.", "Cost / Conv.", "Conv. value", "Conv. rate",
"Conv. value / cost"];
}
var title = "Keyword Performance Report: Cost, Conversions & Revenue";
if (skipRev) {
title = "Keyword Performance Report: Cost & Conversions";
}
var timeZone = AdsApp.currentAccount().getTimeZone();
var today = new Date(); // initialises a date object to today
var d = new Date(today.setMonth(today.getMonth()-1));
// sets the date to a month ago
var lastMonth = Utilities.formatDate(d, timeZone, 'MMMM'); // December
var shortMonth = Utilities.formatDate(d, timeZone, 'MMM'); // Dec
var theYear = parseFloat(Utilities.formatDate(d, timeZone, 'yyyy'));
title += " - " + lastMonth + " " + theYear + " (www.impression.co.uk)";
// Appends the date to the title name
title = AdsApp.currentAccount().getName() + " " + title;
// Make the spreadsheet
var ss = SpreadsheetApp.create(title);
// Give URL for spreadsheet in logs
cleanLog("");
cleanLog("The full report is available here: " + ss.getUrl());
Logger.log("");
if (yourEmailAddress != '') {
try {
var subject = "[KPR] " + AdsApp.currentAccount().getName()
+ " Keyword Performance Report "
+ " - " + shortMonth + " " + theYear;
var body = title + fullCleanLog;
MailApp.sendEmail(yourEmailAddress, subject, body);
Logger.log("Email sent to " + yourEmailAddress + ".");
} catch (e) {
Logger.log("Unable to send keyword report email. Please check the email "
+ "address provided is valid.");
}
}
// Name the sheets
var costSheet = ss.getSheets()[0].setName("Cost Distribution");
var convSheet = ss.insertSheet("Conversions Distribution");
if (!skipRev) {var revSheet = ss.insertSheet("Revenue Distribution");}
// Cost sheet bits
costSheet.deleteColumns(2, costSheet.getMaxColumns() - 1);
costSheet.deleteRows(2, costSheet.getMaxRows() - 1);
costSheet.getRange(1,1,1,costReportHeaders.length).setValues([costReportHeaders]);
// Conv sheet bits
convSheet.deleteColumns(2, convSheet.getMaxColumns() - 1);
convSheet.deleteRows(2, convSheet.getMaxRows() - 1);
convSheet.getRange(1,1,1,convReportHeaders.length).setValues([convReportHeaders]);
if (!skipRev) {
// Rev sheet bits
revSheet.deleteColumns(2, revSheet.getMaxColumns() - 1);
revSheet.deleteRows(2, revSheet.getMaxRows() - 1);
revSheet.getRange(1,1,1,revReportHeaders.length).setValues([revReportHeaders]);
}
// Freeze keyword column:
costSheet.setFrozenColumns(1);
convSheet.setFrozenColumns(1);
if (!skipRev) {
revSheet.setFrozenColumns(1);
}
// Put the data in
costSheet.getRange(2,1,costData.length,costData[0].length).setValues(costData);
convSheet.getRange(2,1,convData.length,convData[0].length).setValues(convData);
if (!skipRev) {
revSheet.getRange(2,1,revData.length,revData[0].length).setValues(revData);
}
// Set formats for columns
var costRangeList = costSheet.getRangeList(['J2:P']);
var costPrcRangeList = costSheet.getRangeList(['E2:F', 'O2:O']);
costRangeList.setNumberFormat('0.00');
costPrcRangeList.setNumberFormat('0.00%');
var convRangeList = convSheet.getRangeList(['J2:P']);
var convPrcRangeList = convSheet.getRangeList(['E2:F', 'O2:O']);
convRangeList.setNumberFormat('0.00');
convPrcRangeList.setNumberFormat('0.00%');
if (!skipRev) {
var revRangeList = revSheet.getRangeList(['J2:P']);
var revPrcRangeList = revSheet.getRangeList(['E2:F', 'O2:O']);
revRangeList.setNumberFormat('0.00');
revPrcRangeList.setNumberFormat('0.00%');
}
// Set column widths
costSheet.setColumnWidths(2, costReportHeaders.length - 1, 175);
convSheet.setColumnWidths(2, convReportHeaders.length - 1, 175);
if (!skipRev) {
revSheet.setColumnWidths(2, revReportHeaders.length - 1, 175);
}
// Set first column to be auto sized
costSheet.autoResizeColumn(1);
convSheet.autoResizeColumn(1);
if (!skipRev) {
revSheet.autoResizeColumn(1);
}
// Add filter
costSheet.getDataRange().createFilter();
convSheet.getDataRange().createFilter();
if (!skipRev) {
revSheet.getDataRange().createFilter();
}
Logger.log(" ");
Logger.log("2 multiplied by 10 plus 1. Romeo done.");
Logger.log("https://youtu.be/g7VhofoV3qs?t=236");
}
function cleanLog(input) {
Logger.log(input);
fullCleanLog += "\n" + input;
}
Negative keyword conflicts by PEMAVOR
Problem
Blocked search patterns in the past may prevent your new keywords from appearing on search results. Also, Pausing keywords with negatives don’t work anymore as Google started matching a lot of close variants.
With this script developed by our team, you’ll be able to determine conflicting keywords in your ads.
The script below finds and records all conflict keywords for you to take appropriate action.
//User reports
// keyword_view : https://developers.google.com/google-ads/api/fields/v8/keyword_view
// shared_criterion : https://developers.google.com/google-ads/api/fields/v7/shared_criterion
//Declare label to mark keywords.
var labelText = "Blocked by Negative Test"
var labelsArr = {}
//foundIdsOnly will keep keywordId,adGroupId -> "keywordid:adgroupid" : [11111,22222]
//Will use this object to call withIds method on AdsApp.keywords() method because Google Suggests : Use IDs for filtering when possible
var foundIdsOnly = {}
//Will keep negative word details for keywordIds incase we want to log it. "keywordId:adGroupId" = { negativeWord: "keyword" }
var foundArr = {}
//Add filters if necessary. NAME IN ['NAME1','NAME2','NAME3']
// var accountFilter = "Name IN ['NAME1']"
var columns = [
"Action", 'Customer ID', 'Ad Group ID', 'Keyword ID', 'Label'
];
//Main function to start script
function main() {
Logger.log("Starting script")
//Run script as MCC. Select accounts with condition.
//var accountSelector = AdsManagerApp.accounts().withCondition(accountFilter)
var accountSelector = AdsManagerApp.accounts()
//Iterate through found client accounts parallel
accountSelector.executeInParallel("processClientAccount", "afterProcessAllClientAccounts");
}
function processClientAccount() {
//Select client account as current AdApp account
var account = AdsApp.currentAccount();
var accObject = { accountId: account.getCustomerId(), accountName: account.getName() }
Logger.log("[" + accObject.accountName + " (" + accObject.accountId + ")] Running script")
//Call function to fetch report keyword_view (keyword list)
var keywords = createReportKeywords(accObject);
//Call function to fetch report shared_criterion (negative list)
var count = createReportNegatives(keywords, accObject);
//Call function to label keywords and return labeled count
//Object to return from parallel function
var returnDetails = {
accId: account.getCustomerId(),
accName: account.getName(),
count: count
}
return JSON.stringify(returnDetails);
}
//All client accounts completed
function afterProcessAllClientAccounts(results) {
Logger.log("===== RESULTS FOR EACH ACCOUNTS =====")
for (var i = 0; i < results.length; i++) {
var result = JSON.parse(results[i].getReturnValue());
Logger.log("[" + result.accId + " (" + result.accName + ")] : " + result.count)
}
}
//Function to fetch keywords report
function createReportLabels() {
var acc = AdsApp.currentAccount();
AdsManagerApp.select(acc);
var customerId = acc.getCustomerId()
var report = AdsApp.report("SELECT label.name,label.status FROM label WHERE customer.id = " + prepareCustomerId(customerId) + " AND label.name LIKE 'Blocked%'");
var rows = report.rows();
var i = 0
if (rows.hasNext()) {
//Loop through reports rows
while (rows.hasNext()) {
row = rows.next()
var text = row['label.name'];
AdsApp.removeLabel(text)
}
}
}
function createReportKeywords(accObject) {
var dateStart = Date.now();
Logger.log("[" + accObject.accountName + "] Fetching report for keywords")
var account = AdsApp.currentAccount()
var customerId = account.getCustomerId()
var keywords = {}
var report = AdsApp.report('SELECT Criteria, Id, AdGroupId, Labels ' + 'FROM KEYWORDS_PERFORMANCE_REPORT WHERE ExternalCustomerId = "' + prepareCustomerId(customerId) + '"');
var rows = report.rows();
Logger.log("[" + accObject.accountName + "] Fetched keywords from report. Preparing...")
var i = 0
if (rows.hasNext()) {
//Loop through reports rows
while (rows.hasNext()) {
var row = rows.next();
//Read columns from reports row
var text = row['Criteria'];
var id = row['Id'];
var adGroupId = row['AdGroupId'];
var labels = row['Labels'];
//Split keyword to words to prepare object.
var keywordsArr = text.split(" ");
//Loop through words in keyword
for (k = 0; k < keywordsArr.length; k++) {
//If key already exists in object
if (keywords[keywordsArr[k]]) {
//If id already exists in key' ids property
if (keywords[keywordsArr[k]].ids.indexOf(id) >= 0) {} else {
//Push new id to ids property
keywords[keywordsArr[k]].ids.push(id)
//Push new adgroup to adGroupIds property
keywords[keywordsArr[k]].adGroupIds.push(adGroupId)
keywords[keywordsArr[k]].labels.push(labels)
keywords[keywordsArr[k]].keywords.push(text)
}
}
//If key does not exist in object
else {
keywords[keywordsArr[k]] = {
ids: [id],
adGroupIds: [adGroupId],
labels: [labels],
keywords: [text]
}
}
}
i++
}
}
Logger.log("[" + accObject.accountName + "] Fetching keywords completed (Took " + (Math.round((Date.now() - dateStart) / 1000)) + " seconds). Found keywords count : " + i)
return keywords
}
//Function to fetch negatives report
function createReportNegatives(keywordList, accObject) {
var account = AdsApp.currentAccount()
//Check if label exists in account
var labelIterator = AdsApp.labels().withCondition("Name CONTAINS 'Blocked:'").get()
if (labelIterator.hasNext()) {
while (labelIterator.hasNext()) {
label = labelIterator.next()
label.remove()
}
}
var upload = AdsApp.bulkUploads().newCsvUpload(
columns);
var dateStart = Date.now();
var count = 0
Logger.log("[" + accObject.accountName + "] Fetching report for negatives")
var account = AdsApp.currentAccount()
var customerId = account.getCustomerId()
var report = AdsApp.report('SELECT shared_criterion.keyword.match_type, shared_set.name, shared_criterion.keyword.text ' + 'FROM shared_criterion ' + 'WHERE shared_set.status = "ENABLED" AND shared_set.type = "NEGATIVE_KEYWORDS" AND customer.id = "' + prepareCustomerId(customerId) + '"');
var rows = report.rows();
Logger.log("[" + accObject.accountName + "] Fetched negatives from report. Preparing...")
var i = 0
if (rows.hasNext()) {
//Loop through rows in report
while (rows.hasNext()) {
var row = rows.next();
var keyword = row["shared_criterion.keyword.text"];
var listName = "Blocked:" + row["shared_set.name"]
var matchType = row["shared_criterion.keyword.match_type"]
var splitted = keyword.split(" ")
var found = 0
var Ids = [];
var adGroupIds = [];
var labels = [];
var originalWords = [];
//Split found negative to words (To check if 1gram or more...)
for (s = 0; s < splitted.length; s++) {
//If word key exists in keywordList object
if (keywordList[splitted[s]]) {
found++
//If first word of negative
if (s == 0) {
//Get ids and adgroupids by key from object
Ids = keywordList[splitted[s]].ids
adGroupIds = keywordList[splitted[s]].adGroupIds
labels = keywordList[splitted[s]].labels
originalWords = keywordList[splitted[s]].keywords
} else {
//If not first word (not ngram)
newAdGroups = []
newIds = []
newLabels = []
newOriginalWords = []
//Filter previous found keys id and adgroup id to check if keyword ids that can be found in all lookups
newIds = Ids.filter(function(val, index) {
if (keywordList[splitted[s]].ids.indexOf(val) >= 0) {
newAdGroups.push(keywordList[splitted[s]].adGroupIds[keywordList[splitted[s]].ids.indexOf(val)])
newLabels.push(keywordList[splitted[s]].labels[keywordList[splitted[s]].ids.indexOf(val)])
newOriginalWords.push(keywordList[splitted[s]].keywords[keywordList[splitted[s]].ids.indexOf(val)])
return true
} else {
return false
}
})
Ids = newIds;
adGroupIds = newAdGroups;
labels = newLabels;
originalWords = newOriginalWords
}
}
}
//If key in object exists for all words in negative
if (found == splitted.length) {
if (Ids && Ids.length > 0) {
for (x = 0; x < Ids.length; x++) {
if (foundIdsOnly[Ids[x] + ":" + adGroupIds[x]]) {} else {
foundIdsOnly[Ids[x] + ":" + adGroupIds[x]] = [adGroupIds[x], Ids[x]]
foundArr[Ids[x] + ":" + adGroupIds[x]] = { negativeWord: keyword }
var labelToArr
if (isJson(labels[x])) {
labelToArr = JSON.parse(labels[x])
} else {
labelToArr = []
}
if (labelsArr[listName]) {} else {
AdsApp.createLabel(listName)
labelsArr[listName] = "ENABLED"
}
labelToArr.push(listName)
var strLabels = labelToArr.toString()
strLabels = strLabels.replace(/,/g, ";")
var willAppend = false
if (matchType == "BROAD") {
willAppend = true
}
if (matchType == "PHRASE") {
var re = new RegExp("(^|\\W)" + keyword + "($|\\W)", "gi")
var res = originalWords[x].match(re)
if (res) {
willAppend = true
}
}
if (matchType == "EXACT") {
if (keyword == originalWords[x]) {
willAppend = true
}
}
if (willAppend) {
upload.append({
'Action': "Edit",
'Customer ID': account.getCustomerId(),
'Ad Group ID': adGroupIds[x],
'Keyword ID': Ids[x],
'Label': strLabels
});
count++
}
}
}
}
}
i++
}
upload.forCampaignManagement();
upload.apply()
//upload.preview();
Logger.log("[" + accObject.accountName + "] Fetching negatives completed (Took " + (Math.round((Date.now() - dateStart) / 1000)) + " seconds). Found negatives count : " + i)
}
return count
}
//Function to mark negativated keywords with label
function isJson(item) {
item = typeof item !== "string" ?
JSON.stringify(item) :
item;
try {
item = JSON.parse(item);
} catch (e) {
return false;
}
if (typeof item === "object" && item !== null) {
return true;
}
return false;
}
//Pollyfill for Object.values
function objectValues(obj) {
var res = [];
for (var i in obj) {
if (Object.prototype.hasOwnProperty.call(obj, i)) {
res.push(obj[i]);
}
}
return res;
}
//Remove - from customerId
function prepareCustomerId(str) {
return str.replace(/[-]/g, "")
}
Analysis Scripts
Google Ads error checker
Problem
Tiny errors like worldwide targeting may drain your budget. It’s best to automate your ads ‘proof.’
With this script by Daniel Gilbert, you’ll be able to get rid of errors in your ad copies and campaigns.
The script emails you a customized report to detect errors with your chosen account structure, preventing potential budget loss.
// ID: f9095d0b6783a7526117ad14e36935c8
/**
*
* MiniChecker
*
* A Google Ads script that carries out a selection of checks to assess account
* health, and emails the user its conclusions. The checks that it carries out
* are:
* - Worldwide Targeting Checker. Checks for any campaigns with worldwide
* targeting
* - Keyword Bid Upper Limit Checker. Compares bids to a user-set upper
* limit, reporting any keywords that exceed this limit
* - Bid Modifier Upper Limit Checker. Compares device, location and ad
* scheduling bid modifiers to a user-set upper limit, reporting any modifiers
* that exceed this limit
*
* Version: 1.1
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
///////////////////////////////////////////////////////////////////////////////
// Options
// The email address that the results of the checks should be sent to
var EMAIL_ADDRESS = "example@example.com";
// Use this if you want to exclude some campaigns. Case insensitive.
// For example ["Brand"] would ignore any campaigns with "brand" in the name,
// while ["Brand","Competitor"] would ignore any campaigns with "brand" or
// "competitor" in the name.
// Leave as [] to not exclude any campaigns.
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = [];
// Use this if you only want to look at some campaigns. Case insensitive.
// 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 CAMPAIGN_NAME_CONTAINS = [];
// Use this if you want to only look at enabled campaigns.
// Set this to true to only look at currently active campaigns.
// Set to false to also include campaigns that are currently paused.
var IGNORE_PAUSED_CAMPAIGNS = true;
// Set the following to true or false to enable or disable the checkers.
// If CHECK_BID_MODIFIERS is set to false, the settings below it are ignored
var CHECK_WORLDWIDE_TARGETING = true;
var CHECK_KEYWORD_BIDS = true;
var CHECK_BID_MODIFIERS = true;
// Use this to set the upper limit on max CPC if using the Keyword Bid Checker.
// This must be a number greater than 0, in the currency of the account.
var BID_UPPER_LIMIT = 20.0;
// Use this if using the Keyword Bid Checker.
// Set this to true to only look at currently active keywords.
// Set to false to also include keywords that are currently paused.
var IGNORE_PAUSED_KEYWORDS = true;
// Use these to set the upper limit on bid modifiers if using the Bid Modifier
// Checker, as well as turning off checks on specific modifiers.
// The upper limits must be numbers between 0.1 and 9 corresponding to a
// percentage bid adjustment.
// adjustment.
// For example, 0.1 corresponds to a bid adjustment of -90% and 9 corresponds
// to a bid adjustment of +900%
var CHECK_DEVICE_BID_MODIFIERS = true;
var DEVICE_UPPER_LIMIT = 5.0;
var CHECK_LOCATION_BID_MODIFIERS = true;
var LOCATION_UPPER_LIMIT = 5.0;
var CHECK_AD_SCHEDULE_BID_MODIFIERS = true;
var AD_SCHEDULING_UPPER_LIMIT = 5.0;
///////////////////////////////////////////////////////////////////////////////
// Constants for use in the script
// DO NOT CHANGE
var CAMPAIGN_NAME = 'CampaignName';
var OVER_SIZED_MODIFIERS = 'OverSizedModifiers';
var CRITERION_TYPE = 'CriterionType';
var CRITERION = 'Criterion';
var DEVICE = 'Device';
var LOCATION = 'Location';
var AD_SCHEDULING = 'AdScheduling';
var BID_MODIFIER = 'BidModifier';
var HIGH_BID_KEYWORDS = 'HighBidKeywords';
var KEYWORD = 'Keyword';
var AD_GROUP_NAME = 'AdGroupName';
var BID = 'Bid';
var BID_LIMIT = 'BidLimit';
var COUNT = 'Count'
var DEVICE_MODIFIER_FIELDS = {
"CampaignDesktopBidModifier": "Desktop",
"CampaignMobileBidModifier": "Mobile",
"CampaignTabletBidModifier": "Tablet"
};
///////////////////////////////////////////////////////////////////////////////
function main() {
var validCampaignIds = getCampaignIds();
var worldwideTargetingResult = checkWorldwideTargeting(validCampaignIds);
var bidModifiersResult = checkBidModifiers(validCampaignIds);
var keywordsResult = checkKeywordBids(validCampaignIds);
sendSummaryEmail(
worldwideTargetingResult,
bidModifiersResult,
keywordsResult
);
}
// Get the IDs of campaigns which match the given options
function getCampaignIds() {
var whereStatement = "WHERE ";
var whereStatementsArray = [];
var campaignIds = [];
if (IGNORE_PAUSED_CAMPAIGNS) {
whereStatement += "CampaignStatus = ENABLED ";
} else {
whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] ";
}
for (var i = 0; i < CAMPAIGN_NAME_DOES_NOT_CONTAIN.length; i++) {
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '"
+ CAMPAIGN_NAME_DOES_NOT_CONTAIN[i].replace(/"/g, '\\\"') + "' ";
}
if (CAMPAIGN_NAME_CONTAINS.length == 0) {
whereStatementsArray = [whereStatement];
} else {
for (var i = 0; i < CAMPAIGN_NAME_CONTAINS.length; i++) {
whereStatementsArray.push(
whereStatement
+ 'AND CampaignName CONTAINS_IGNORE_CASE "'
+ CAMPAIGN_NAME_CONTAINS[i].replace(/"/g, '\\\"')
+ '" '
);
}
}
for (var i = 0; i < whereStatementsArray.length; i++) {
var campaignReport = AdsApp.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 checkWorldwideTargeting(campaignIds) {
if (!CHECK_WORLDWIDE_TARGETING) {
return [];
}
Logger.log("Checking Worldwide Targeting");
var worldwideTargetingIssues = [];
var campaignIterator = AdsApp.campaigns().withIds(campaignIds).get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var locationIterator = campaign
.targeting()
.targetedLocations()
.get();
if (!locationIterator.hasNext()) {
var proximityIterator = campaign
.targeting()
.targetedProximities()
.get();
if (!proximityIterator.hasNext()) {
var issue = {};
issue[CAMPAIGN_NAME] = campaign.getName();
worldwideTargetingIssues.push(issue);
}
}
}
return worldwideTargetingIssues;
}
function checkBidModifiers(campaignIds) {
var combinedIssues = [];
if (!CHECK_BID_MODIFIERS) {
combinedIssues[COUNT] = 0;
return combinedIssues;
}
Logger.log("Checking Bid Modifiers");
var totalBidIssues = 0
var deviceIssues = getDeviceIssues(campaignIds);
var locationIssues = getLocationIssues(campaignIds);
var adScheduleIssues = getAdScheduleIssues(campaignIds);
var allIssues = deviceIssues.concat(locationIssues, adScheduleIssues);
var combinedIssuesObject = {};
for (var i in allIssues) {
var issue = allIssues[i];
if (issue[CAMPAIGN_NAME] in combinedIssuesObject) {
combinedIssuesObject[issue[CAMPAIGN_NAME]] =
combinedIssuesObject[issue[CAMPAIGN_NAME]]
.concat(issue[OVER_SIZED_MODIFIERS]);
} else {
combinedIssuesObject[issue[CAMPAIGN_NAME]] = issue[OVER_SIZED_MODIFIERS];
}
}
for (var campaignName in combinedIssuesObject) {
var issue = {};
issue[CAMPAIGN_NAME] = campaignName;
issue[OVER_SIZED_MODIFIERS] = combinedIssuesObject[campaignName];
totalBidIssues += combinedIssuesObject[campaignName].length
combinedIssues.push(issue);
}
combinedIssues[COUNT] = totalBidIssues
return combinedIssues;
}
function checkKeywordBids(campaignIds) {
var dataToReport = [];
if (!CHECK_KEYWORD_BIDS) {
dataToReport[COUNT] = 0;
return dataToReport;
}
Logger.log('Checking Keyword Bids');
if (IGNORE_PAUSED_KEYWORDS) {
var keywordCondition = 'Status = ENABLED';
} else {
var keywordCondition = 'Status IN [ENABLED, PAUSED]';
}
var campaignIdsCondition = 'CampaignId IN ['
+ campaignIds.join(', ')
+ ']';
var biddingStrategyCondition = 'BiddingStrategyType = MANUAL_CPC';
var whereCondition = [
keywordCondition,
campaignIdsCondition,
biddingStrategyCondition
].join(' AND ');
var report = AdsApp.report(
'SELECT CampaignName, AdGroupName, Criteria, CpcBid '
+ 'FROM KEYWORDS_PERFORMANCE_REPORT '
+ 'WHERE ' + whereCondition
);
var rowIterator = report.rows();
var rawData = {};
while (rowIterator.hasNext()) {
var row = rowIterator.next();
var bid = row['CpcBid'];
if (bid <= BID_UPPER_LIMIT) {
continue;
}
var campaignName = row['CampaignName'];
if (rawData[campaignName] === undefined) {
rawData[campaignName] = {};
}
var adGroupName = row['AdGroupName'];
if (rawData[campaignName][adGroupName] === undefined) {
rawData[campaignName][adGroupName] = [];
}
var keywordInformation = {};
keywordInformation[KEYWORD] = row['Criteria'];
keywordInformation[BID] = bid;
rawData[campaignName][adGroupName].push(
keywordInformation
);
}
dataToReport[COUNT] = 0;
for (var campaignName in rawData) {
var adgroupInformation = rawData[campaignName];
var highBidKeywords = [];
for (var adGroupName in adgroupInformation) {
var keywordArray = adgroupInformation[adGroupName];
for (var i = 0; i < keywordArray.length; i++) {
var keywordObject = keywordArray[i];
keywordObject[AD_GROUP_NAME] = adGroupName;
highBidKeywords.push(keywordObject);
}
}
if (highBidKeywords.length === 0) {
continue;
}
var campaignObject = {};
campaignObject[CAMPAIGN_NAME] = campaignName;
campaignObject[HIGH_BID_KEYWORDS] = highBidKeywords;
dataToReport[COUNT] += highBidKeywords.length
dataToReport.push(
campaignObject
);
}
return dataToReport;
}
function getDeviceIssues(campaignIds) {
if (!CHECK_DEVICE_BID_MODIFIERS) {
return [];
}
Logger.log("Checking Device Bid Modifiers");
var deviceIssues = [];
var query = "SELECT CampaignName, "
+ Object.keys(DEVICE_MODIFIER_FIELDS).join(", ")
+ " FROM CAMPAIGN_PERFORMANCE_REPORT "
+ "WHERE CampaignId IN [" + campaignIds.join(", ") + "]"
var report = AdsApp.report(query);
var rowIterator = report.rows();
while (rowIterator.hasNext()) {
var row = rowIterator.next();
var campaignDeviceIssues = [];
for (var field in DEVICE_MODIFIER_FIELDS) {
var bidModifier = row[field].slice(0, -1) / 100;
if (bidModifier > DEVICE_UPPER_LIMIT) {
var issue = {};
issue[CRITERION_TYPE] = DEVICE;
issue[CRITERION] = DEVICE_MODIFIER_FIELDS[field];
issue[BID_MODIFIER] = bidModifier;
issue[BID_LIMIT] = DEVICE_UPPER_LIMIT
campaignDeviceIssues.push(issue);
}
}
if (campaignDeviceIssues.length > 0) {
var issue = {};
issue[CAMPAIGN_NAME] = row["CampaignName"];
issue[OVER_SIZED_MODIFIERS] = campaignDeviceIssues;
deviceIssues.push(issue);
}
}
return deviceIssues;
}
function getLocationIssues(campaignIds) {
if (!CHECK_LOCATION_BID_MODIFIERS) {
return [];
}
Logger.log("Checking Location Bid Modifiers");
var locationIssues = [];
var campaignIterator = AdsApp.campaigns().withIds(campaignIds).get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var campaignLocationIssues = [];
var locationIterator = campaign
.targeting()
.targetedLocations()
.withCondition("BidModifier > " + (LOCATION_UPPER_LIMIT + 1))
.get();
while (locationIterator.hasNext()) {
var location = locationIterator.next();
var issue = {};
issue[CRITERION_TYPE] = LOCATION;
issue[CRITERION] = location.getName();
issue[BID_MODIFIER] = location.getBidModifier() - 1;
issue[BID_LIMIT] = LOCATION_UPPER_LIMIT
campaignLocationIssues.push(issue);
}
if (campaignLocationIssues.length > 0) {
var issue = {};
issue[CAMPAIGN_NAME] = campaign.getName();
issue[OVER_SIZED_MODIFIERS] = campaignLocationIssues;
locationIssues.push(issue);
}
}
return locationIssues;
}
function getAdScheduleIssues(campaignIds) {
if (!CHECK_AD_SCHEDULE_BID_MODIFIERS) {
return [];
}
Logger.log("Checking Ad Schedule Bid Modifiers");
var adScheduleIssues = [];
var campaignIterator = AdsApp.campaigns().withIds(campaignIds).get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var campaignAdScheduleIssues = [];
var adScheduleIterator = campaign.targeting()
.adSchedules()
.withCondition("BidModifier > " + (AD_SCHEDULING_UPPER_LIMIT + 1))
.get();
while (adScheduleIterator.hasNext()) {
var adSchedule = adScheduleIterator.next();
var adScheduleTimeSlotString = adSchedule.getDayOfWeek()
+ " " + adSchedule.getStartHour()
+ ":" + padToLength(adSchedule.getStartMinute(), 2)
+ " - " + adSchedule.getEndHour()
+ ":" + padToLength(adSchedule.getEndMinute(), 2);
var issue = {};
issue[CRITERION_TYPE] = AD_SCHEDULING;
issue[CRITERION] = adScheduleTimeSlotString;
issue[BID_MODIFIER] = adSchedule.getBidModifier() - 1;
issue[BID_LIMIT] = AD_SCHEDULING_UPPER_LIMIT
campaignAdScheduleIssues.push(issue);
}
if (campaignAdScheduleIssues.length > 0) {
var issue = {};
issue[CAMPAIGN_NAME] = campaign.getName();
issue[OVER_SIZED_MODIFIERS] = campaignAdScheduleIssues;
adScheduleIssues.push(issue);
}
}
return adScheduleIssues;
}
function padToLength(int, len) {
var intString = int.toString();
while (intString.length < len) {
intString = "0" + intString;
}
return intString;
}
function sendSummaryEmail(worldwideTargetingResult, bidModifiersResult, keywordsResult) {
var html = ['<html>', '<body>'];
html.push("Keyword bids above " + BID_UPPER_LIMIT + ": " + keywordsResult[COUNT])
html.push("<br>Bids exceeding modifier limits: " + bidModifiersResult[COUNT])
html.push("<br>Campaigns with worldwide location targeting: " + worldwideTargetingResult.length)
var totalCampaignIssues = keywordsResult[COUNT] + bidModifiersResult[COUNT] + worldwideTargetingResult.length
html.push('<h2>Keywords with bids above ' + BID_UPPER_LIMIT + ':</h2>');
if (keywordsResult.length == 0) {
html.push("<br>No keywords found above bid limit<br>")
} else {
html.push('<br><table width=750>',
"<tr bgcolor='#ddd'>",
"<th>Campaign</th>",
"<th>Keyword</th>",
"<th>Bid</th>",
"<th>Ad Group</th>",
'</tr>');
for (var i = 0; i < keywordsResult.length; i++) {
var highBidKeywords = keywordsResult[i][HIGH_BID_KEYWORDS];
for (var j = 0; j < highBidKeywords.length; j++) {
html.push('<tr>',
"<td>" + keywordsResult[i][CAMPAIGN_NAME] + '</td>',
"<td style='text-align: center'>" + highBidKeywords[j]["Keyword"] + '</td>',
"<td style='text-align: center'>" + highBidKeywords[j]["Bid"] + '</td>',
"<td style='text-align: right'>" + highBidKeywords[j]["AdGroupName"] + '</td>',
'</tr>');
}
}
html.push('</table><br>');
}
html.push('<br><h2>Bid modifiers above limit:</h2>');
if (bidModifiersResult.length == 0) {
html.push("<br>No bid modifiers found above limit<br>")
} else {
html.push('<br><table width=600>',
"<tr bgcolor='#ddd'>",
"<th>Campaign</th>",
"<th>Criterion Type</th>",
"<th>Criterion</th>",
"<th>Limit</th>",
"<th>Bid Modifier</th>",
'</tr>');
for (var i = 0; i < bidModifiersResult.length; i++) {
var overSizedModifiers = bidModifiersResult[i][OVER_SIZED_MODIFIERS]
for (var j = 0; j < overSizedModifiers.length; j++) {
html.push('<tr>',
"<td>" + bidModifiersResult[i][CAMPAIGN_NAME] + '</td>',
"<td style='text-align: center'>" + overSizedModifiers[j]["CriterionType"] + '</td>',
"<td style='text-align: center'>" + overSizedModifiers[j]["Criterion"] + '</td>',
"<td style='text-align: center'>" + overSizedModifiers[j]["BidLimit"] + '</td>',
"<td style='text-align: right'>" + overSizedModifiers[j]["BidModifier"].toFixed(2) + '</td>',
'</tr>');
}
}
html.push('</table><br>');
}
html.push('<br><h2>Campaigns with worldwide targeting:</h2>');
if (worldwideTargetingResult.length == 0) {
html.push("<br>No campaigns found with worldwide targeting<br>")
} else {
html.push('<br><table width=200>',
"<tr bgcolor='#ddd'>",
"<th>Campaigns</th>",
'</tr>');
}
for (var i = 0; i < worldwideTargetingResult.length; i++) {
html.push('<tr>',
"<td>" + worldwideTargetingResult[i][CAMPAIGN_NAME] + '</td>',
'</tr>');
}
html.push('</table>');
html.push('</body>', '</html>');
MailApp.sendEmail(EMAIL_ADDRESS, 'Google Ads Account ' +
AdsApp.currentAccount().getCustomerId() + ' Summary Report: '
+ totalCampaignIssues + ' issues', '',
{ htmlBody: html.join("") });
}
Pause suspicious ads (invalid clicks)
Problem
A high number of invalid clicks could to your campaigns mean that you’re under attack, and you need to do something.
With this script by Hoofay, you’ll be able to automatically pause the campaigns that get suspicious clicks.
Fraudulent bots organized by your competitors may lead to wasted spend. So, making sure of it with a simple code is a wise thing to do.
//-----------------------------------
// Pause Campaigns for Potential Invalid Activity
// Created By: Hoofay
//-----------------------------------
function main() {
// script to pause campaigns that have > 30% invalid clicks (and > 20 clicks) during the day.
// this assumes that a > 30% invalid click rate means that a specific campaign is being
// targeted by potential invalid activity.
var report = AdWordsApp.report(
'SELECT CampaignName, InvalidClickRate ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE InvalidClickRate > 0.3 ' +
'DURING TODAY');
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['CampaignName'];
var invalidclickrate = row['InvalidClickRate'];
Logger.log(campaignName + ',' + invalidclickrate);
}
var campaigns = AdWordsApp.campaigns().withCondition("InvalidClickRate > 0.3").withCondition("Clicks > 20").forDateRange('TODAY').get();
while(campaigns.hasNext()) {
var campaign = campaigns.next();
var recipient = "xxx@xxx.com";
var subject = "Account Paused Campaigns";
var body = "Campaign paused due to potential invalid activity: \n\n" + campaign;
if(!campaign.isPaused()){
campaign.pause();
MailApp.sendEmail(recipient, subject, body);
}
if(campaign.isPaused()){
Logger.log(campaign + ' is already paused');
}
}
}
Identify duplicated ads
Problem
Finding a duplicated ad in Google Ads account is like a “finding a needle in the haystack.”
With this script developed by BrainLabs, automatically identify duplicate or entirely identical ads on your account so that you completely clear your account from duplicate clutter.
The script creates a report detailing which searches are triggering multiple ad groups. Copy the script below, then adjust the options at the top, such as: campaignNameDoesNotContain, ignorePausedCampaigns, checkUrl, metric, caseSensitive, keepLabel, and pauseLabel.
// ID: 7059dfb2a845da47d061250d506a0491
* Creates a report detailing which searches are triggering multiple ad groups.
//////////////////////////////////////////////////////////////////////////////
// Options
var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.
var impressionThreshold = 0;
// Only queries with more than this number of impressions will be looked at.
// Set as 0 to look at all available queries.
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 also include campaigns that are currently paused.
//////////////////////////////////////////////////////////////////////////////
function main() {
var writeSpreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");
var writeSheet = writeSpreadsheet.getSheets()[0];
var campaignIds = getCampaignIds(ignorePausedCampaigns, campaignNameDoesNotContain, campaignNameContains);
var queries = getQueries(campaignIds);
writeReport(queries, writeSheet);
}
// 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(ignorePausedCampaigns, campaignNameDoesNotContain, campaignNameContains) {
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;
}
/*
Downloads a searh query performance report
Stores data in an array.
Returns that array.
Builds array of Adgroups indexed by Query.
Structure:
Queries => [adGroups, CampaignId, ...], ...]
*/
function getQueries(campaignIds){
var queries = {};
var report = AdWordsApp.report(
"SELECT Query, CampaignId, CampaignName, AdGroupId, AdGroupName, KeywordTextMatchingQuery, Impressions, Clicks, Cost, Conversions" +
" FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
" WHERE " +
" CampaignId IN [" + campaignIds.join(",") + "]" +
" AND Impressions > " + impressionThreshold + " " +
" DURING LAST_30_DAYS");
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
if (row['KeywordTextMatchingQuery'].indexOf("==") > -1){ //The 'keyword' is a product in a Shopping campaign
continue;
}
var metrics = [row['AdGroupId'], row['AdGroupName'], row['CampaignId'], row['CampaignName'], row['KeywordTextMatchingQuery'], row['Impressions'], row['Clicks'], row['Cost'], row['Conversions']]
if (typeof queries[row['Query']] == 'undefined'){
queries[row['Query']] = [metrics];
}else{
queries[row['Query']].push(metrics);
}
}
for (var property in queries){
if (queries[property].length ==1){
delete queries[property];
}
}
Logger.log(Object.keys(queries).length + ' Search Queries appear in two or more Ad Groups.');
return queries;
}
/*
Goes through object writing each line to a sheet.
Search Terms are ordered by total impressions.
*/
function writeReport(queries, writeSheet){
writeSheet.clear();
var queryTotalImpressions = {};
for (var query in queries){
var impressions = 0;
var metrics = queries[query];
for (var j=0; j<metrics.length; j++){
impressions += parseInt(metrics[j][5].replace(/,/g,""),10);
}
queryTotalImpressions[query] = impressions;
}
var orderedQueries = Object.keys(queries).sort(function (a, b) {return queryTotalImpressions[b] - queryTotalImpressions[a];});
writeSheet.getRange(1, 1, 1, 10).setValues([["Search Term", "AdGroup Id", "AdGroup Name", "Campaign Id", "Campaign Name", "Triggered Keyword", "Impressions", "Clicks", "Cost", "Conversions"]]);
var vertical = 2;
var sizes = [];
for (var i in orderedQueries){
sizes.push(queries[orderedQueries[i]].length);
}
for (var i in orderedQueries){
var entry = orderedQueries[i];
var currentArrays = queries[entry];
var size = sizes[i];
writeSheet.getRange(vertical, 1).setValue(entry);
writeSheet.getRange(vertical, 2, size, 9).setValues(currentArrays);
vertical += size ;
}
Logger.log('The data has been written to the sheet specified by URL provided');
}