This AdWords MCC Script Will Save You An Hour Every Day!

If you’ve kept count of how many of my posts during the past year have touched on AdWords Scripts (6 out of 16), you’ll know how useful I believe they can be to streamline repetitive tasks in AdWords. I use them all the time for accounts I manage; and without them, I simply couldn’t be […]

Chat with SearchBot

If you’ve kept count of how many of my posts during the past year have touched on AdWords Scripts (6 out of 16), you’ll know how useful I believe they can be to streamline repetitive tasks in AdWords. I use them all the time for accounts I manage; and without them, I simply couldn’t be as efficient as I am now.

Because I’ve become so reliant on Scripts, I’ve secretly been worried that one day they might face the same fate as some other Google products that get cancelled during their annual spring cleaning.

So imagine my excitement when I read the announcement from Google that AdWords Scripts are now in beta for My Client Center (MCC) accounts. It seems to have hit some critical mass to deserve this promotion to the big leagues of AdWords, the MCC interface.

Below are my initial thoughts about MCC Scripts and some ways I think they can be of use. And as always, no post of mine about Scripts is complete without a juicy example, so read all the way to the end for a Script that should save you an hour every day for AdWords MCC reporting.

Sign up for the beta to get AdWords Scripts added to your AdWords My Client Center

Sign up for the beta to get AdWords Scripts added to your AdWords My Client Center

What Are MCC Scripts?

AdWords Scripts: many of you have tried some Scripts by now, but if you’re new to them, here’s a quick primer:

Scripts let advertisers manage their accounts programmatically, but unlike the API, they don’t require you to maintain your own servers. Everything is hosted directly inside AdWords where you can start coding immediately. If you don’t code but you have a script that you found somewhere on the internet, you can copy-and-paste it into any account you manage and have the automation running in minutes.

MCC: for those of you managing a single AdWords account, you may not be familiar with My Client Center accounts, or MCC for short. When we built this at Google back in 2004, the goal was to give advertisers that managed multiple accounts a simple way to use one login to access all the accounts they managed. Today, it’s much more evolved and includes cross account reporting, budget management, a dashboard, multiple levels of hierarchy and labels for keeping accounts organized and consolidated notifications.

And what happens when you combine the two? (Angels singing in the background…)

AdWords Scripts + MCC = Saving Tons of Time!

Whether your primary need is to run sophisticated reports or use a consistent account management methodology across many accounts, MCC Scripts are sure to be a time saver, and I’ll give some examples below.

What Can Be Automated With MCC Scripts

Let me start with the bad news first: AdWords Scripts have more limited capabilities than the API, so they can’t do everything you may want to; for example, they can’t be used to manage remarketing or display campaigns. Maybe now that MCC Scripts are out, Google will move on to support bid strategies, PLAs and GDN campaigns in future versions.

Now the good news: Scripts can access all reports and can do a lot of management on search ads, including modifying budgets, setting sitelinks, changing targeting criteria and much more. The full reference of what they can do is on Google’s Developer’s site.

With these capabilities, there are a tremendous number of repetitive and tedious tasks that can be automated. Here are just a handful of examples:

  • Find ads with broken URLs and pause them
  • Create a report showing ad groups with only 1 ad text, those that aren’t doing any ad text testing
  • Increase bids for keywords related to ice cream when the temperature goes above 80 degrees

The obvious benefit of MCC-level scripts is that you can run these same automations more easily for the many accounts you manage. Rather than maintaining the code in each managed account, now you can maintain the code in an MCC account and run it for all linked accounts under that MCC. Not having to worry about keeping the code in sync on many accounts will save you a lot of time.

Automations That Span Multiple Accounts

But, a less obvious benefit is that you can start to do automations that rely on data in multiple accounts. For example, say your company’s search marketing program encompasses so many different divisions that you have multiple accounts.

Have you checked if some of these accounts are competing against one another for the same keywords? With an MCC-level script, finding duplicate keywords across many accounts all of a sudden becomes a task that can be more easily programmed and scheduled to run on a schedule so that you’ll always stay on top of duplicate keywords.

Or, how about generating a report from data that spans multiple accounts? In the past you could have run reports for each account through Scripts, but then you would have had to use another piece of code to aggregate all the results. Now, you can create all the code in a single Script, and host it in AdWords which is a more elegant solution.

