Automatically Put Pivoted AdWords Data in a Spreadsheet

I've written several free scripts that export AdWords data to a Google spreadsheet but the one I'll share below does something a little more and pivots the reports so that segment data shows up in columns rather than rows.

The Problem With Google's AdWords Reports

Here's what the typical output from an AdWords report looks like if you use a script like this one to add it in a Google sheet.

AdWords report data that was automatically added to a Google spreadsheet. The report contains keyword conversion data segmented by conversion type name and day of week, resulting in multiple rows of data for each keyword.

I highlighted the cells for the same keyword ID in green so that you can easily see that there are 7 rows of data for the same keyword. Each row has different values for the segments 'ConversionType' and 'DayOfWeek'.

Here's why I don't like getting my reports this way... In a typical AdWords optimization, I might evaluate a keyword to see if it meets certain criteria, and when it does, change the bid. For example, for keywords with more than 5 total "Sign Up" conversions, and at least 2 of those conversions on today's day of the week,  I might raise the bid.

This is not easy to do with the spreadsheet above because I can't simply build a formula for each row as that would ignore the data from the other 6 rows about the same entity.

A Better Format for AdWords Reports

To get the report the way I want, I need to do some vlookups and data aggregation so that all the data for a keyword is moved onto a single row. This is what my spreadsheet should look like to make optimizing easy:

AdWords report data that is aggregated by entities. Here there is one row for every keyword along with one column for every combination of metrics and segments.

Now you can see there is only 1 row for that same keyword ID as before (highlighted in green). Now I can write a spreadsheet formula that has access to all the elements I need on one row.

How Scripts Solved This Problem

To get the spreadsheet output in the format I wanted, I wrote a script that finds all possible values for the included segments and then combines this with the different metrics. For example, possible values for the DayOfWeek segment are 'Monday', 'Tuesday', 'Wednesday', etc. Combining this with my metrics, I get Monday.Conversions, Monday.AllConversions, etc. Each of these is then treated as a column in the sheet.

So now I can more easily do my optimization. I could also feed this sheet into a tool like Optmyzr's Rule Engine so that I could start automating account management based on factors like day of week, type of conversion, or any other segment I include on the sheet. Optmyzr is my company and also runs this blog.

How to run this script:

After installing the script code that's all the way at the bottom of this post into an AdWords account (a child account, not an MCC account), you can edit the following settings to customize it:

1. attributes: these are the AdWords reporting attributes. You will get a single spreadsheet row per unique combination of attributes
2. segments: these are the AdWords reporting segments. The values of each segment will be joined with metrics and create 1 column for each combination
3. metrics: the AdWords reporting metrics to include.
4. sourceReport: the report type from Google. See the link below for more information
5. spreadsheetUrl: the Url of the Google spreadsheet that this script will update.
6. tabName: the name of the sheet (tab) in the spreadsheet that should be updated.
7. reportVersion: the version of the AdWords API reports you’re using. The data available in the Ads API changes periodically so this ensures our script talks to the right version of AdWords.

Where to Find Valid Settings for the Script

Here is the list of acceptable attributes, segments and metrics for the keywords performance report:
https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report. As you can see, Google makes it really easy to see what are attributes vs. segments vs metrics. Just be sure to populate the right settings in the script based on what type of data you're adding.

So for example, if you wanted to add ApprovalStatus to the report, you'd put add it to the comma separated list of attributes as seen here:

Here is the full list of all AdWords reports with the available attributes, segments, and metrics to help you create correct settings.

One thing to watch out for:

Note that in the resulting spreadsheet, you can't total up the numbers across a row because metrics are included multiple times, once per included segment. For example, in a report with the 2 segments DayOfWeek and ConversionType, if a keyword has 10 conversions, then the sum of all the conversion columns for that row will be 20 (10 conversions * 2 segments).

Another Popular Script For Putting AdWords Data in Spreadsheets

Last June I wrote a script that makes it super easy to do custom data pulls from AdWords and put the data in a spreadsheet so I was happy to hear people still talking about this script at SMX West in March. You can find it on Search Engine Land if you're looking for more options to get AdWords data in spreadsheets without paying someone for it.

Working with Experiment Campaigns in AdWords Scripts

If you've started using Google's Draft and Experiment campaigns, you may have noticed some odd behavior with scripts. We were getting these campaigns back in our selectors, even if they were no longer active experiments. And if we tried to change their status to 'ended', we couldn't do so.

