When managing Google Ads accounts, there are several tasks that should be completed on a regular basis. This includes regular search query analysis to ensure query matching is accurate and relevant. This is particularly important with the changes made to Exact Match keywords and the addition of “close variants”. Google’s algorithm’s aren’t perfect and we’ve had more than one occasion where new close variants have been irrelevant.
This script aims to assist in search term analysis in 2 ways:
The script will identify new search terms that have spent more than $X and were not present in the previous week’s report. This monetary value can be adjusted in the scripts config. Identify search terms that have spent more than x% more than the previous week. Again this % is adjustable in the config.
Limitations
The main limitation of this script is that the config applies to all accounts within a manager level account. Account level parameters can not be set and require either an expanded config or custom UI.
function main() {
//add account CIDs and emails as a comma seperate list in config arrays.
let config = {
account_ids: ["123-456-7891"],
emails: ['example@gmail.com'],
min_spend: 10,// the minimum spend a new keyword has to have in order to trigger an alert. Stops low volume long tail keywords triggering alert.
differnce_spend: 1.35 // the % a keyword spend has to increase vs the previous period to trigger alert (1.35 = 35% increase)
}
const dates = createDates()
let accountIterator = AdsManagerApp.accounts()
.withIds(config.account_ids) // comment out using // to get all accounts in MCC.
.get();
let alerts = new Array()
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var accountName = account.getName();
Logger.log(`Checking search queries for ${accountName}`)
let queryOne = `SELECT search_term_view.search_term, metrics.cost_micros FROM search_term_view WHERE metrics.cost_micros > 1000 AND segments.date BETWEEN '${dates[0]}' AND '${dates[1]}'`
let queryTwo = `SELECT search_term_view.search_term, metrics.cost_micros FROM search_term_view WHERE metrics.cost_micros > 1000 AND segments.date BETWEEN '${dates[2]}' AND '${dates[3]}'`
let reportOne = getReport(queryOne)
let reportTwo = getReport(queryTwo)
let mapOne = reportToMap(reportOne)
let mapTwo = reportToMap(reportTwo)
//Find new keywords for second date range
mapTwo.forEach((value, key) => {
if (mapOne.get(key) == undefined && value > config.min_spend) {
alerts.push([accountName, `New Keyword`, key, `-`, value])
}
})
//Find keywords with increase in spend
mapTwo.forEach((value, key) => {
let previousPeriodSpend = mapOne.get(key)
if (value > previousPeriodSpend * config.differnce_spend) {
alerts.push([accountName, `Increased Spend`, key, previousPeriodSpend, value])
}
})
}
if (alerts.length > 0) {
let email = createEmail(alerts)
sendEmail(config.emails.join(','), email)
}
}
function getReport(query) {
let report = AdsApp.report(query)
return report
}
function reportToMap(report) {
let map = new Map()
let rows = report.rows();
while (rows.hasNext()) {
let row = rows.next();
let searchTerm = row['search_term_view.search_term']
let cost = parseFloat(row['metrics.cost_micros']) / 1000000
map.set(searchTerm, cost)
}
return map
}
// Function to create comparison date ranges, defaults to last 2 weeks.
function createDates() {
const timeZone = AdsApp.currentAccount().getTimeZone();
const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
const now = new Date();
const periodOneFrom = new Date(now.getTime() - 15 * MILLIS_PER_DAY);
const periodOneTo = new Date(now.getTime() - 8 * MILLIS_PER_DAY)
const periodTwoFrom = new Date(now.getTime() - 7 * MILLIS_PER_DAY);
const periodTwoTo = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
//Formats dates to yyyy-MM-dd format for query
let datesArray = [periodOneFrom, periodOneTo, periodTwoFrom, periodTwoTo]
datesArray = datesArray.map(e => Utilities.formatDate(e, timeZone, 'yyyy-MM-dd'))
return datesArray
}
function createEmail(array) {
let htmlHead = '<html><head><style type="text/css">.tg{border-collapse:collapse;border-spacing:0;}.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}.tg .tg-5w1r{background-color:#212735;border-color:inherit;color:#e0a912;text-align:center;vertical-align:middle}.tg .tg-0pky{border-color:inherit;text-align:center;vertical-align:middle}</style></head>'
let htmlBody = '<body><table class="tg"><thead><tr><th class="tg-5w1r">Account Name</th><th class="tg-5w1r">Type</th><th class="tg-5w1r">Search Term</th><th class="tg-5w1r">Previous Spend</th><th class="tg-5w1r">New Spend</th></tr></thead><tbody>'
let htmlRows = createTableRows(array)
let fullHtml = htmlHead + htmlBody + htmlRows + '</tbody></table><div><a href="https://digital-expanse.com/" target="_blank"><img align="left" border="0" src="https://assets.unlayer.com/projects/114688/1668897510641-secondary-logo-fullcolor.png" alt="" title="" style="outline: none;text-decoration: none;-ms-interpolation-mode: bicubic;clear: both;display: inline-block !important;border: none;height: auto;float: none;width: 20%;max-width: 96px;"width="96" /></a></div></body></html>'
return fullHtml
}
function createTableRows(array) {
let rows = new Array()
array.forEach(e => {
let accountName = e[0]
let type = e[1]
let searchTerm = e[2]
let previousSpend = e[3]
let newSpend = e[4]
let rowHtml = `<tr><td class="tg-0pky">${accountName}</td><td class="tg-0pky">${type}</td><td class="tg-0pky">${searchTerm}</td><td class="tg-0pky">${previousSpend}</td><td class="tg-0pky">${newSpend}</td></tr>`
rows.push(rowHtml)
})
return rows.join("")
}
function sendEmail(emailAddress, html) {
MailApp.sendEmail({
to: emailAddress,
name: 'Digital Expanse Scripts',
subject: `Google Ads Scripts - Keyword Changes`,
htmlBody: html
});
}
