Thursday, July 30, 2015

Automate Alpha Beta Campaign Structure Using AdWords Scripts

google-adwords-bigA3-1920

I’ve read a lot of blogs and articles about the Alpha Beta Campaign Structure and how it can improve the overall performance of your account. In fact, in a recent AMA with Optmyzr founder and ex-Googler Fredrick Vallaeys on Reddit, he had nothing but good things to say about this approach for campaign management.

I’ve also read that many people believe that maintaining a structure like this is labor-intensive and prone to errors if you aren’t careful. That sounds like a perfect recipe for AdWords Scripts. When I did a few searches on the topic, I saw a few articles mention AdWords Scripts for solving those issues, but I couldn’t find any actual code to use. Maybe it’s out there somewhere, but in the meantime, I figured I’d write my own.

Alpha Beta Campaign Structure

This idea for Alpha Beta campaigns was originally proposed by David Rodnitzky of 3Q Digital. The basic idea is that you to have a set of “beta” campaigns leveraging broad match to find new keywords, and a set of corresponding “alpha” campaigns containing exact match keywords with a proven performance record. I’m not going to describe the technique in full here, but you can read all about it in their guide.

There are a few things that scripts can help with if you decide to maintain this type of campaign structure. First, you can automate pulling the winners and losers from your beta campaigns based on a set of criteria. There are a few scripts out there that do something similar to this already, so it shouldn’t be too difficult.

From that list, you can easily automate adding the losers as negatives to our beta campaigns. You can also create a script to automatically apply the winners in the alpha campaigns as negatives to the beta campaigns.

As for the winners, it is a little harder to automate, so we won’t be covering that here. With proper naming conventions, moving those keywords into their own ad group and adding a set of default ads shouldn’t be too hard. But with all the collection and negative adding taken care of, you should be able to spend more of your time optimizing the winners.

Sounds like we have a lot of work ahead of us, so let’s get started.

Finding Winners, Losers and Not Enough Data

Let’s start with a script to pull the search query report and slice the results into three categories: Winners, Losers, and Not Enough Data. At the top of the script, we will need to define a few settings which should be pretty self-explanatory.

The critical part is determining the winning and losing criteria. As you can see, these consist of a list of clauses that will filter the results of the search query report so that only the keywords that match those criteria are left. You can use almost any column from the search query report and any operator from the AWQL. The other important thing is to make sure that the columns you use in your criteria are in the columns for the search query report.

/*******************************
 * Find the Winners and Losers from the 
 * Search Query Performance report for 
 * Alpha Beta Campaign Strategy.
 *******************************/
// The list of email addresses to send the report to.
// We will also give editor access to the Google Spreadsheet.
var NOTIFY = ['your_email@example.com'];
// The name of the report in your Google drive
var SPREADSHEET_PREFIX = 'AlphaBetaResults - ';
// This string is used to identify your Beta campaigns.
// Make sure this string is found in all your Beta campaign names
var BETA_SUFFIX = '| Beta';
// This is the timeframe used to calculate the statistics.
var REPORT_TIMEFRAME = 'YESTERDAY';
// This is the list of columns that will be displayed in the 
// Winners and Losers report. Make sure that any columns used in
// the Criteria below are listed here as well.
var REPORT_COLUMNS = [
  'CampaignName','AdGroupName',
  'KeywordTextMatchingQuery','MatchType','Query',
  'Impressions','Clicks','Cost','ConvertedClicks'];
// This is the set of criteria that will be used to
// determine the winners. Any of the numerical columns
// should work here. These are just samples, find what
// works for you.
var WINNING_CRITERIA = [
  'Clicks > 100',
  // If using money columns, be sure to represent them in micros
  // http://ift.tt/1tKzUhx
  'Cost < 1000000',   'ConvertedClicks >= 5'
];
var LOSING_CRITERIA = [
  'Impressions > 100',
  'Clicks < 5',   
  'ConvertedClicks = 0' ]; 

function main() {
   generateWinnersLosersReport();
}