I'll explain the similarities and differences so that hopefully you can avoid some of the frustration I faced when we started using experiments in AdWords and some scripts started to break.

Experiment campaigns are like other campaigns in the following ways:

1. Experiment campaigns are returned in API reports like CAMPAIGN_PERFORMANCE_REPORT
2. Experiment campaigns are returned by AW Scripts selectors like campaignSelector = AdWordsApp.campaigns()

Experiment campaigns are unlike normal campaigns in some important ways:

1. Experiment campaigns, once enabled are always enabled. If you end the experiment, the ServingStatus becomes 'ENDED' but the CampaignStatus remains 'ENABLED'
2. Experiment campaigns cannot have labels
3. Experiment campaigns cannot have their status or budget changed (as these are tied to the corresponding 'base' campaign)

As you can see, there are situations where some of these oddities can lead to issues. For example, if you have a script that pauses any campaigns that have exceeded a certain budget for the month (like those that Optmyzr provides), the script will recognize that an experiment campaign has spent too much but it will be unable to label it or pause it.

So I wanted to exclude experiment campaigns from my selectors and found this was possible and documented. You can exclude campaigns of different types by adding the following to a selector:

• withCondition("CampaignExperimentType = BASE")
• withCondition("CampaignExperimentType = DRAFT")
• withCondition("CampaignExperimentType = EXPERIMENT")

But there is also an undocumented feature in AdWords Scripts that can help filter these campaigns:

• withCondition("ServingStatus = SERVING")

The other possible condition values are ENDED, PENDING, NONE, SUSPENDED

The cool trick I learned here is that you can use conditions that are not documented in the AdWords Scripts reference. You just refer to the fields in the corresponding API report and try adding them to selectors.

Thanks to Alex from the Google team for pointing this out.

Automatically Exclude Placements With a Particular Domain

AdWords lets advertisers show ads on the Display Network (GDN) and while it has ways to target ads to show on specific domains, or to exclude specific domains, they don't have a wildcard placement exclusion feature. 

So if you want to show your ads on all relevant placements, except those with a Polish domain extension (.pl) or an extension like .org, you would need to monitor automatic placements and add exclusions every time you saw one with this extension.

The following script makes it possible to automate excluding placements when the domain includes a particular string.

Run this daily or weekly in your AdWords account to prevent accruing too many clicks from unwanted placements.

Enjoy!
Frederick Vallaeys
Co-Founder, Optmyzr.com

Get alerted when keywords or product groups spend too much

Automated Rules in AdWords are great to set up alerts for when things spend too much without converting enough. But unfortunately these Automated Rules can only be run once per day, so they're not very useful if you want to get notified as soon as a keyword or product group exceeds your thresholds.

So here's a simple script that queries for keywords or product groups that have exceeded a specific amount of cost, and have fewer than a specified number of conversions to show for that cost.

It then emails the list of alerts to the user. The email can include deep links to AdWords to make it really easy to go and fix issues. If the instructions for the settings currentSetting.customerId, currentSetting.effectiveUserId, and currentSetting.ocid confuse you, check out an earlier post by Russ that explains deep linking to ad groups in AdWords in more detail.

Thanks,
Fred Vallaeys

Limit AdWords Overdelivery to Any Amount You Want

Google announced that daily budgets will now be able to overdeliver up to 100% rather than 20% as had been the case since the earliest days of AdWords.

Overdelivery allows Google to help advertisers meet monthly budget targets by making up for slow traffic days by spending more money on high volume days. They assume that advertisers will divide their monthly budget by 30.4, and set this as the daily budget. Then when there may not be a whole lot of traffic on Saturdays and Sundays, Google can exceed the daily budget on Mondays and Tuesdays when there might be more people looking for what the advertiser sells.

Here's Google's announcement:

To truly control budgets they way you need, you'll probably want to use tools, automations, and AdWords Scripts. Here's a very basic script that lets you enforce a more strict overdelivery for a campaign. The script assumes that your daily budgets are the baseline of what you'd like to deliver. Use the setting 'allowedOverdeliveryPercentage' to control a maximum spend for the day by setting a value between 0% and 100%. The script fetches every active campaign's daily budget and accrued cost for the day. If the cost exceeds the daily budget + the allowed percentage of overdelivery, it will label that campaign and pause it.

Important Notes:

• Remember to use another automation to re-enable all paused campaigns during the first hour of every day. You can look for campaigns that have the label set by the script and re-enable those. 
• The script can be run once per hour so you may still exceed the total cost until the next time the script runs. 
• The script doesn't deal with shared budgets.
• The script doesn't deal with shopping and video campaigns. Making it work for those is really easy, you just have to update the campaigns call to use the video and shopping methods for getting campaigns.

