Number of Ads per Ad Group in Auto-Generated Google Sheet

494 views
Skip to first unread message

Alberto Ayala

unread,
Nov 3, 2016, 2:55:35 PM11/3/16
to AdWords Scripts Forum
Hi everyone,

I am trying to run a script to know the number of ads per ad group and if there is any ad group with no ads.
The following script does what I have described but it needs the user to update the URL every time I want to run it.

http://searchengineland.com/heres-adwords-script-check-empty-ad-groups-249034

How could I tell the system to automatically create a new sheet and get the link?
I believe it's simple, but I am quite new to scripts and haven't figured it out yet.

Thank you in advance!

The information contained in this communication is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by responding to this email and then delete it from your system. Lion&Lion is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt.

Tyler Sidell (AdWords Scripts Team)

unread,
Nov 3, 2016, 4:29:14 PM11/3/16
to AdWords Scripts Forum
Hi Alberto,

To get started you could use this simple script to output the amount of ads per ad group.  

function main() {
  var ss = 'SPREADSHEET_URL';
  var sheet = SpreadsheetApp.openByUrl(ss).getActiveSheet();
  var ag = AdWordsApp.adGroups().get();  
  while (ag.hasNext()) {
    var a = ag.next();
    Logger.log(a.getName() + ": " + a.ads().get().totalNumEntities());
    sheet.appendRow([a.getName(),a.ads().get().totalNumEntities()]);
}
}

You would just need to output the spreadsheet url above.  Let us know if you have any questions.

Thanks,
Tyler Sidell
AdWords Scripts Team

Alberto Ayala

unread,
Nov 3, 2016, 11:16:36 PM11/3/16
to AdWords Scripts Forum
Hi Tyler,

Thank you for your reply. 

Truth is the script that I posted has a couple of conditions that I would like to keep, for example, getting only the ad groups which have more than two ads, and excluding those that are paused. - I didn't specify it in the previous message, sorry.

Keeping the original script, what it would be very helpful, is if I could get an email only if the script finds:
1. Ad group with no ads
2. Ad group with more than two ads

And the email would give me the URL to the report.
I've seen in other scripts, but haven't been able to do it myself.

Thank you for your help!

/**
*
* 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.0
* Google AdWords Script maintained by brainlabsdigital.com
*
**/

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

// 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 = 5;
// An ad group will be reported if it has more than this number of keywords.

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

var findAdGroupsWithoutMobileAds = false;
// 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 = false;
// 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 maxAdsPerAdGroup != "number") {
    throw("Problem with minimumAdsPerAdGroup: the given value '" + maxAdsPerAdGroup + "' 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 CreativeApprovalStatus != 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 CreativeApprovalStatus != 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"]] > maxAdsPerAdGroup) {
      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 Greater Than " + maxAdsPerAdGroup + " 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 Greater Than " + maxAdsPerAdGroup + " 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);
  }
}

Tyler Sidell (AdWords Scripts Team)

unread,
Nov 4, 2016, 11:02:10 AM11/4/16
to AdWords Scripts Forum
Hi Alberto,

As the original script is a third-party one written by a different party, have you reached out to Search Engine Land as well?  You can use the MailApp class to send an email with a link to the report.  For instance using the sendEmail() method will help you send an attachment with this information.  The documentation has the following example to get you started:

// Send an email with two attachments: a file from Google Drive (as a PDF) and an HTML file.
var file = DriveApp.getFileById('1234567890abcdefghijklmnopqrstuvwxyz');
var blob = Utilities.newBlob('Insert any HTML content here', 'text/html', 'my_document.html');
MailApp.sendEmail('em...@email.com', 'Attachment example', 'Two files are attached.', {
name: 'Automatic Emailer Script',
attachments: [file.getAs(MimeType.PDF), blob]
});

You can also add the following line to the original script to have it email you the report:

adGroupChecking(campaignIds, sheet);
Logger.log("Finished ad group checks.");
MailApp.sendEmail('EMAIL ADDRESS', 'Ad Group Report', 'Spreadsheet found at: ' + spreadsheetUrl);

Thanks,
Tyler Sidell
AdWords Scripts Team
Reply all
Reply to author
Forward
0 new messages