function generateWinnersLosersReport() {
  // This string will be appended to the report to create a unique   
  // name each day. If you run this intra-day, you can add hours (HH) to   
  // to the format string. 
  var dateString = Utilities.formatDate(new Date(), 
                                        AdWordsApp.currentAccount().getTimeZone(), 
                                        'yyyy-MM-dd HH');
  var crits = [WINNING_CRITERIA,LOSING_CRITERIA];
  var sheets = [
    getSheet(SPREADSHEET_PREFIX+dateString,'Winners'),
    getSheet(SPREADSHEET_PREFIX+dateString,'Losers')
  ];
  // Grab all the results first. That way we can remove the winners and losers   
  // to keep the ones with "not enough data".
  var allResults = pullSearchQueryPerfReport(REPORT_TIMEFRAME,REPORT_COLUMNS,[]);
  for(var i in crits) {
    var results = pullSearchQueryPerfReport(
      REPORT_TIMEFRAME,
      REPORT_COLUMNS,
      crits[i]
    );
    writeResultsToSheet(results,sheets[i]);
    removeFromAllResults(allResults,results);
  }
  //Handle the 'Not Enough Data' case
  var notEnoughDataSheet = getSheet(SPREADSHEET_PREFIX+dateString,'Not Enough Data');
  writeResultsToSheet(allResults,notEnoughDataSheet);
  sendEmail(sheets,dateString);
}

// This function pulls the search query report and 
// formats it to be easy to insert into a Google Sheet.
function pullSearchQueryPerfReport(timeframe,columns,crit) {
  var reportName = 'SEARCH_QUERY_PERFORMANCE_REPORT';
  var reportQueryTemplate = 'SELECT %s FROM %s WHERE %s DURING %s';
  // Add a criteria so that we only look at data from Beta campaigns.
  crit.push("CampaignName CONTAINS '"+BETA_SUFFIX+"'");
  var reportQuery = Utilities.formatString(reportQueryTemplate, 
                                           columns.join(','), 
                                           reportName,
                                           crit.join(' AND '), 
                                           timeframe);
  var reportIter = AdWordsApp.report(reportQuery,{
      includeZeroImpressions: true
    }).rows();
  var results = [];
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    var rowArray = [];
    for(var i in columns) {
      rowArray.push(row[columns[i]]);
    }
    results.push(rowArray); 
  }
  return results;
}

// This function writes the results to a given spreadsheet
function writeResultsToSheet(results,sheet) {
  if(results.length > 0) {
    var keywordIndex = REPORT_COLUMNS.indexOf('KeywordTextMatchingQuery');
    sheet.appendRow(REPORT_COLUMNS)
    for(var i in results) {
      // if the keyword starts with a plus sign,
      // we need to add an apostrophe so google sheets
      // doesn't get annoyed.
      if(results[i][keywordIndex].indexOf('+') === 0) {
        results[i][keywordIndex] = "'"+results[i][keywordIndex];
      }
      sheet.appendRow(results[i]);
    }
  }
}

// This function removes the results in toRemove from the results
// in the allResults array. Used to remove winners and losers so 
// that all we have left are the "not enough data" queries.
function removeFromAllResults(allResults,toRemove) {
  var allResultsRowHash = {};
  for(var i in allResults) {
    var rowHash = Utilities.base64Encode(JSON.stringify(allResults[i]));
    allResultsRowHash[rowHash] = 1;
  }
  for(var i in toRemove) {
    var rowHash = Utilities.base64Encode(JSON.stringify(toRemove[i]));
    if(allResultsRowHash[rowHash]) {
      allResults.splice(i,1);
    }
  }
}

// Sends the spreadsheet in an email to the people in the
// NOTIFY list. 
function sendEmail(sheets,dateString) {
  var subjectLineTemplate = 'Alpha Beta Results - %s - %s';
  var subjectLine = Utilities.formatString(subjectLineTemplate,
                                           AdWordsApp.currentAccount().getName(),
                                           dateString);
  var bodyTemplate = 'Here is a spreadsheet with the ' +
                     'winners and losers for account: %s: \n\n %s';
  var body = Utilities.formatString(bodyTemplate,
                                    AdWordsApp.currentAccount().getName(),
                                    sheets[0].getParent().getUrl());
  for(var i in NOTIFY) {
    MailApp.sendEmail(NOTIFY[i], subjectLine, body);
  }
}