If you need more control over budgets, and you don't want to do any coding, consider our prebuilt scripts available as part of an Optmyzr subscription (Optmyzr is my company).

/******************************************
*
* Version 1.0 
* Created By: Frederick Vallaeys
* FreeAdWordsScripts.com
******************************************/

function main() {
  
  var allowedOverdeliveryPercentage = 0.2; // set percentage as decimal, i.e. 20% should be set as 0.2
  var labelName = "paused by overdelivery checker script";
  
  AdWordsApp.createLabel(labelName, "automatic label needed to reenable campaigns");
  
  var campaigns = AdWordsApp.campaigns()
   .withCondition("Status = ENABLED")
   .withCondition("Cost > 0")
   .forDateRange("TODAY");
  
  var campaignIterator = campaigns.get();
  
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();
    var budgetAmount = campaign.getBudget().getAmount();
    var costToday = campaign.getStatsFor("TODAY").getCost();
    
    if(costToday > budgetAmount * (1 + allowedOverdeliveryPercentage)) {
      Logger.log(campaignName + " has spent " + costToday + " which is more than allowed.");
      campaign.applyLabel(labelName);
      campaign.pause();
    } else {
      Logger.log(campaignName + " has spent " + costToday + " and can continue to run.");
    }
  }

}

How to Keep AdWords Scripts Running When the AdWords API Changes

The AdWords API is regularly updated by Google with their latest capabilities. While it's great not to have to wait too long to get access to new capabilities, it comes with a downside too: AdWords Scripts may stop working on the day Scripts switch to using a newer version of the API.

The reason is that new API version may rename or remove metrics and attributes. An AdWords Script that is not updated with these latest names will stop working. 

You can find the release dates of new API versions here and the table looks like this:

The release date is not always the date that AdWords Scripts start to use the newer version. As a result, it is very tricky to ensure that scripts you write continue to work. 

Luckily there is a solution and it's as simple as telling your script which API version it should use by including the optional apiVersion argument. 

A reporting call without the API version: 

var report2 = AdWordsApp.report(
     'SELECT AdGroupId, Id, KeywordText, Impressions, Clicks ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
     'DURING 20130101,20130301');

And that same call with the API version:

var report2 = AdWordsApp.report(
     'SELECT AdGroupId, Id, KeywordText, Impressions, Clicks ' +
     'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
     'DURING 20130101,20130301', {
       apiVersion: 'v201605'
     });

By telling the script which API version to use, you guarantee that it will continue to work on the day that Google switches the default the a new version because you now control the switch that tells the script when your code has been updated and should start using a new API version.

You'll still need to do the migration at some point, but you'll have several months to do so. The sunset date in the table above indicates the final day that a script can use a particular API version. After that date, the old version will cease to work.

Note that you do NOT have to go through every API version. It's completely acceptable to skip a version if you don't need any of its capabilities. For example, say you were using v201609. Since it doesn't sunset until October 2, 2017, you could have waited for the release of v201708 on August 9, 2017, and skipped the 2 API versions in between.

The scripts in the Optmyzr Enhanced Scripts library handle all of these API transitions automatically for our users so if you'd rather not deal with API versions, it's a great solution to try. (Optmyzr is my employer)

Automatically add AdWords Data to a Google Slide

Have you ever had to give a presentation about the performance of an AdWords account and spent a lot of time copy-and-pasting data from AdWords into your slides? If so, now you can automatically push data from AdWords into Google Slides.

This script leverages the recently announced integration of AdWords Scripts with the Google Slides API. Because this is one of the advanced APIs, the code is a bit more complicated and you will have to enable the Google Slides API from the script through an additional authorization step.

The code below appends a new slide to your Google Slide deck and adds some basic AdWords metrics. You can modify this code to add exactly the data from AdWords you want.

/* 
// AdWords Script: Add a Slide with AdWords Data
// --------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
// 
// This script takes a Google Presentation as input and appends a slide with basic AdWords metrics.
// Use this to automate creating an appendix of AdWords data to existing PPC report slides.
// The AW data we append is basic but can easily be tweaked to your own needs.
//
// For more PPC management tools and reports, visit www.optmyzr.com
//
*/

