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:

  • Ad relevance
  • Quality of the landing page
  • Expected CTR
  • Bouncerate
  • Time on Site (TOS)

Settings

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.

Script
// Copyright 2019. 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.
// 
// Last update: 29-11-2018
//
// ABOUT THE SCRIPT
// 
// With this script, we will create a report with all keywords
// in your accounts that have quality issues.
//
// ------------------------------------------------------------

// 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, 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["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()

Sharing knowledge

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.

Contact us

Nils Rooijmans
Google Ads Scripter
Water Cooler Topics
Nils Rooijmans, Google Ads Scripter
Bas Baudoin
Teamleader PPC
Happy Leads
Bas Baudoin, Teamleider SEA
Tibbe van Asten
PPC Specialist
Founder Adsscripts
Tibbe van Asten, Senior PPC Automation Specialist