MCC script: ROAS and CPA trends for all accounts

This script is really great for daily overview of your account’s performance.

Key functions:

ROAS trends for yesterday, 7 days, 30 days for each account.
Color indication if performance(ROAS or CPA) does not meet set targets in each date range.
Performance information for conversions, cost, and impression share metrics.
View the most important performance metrics side by side over 3 different time periods.
Linear prediction of your cost at the end of the month. You can set-up the configuration for ROAS and Cost in the Settings list.
Uses MCC parallel function. You can process up to 100 accounts per run.

How to install script:

Copy our template, input your Target ROAS and Budgets for each account in the Settings list.
Insert the URL of your spreadsheet in line 1 of the script inside the brackets.
Create a label in your MCC and then add the label to the accounts which you would like to see in the report.
Put the name of your label on line 7 of the script after “CONTAINS” inside the brackets.
Authorize script and set frequency to 1 hour.
If you’d like to change the settings, just type the name of the account and change the cost of the target ROAS and it will be adjusted automatically.

mcc-script-for.gs

/* SETTINGS */

//Your own copy of our template - https://docs.google.com/spreadsheets/d/1yKcxyBJU-p5TIDaD3ynWENyGdlY6wHZs1CfLB4reUxQ/edit#gid=71729685
var SPREADSHEET_URL = "";

var SHEET_NAME = "Example";
var ACCOUNT_LABEL = "Lynt: eshop";

/* ------ */

function main() {
  
  MccApp.accounts().withCondition("LabelNames CONTAINS '"+ACCOUNT_LABEL+"'").withLimit(50).executeInParallel('processAccount', 'allFinished');
  
}


function getAccount(oneAccount) {
  
        var CurrentAccountNum = oneAccount.getCustomerId();
      	var CurrentAccountName = oneAccount.getName();
       
        var data = GetData();
        
        var today = new Date();
        var MonthDays = [31,28,31,30,31,30,31,31,30,31,30,31];
	    var thisMonthDays = MonthDays[today.getMonth()];
        var currentDays = today.getDate();
        
     	var output = [
          CurrentAccountName,
          CurrentAccountNum,
          //ROAS
      		_f(data['YESTERDAY'].AllConversionValue) / _f(data['YESTERDAY'].Cost),
      		_f(data['LAST_7_DAYS'].AllConversionValue) / _f(data['LAST_7_DAYS'].Cost),
      		_f(data['LAST_30_DAYS'].AllConversionValue) / _f(data['LAST_30_DAYS'].Cost),
          //ConversionValue
      		_f(data['YESTERDAY'].AllConversionValue),
      		_f(data['LAST_WEEK'].AllConversionValue),
      		_f(data['THIS_MONTH'].AllConversionValue),
      		_f(data['LAST_MONTH'].AllConversionValue),
          //Conversion
       		_f(data['YESTERDAY'].AllConversions),
      		_f(data['LAST_WEEK'].AllConversions),
      		_f(data['THIS_MONTH'].AllConversions),
      		_f(data['LAST_MONTH'].AllConversions),
            _f(data['LAST_14_DAYS'].AllConversions)-_f(data['LAST_7_DAYS'].AllConversions) ,
          //Cost
       		_f(data['YESTERDAY'].Cost),
      		_f(data['THIS_MONTH'].Cost),
      		_f(data['LAST_MONTH'].Cost),
      		_f(data['THIS_MONTH'].Cost) / (currentDays - 1) * thisMonthDays, //This month prediction
            _f(data['LAST_14_DAYS'].Cost)-_f(data['LAST_7_DAYS'].Cost) ,
          //Impression Share
      		_fp(data['THIS_MONTH'].SearchImpressionShare),
      		_fp(data['THIS_MONTH'].SearchRankLostImpressionShare),
      		_fp(data['THIS_MONTH'].SearchBudgetLostImpressionShare),
      		_fp(data['THIS_MONTH'].ContentImpressionShare),
      		_fp(data['THIS_MONTH'].ContentRankLostImpressionShare),
      		_fp(data['THIS_MONTH'].ContentBudgetLostImpressionShare),
          //Yesterday
      		_f(data['YESTERDAY'].Impressions),
      		_f(data['YESTERDAY'].Clicks),
      		_f(data['YESTERDAY'].AverageCpc),
      		_f(data['YESTERDAY'].Clicks) / _f(data['YESTERDAY'].Impressions),
      		_f(data['YESTERDAY'].AllConversionValue),
          ];
          return output;
}


function processAccount() {

 	 return JSON.stringify(getAccount(AdWordsApp.currentAccount()));
}


function allFinished(results) {
 
  var output = [];
  var sheet = createOrGetSheet(SpreadsheetApp.openByUrl(SPREADSHEET_URL),SHEET_NAME,0);
  
  
  for (var i = 0; i < results.length; i++) { output[i] = JSON.parse(results[i].getReturnValue()); }
   var mccAccountsIterator = MccApp.accounts().withCondition("LabelNames CONTAINS '"+ACCOUNT_LABEL+"'").get();
   if(mccAccountsIterator.totalNumEntities()>50) {
    Logger.log("More than 50 accounts - using altenative method");
    for(var i=0; mccAccountsIterator.hasNext(); i++) {
      if (i<50) continue;
      var oneAccount = mccAccountsIterator.next();
      MccApp.select(oneAccount);
      output[i] = getAccount(oneAccount);
    }
  }
  
    //clear the old report
  sheet.getRange(3,4,200,28).clearContent();
  Logger.log("Output to spreadsheet");
  output = output.sort(sort1st);
  sheet.getRange(3,4,output.length,output[0].length).setValues(output);
 
}


function GetData(){

  var dates = ['YESTERDAY', 'LAST_WEEK', 'THIS_MONTH', 'LAST_MONTH', 'LAST_7_DAYS', 'LAST_14_DAYS', 'LAST_30_DAYS'];
  var metrics = "AllConversions, AllConversionValue, Cost, SearchImpressionShare, SearchRankLostImpressionShare, \
   SearchBudgetLostImpressionShare, ContentImpressionShare, ContentRankLostImpressionShare, ContentBudgetLostImpressionShare, \
   Impressions, Clicks, AverageCpc, Ctr";
  
  var data = [];
  
  //PERFORMANCE_REPORT
  for (var i = 0; i < dates.length; i++){
    Logger.log("Getting data: "+AdWordsApp.currentAccount().getName()+" - "+dates[i]);
    data[dates[i]] = AdWordsApp.report("SELECT "+metrics+" FROM ACCOUNT_PERFORMANCE_REPORT DURING "+dates[i]).rows().next();
  }

  return data;
  
}


function createOrGetSheet(reportSpreadsheet, name, position) {
  var sheet = reportSpreadsheet.getSheetByName(name);
  if(sheet == null) {
    return reportSpreadsheet.insertSheet(name,position);
  } else {
    return sheet;
  }
}


function sort1st(a, b) {
    if (a[0] === b[0]) {
        return 0;
    }
    else {
        return (a[0] < b[0]) ? -1 : 1;
    }
}


function _f(data){
  //remove thousands separators
  return data.replace(/,/g, "") * 1;

}

function _fp(data){
  //remove percents sign and corrects "< 10%" string
  return data.replace("%","").replace("< 10",10) * 1 / 100;

}

More articles