// Update this line with the presentation you want to edit. 
// E.g. this is for presentation https://docs.google.com/presentation/d/1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg/edit#slide=id.optmyzr_slide_a1f911e6-9538-427d-9e2f-12fdc951f752
var PRESENTATION_ID = "1RxIzTJC6Jwwd3H5aaRjA-zj3d5IhcG9uOTuOfwk8PUg"

function main() {
  
  var pageId = createSlide(PRESENTATION_ID);
  
  // Get the page element IDs for a basic TITLE_AND_BODY layout
  var baseElementId = readPageElementIds(PRESENTATION_ID, pageId);
  var titleId = baseElementId + "_0";
  var textId = baseElementId + "_1";
  
  // Edit the following with the text for the slide's title
  var titleText = "Automatically Fetched AdWords Data";
  updateElement(PRESENTATION_ID, titleId, titleText);
  
  // The next line gets text for the body section
  var dataForSlide = getLastMonthData();
  updateElement(PRESENTATION_ID, textId, dataForSlide);
  
  Logger.log("Done updating slides at https://docs.google.com/presentation/d/" + PRESENTATION_ID);
  
}

function getLastMonthData() {
  var currentAccount = AdWordsApp.currentAccount();
  //Logger.log('Customer ID: ' + currentAccount.getCustomerId() +
  //    ', Currency Code: ' + currentAccount.getCurrencyCode() +
  //    ', Timezone: ' + currentAccount.getTimeZone());
  var stats = currentAccount.getStatsFor('LAST_MONTH');
  var clicks = stats.getClicks();
  var impressions = stats.getImpressions();
  var text = clicks + " clicks from " + impressions + " impressions.";
  return(text);
}

function createSlide(presentationId) {
  // You can specify the ID to use for the slide, as long as it's unique.
  var pageId = Utilities.getUuid();

  var requests = [{
    "createSlide": {
      "objectId": pageId,
      //"insertionIndex": 1,
      "slideLayoutReference": {
        "predefinedLayout": "TITLE_AND_BODY"
      }
    }
  }];
  var slide =
      Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
  //Logger.log(slide);
  //Logger.log("Created Slide with ID: " + slide.replies[0].createSlide.objectId);
  
  return (pageId);
}

function updateElement(presentationId, elementId, textToAdd) {
  
  var requests = [{
      "insertText": {
        "objectId": elementId,
        "text": textToAdd,
      }
    }];
  var result =
      Slides.Presentations.batchUpdate({'requests': requests}, presentationId);
  //Logger.log(result);
}

function readPageElementIds(presentationId, pageId) {
  // You can use a field mask to limit the data the API retrieves
  // in a get request, or what fields are updated in an batchUpdate.
  var response = Slides.Presentations.Pages.get(
      presentationId, pageId, {"fields": "pageElements.objectId"});
  //Logger.log(response);
 var objectIds = response.pageElements[0].objectId;
  var parts = objectIds.split("_");
  var objectIdBase = parts[0] + "_" + parts[1];
  //Logger.log("objectIdBase: " + objectIdBase);
  return(objectIdBase);
}

We maintain the most current version of this code on GitHub.

For a fully automated way to create PPC reports with interesting visualizations like Quality Score, a word cloud, a cause chart, or a heatmap, take a look at Optmyzr, my company.

Thanks,
Fred

Pull Stock Quotes Into AdWords Scripts Using Yahoo! Finance API

I was recently asked on Twitter if I had ever seen a script that used stock market performance to adjust bids. Honestly I never have, but I have been asked about this ability multiple times. So I thought I'd build something to do just that.

Finding a reliable and free API for stock data is a little difficult, but everyone seems to point to a somewhat hidden Yahoo! Finance API. Despite the fact that there are multiple libraries built around it, I couldn't find much in the way of documentation other than a StackOverflow post that talks about it. So long story short, this API could stop working at anytime, so use at your own risk.

Here is some sample code to get you started using this. The code below simply looks up a few quotes (one from Bitcoin) and loads them into a Google Spreadsheet of your choosing. Pretty straightforward. The one confusing thing is the "f=" parameter that you need to pass to the API. It is documented a little bit in this blog post but is still pretty confusing. It is a string of one or two character codes that is used to define the columns you want to return. For most people, the symbol, name, and current price should be enough. Feel free to customize it as needed.

Thanks,
Russ