Here are a few ideas for scripts that use data from multiple accounts:

  • Find the best performing call-to-action from the ad texts used across several accounts for one company
  • Maintain a shared negative keyword list for all accounts under an MCC
  • Create a dashboard in Google Sheets that aggregates data from campaigns with the same label in many accounts

Problems Solved By MCC Scripts

Here’s a quick recap of the issues that MCC Scripts resolves:

  • If you make a change to a Script, you can make it once in your MCC account; whereas in the past, you had to make the same change in each account that used the Script
  • You get an overview of the status and logs of all your Scripts in your MCC account and you no longer need to drill down to each managed account to see which Scripts are running

Limitations Of MCC Scripts

I wish MCC Scripts were the end-all, be-all solution, but that’s wishful thinking. Here are some of the remaining limitations and some new problems they introduce:

 Scripts can only run for 30 minutes, and that’s often not enough time to do all the work, especially for a Script that makes changes to an account. MCC Scripts can do work in parallel and they get an additional 30 minutes to wrap up and consolidate all the work done in individual accounts, but that’s still a limit which you’ll have to think about anytime you write a more complex Script.

• The way MCC Scripts are set up, they assume you’ll want to do the exact same thing in all the accounts under the MCC. For reporting, that’s often not an issue, but it’s more problematic for bid and budget management scripts.

Say you have a Script that updates budgets to help you reach a target budget at the end of the month. It’s unlikely that all your accounts have the exact same target so now you need to set up a way to run the same Script, but with different settings for each account. We normally store settings in a Google Sheet to achieve this.

• A related issue is that when your Script calls the executeInParallel function, it can’t pass in any variables. So the function called by executeInParallel needs to connect to a place where you store settings for each account. In other words, you have to maintain a Google Sheet where each row includes the account ID and the setting, like a target budget for that account.

• When running the same Script on multiple accounts using executeInParallel, there is a 50 account limit at a time. When you want to do the same work on more than 50 accounts, you have to break up that job into smaller chunks.

One way is to use Labels to select only a subset of accounts; or, our preferred method is to use a Google Sheet or the Optmyzr database to store when a Script last ran. Then next time it runs, we can prioritize the oldest accounts first.

So that’s the lowdown on MCC Scripts from someone who’s been writing AdWords Scripts for a long time (at least in internet years).

Sample Script: MCC Performance Dashboard

To whet your appetite, here’s a Script which builds a nice little dashboard for all your accounts and which shows how yesterday’s spend compares to the day before and to the same day last week. It does the same for weekly and monthly spend and puts it into a Google Sheet. You can use the basic version below, but we’ll post updated versions in our AdWords Script library.

The output from our script that compares recent account performance to previous periods.

The output from our script that compares recent account performance to previous periods.

This Script was built in less than day. We have an account dashboard in our Optmyzr toolset, but building this into the tool would have taken a few days. With Scripts, I had the idea for it and by the evening we had a working version. And that’s exactly what makes Scripts so powerful for us internet marketers… we need to act fast to be good, and AdWords Scripts helps us do that!

Here’s the code:

