Script to check the number of ads and keywords in ad groups.

Check For Empty Ad Groups.js

/**
*
* AdWords Script to check the number of entities in ad groups: reports ad
* groups with no ads, no keywords, too few ads or too many keywords.
* Optionally reports ad groups with no mobile preferred ads, and ad groups
* with broad match keywords but no negative keywords.
*
* Version: 1.1
* Updated 2017-01-05: changed 'CreativeApprovalStatus' to 'CombinedApprovalStatus'
* Google AdWords Script maintained by brainlabsdigital.com
*
**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options

var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.

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.

var ignorePausedKeywords = true;
// If this is true, then ad groups can be reported as empty if they have paused
// keywords.
// Set this to false to make the script count paused keywords when deciding if
// an ad group is not empty. Only ad groups with no keywords (or only removed
// keywords) will be reported as empty.

var ignorePausedAds = true;
// If this is true, then ad groups can be reported as empty if they have paused
// ads.
// Set this to false to make the script count paused ads when deciding if an ad
// group is not empty. Only ad groups with no ads (or only removed or 
// disapproved ads) will be reported as empty.

var maximumKeywordsPerAdGroup = 1;
// An ad group will be reported if it has more than this number of keywords.

var minimumAdsPerAdGroup = 2;
// An ad group will be reported if it has fewer than this number of ads.

var findAdGroupsWithoutMobileAds = true;
// Set this to true to get a list of ad groups that do have ads but don't have
// any mobile preferred ads.
// If this is not wanted, set to false.

var findAdGroupsWithNoNegatives = true;
// Set this to true to get a list of ad groups which contain broad match
// keywords but have no negatives.
// If this is not wanted, set to false.


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions

function main() {
  var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");
  var sheet = spreadsheet.getSheets()[0];
  var campaignIds = getCampaignIds();
  
  if (typeof maximumKeywordsPerAdGroup != "number") {
    throw("Problem with maximumKeywordsPerAdGroup: the given value '" + maximumKeywordsPerAdGroup + "' does not appear to be a number.");
  }
  
  if (typeof minimumAdsPerAdGroup != "number") {
    throw("Problem with minimumAdsPerAdGroup: the given value '" + minimumAdsPerAdGroup + "' does not appear to be a number.");
  }
  
  adGroupChecking(campaignIds, sheet);
  Logger.log("Finished ad group checks.");
  
}

// 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);
    return spreadsheet;
  } catch (e) {
    throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
  }
}

// Get the IDs of campaigns which match the given options
function getCampaignIds() {
  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 adTextReport = AdWordsApp.report(
      "SELECT CampaignId " +
      "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
      whereStatementsArray[i] +
      "DURING LAST_30_DAYS");
    
    var rows = adTextReport.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;
}

// Prints an array of rows into the spreadsheet
function printRows(sheet, title, headers, rows) {
  try {
    sheet.getRange("R" + (sheet.getLastRow()+2) + "C1").setValue(title);
    sheet.getRange("R" + sheet.getLastRow() + "C1").clearFormat();
    sheet.getRange("R" + sheet.getLastRow() + "C1").setFontWeight("bold");
	
    if (rows.length == 0) {
      sheet.appendRow(["No issues found"]);
      sheet.getRange("R" + sheet.getLastRow() + "C1").clearFormat();
      Logger.log("Nothing to output for '" + title + "'");
      return;
    }
    
    if (headers.length > 0) {
      sheet.appendRow(headers);
      sheet.getRange("R" + sheet.getLastRow() + "C1:R" + sheet.getLastRow() + "C" + headers.length).clearFormat();
      sheet.getRange("R" + sheet.getLastRow() + "C1:R" + sheet.getLastRow() + "C" + headers.length).setFontStyle("italic");
    }
    
    var lastRow = sheet.getLastRow();
    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+rows.length)
    + "C" + (rows[0].length) ).setValues(rows);
    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+rows.length)
    + "C" + (rows[0].length) ).clearFormat();
    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+rows.length)
    + "C" + (rows[0].length) ).setNumberFormat("#,###,##0");
    
    Logger.log("Printed " + rows.length + " rows for '" + title + "'");
    
  } catch (e) {
    Logger.log("Printing rows '" + title + "' failed: " + e);
    
    if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
      try {
        sheet.appendRow(["Not enough space to write the data."]);
      } catch (e2) {
        Logger.log("Error writing 'not enough space' message: " + e2);
      }
    }
  }
}

// Returns an array mapping the ad group ID to the number of entities found in the ad group.
// reportType and extraWhereStatements are used to specify the type of entity.
// campaignIds specifies which campaigns to look in.
function getNumberOfEntitiesPerAdGroup(reportType, extraWhereStatements, campaignIds) {
  var numberOfEntities = {};
  
  var report = AdWordsApp.report(
    "SELECT AdGroupId " +
    "FROM   " + reportType + " " + 
    "WHERE AdGroupStatus = ENABLED " +
    extraWhereStatements +
    "AND CampaignId IN [" + campaignIds.join(",") + "] " +
    "DURING LAST_30_DAYS");
  
  var rows = report.rows();
  
  while (rows.hasNext()) {
    var row = rows.next();
    if (numberOfEntities[row["AdGroupId"]] == undefined) {
      numberOfEntities[row["AdGroupId"]] = 1;
    } else {
      numberOfEntities[row["AdGroupId"]]++; 
    }
  }
  
  return numberOfEntities;
}

// Finds ad groups without the right number of ads or keywords,
// and outputs these to a spreadsheet
function adGroupChecking(campaignIds, sheet) {
  
  // Get the arrays mapping ad group IDs to the number of ads and
  // keywords those groups contain
  if (ignorePausedAds) {
    var adStatus = "AND Status = ENABLED ";
  } else {
    var adStatus = "AND Status IN [ENABLED,PAUSED] ";
  }
  var numberOfAds = getNumberOfEntitiesPerAdGroup("AD_PERFORMANCE_REPORT", adStatus + "AND CombinedApprovalStatus != DISAPPROVED ", campaignIds);
  Logger.log("Found ad groups with ads.");
  
  if (ignorePausedKeywords) {
    var keywordStatus = "AND Status = ENABLED ";
  } else {
    var keywordStatus = "AND Status IN [ENABLED,PAUSED] ";
  }
  var numberOfKeywords = getNumberOfEntitiesPerAdGroup("KEYWORDS_PERFORMANCE_REPORT", keywordStatus + "AND ApprovalStatus != DISAPPROVED AND IsNegative = FALSE ", campaignIds);
  Logger.log("Found ad groups with keywords.");
  
  if (findAdGroupsWithoutMobileAds) {
    // DevicePreference = 30001 means the ads are mobile preferred
    var numberOfMobileAds = getNumberOfEntitiesPerAdGroup("AD_PERFORMANCE_REPORT", adStatus + "AND CombinedApprovalStatus != DISAPPROVED AND DevicePreference = 30001 ", campaignIds);
    Logger.log("Found ad groups with mobile ads.");
  }
  
  if (findAdGroupsWithNoNegatives) {
    // First get the number of broad match keywords in each ad group,
    // and the number of ad group level negatives
    var numberOfBroadKeywords = getNumberOfEntitiesPerAdGroup("KEYWORDS_PERFORMANCE_REPORT", keywordStatus + "AND ApprovalStatus != DISAPPROVED AND IsNegative = FALSE AND KeywordMatchType = BROAD ", campaignIds);
    var numberOfGroupNegatives = getNumberOfEntitiesPerAdGroup("KEYWORDS_PERFORMANCE_REPORT", "AND Status = ENABLED AND IsNegative = TRUE ", campaignIds);
    
    // Get the number of campaign level negatives in each campaign
    var numberOfCampaignNegatives = {};
    var report = AdWordsApp.report(
      "SELECT CampaignId " +
      "FROM   CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " + 
      "WHERE  IsNegative = TRUE " +
      "AND CampaignId IN [" + campaignIds.join(",") + "]");
    var rows = report.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      if (numberOfCampaignNegatives[row["CampaignId"]] == undefined) {
        numberOfCampaignNegatives[row["CampaignId"]] = 1;
      } else {
        numberOfCampaignNegatives[row["CampaignId"]]++; 
      }
    }
    
    // Get the number of negatives in each shared set
    var numberOfNegativesInSharedSets = {};
    var sharedSetReport = AdWordsApp.report(
      "SELECT Name, MemberCount " +
      "FROM   SHARED_SET_REPORT " +
      "WHERE Status = ENABLED AND Type = NEGATIVE_KEYWORDS " +
      "AND ReferenceCount != 0 AND MemberCount != 0");
    var rows = sharedSetReport.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      numberOfNegativesInSharedSets[row["Name"]] = parseInt(row["MemberCount"],10);
    }
    
    // Add the number of negatives from a shared set to the number recorded
    // for each campaign the shared set is used by.
    var campaignSharedSetReport = AdWordsApp.report(
      "SELECT CampaignId, SharedSetName " +
      "FROM   CAMPAIGN_SHARED_SET_REPORT " +
      "WHERE Status = ENABLED AND SharedSetType = NEGATIVE_KEYWORDS " +
      "AND CampaignId IN [" + campaignIds.join(",") + "]");
    var rows = campaignSharedSetReport.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      if (numberOfNegativesInSharedSets[row["SharedSetName"]] != undefined && numberOfNegativesInSharedSets[row["SharedSetName"]] > 0) {
        if (numberOfCampaignNegatives[row["CampaignId"]] == undefined) {
          numberOfCampaignNegatives[row["CampaignId"]] = numberOfNegativesInSharedSets[row["SharedSetName"]];
        } else {
          numberOfCampaignNegatives[row["CampaignId"]] += numberOfNegativesInSharedSets[row["SharedSetName"]];
        }
      }
    }
    Logger.log("Found ad groups and campaigns with negatives.");
  }
  
  // These record the rows to be written to the spreadsheet.
  var adGroupsWithoutKeywords = [];
  var adGroupsWithTooManyKeywords = [];
  var adGroupsWithoutAds = [];
  var adGroupsWithTooFewAds = [];
  var adGroupsWithoutMobileAds = [];
  var adGroupsWithoutNegatives = [];
  
  // Go through each ad group, and check the number of entities
  // If the number of entities is undefined, then there are 0 of those
  // entities within the ad group.
  var adGroupReport = AdWordsApp.report(
    "SELECT AdGroupId, CampaignId, CampaignName, AdGroupName " +
    "FROM   ADGROUP_PERFORMANCE_REPORT " +
    "WHERE AdGroupStatus = ENABLED " +
    "AND CampaignId IN [" + campaignIds.join(",") + "] " +
    "DURING LAST_30_DAYS");
  var rows = adGroupReport.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    
    if (numberOfKeywords[row["AdGroupId"]] == undefined) {
      adGroupsWithoutKeywords.push([row["CampaignName"],row["AdGroupName"]]);
    } else if (numberOfKeywords[row["AdGroupId"]] > maximumKeywordsPerAdGroup) {
      adGroupsWithTooManyKeywords.push([row["CampaignName"],row["AdGroupName"], numberOfKeywords[row["AdGroupId"]]]);
    }
    
    if (numberOfAds[row["AdGroupId"]] == undefined) {
      adGroupsWithoutAds.push([row["CampaignName"],row["AdGroupName"]]);
    } else if (numberOfAds[row["AdGroupId"]] < minimumAdsPerAdGroup) {
      adGroupsWithTooFewAds.push([row["CampaignName"],row["AdGroupName"], numberOfAds[row["AdGroupId"]]]);
    }
    
    if (findAdGroupsWithoutMobileAds && numberOfAds[row["AdGroupId"]] != undefined && numberOfMobileAds[row["AdGroupId"]] == undefined) {
      adGroupsWithoutMobileAds.push([row["CampaignName"],row["AdGroupName"]]);
    }
    
    if (findAdGroupsWithNoNegatives &&
        numberOfBroadKeywords[row["AdGroupId"]] != undefined &&
        numberOfGroupNegatives[row["AdGroupId"]] == undefined &&
        numberOfCampaignNegatives[row["CampaignId"]] == undefined
       ) {
      adGroupsWithoutNegatives.push([row["CampaignName"],row["AdGroupName"],numberOfBroadKeywords[row["AdGroupId"]]]);
    }
    
  }
  
  // Create a summary, with the number of ad groups found.
  var summary = [];
  summary.push(["Ad Groups With No Keywords", adGroupsWithoutKeywords.length]);
  summary.push(["Ad Groups With No Ads", adGroupsWithoutAds.length]);
  summary.push(["Ad Groups With More Than " + maximumKeywordsPerAdGroup + " Keywords",adGroupsWithTooManyKeywords.length]);
  summary.push(["Ad Groups With Fewer Than " + minimumAdsPerAdGroup + " Ads", adGroupsWithTooFewAds.length]);
  if (findAdGroupsWithoutMobileAds) {
    summary.push(["Ad Groups With No Mobile Preferred Ads", adGroupsWithoutMobileAds.length]);
  }
  if (findAdGroupsWithNoNegatives) {
    summary.push(["Ad Groups With Broad Keywords But No Negatives", adGroupsWithoutNegatives.length]);
  }
  printRows(sheet, "Summary", [], summary);
  
  
  // Output the ad group names
  var headers = ["Campaign", "Ad Group"];
  printRows(sheet, "Ad Groups With No Keywords", headers, adGroupsWithoutKeywords);
  printRows(sheet, "Ad Groups With No Ads", headers, adGroupsWithoutAds);
  
  var headers = ["Campaign", "Ad Group", "Number of Keywords"];
  printRows(sheet, "Ad Groups With More Than " + maximumKeywordsPerAdGroup + " Keywords", headers, adGroupsWithTooManyKeywords);
  
  var headers = ["Campaign", "Ad Group", "Number of Ads"];
  printRows(sheet, "Ad Groups With Fewer Than " + minimumAdsPerAdGroup + " Ads", headers, adGroupsWithTooFewAds);
  
  if (findAdGroupsWithoutMobileAds) {
    var headers = ["Campaign", "Ad Group"];
    printRows(sheet, "Ad Groups With No Mobile Preferred Ads", headers, adGroupsWithoutMobileAds);
  }
  
  if (findAdGroupsWithNoNegatives) {
    var headers = ["Campaign", "Ad Group", "Number of Broad Keywords"];
    printRows(sheet, "Ad Groups With Broad Keywords But No Negatives", headers, adGroupsWithoutNegatives);
  }
}

it would be better if the ad text limits consider using DKI or ad customizer. Also labeling campaigns to ignore is not the best idea, as there could be new ad groups created in one of these labeled campaigns. Just saying!

doesn't work on MMC level. Getting "No campaigns found with the given settings" error in the logs first 3 secs after running the script.