/******************************************
* Yahoo Finance API Class Example
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
  var sheetUrl = 'ENTER A GOOGLE SHEET URL HERE';
  
  var yfa = new YahooFinanceAPI({
    symbols: ['^GSPC','VTI','^IXIC','BTCUSD=X'],
    f: 'snl1' // or something longer like this 'sl1abb2b3d1t1c1ohgv'
  });
  for(var key in yfa.results) {
    Logger.log(Utilities.formatString('Name: "%s", Symbol: "%s", Last Trade Price: $%s', 
                                      yfa.results[key].name,
                                      key,
                                      yfa.results[key].last_trade_price_only));
  }
  
  var includeColumnHeaders = true;
  var sheetData = yfa.toGoogleSheet(includeColumnHeaders);
  var ss = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
  for(var i in sheetData) {
    ss.appendRow(sheetData[i]);
  }
}

Just copy the follow code into the bottom of your AdWords script and you should be good to go.

/******************************************
* Yahoo Finance API Class
* Use this to pull stock market quotes from Yahoo Finance
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function YahooFinanceAPI(configVars) {
  var QUERY_URL_BASE = 'https://query.yahooapis.com/v1/public/yql';
  var FINANCE_URL_BASE = 'http://download.finance.yahoo.com/d/quotes.csv';
  this.configVars = configVars;
  
  /*************
   * The results are stored here in a 
   * map where the key is the ticker symbol
   * { 'AAPL' : { ... }, 'GOOG' : { ... }
   *************/
  this.results = {};
  
  /************
   * Function used to refresh the results
   * from Yahoo! Finance API. Called automatically
   * during object reaction.
   ************/
  this.refresh = function() {
    var queryUrl = getQueryUrl(this.configVars);
    var resp = UrlFetchApp.fetch(queryUrl,{muteHttpExceptions:true});
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      if(jsonResp.query.count == 1) {
        var row = jsonResp.query.results.row;
        this.results[row.symbol] = row;
      } else if(jsonResp.query.count > 1) {
        for(var i in jsonResp.query.results.row) {
          var row = jsonResp.query.results.row[i];
          this.results[row.symbol] = row;
        }
      }
    } else {
      throw resp.getContentText();
    }
  }
  
  /************
   * Translates the results into a 2d array
   * to make it easier to add into a Google Sheet.
   * includeColumnHeaders - true or false if you want
   *   headers returned in the results.
   ************/
  this.toGoogleSheet = function(includeColumnHeaders) {
    if(!this.results) { return [[]]; }
    var retVal = [];
    var headers = null;
    for(var key in this.results) {
      if(!headers) {
        headers = Object.keys(this.results[key]).sort();
      }
      var row = [];
      for(var i in headers) {
        row.push(this.results[key][headers[i]]);
      }
      retVal.push(row);
    }
    if(includeColumnHeaders) {
      return [headers].concat(retVal);
    } else {
      return retVal;
    }
  }
  
  // Perform a refresh on object creation.
  this.refresh();
  
  // Private functions
  
  /************
   * Builds Yahoo Finance Url
   ************/
  function getFinanceUrl(configVars) {
    var financeUrlParams = {
      s : configVars.symbols.join(','),
      f : configVars.f,
      e : '.json'
    }
    return FINANCE_URL_BASE + serialize(financeUrlParams);
  }
  
  /************
   * Builds Yahoo Query Url
   ************/
  function getQueryUrl(configVars) {
    var financeUrl = getFinanceUrl(configVars);
    var cols = fToCols(configVars.f);
    var queryTemplate = "select * from csv where url='%s' and columns='%s'";
    var query = Utilities.formatString(queryTemplate, financeUrl,cols.join(','));
    var params = {
      q : query,
      format : 'json'
    }
    var finalRestUrl = QUERY_URL_BASE + serialize(params);
    return finalRestUrl;
  }

  /************
   * This function translates the f parameter
   * into actual field names to use for columns
   ************/
  function fToCols(f) {
    var cols = [];
    var chunk = '';
    var fBits = f.split('').reverse();
    for(var i in fBits) {
      chunk = (fBits[i] + chunk);
      if(fLookup(chunk)) {
        cols.push(fLookup(chunk));
        chunk = '';
      }
    }
    return cols.reverse();
  }
  
  /************
   * Copied from: http://stackoverflow.com/a/18116302
   * This function converts a hash into 
   * a url encoded query string.
   ************/
  function serialize( obj ) {
    return '?'+
      Object.keys(obj).reduce(
        function(a,k) { 
          a.push(k+'='+encodeURIComponent(obj[k]));
          return a
        },
        []).join('&');
  }
  
  /************
   * Adapted from http://www.jarloo.com/yahoo_finance/
   * This function maps f codes into 
   * friendly column names.
   ************/
  function fLookup(f){
    return{
      a:'ask',b:'bid',b2:'ask realtime',b3:'bid realtime',p:'previous close',o:'open',
      y:'dividend yield',d:'dividend per share',r1:'dividend pay date',
      q:'ex-dividend date',c1:'change',c:'change & percent change',c6:'change realtime',
      k2:'change percent realtime',p2:'change in percent',d1:'last trade date',
      d2:'trade date',t1:'last trade time',c8:'after hours change realtime',
      c3:'commission',g:'days low',h:'days high',k1:'last trade realtime with time',
      l:'last trade with time',l1:'last trade price only',t8:'1 yr target price',
      m5:'change from 200 day moving average',m6:'percent change from 200 day moving average',
      m7:'change from 50 day moving average',m8:'percent change from 50 day moving average',
      m3:'50 day moving average',m4:'200 day moving average',w1:'days value change',
      w4:'days value change realtime',p1:'price paid',m:'days range',m2:'days range realtime',
      g1:'holdings gain percent',g3:'annualized gain',g4:'holdings gain',
      g5:'holdings gain percent realtime',g6:'holdings gain realtime',t7:'ticker trend',
      t6:'trade links',i5:'order book realtime',l2:'high limit',l3:'low limit',
      v1:'holdings value',v7:'holdings value realtime',s6: 'revenue',k:'52 week high',
      j:'52 week low',j5:'change from 52 week low',k4:'change from 52 week high',
      j6:'percent change from 52 week low',k5:'percent change from 52 week high',
      w:'52 week range',v:'more info',j1:'market capitalization',j3:'market cap realtime',
      f6:'float shares',n:'name',n4:'notes',s:'symbol',s1:'shares owned',x:'stock exchange',
      j2:'shares outstanding',v:'volume',a5:'ask size',b6:'bid size',k3:'last trade size',
      a2:'average daily volume',e:'earnings per share',e7:'eps estimate current year',
      e8:'eps estimate next year',e9:'eps estimate next quarter',b4:'book value',j4:'ebitda',
      p5:'price sales',p6:'price book',r:'pe ratio',r2:'pe ratio realtime',r5:'peg ratio',
      r6:'price eps estimate current year',r7:'price eps estimate next year',s7:'short ratio'
    }[f];
  }
}

