MCC script to count avg. ads

This PPC script will help you count average amount of ads per each campaign for the whole MCC/Manager account. The results will be showed in the spreadsheet document.

WHEN TO USE IT

quick check for auditing multiple accounts
noticing potential fuck up in your own campaign across clients
quick check of active ads after a campaign/account launch while you wait on approvals

HOW TO USE IT

To make it work – paste the code, change URL in the var spreadsheet to a URL of your own spreadsheet.

Run the script and watch data going into the spreadsheet.

After the script is done, apply filtering in the document to focus on accounts with most spent and least ads per campaign.

SO COPY THE SCRIPTAND GIVE IT A TRY!

function main() {

var time = "LAST_7_DAYS";
var spreadsheet = "https://docs.google.com/spreadsheets/d/abracadabra"


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


var accountIterator = AdsManagerApp.accounts().get();
var sheet = SpreadsheetApp.openByUrl(spreadsheet).getActiveSheet();
  	sheet.clearContents();
  	sheet.appendRow(['Account', 'CampaignName', 'Impressions', 'Cost', 'Avg Ads']);


while (accountIterator.hasNext()) {
var account = accountIterator.next();
var accountName = account.getName();
AdsManagerApp.select(account)

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

var campaigns = AdsApp.campaigns().withCondition("Status = ENABLED").get();


while (campaigns.hasNext()) {
var campaign = campaigns.next();
var campaignName = campaign.getName();
var campaignImpr = campaign.getStatsFor(time).getImpressions();
var campaignCost = campaign.getStatsFor(time).getCost();

var adCountInCampaign = campaign.ads()
	.withCondition("AdGroupStatus = ENABLED")
	.withCondition("Status = ENABLED")
	.withCondition("CombinedApprovalStatus NOT_IN [DISAPPROVED, UNDER_REVIEW]")
	.get().totalNumEntities();
var adGroupCountInCampaign = campaign.adGroups().withCondition("Status = ENABLED").get().totalNumEntities();

  if (adGroupCountInCampaign === 0) {
var avgAdsR = 0; }
  else {
var avgAds = adCountInCampaign / adGroupCountInCampaign;
var avgAdsR = Math.round(avgAds);


var emptyRow = findEmptyRow(sheet);

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

  row[0][0] = accountName;
  row[0][1] = campaignName;
  row[0][2] = campaignImpr;
  row[0][3] = campaignCost;
  row[0][4] = avgAdsR;

  range.setValues(row);




}
}
    Logger.log(accountName + "  done");
}
}