Automatice la estructura de la campaña Alpha Beta mediante secuencias de comandos de AdWords

/*******************************
 * 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
  // https://developers.google.com/adwords/api/docs/guides/reporting-concepts#money
  '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: nn %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;
  }
}

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

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