executeInParallel on More than 50 Accounts Using Labels

One of the biggest limitations for people running MCC level scripts is the 50 account limit imposed by the executeInParallel function. Until recently, one of the ways to get around this limit was to store the processed accounts list on Google Drive and update the file when needed.

Recently, the AdWords Scripts team enabled the ability to apply labels to each account from the MccApp object. With this, we gain the ability to write a much cleaner version of the script that uses labels to indicate when each account has been processed. Using this method and running this script every hour, you could process up to 1,200 accounts per day.

The following code is meant to provide a framework for you to substitute your own MccApp code into. You can then schedule this code to run every hour, and it will continue processing the accounts in your MCC until each one of them is finished.

It will also attempt to notify you when there are accounts that return an error so that you can investigate. The one caveat about this script is that if you run into timeout limits on the Mcc level, namely in the results function, you might not apply the label to each successfully completed account. You can solve this by replacing line 76 with the following and removing line 89:

applyLabelsToCompletedAccounts([result.getCustomerId()]);

Anyway, I hope this helps and let me know if you run into any issues in the comments.

Thanks,
Russ

/******************************************
* MccApp Generic Runner Framework for any number of acounts
* Version 1.1
* Changelog v1.1 - fixed issue with selector in yesterdays label function
* Created By: Russ Savage (@russellsavage)
* FreeAdWordsScripts.com
******************************************/
// The name of the script you are running
// Used in error email subject line and label name
var SCRIPT_NAME = 'Generic MCC App';
// Since timezones are not available at the MCC level
// you need to set it here. You can use the local timezone
// of each account in the function processing each account
var TIMEZONE = 'PST';
// The date for today based on the timezone set above
var TODAY_STR = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM-dd');
// The label prefix which is used to figure out today's label and
// yesterday's label
var LABEL_PREFIX = SCRIPT_NAME + ' - Completed - ';
// This is the label that will be applied to each account
// when it is successfully processed
var FINISHED_LABEL_NAME = LABEL_PREFIX + TODAY_STR;
// This is a list of email addresses to notify when
// one of the accounts returns an error and is not processed.
var NOTIFY = ['your_email@your_domain.com'];

