Account script to count impressions weighted ads in campaigns and ad groups

This PPC script will help you count impression weighted avg. amount of ads per each campaign and ad group. The results will be shown in the spreadsheet report.

What it does

This ppc script downloads data from your account into the spreadsheet in the tab "input" and with QUERY organizes and filter data into 3 tables:

Overview All the campaigns in your account with impression weighted avg. number of ads. The column "# of processed ad groups" will tell you on how many ad groups this data is based on.
Campaigns that need most action Only the adgroups that have less than 3 ads, grouped on campaign level so you can see priority which campaigns need most attention.
Adgroups that need most action Ad groups with the issue arranged by the most spend.

When to use it

Pre-holiday seasonal check
Optimization of biggest adgroups
After changes in product ads auto-generators

Implementation

Make a copy of this spreadsheet to your GDriveFile > Make a copy https://docs.google.com/spreadsheets/d/1wz0m4CSv39k-cXmD0wwxAWb0MoF-PKMqeton_ETl5RY/edit?usp=sharing
Insert the URL of your new spreadsheet in the 1st line of the code
Authorize and run the script

Extra notes:

Script can manage about 1500 adgroups in 30 min
you can change "minImpressions" depending on your account size
you can un"//" line 33 if you want to go through one bid campaign with a lot of adgroups

Copy the codeand give it a try!

var spreadsheet = "YOUR_URL";          // add your URL here
var minImpressions = 200;              // analyse only adgroups with this minimum of impressions in last 30 days, put in the value that makes sense for your account
var time = "LAST_30_DAYS";



function main() {

  Logger.log("script starting to work...");


function findEmptyRow(sheet) {
  var dates = sheet.getRange(1, 1, 10000, 1).getValues();
  for (var emptyDate = 0; emptyDate < dates.length; emptyDate ++) {
    if (dates[emptyDate][0].length == 0) {
      return emptyDate;

  }
}
}


var sheet = SpreadsheetApp.openByUrl(spreadsheet).getSheetByName('input');
    sheet.clearContents();
  sheet.appendRow(['Campaign Name','Adgroup Name','Impressions','Ad count', 'Cost', '1 for groups with 1-2 ads','Impr by AdCount']);




  var adgroups = AdWordsApp.adGroups()
.withCondition("Status = ENABLED")
.withCondition("CampaignStatus = ENABLED")
//.withCondition("CampaignName CONTAINS 'YOUR_CAMPAIGN'")
.withCondition("Impressions >= " + minImpressions).forDateRange(time)
.get();

if (adgroups.totalNumEntities() > 5000 ) {
Logger.log("there seems to be over 5000 adgroups, consider increasing minImpressions to analyse");
}
  else {

Logger.log("putting data in the spreadsheet...");

while (adgroups.hasNext()) {
var adgroup = adgroups.next();

var adCount = adgroup.ads().withCondition("Status = ENABLED").get().totalNumEntities();
var adgroupNAME = adgroup.getName();
var campaignNAME = adgroup.getCampaign().getName();
var impressions = adgroup.getStatsFor(time).getImpressions();
var cost = adgroup.getStatsFor(time).getCost();
var imprXadcount = adCount * impressions;

  if (adCount == 1 || adCount == 2) {
var checking1or2 = 1}
  else {
var checking1or2 = 0;}



var emptyRow = findEmptyRow(sheet);
var range = sheet.getRange(emptyRow + 1, 1, 1, 10);
var row = range.getValues();

  row[0][0] = campaignNAME;
  row[0][1] = adgroupNAME;
  row[0][2] = impressions;
  row[0][3] = adCount;
  row[0][4] = cost;
  row[0][5] = checking1or2;
  row[0][6] = imprXadcount;

  range.setValues(row);

  }
}