As part of the Quality Score assigned to each keyword in your account, a number of statistics are used that you can find in your account. It concerns the following metrics:
In the config section of the script, you can adjust multiple values as desired.
LOG: Change this to 'true' to see what exactly happened. Leave on 'false' when the script is really running, because that's faster.
THRESHOLD_IMPRESSIONS: change this number if you think you need more impressions to get good statistics.
DATE_RANGE: Has an effect on the previous variable. The script looks at the number of impressions in the specified time frame.
ACCOUNT_LABEL: Use this to select specific accounts.
KEYWORD_LABEL: Applies to each keyword when it is included in the sheet to avoid duplication. You can adjust this as you wish, the script creates the label when it does not exist.
THRESHOLD_BOUNCE: The keyword must have at least this bouncerate to be reported.
THRESHOLD_TOS: The keyword can have up to this time on site (in seconds) to be reported.
SPREADSHEET_URL: Make a copy of this spreadsheet and enter the URL here to use the results of the script.
MCC level
The following script can be used at MCC level, so that you get a tab in a Google Sheet per account with the keywords where you can make improvements. In the accountSelector, I filter all accounts that are labeled 'Active' in your MCC. If you do not want this, you can delete line 43 from the script. In addition, there is a label attached to each keyword when they are included in the report. As a result, keywords do not return multiple times in the sheet.
Scheduling: Run this script every hour, because it may take some time for all keywords from all accounts to run through.
The script
// Copyright 2020. Increase BV. All Rights Reserved.
// Not to be used without permission of the creator or Increase B.V.
//
// Created By: Tibbe van Asten
// for Increase B.V.
//
// Created: 29-11-2018
// Last update: 17-08-2020
//
// 17-08-2020: Added Keyword Match Type (idea by Arjan Schoorl)
//
// ABOUT THE SCRIPT
// With this script, we will create a report with all keywords
// in your accounts that have quality issues.
//
// Link to script: https://adsscripts.com/scripts/google-ads-scripts/report-keyword-quality
//
// ------------------------------------------------------------
// Script settings
var config = {
LOG : false,
THRESHOLD_IMPRESSIONS : 20,
DATE_RANGE : "LAST_30_DAYS",
ACCOUNT_LABEL : "Active",
KEYWORD_LABEL : "Keyword - Quality - Issue",
THRESHOLD_BOUNCE : 80,
THRESHOLD_TOS : 20,
// Copy from http://tinyurl.com/y536ld89
SPREADSHEET_URL : "XXX",
API_VERSION : "v201809"
}
// ------------------------------------------------------------
function main() {
var ss = connectSheet();
// Selecting all Ads Accounts
var accountIterator = AdsManagerApp
.accounts()
.withCondition("LabelNames CONTAINS '" + config.ACCOUNT_LABEL + "'")
.get();
while(accountIterator.hasNext()){
var account = accountIterator.next();
MccApp.select(account);
Logger.log("Account: " + account.getName());
Logger.log("-----");
// Create a label when it doesn't already exists
keywordLabel(account);
var label = AdsApp.labels().withCondition("Name = '" + config.KEYWORD_LABEL + "'").get().next();
// Check if a sheet already exists for this account
var sheet = checkSheet(ss, account);
// Selecting all keywords
var report = AdsApp.report(
"SELECT AccountDescriptiveName, CampaignName, AdGroupName, AdGroupId, Criteria, KeywordMatchType, Id, BounceRate, AverageTimeOnSite, FinalUrls, CpcBid, SearchPredictedCtr, CreativeQualityScore, HistoricalLandingPageQualityScore, Clicks " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE LabelIds CONTAINS_NONE [" + label.getId() + "] " +
"AND CampaignStatus = ENABLED " +
"AND AdGroupStatus = ENABLED " +
"AND Status = ENABLED " +
"AND Impressions > " + config.THRESHOLD_IMPRESSIONS +
" DURING " + config.DATE_RANGE
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
reportRows(row, sheet);
} // row iterator
Logger.log(account.getName() + " afgerond");
} // account iterator
} // function main()
// ------------------------------------------------------------
function reportRows(row, sheet){
// Checking a couple of variables, to make sure everything works as expected
var bounceRate = checkBounce(row);
var tos = checkTos(row)
if((tos < config.THRESHOLD_TOS && tos != "") || bounceRate > config.THRESHOLD_BOUNCE || row["SearchPredictedCtr"] == "Below average" || row["CreativeQualityScore"] == "Below average" || row["HistoricalLandingPageQualityScore"] == "Below average"){
if(config.LOG == true){
Logger.log("Keyword: " + row["Criteria"]);
Logger.log("TOS: " + row["AverageTimeOnSite"]);
Logger.log("Bounce: " + row["BounceRate"]);
Logger.log("Ad Quality: " + row["CreativeQualityScore"]);
Logger.log("LP Experience: " + row["HistoricalLandingPageQualityScore"]);
Logger.log("Exp. CTR: " + row["SearchPredictedCtr"]);
Logger.log(" ");
}
// Checking the final URL of a keyword. If not set, we will find the URL of the best performing ad in the adgroup
var finalUrl = findUrl(row);
// Now we put all the info in the sheet
sheet.appendRow([row["AccountDescriptiveName"],row["CampaignName"],row["AdGroupName"],row["AdGroupId"],"'" + row["Criteria"],row["KeywordMatchType"],row["Id"],bounceRate,row["CreativeQualityScore"],row["HistoricalLandingPageQualityScore"],row["SearchPredictedCtr"],tos,row["CpcBid"],finalUrl]);
// Label the keyword, so we know it's processed
labelKeywords(row["AdGroupId"], row["Id"]);
} // keyword selection
} // function reportRows
// ------------------------------------------------------------
function findUrl(row){
if(row["FinalUrls"] == "--"){
var keywordIterator = AdsApp
.keywords()
.withIds([[row["AdGroupId"], row["Id"]]])
.get();
while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var finalUrl = keyword.getAdGroup().ads().orderBy("Ctr DESC").forDateRange(config.DATE_RANGE).withLimit(1).get().next().urls().getFinalUrl();
} // keyword iterator
} else {
var finalUrl = row["FinalUrls"];
}
return finalUrl;
} // function findUrl()
// ------------------------------------------------------------
function checkBounce(row){
if(row["Clicks"] >= 10){
var bounceRate = parseInt(row["BounceRate"]);
} else {
var bounceRate = "";
}
return bounceRate;
} // checkBounce()
// ------------------------------------------------------------
function checkTos(row){
if(row["Clicks"] >= 10){
var tos = parseInt(row["AverageTimeOnSite"]);
} else {
var tos = "";
}
return tos;
} // function checkTos()
// ------------------------------------------------------------
function keywordLabel(account){
if(!AdsApp.labels().withCondition("Name = '"+config.KEYWORD_LABEL+"'").get().hasNext()) {
AdsApp.createLabel(config.KEYWORD_LABEL);
if(config.LOG == true){
Logger.log("Label " + config.KEYWORD_LABEL + " created");
}
}
} // function keywordLabel()
// ------------------------------------------------------------
function labelKeywords(adGroupId, keywordId){
var keywordIterator = AdsApp
.keywords()
.withIds([[adGroupId, keywordId]])
.get();
while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
keyword.applyLabel(config.KEYWORD_LABEL);
if(config.LOG == true){
Logger.log("Label toegepast op " + keyword.getText());
Logger.log(" ");
}
} // keyword iterator
} // function labelKeywords
// ------------------------------------------------------------
function connectSheet(){
var ss = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
return ss;
} // function connectSheet()
// ------------------------------------------------------------
function checkSheet(ss, account){
var sheet = ss.getSheetByName(account.getName());
if (sheet == null) {
var templateSheet = ss.getSheetByName("Template");
ss.insertSheet(account.getName(), {template: templateSheet});
var sheet = ss.getSheetByName(account.getName());
Logger.log("New sheet created for " + account.getName());
} // if sheet doesn't exists
return sheet;
} // checkSheet()
Show whole script!
Loading Comments
The Experts
Tibbe van AstenTeam Lead Performance Marketing
Nils RooijmansWater Cooler Topics
Martijn KraanFreelance PPC Specialist
Bas BaudoinTeamlead SEA @ Happy Leads
Jermaya LeijenDigital Marketing Strategist
Krzysztof BycinaPPC Specialist from Poland
How about you?JOIN US!
Caring
Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.
Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.