function main() {
  // Warning: if running in preview mode, this function will fail
  // and the selector that follows will also fail with "cannot read from AdWords"
  createLabelIfNeeded();
  removeYesterdaysLabel(); // This might not exist, but try to remove it
  // Find all the accounts that have not been processed
  var accountIter = MccApp.accounts()
    .withCondition("LabelNames DOES_NOT_CONTAIN '"+FINISHED_LABEL_NAME+"'")
    .withLimit(50)
    .get();
  // Add them to a list for the executeInParallel later
  var accountList = [];
  while(accountIter.hasNext()) {
    var account = accountIter.next();
    accountList.push(account.getCustomerId());
  }
  // If there are unprocessed accounts, process them
  if(accountList.length > 0) {
    MccApp.accounts()
      .withIds(accountList)
      .executeInParallel('doSomethingInEachAccount', 'reportOnResults');
  }
}

// This function is called from executeInParallel and contains the
// business logic for each account. Right now, it just has some 
// dummy logic to illustrate how this works.
function doSomethingInEachAccount() {
  /**************
   * Replace this function with what 
   * you want to do on each account
   **************/
  Logger.log("In account: "+AdWordsApp.currentAccount().getName()+
                        " "+AdWordsApp.currentAccount().getCustomerId());
  // This function must return a string so we use JSON.stringify() to
  // turn almost any object into a string quickly.
  return JSON.stringify({something:'else'});
}

// This function will be called as soon as the function above
// has been run on each account. The results object is an array
// of the results returned by the function run in each account.
function reportOnResults(results) {
  var completedAccounts = [];
  var erroredAccounts = [];
  for(var i in results) {
    var result = results[i];
    // If the account function returns success
    if(result.getStatus() == 'OK') {
      // Add it to the list to apply the label to
      completedAccounts.push(result.getCustomerId());
      /**********************
       * Fill in the code to process the results from 
       * each account just below this.
       **********************/
      var returnedValue = JSON.parse(result.getReturnValue());
    } else {
      // In case of an error, we should notify someone so they can
      // check it out.
      erroredAccounts.push({customerId:result.getCustomerId(), error: result.getError()});
    }
  }
  // Finally we apply the labels to each account
  applyLabelsToCompletedAccounts(completedAccounts);
  // And send an email with any errors
  notifyOfAccountsWithErrors(erroredAccounts);
}


/*******************************
 * Do not edit code below unless you know
 * what you are doing.
 *******************************/
// This function creates the required label to apply
// to completed accounts. You can change the label name
// by editing the FINISHED_LABEL_NAME variable at the top
// of this script.
function createLabelIfNeeded() {
  try {
    var labelIter = MccApp.accountLabels()
      .withCondition("LabelNames CONTAINS '"+FINISHED_LABEL_NAME+"'")
      .get();
  } catch(e) {
    MccApp.createAccountLabel(FINISHED_LABEL_NAME);
  }
}

// This function applies FINISHED_LABEL_NAME to each completed account
function applyLabelsToCompletedAccounts(completedAccounts) {
  var finishedAccountsIter = MccApp.accounts().withIds(completedAccounts).get();
  while(finishedAccountsIter.hasNext()) {
    var account = finishedAccountsIter.next();
    account.applyLabel(FINISHED_LABEL_NAME);
  }
}

// This function attempts to remove yesterday's label if it exists.
// If it doesn't exist, it does nothing.
function removeYesterdaysLabel() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var yesterdayStr = Utilities.formatDate(yesterday, TIMEZONE, 'yyyy-MM-dd');
  var yesterdayLabel = LABEL_PREFIX + yesterdayStr;
  Logger.log("Attempting to remove label: "+yesterdayLabel);
  try {
    var labelIter = MccApp.accountLabels().withCondition("Name CONTAINS '"+yesterdayLabel+"'").get();
    while(labelIter.hasNext()) {
      labelIter.next().remove();
    }
  } catch(e) { 
    // do nothing
  }
}

