Sunday, November 9, 2014

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);
  }
}

Tuesday, September 30, 2014

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;
  }
}

Monday, September 29, 2014

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.
Creating a new connected app

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.
Enabling the OAuth Settings

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.
Salesforce Consumer and Secret Keys

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.
Reset your Security 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.

/******************************************
* Get Won Opportunity Revenue Amounts
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function main() {
  var CONSUMER_KEY = "YOUR APP CONSUMER KEY";
  var CONSUMER_SECRET = "YOUR APP SECRET KEY";
  var USERNAME = "YOUR SALESFORCE USERNAME";
  var PASSWORD = "YOUR SALESFORCE PASSWORD";
  var SECURITY_TOKEN = "YOUR SECURITY TOKEN";
  
  // Create a new SalesforceAPI object
  var sf = new SalesforceAPI({
    client_id : CONSUMER_KEY,
    client_secret : CONSUMER_SECRET,
    username : USERNAME,
    password : PASSWORD,
    token: SECURITY_TOKEN
  });
  
  // Query the results
  var results = sf.query("SELECT Name \
                         , StageName \
                         , ExpectedRevenue \
                         FROM Opportunity \
                         WHERE IsWon = True \
                         AND CloseDate < THIS_WEEK");
  // Log the results
  for(var i in results) {
    Logger.log(['Name:',results[i].Name,
                'Stage Name:',results[i].StageName,
                'Expected Revenue:',results[i].ExpectedRevenue,
                'Url:',sf.getFullUrl(results[i].attributes.url)].join(' '));
  }
}

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

/******************************************
* Salesforce.com CRM API Class
* Use it to query data out of Salesforce CRM
* Version 1.0 
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
function SalesforceAPI(configVars) {
  this.login = function(configVars) {
    var LOGIN_ENDPOINT = "https://login.salesforce.com/services/oauth2/token";
    var options = {
      muteHttpExceptions : false,
      method: "POST",
      payload: {
        grant_type : "password",
        client_id : configVars.client_id,
        client_secret : configVars.client_secret,
        username : configVars.username,
        password : configVars.password+configVars.token
      }
    };
    var resp = UrlFetchApp.fetch(LOGIN_ENDPOINT, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      this.id = jsonResp.id;
      this.instanceUrl = jsonResp.instance_url;
      this.signature = jsonResp.signature;
      this.accessToken = jsonResp.access_token;
      Logger.log('Successfully logged in user with id: '+this.id);
    }
  }
  
  this.getServices = function() {
    if(this.serviceUrls) { return this.serviceUrls };
    var ENDPOINT_URL = this.instanceUrl+"/services/data/v26.0/.json";
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(ENDPOINT_URL, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      this.serviceUrls = jsonResp;
      return this.serviceUrls;
    }
  }
  
  this.query = function(queryStr) {
    if(!this.serviceUrls.query) { throw "Query service is not enabled in this SF instance."; }
    var ENDPOINT_URL = this.instanceUrl+this.serviceUrls.query+'.json';
    var url = ENDPOINT_URL + '?q=' + encodeURIComponent(queryStr);
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(url, options);
    if(resp.getResponseCode() == 200) {
      var jsonResp = JSON.parse(resp.getContentText());
      if(jsonResp.done) {
        return jsonResp.records;
      } else {
        var retVal = jsonResp.records;
        while(!jsonResp.done) {
          resp = UrlFetchApp.fetch(jsonResp.nextRecordsUrl, options);
          if(resp.getResponseCode() == 200) {
            jsonResp = JSON.parse(resp.getContentText());
            retVal = retVal.concat(jsonResp.records);
          }
        }
        return retVal;
      }
    }
  }
  
  this.getObjectByUrl = function(url) {
    var url = this.instanceUrl + url + '.json';
    var options = getBasicOptions(this.accessToken);
    var resp = UrlFetchApp.fetch(url, options);
    Logger.log(resp.getContentText());
    if(resp.getResponseCode() == 200) {
      return JSON.parse(resp.getContentText());
    }
  }
  
  this.getFullUrl = function(url) {
    return this.instanceUrl + url;
  }
  
  this.login(configVars);
  this.getServices();
  
  
  function getBasicOptions(token) {
    return {
      muteHttpExceptions : false,
      method: 'GET',
      headers: {
        Authorization : "Bearer " + token
      }
    };
  }
}

Wednesday, September 17, 2014

Come Join Me at Marketing Festival 2014

I just wanted to take a second to mention that I will be speaking at Marketing Festival 2014 this year as well as running a workshop on more advanced techniques for scripting.  If you are able to attend, it should be a very productive conference and workshop.  I look forward to meeting you in person!

Thanks,
Russ