function main() {
 var newSpreadSheet = SpreadsheetApp.openByUrl("put a link to a Google Sheet 
     here - this is where the results will be placed");
var numOfSheets = newSpreadSheet.getSheets(); 
 if(numOfSheets.length>0){
 for(var i=1,len=numOfSheets.length;i<len;i++)
 newSpreadSheet.deleteSheet(numOfSheets[i]);
 }
 newSpreadSheet.getActiveSheet().clear().setName("Last 1 Day");

 var allHeaders = ["Conversions","Impressions","Clicks","Cost","ConversionValue"];
 var headerIndexes = {};
 var headerString = "";
 var daysForMonth = 28;
 var sheet = newSpreadSheet.getActiveSheet();

 var tempIndex = 3;

 for(var i=0,len=allHeaders.length;i<len;i++){
 sheet.activate();
 sheet.getRange(1,tempIndex).setValue(allHeaders[i]);

 headerIndexes[allHeaders[i]]=tempIndex;
 headerString=headerString+allHeaders[i]+", ";
 tempIndex = tempIndex+7;
 }

 sheet.getRange("1:1").setFontWeight("bold");
 sheet.appendRow(["Account Name","Account Id","","","difference","% diff"]);

 newSpreadSheet.duplicateActiveSheet().setName("Last 7 Days");
 newSpreadSheet.duplicateActiveSheet().setName("Last 30 Days");

 var allSheets = newSpreadSheet.getSheets();

 allSheets[1].setName("Last 7 Days");
 allSheets[2].setName("Last 30 Days");

 headerString = headerString.substring(0,headerString.length-2);

 Logger.log(headerIndexes);
 var datesForFirst=[];var dateForSecond=[];var dateForThird = [];
 var currentDate = new Date();var prevDate = new Date();var anotherPrevDate = new Date();
 var sheetIndex =0;

 //insert dates in the next rows 
 //for 1st sheet
 var fixedDate = new Date(Utilities.formatDate(new Date(),AdWordsApp.currentAccount()
     .getTimeZone(), "MMM dd,yyyy HH:mm:ss")); 

 var time = fixedDate.getTime() -(1 * 24 * 60 * 60 * 1000);
 fixedDate = new Date(time);

 time = fixedDate.getTime() -(1 * 24 * 60 * 60 * 1000);
 prevDate = new Date(time);
 time = fixedDate.getTime() -(7 * 24 * 60 * 60 * 1000);
 anotherPrevDate = new Date(time);
 datesForFirst.push(fixedDate);datesForFirst.push(prevDate);datesForFirst.push(anotherPrevDate);

 appendDates(sheetIndex, datesForFirst, 0); 

 //for second sheet
 sheetIndex = 1;
 time = fixedDate.getTime() -(7 * 24 * 60 * 60 * 1000);
 currentDate = new Date(time);
 time = fixedDate.getTime() -(14 * 24 * 60 * 60 * 1000);
 prevDate = new Date(time);
 time = currentDate.getTime() -(30 * 24 * 60 * 60 * 1000);
 anotherPrevDate = new Date(time);
 dateForSecond.push(currentDate);dateForSecond.push(prevDate);dateForSecond.push(anotherPrevDate);

 appendDates(sheetIndex, dateForSecond,7);

 //for third sheet
 sheetIndex = 2;
 time = fixedDate.getTime() -(daysForMonth * 24 * 60 * 60 * 1000);
 currentDate = new Date(time);
 time = fixedDate.getTime() -(daysForMonth*2 * 24 * 60 * 60 * 1000);
 prevDate = new Date(time);
 time = currentDate.getTime() -(daysForMonth*12 * 24 * 60 * 60 * 1000);
 anotherPrevDate = new Date(time);
 dateForThird.push(currentDate);dateForThird.push(prevDate);dateForThird.push(anotherPrevDate);

 appendDates(sheetIndex, dateForThird,daysForMonth);
 //dates inserted

 //get accounts and data respectively
 var accounts_iterator = MccApp.accounts().withCondition("Impressions>0").forDateRange("YESTERDAY").get();
 var current_mccaccount = AdWordsApp.currentAccount();
 var all_accounts=[];
 while(accounts_iterator.hasNext()){
 all_accounts.push(accounts_iterator.next());
 }

 Logger.log("no of accounts"+all_accounts.length);
 for(var i=0,len=all_accounts.length;i<len;i++){
 MccApp.select(all_accounts[i]);
 appendData(0,datesForFirst,0);
 appendData(1,dateForSecond,7);
 appendData(2,dateForThird,daysForMonth);

 for(var j=0;j<3;j++){
 var sheetCurrent = allSheets[j];

 sheetCurrent.activate();
 var lRow = sheetCurrent.getLastRow();
 for(var key in headerIndexes){
 var index = headerIndexes[key];
 var positiveColor = "green";
 var negativeColor = "red";
 if(key=="Cost"){
 positiveColor="red";
 negativeColor="green";
 }

 var firstVal = sheetCurrent.getRange(lRow,index).getValue();
 var secondVal = sheetCurrent.getRange(lRow,index+1).getValue();

 var diff = firstVal-secondVal;
 sheetCurrent.getRange(lRow, index+2).setValue(diff);
 var pcent = (diff/secondVal)*100;
 if(secondVal==0)
 pcent=firstVal*100;
 if(diff==0)
 pcent=0;

 sheetCurrent.getRange(lRow, index+3).setValue(pcent+"%");
 if(pcent>0){
 sheetCurrent.getRange(lRow, index+3).setFontColor(positiveColor);
 }
 else{
 sheetCurrent.getRange(lRow, index+3).setFontColor(negativeColor);
 }
 secondVal = sheetCurrent.getRange(lRow,index+4).getValue();
 var diff = firstVal-secondVal;
 sheetCurrent.getRange(lRow, index+5).setValue(diff);
 var pcent = (diff/secondVal)*100;
 if(diff==0)
 pcent=0;
 if(secondVal==0)
 pcent=firstVal*100;
 sheetCurrent.getRange(lRow, index+6).setValue(pcent+"%");
 if(pcent>0){
 sheetCurrent.getRange(lRow, index+6).setFontColor(positiveColor);
 }
 else{
 sheetCurrent.getRange(lRow, index+6).setFontColor(negativeColor);
 }
 }
 }
 }

 MailApp.sendEmail("[email protected]","Mcc accounts performance", 
     "Click this url -\n\n"+newSpreadSheet.getUrl());

 function appendData(indexForSheet,dateArray,days){

 currentSheet = allSheets[indexForSheet];
 currentSheet.activate();
 currentRow = currentSheet.getLastRow()+1;
 var date_range = "";
 var fieldGap = [0,1,4];
 for(var i=0,len=dateArray.length;i<len;i++){
 var toDate = dateArray[i];
 if(indexForSheet!=0){
 tempDate = dateArray[i].getTime()+(days * 24 * 60 * 60 * 1000);
 toDate = new Date(tempDate);

 }
 date_range = ""+Utilities.formatDate(dateArray[i], "PST", "yyyyMMdd")+",
     "+Utilities.formatDate(toDate, "PST", "yyyyMMdd");

 var report = AdWordsApp.report("SELECT "+headerString+ 
 " FROM ACCOUNT_PERFORMANCE_REPORT "+
 "DURING "+date_range);
 var rows = report.rows();
 while(rows.hasNext()){
 var row = rows.next();

 var currentIndex = 0;
 currentSheet.getRange(currentRow, 1).setValue(AdWordsApp.currentAccount().getName());
 currentSheet.getRange(currentRow, 2).setValue(AdWordsApp.currentAccount().getCustomerId());
 for(var key in headerIndexes){
 var index = headerIndexes[key];
 currentSheet.getRange(currentRow, index+fieldGap[i]).setValue(row[key]);
 }
 }
 }

 }

 function appendDates(sheetIndex, dateArray, days){
 currentSheet = allSheets[sheetIndex];
 currentSheet.activate();
 currentRow = currentSheet.getLastRow()+1;

 var date_range = [];

 for(var i=0,len=dateArray.length;i<len;i++){
 var toDate = dateArray[i];
 if(sheetIndex!=0){
 tempDate = dateArray[i].getTime()+(days * 24 * 60 * 60 * 1000);
 toDate = new Date(tempDate);
 date_range.push(""+Utilities.formatDate(dateArray[i], "PST", "MM/dd/yyyy")+" - 
     "+Utilities.formatDate(toDate, "PST", "MM/dd/yyyy"));
 }
 else{
 date_range.push(Utilities.formatDate(dateArray[i], "PST", "MM/dd/yyyy"));
 }
 }
 for(var key in headerIndexes){
 var index = headerIndexes[key];
 currentSheet.getRange(currentRow, index).setValue(date_range[0]);
 currentSheet.getRange(currentRow, parseInt(index)+1).setValue(date_range[1]);
 currentSheet.getRange(currentRow, parseInt(index)+4).setValue(date_range[2]);
 }
 }

}

Happy Scripting!


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Frederick Vallaeys
Contributor
Frederick (“Fred”) Vallaeys was one of the first 500 employees at Google where he spent 10 years building Google Ads and teaching advertisers how to get the most out of it as the first Google AdWords Evangelist. Today he is the Cofounder and CEO of Optmyzr, a PPC management SaaS company focused on making search, shopping, and display ads easier to manage with rules, scripts, reports, audits, and more. He is a frequent guest speaker at events where he inspires organizations to be more innovative and use AI and Automation Layering to become better marketers. His latest book, Unlevel the Playing Field, follows his best-seller, Digital Marketing in an AI World.

Get the must-read newsletter for search marketers.