// This function will send an email to each email in the
// NOTIFY list from the top of the script with the specific error
function notifyOfAccountsWithErrors(erroredAccounts) {
  if(!erroredAccounts || erroredAccounts.length == 0) { return; }
  if(typeof NOTIFY == 'undefined') { throw 'NOTIFY is not defined.'; }
  var subject = SCRIPT_NAME+' - Accounts with Errors - '+TODAY_STR;
  
  var htmlBody = 'The following Accounts had errors on the last run.<br / >';
  htmlBody += 'Log in to AdWords: http://goo.gl/7mS6A';
  var body = htmlBody;
  htmlBody += '<br / ><br / >';
  htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">' +
              '<tr><td>Account Id</td><td>Error</td></tr>';
  for(var i in erroredAccounts) {
    htmlBody += '<tr><td>'+ erroredAccounts[i].customerId +
      '</td><td>' + erroredAccounts[i].error + '</td></tr>';
  }
  htmlBody += '</table>';
  // Remove this line to get rid of the link back to this site.
  htmlBody += '<br / ><br / ><a href = "http://www.freeadwordsscripts.com" >FreeAdWordsScripts.com</a>';
  var options = { htmlBody : htmlBody };
  for(var i in NOTIFY) {
    Logger.log('Sending email to: '+NOTIFY[i]+' with subject: '+subject);
    MailApp.sendEmail(NOTIFY[i], subject, body, options);
  }
}

Use Google Feed API to Convert RSS to JSON

Here is a quick tip for anyone sick of working with the xml in RSS feeds. You can use the Google Feed API in your scripts to automatically convert the XML in the RSS feed to JSON which makes working with them much easier. For example, there is an RSS Feed from the Consumer Product Safety Commission and it has all the government recalls in the United States. Using this simple trick, you could easily pull the fresh feed on a regular basis and parse out information about recalls to use in your account. You can also configure Google Alerts to generate RSS feeds based on specific keywords you are interested in. For example, here is an RSS feed for the keyword "smx".

Thanks,
Russ

/******************************************
* Use Google Feed API to convert RSS to json
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
// Usage: var jsonData = convertRssToJson('http://www.cpsc.gov/en/Newsroom/CPSC-RSS-Feed/Recalls-RSS/');
function convertRssToJson(rssUrl) {
  var FEED_API_URL = "https://ajax.googleapis.com/ajax/services/feed/load?v=1.0&q="
  var url = FEED_API_URL+encodeURIComponent(rssUrl);
  var resp = UrlFetchApp.fetch(url);
  if(resp.getResponseCode() == 200) {
    return JSON.parse(resp.getContentText());
  } else {
    throw "An error occured while trying to parse: "+rssUrl;
  }
}

Pull Salesforce Data into AdWords Using Scripts

After my post about importing Zoho CRM data into AdWords, I received a lot of comments about doing something similar for Salesforce. I finally had some time to build a simple class that allows you to query data and objects from your Salesforce instance and use it in your scripts.

First, we will need to set you up with some OAuth credentials, and for that, you need to set up a new connected app in Salesforce. It is in slightly different places in each version, but using the developer version, I was able to find it under Setup > Build > Create > Apps. From there, all the way at the bottom, you can see a section for Connected Apps.

If for some reason you can't find it in your Salesforce instance, your admin may not have given you access to it. Hopefully, they can help you.

After clicking the new button, you will need to fill out a few required fields and then select the option to "Enable Oauth Settings." You will need to enter a callback url but we won't be using it so you can enter any url that starts with https. For "Scopes", I just said "Full Access" but you may have to talk to your Salesforce Admin about that one. We will only be reading from Salesforce so it shouldn't be an issue.

That's all you need to fill out and you should have a new app created. The important thing here is the "Consumer Key" and the "Consumer Secret" that you will need for the script to connect to your Salesforce instance.

The last thing you will need from your Salesforce instance is a security token. You may already have one in which case, you can skip this. But if not, you can reset it under My Settings > Personal > Reset my Security Token. It will email you a new token.

Ok, now we are finally ready to get to the AdWords Scripts code. The following code will set up a new SalesforceAPI object and query the most recent Opportunities that were Close Won so that you can use that revenue in your AdWords account.

It's that simple. If you want to get all the information about a particular object after you query for it, you can use the function getObjectByUrl() and send it the url from the query results. To learn more about the query syntax, check out the Salesforce SOQL documentation.

There are a few caveats for this code. Every Salesforce installation is unique so there really is no way for me to really troubleshoot issues with your specific install. This code was tested on a fresh Salesforce for Developers account so your results may vary. You will probably have more luck contacting your Salesforce Admin than leaving a comment here. Also, you may notice that the code it using the least secure option to log into Salesforce. This code with your username and password will be accessible to all users of your AdWords account, so be careful. It might be better to create a special Salesforce user with very limited permissions for something like this.

If you think this is useful, come let me know at SMX East this week.

Thanks,
Russ