// Helper function to get or create a given sheet in 
// a spreadsheet. When creating a new spreadsheet, it also
// adds the emails in the NOTIFY list as editors.
function getSheet(spreadsheetName,sheetName) {
  var fileIter = DriveApp.getFilesByName(spreadsheetName);
  if(fileIter.hasNext()) {
    var ss = SpreadsheetApp.openByUrl(fileIter.next().getUrl());
    var sheets = ss.getSheets();
    for(var i in sheets) {
      if(sheets[i].getName() == sheetName) { 
        sheets[i].clear();
        return sheets[i]; 
      }
    }
    return ss.insertSheet(sheetName);
  } else {
    var ss = SpreadsheetApp.create(spreadsheetName);
    ss.addEditors(NOTIFY);
    var sheet = ss.insertSheet(sheetName);
    ss.deleteSheet(ss.getSheetByName('Sheet1'));
    return sheet;
  }
}

Eliminating The Poor Performers

I recommend running the script above for a little while to make sure you have your criteria set up correctly. Once you’re satisfied with the results, you can easily automate the task of eliminating poor performers from the Beta campaigns.

The following code builds upon what you have already and will automatically add the poor performers as exact match negatives to the corresponding Beta campaign. Just replace the main() function in the previous script and add this additional code.

// Replace the main from above with this one.
// We create the report from before but then
// also add the losers to the beta group.
function main() {
  generateWinnersLosersReport();
  addLosersToBetaGroup();
}

// This code pulls the losers from from the 
// Beta campaign using the same criteria as before.
function addLosersToBetaGroup() {
  var loserResults = pullSearchQueryPerfReport(
    REPORT_TIMEFRAME,
    REPORT_COLUMNS,
    LOSING_CRITERIA
  );
  if(loserResults.length > 0) {
    var campKwHash = transformSearchQueryResults(loserResults);
    var campaignNames = Object.keys(campKwHash);
    // Find all the Beta campaigns
    var campIter = AdWordsApp.campaigns().withCondition("Name CONTAINS '"+BETA_SUFFIX+"'").get();
    while(campIter.hasNext()) {
      var camp = campIter.next();
      var campName = camp.getName();
      // If the campaign is in the list of Beta campaigns we need
      // to add negative to
      if(campaignNames.indexOf(camp.getName()) >= 0) {
        var negativesList = campKwHash[campName];
        for(var i in negativesList) {
          // Add the negatives.
          camp.createNegativeKeyword(negativesList[i]);
        }
      }
    }
  }
}

// This function transforms the data from the Search Query report
// into a map of { campaignName : [ "[query1]","[query2]", ... ] }
function transformSearchQueryResults(results) {
  var campKwHash = {};
  var campColumn = REPORT_COLUMNS.indexOf('CampaignName');
  var queryColumn = REPORT_COLUMNS.indexOf('Query');
  for(var i in loserResults) {
    var row = loserResults[i];
    var campName = row[campColumn];
    var query = row[queryColumn];
    if(!campKwHash[campName]) {
      campKwHash[campName] = [];
    }
    campKwHash[campName].push('['+query+']');
  }
  return campKwHash;
}

 

Protecting Your Alpha Queries

The last aspect of the Alpha Beta Campaign structure we can automate is making sure your Alpha campaigns are protected. We don’t want those exact match Alpha keywords you worked so hard on optimizing showing up in any of our Beta campaigns.

This script can run independently of our previous script. It will go through all of your Alpha Campaigns and automatically add any new keywords it finds as exact match negatives to the corresponding Beta campaign. You can schedule this script to run hourly on your account, so that your Alpha and Beta campaigns will never be out of sync.

/*******************************
 * Automatically add any new keywords in your
 * Alpha campaigns as exact match negatives in
 * the corresponding Beta campaign.
 *******************************/
// Just as before, these strings will be
// used to identify your Alpha and Beta campaigns.
// This script assumes that your Alpha campaigns are
// named "Campaign Name | Alpha" with the corresponding
// Beta campaign named "Campaign Name | Beta"
var ALPHA_SUFFIX = '| Alpha';
var BETA_SUFFIX = '| Beta';

function main() {
  var results = getKeywordReport();
  var toUpdate = {};
  for(var key in results) {
    var campData = results[key];
    for(var i in campData.alpha.keywords) {
      var kw = campData.alpha.keywords[i];
      if(campData.beta.negatives.indexOf(kw) == -1) {
        if(!toUpdate[campData.beta.campName]) {
          toUpdate[campData.beta.campName] = [];
        }
        toUpdate[campData.beta.campName].push(kw);
      }
    }
  }
  var campIter = AdWordsApp.campaigns().withCondition("Name CONTAINS '"+BETA_SUFFIX+"'").get();
  while(campIter.hasNext()) {
    var betaCamp = campIter.next();
    var betaCampName = betaCamp.getName();
    if(toUpdate[betaCampName]) {
      var negativesToAdd = toUpdate[betaCampName];
      for(var i in negativesToAdd) {
        betaCamp.createNegativeKeyword('['+negativesToAdd[i]+']');
      }
    }
  }
}

// This function uses the Keywords report and
// the campaign negatives report to build a list
// of the keywords and negatives in each campaign.
function getKeywordReport() {
  var columns = ['CampaignName','Criteria','IsNegative'];
  var reportQueryTemplate = "SELECT %s FROM %s "+
                            "WHERE IsNegative IN [true,false] "+
                            "AND CampaignName CONTAINS '%s' ";
  var alphaReportQuery = Utilities.formatString(reportQueryTemplate, 
                                                columns.join(','), 
                                                'KEYWORDS_PERFORMANCE_REPORT',
                                                ALPHA_SUFFIX);
  var betaReportQuery = Utilities.formatString(reportQueryTemplate, 
                                               columns.join(','), 
                                               'CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT',
                                               BETA_SUFFIX);
  var queries = [alphaReportQuery,betaReportQuery];
  var results = {};
  for(var i in queries) {
    var reportIter = AdWordsApp.report(queries[i],{
      includeZeroImpressions: true
    }).rows();
    while(reportIter.hasNext()) {
      var row = reportIter.next();
      if(row.CampaignName.indexOf(ALPHA_SUFFIX) == -1 &&
         row.CampaignName.indexOf(BETA_SUFFIX) == -1) {
        continue;
      }
      var campType = (row.CampaignName.indexOf(ALPHA_SUFFIX) >= 0) ? 'alpha' : 'beta';
      
      var cleanCampName = row.CampaignName.split(ALPHA_SUFFIX)[0];
      cleanCampName = cleanCampName.split(BETA_SUFFIX)[0];
      
      if(!results[cleanCampName]) {
        results[cleanCampName] = {
          alpha: { keywords: [], negatives: [], campName: '' },
          beta:  { keywords: [], negatives: [], campName: '' }
        };
      }
      results[cleanCampName][campType].campName = row.CampaignName;
      if(row.IsNegative == 'true') {
        results[cleanCampName][campType].negatives.push(row.Criteria);
      } else {
        results[cleanCampName][campType].keywords.push(row.Criteria);
      }
    }
  }
  return results;
}

 

Wrapping Things Up

So once you have these scripts up and running in your Alpha Beta accounts, all that’s left for you to do is focus on making sure your Alpha campaigns are the best they can be. The reporting and negatives should be taken care of.

Of course, you should always pay careful attention to what your scripts are doing and audit them on a regular basis to make sure things are still working correctly. Anything that makes changes to your account should be previewed many times before you run it on a regular basis. It might even be a good idea to separate these into three scripts, so you can run and test them as needed.

The post Automate Alpha Beta Campaign Structure Using AdWords Scripts appeared first on Search Engine Land.

No comments:

Post a Comment