Showing posts with label ads. Show all posts
Showing posts with label ads. Show all posts

Sunday, October 20, 2013

Manage Your AdWords Creatives Using Excel

Since my last post, Google has been busy releasing a ton of new features for AdWords scripts.  Among them was the ability to interact directly with your Google Drive files.  This is awesome for a few reasons and one of them is that it allows you to manage your account through spreadsheets that are automatically synced back to your Google AdWords account every hour.

There is the opportunity to create keywords, manage Ads, AdGroups, or Ad Extensions all using the power of Excel spreadsheets. Still not convinced? You can also have all your account metrics automatically delivered to your Google Drive and automatically downloaded to your computer each day.

So I thought I would demonstrate how you can manage your Creatives using this new functionality.

The first thing you need to do is install Google Drive onto your computer. It's pretty easy to do but does require Admin rights on your machine.  If you can't install it right now, no need to worry.  You can manage your SiteLinks by updating the files in Google Drive via your browser.

Now take a look at the script below. There are a few config options that you should set.  FOLDER_PATH is the path to the folder where you want to store these files.  For example, 'AdWords/Creatives'. If the folder path doesn't exist, this script will create it for you.

The next parameter is FILE_NAME, which would be the file that the Creative data will be stored in. In this example, the file must be written as a .CSV.  Google makes it pretty easy to parse CSV files using the Utilities.parseCsv function.

This script allows you to make changes to headlines, descriptions, urls, status, and device preferences all from the csv file using Excel.  Then, simply save the file back to Google drive and the next time the script runs (daily or hourly), your changes will be reflected.

What other AdWords features might be easier to manage through Excel rather than through some of the UIs out there?

Thanks,
Russ

/******************************************
 * Manage AdWords Ads Using Excel
 * Version 1.0
 * Author: Russ Savage
 * FreeAdWordsScripts.com
 ****************************************/
var FOLDER_PATH = 'AdWordsData'; //The path where the file will be stored on GDrive
var FILE_NAME = 'creatives.csv'; //The name of the file on GDrive
 
var INCLUDE_STATS = true; // Set to false to remove stats from the file
var DATE_RANGE = 'LAST_30_DAYS'; //The date range for the stats
 
var INCLUDE_PAUSED_ADS = true; //Set to false to only report on active ads
var DELETE_ORIGINAL_ADS = true; //When set to false, the original ads will be paused instead of deleted
 
function main() {
  var file = getFile(FILE_NAME,FOLDER_PATH);
  if(!file) {
    file = createNewFile(FILE_NAME,FOLDER_PATH,buildCreativesFile());
    return;
  }
  
  var fileContent = file.getBlob().getDataAsString();
  var creatives = {};
  if(fileContent) {
    creatives = parseFileContent(fileContent);
  }
  
  var creativeIter = getAdIterator();
  while(creativeIter.hasNext()) {
    var creative = creativeIter.next();
    var adId = creative.getId();
    if(creatives[adId]) {
      //ok we found the ad.
      //Checking status
      var isEnabledFile = (creatives[adId]['Status'] === 'Enabled');
      if(creative.isEnabled() != isEnabledFile) {
        if(isEnabledFile) { 
          creative.enable(); 
        } else { 
          creative.pause(); 
        }
      }
      
      if(hadAdChanged(creative,creatives[adId])) {
        if(DELETE_ORIGINAL_ADS) {
          creative.remove();
        } else {
          creative.pause();
        }
        createNewAd(creative.getAdGroup(),creatives[creative.getId()]);
      }
    }
  }
  file.setContent(buildCreativesFile());
}
 
//Helper function to create a new ad
function createNewAd(ag,newAd) {
  var optArgs = {
    isMobilePreferred: (newAd['Device'] === 'Mobile') ? true : false
  };
  ag.createTextAd(newAd['Headline'],newAd['Desc1'],newAd['Desc2'],newAd['DisplayUrl'],newAd['DestinationUrl'],optArgs);
}
 
//This checks to see if the ad has been changed
function hadAdChanged(ad,oldAd) {
  var newAdText = [ad.getHeadline(),
                   ad.getDescription1(),
                   ad.getDescription2(),
                   ad.getDisplayUrl(),
                   ad.getDestinationUrl(),
                   (ad.isMobilePreferred()) ? 'Mobile' : 'Desktop'].join('~~!~~');
  var oldAdText = [oldAd['Headline'],
                   oldAd['Desc1'],
                   oldAd['Desc2'],
                   oldAd['DisplayUrl'],
                   oldAd['DestinationUrl'],
                   oldAd['Device']].join('~~!~~');
  Logger.log(newAdText);
  Logger.log(oldAdText);
  return (newAdText !== oldAdText);
}
 
//This builds the creatives file from all the ads in the account.
function buildCreativesFile() {
  var report = getReportColumns();
  var creativeIter = getAdIterator();
  while(creativeIter.hasNext()) {
    var creative = creativeIter.next();
    report += getReportRow(creative);
  }
  return report;
}
 
//This returns the ad iterator based on options.
function getAdIterator() {
  var adSelector = AdWordsApp.ads().withCondition("Type = 'TEXT_AD'"); 
  if(!INCLUDE_PAUSED_ADS) {
    adSelector = adSelector.withCondition('CampaignStatus = ENABLED')
                           .withCondition('AdGroupStatus = ENABLED')
                           .withCondition('Status = ENABLED');
  }
  return adSelector.get();
}
 
//This returns a CSV fow for the report.
function getReportRow(ad) {
  var retVal = [
    ad.getId(),
    ad.getCampaign().getName(),(ad.getCampaign().isPaused()) ? 'Paused' : 'Enabled',
    ad.getAdGroup().getName(),(ad.getAdGroup().isPaused()) ? 'Paused' : 'Enabled',
    ad.getHeadline(),
    ad.getDescription1(),
    ad.getDescription2(),
    ad.getDisplayUrl(),
    ad.getDestinationUrl(),
    (ad.isPaused()) ? 'Paused' : 'Enabled',
    (ad.isMobilePreferred()) ? 'Mobile' : 'Desktop',
  ];
  if(INCLUDE_STATS) {
    var stats = ad.getStatsFor(DATE_RANGE);
    retVal = retVal.concat([
      stats.getImpressions(),
      stats.getClicks(),
      stats.getCtr(),
      stats.getCost(),
      stats.getAverageCpc(),
      stats.getConversions(),
      stats.getConversionRate(),
      stats.getAveragePageviews(),
      stats.getAveragePosition(),
      stats.getAverageTimeOnSite(),
      stats.getBounceRate()
      ]);
  }
  return '"' + retVal.join('","') + '"\n';
}
 
//This returns the column headings used for the report.
function getReportColumns() {
  var columnHeadings = [
    'AdId',
    'CampaignName','CampaignStatus',
    'AdGroupName','AdGroupStatus',
    'Headline',
    'Desc1',
    'Desc2',
    'DisplayUrl',
    'DestinationUrl',
    'Status',
    'Device'];
  if(INCLUDE_STATS) {
    columnHeadings = columnHeadings.concat([
      'Impressions',
      'Clicks',
      'Ctr',
      'Cost',
      'Cpc',
      'Conversions',
      'ConversionRate',
      'AveragePageviews',
      'AvgPosition',
      'AvgTimeOnSite',
      'BounceRate'
      ]);
  }
  return '"' + columnHeadings.join('","') + '"\n';
}   
 
//This function parses the creatives file into an object for processing
function parseFileContent(fileContent) {
  var headers = [];
  var idHash = {};
  var data = Utilities.parseCsv(fileContent);
  for(var i in data) {
    var cells = data[i]
    if(cells.length == 1) { continue; } //skip any empty rows
    if(i == 0) { 
      headers = cells; 
    } else {
      var rowMap = {};
      for(var x in headers) {
        headers[x] = headers[x];
        cells[x] = cells[x];
        rowMap[headers[x]] = cells[x];
      }
      idHash[rowMap['AdId']] = rowMap;
    }
  }
  return idHash;
}
 
//This function gets the file from GDrive
function getFile(fileName,folderPath) {
  var folder = getFolder(folderPath);
  if(folder.getFilesByName(fileName).hasNext()) {
    return folder.getFilesByName(fileName).next();
  } else {
    return null;
  }
}
 
//This function creates a new file on GDrive
function createNewFile(fileName,folderPath,content) {
  if(!fileName) { throw 'createNewFile: Missing filename.'; }
  var folder = getFolder(folderPath);
  
  return folder.createFile(fileName, content);
}
 
//This function finds the folder for the file and creates folders if needed
function getFolder(folderPath) {
  var folder = DriveApp.getRootFolder();
  if(folderPath) {
    var pathArray = folderPath.split('/');
    for(var i in pathArray) {
      var folderName = pathArray[i];
      if(folder.getFoldersByName(folderName).hasNext()) {
        folder = folder.getFoldersByName(folderName).next();
      } else {
        folder = folder.createFolder(folderName);
      }
    }
  }
  return folder;
}

Tuesday, July 30, 2013

Figuring Out When Your Ad, AdGroup, Keyword, or Campaign Was Created

Knowing when an Ad (or entity) was created is impossible using scripts. That information is simply not tracked in AdWords. The next best thing is to find out when your Ad first started receiving impressions and assume that is when it was created (If an Ad is created but no one sees it, does it really exist?).

So in order to help me keep track of when my Ads entities were created, I put together the following script to apply labels to my Ads entities with the date of the first impression. That way, I can find out what ads entities I created and make sure I don't take action on anything that is too young. I can also make changes to all the ads entities built on a given day relatively easily in the AdWords UI by just selecting the right label.

Thanks,
Russ



/**************************************
* Track Entity Creation Date
* Version 1.4
* Changelog v1.4
*  - Removed apiVersion from reporting call
* Changelog v1.3
*  - Updated script to handle all entities
* Changelog v1.2
*  - Fixed an issue with comparing dates
* ChangeLog v1.1
*  - Updated logic to work with larger accounts
* Created By: Russ Savage
* http://www.FreeAdWordsScripts.com
**************************************/
//All my labels will start with this. For example: Created:2013-05-01
var LABEL_PREFIX = 'Created:';
var DAYS_IN_REPORT = 30;
var ENTITY = 'ad'; //or adgroup or keyword or campaign
 
function main() {
  //First we get the impression history of our entity
  var ret_map = getImpressionHistory();
  //Then we apply our labels
  applyLabels(ret_map);
}
 
//Function to apply labels to the ads in an account
function applyLabels(ret_map) {
  var iter;
  if(ENTITY === 'campaign') { iter = AdWordsApp.campaigns().get(); }
  if(ENTITY === 'adgroup') { iter = AdWordsApp.adGroups().get(); }
  if(ENTITY === 'ad') { iter = AdWordsApp.ads().get(); }
  if(ENTITY === 'keyword') { iter = AdWordsApp.keywords().get(); }
  
  while(iter.hasNext()) {
    var entity = iter.next();
    var id = entity.getId();
    if(ret_map[id]) {
      var label_name = LABEL_PREFIX+Utilities.formatDate(ret_map[id], AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
      createLabelIfNeeded(label_name);
      entity.applyLabel(label_name);
    }
  }
}
 
//This is a helper function to create the label if it does not already exist
function createLabelIfNeeded(name) {
  if(!AdWordsApp.labels().withCondition("Name = '"+name+"'").get().hasNext()) {
    AdWordsApp.createLabel(name);
  }
}
 
//A helper function to find the date days ago
function getDateDaysAgo(days) {
  var the_past = new Date();
  the_past.setDate(the_past.getDate() - days);
  return Utilities.formatDate(the_past,AdWordsApp.currentAccount().getTimeZone(),"yyyyMMdd");
}
 
//A helper function to compare dates.
//Copied from: http://goo.gl/uW48a
function diffDays(firstDate,secondDate) {
  var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds
  return Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay))); 
}
 
function getImpressionHistory() {
  var API_VERSION = { includeZeroImpressions : false };
  var first_date = new Date('10/23/2000');
  var max_days_ago = diffDays(first_date,new Date());
  var cols = ['Date','Id','Impressions'];
  var report = { 
    'campaign' : 'CAMPAIGN_PERFORMANCE_REPORT',
    'adgroup' : 'ADGROUP_PERFORMANCE_REPORT',
    'ad' : 'AD_PERFORMANCE_REPORT',
    'keyword' : 'KEYWORDS_PERFORMANCE_REPORT'}[ENTITY];
  var ret_map = {};
  var prev_days_ago = 0;
  for(var i = DAYS_IN_REPORT; i < max_days_ago; i+=DAYS_IN_REPORT) {
    var start_date = getDateDaysAgo(i);
    var end_date = getDateDaysAgo(prev_days_ago);
    var date_range = start_date+','+end_date;
    Logger.log('Getting data for ' + date_range);
    var query = ['select',cols.join(','),'from',report,'during',date_range].join(' ');
    var report_iter = AdWordsApp.report(query, API_VERSION).rows();
    if(!report_iter.hasNext()) { Logger.log('No more impressions found. Breaking.'); break; } // no more entries
    while(report_iter.hasNext()) { 
      var row = report_iter.next();
      if(ret_map[row['Id']]) {
        var [year,month,day] = (row['Date']).split('-');
        var from_row = new Date(year, parseFloat(month)-1, day);
        var from_map = ret_map[row['Id']];
         
        if(from_row < from_map) {
          ret_map[row['Id']] = from_row;
        }
      } else {
        var [year,month,day] = (row['Date']).split('-');
        ret_map[row['Id']] = new Date(year, parseFloat(month)-1, day);
      }
    }
    prev_days_ago = i;
  }
  return ret_map;
}

Saturday, June 22, 2013

Ad Creative Test Automation Script

This is a script I've been working on for a little while and I think it's really useful. I hope you do too.

Every SEM manager worth anything is constantly running creative tests. And many of you, since you are reading this blog, probably use scripts in some way to make that easier. But how do you figure out exactly when the tests start? They could be different for every AdGroup.

I have seen some people use labels, some people use schedules, but I never really liked having to keep track of those things. So I created this script which will keep track of the start dates for creative tests automatically.

The approach I took stores a copy of your ad ids in a google spreadsheet. Then it checks the current ads in the AdGroup against the ads from the spreadsheet and figures out if something changed by looking at the ad ids. If it has, the script assumes a new test has started and notes the date in the spreadsheet accordingly. Using this method, a marketer can be sure that the script is comparing statistics from the proper time frame to determine a test winner.

But then a reader mentioned that they wanted to be able to choose the metric to measure the ads by and also use a threshold to only start measuring after a certain number of clicks in the AdGroup. So I added that ability to the script as well. You only need to adjust a few parameters at the top of the script and you have a fully functional creative testing script in place.

Whenever it takes action on an ad by pausing it, the results will be sent to you in an email so that you can create a new challenger ad for that AdGroup.

I did a few things differently with this script. I always hate making my readers go into Google Docs and create a blank spreadsheet for my scripts to use as storage. So to get around that, this script will check for the existence of a special label in the account where the spreadsheet id is stored. If it finds one, it will use it. And if it is missing (like it will be on the first run), it will create the spreadsheet and label. This way, no messy spreadsheet urls in the scripts. It is a little bit of a workaround but until Google allows us to store additional config data for scripts, I thought this was an ok way to handle this.

As always, I'm open to your feedback on if this script is useful to you. What other metrics do you judge ad performance by? What else is this script missing?

Thanks,
Russ

/************************************
* Ad Creative Test Automation Script
* Version: 1.3
* Changelog v1.3 - Data is written to the spreadsheet a little faster
* Changelog v1.2 - Added additional threshold options
* Changelog v1.1 - Fixed issue with dates in email
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
// You can use any of the same values here as you can for METRIC below
var THRESHOLD = { metric : 'Clicks', value : 100 };
var TO = ['example@example.com'];
  
//Try any of these values for METRIC:
//AverageCpc, AverageCpm, AveragePageviews, AveragePosition, 
//AverageTimeOnSite, BounceRate, Clicks, ConversionRate, 
//Conversions, Cost, Ctr, Impressions
var METRIC = 'Ctr';
var ASC_OR_DESC = 'ASC'; //ASC - pause ad with lowest value, DESC - pause ad with highest value
  
function main() {
  //Start by finding what has changed in the account
  var ad_map = buildCurrentAdMap();
  var prev_ad_map = readMapFromSpreadsheet();
  prev_ad_map = updatePreviousAdMap(prev_ad_map,ad_map);
  writeMapToSpreadsheet(prev_ad_map);
    
  //Now run through the adgroups to find creative tests
  var ag_iter = AdWordsApp.adGroups().get();
  var paused_ads = [];
  while(ag_iter.hasNext()) {
    var ag = ag_iter.next();
    if(!prev_ad_map[ag.getId()]) { continue; }
      
    //Here is the date range for the test metrics
    var last_touched_str = _getDateString(prev_ad_map[ag.getId()].last_touched,'yyyyMMdd');
    var get_today_str = _getDateString(new Date(),'yyyyMMdd');
      
    var ag_stats = ag.getStatsFor(last_touched_str, get_today_str);
    if(ag_stats['get'+THRESHOLD.metric]() >= THRESHOLD.value) {
      var ad_iter = ag.ads().withCondition('Status = ENABLED')
                            .forDateRange(last_touched_str, get_today_str)
                            .orderBy(METRIC+" "+ASC_OR_DESC).get();
      var ad = ad_iter.next();
      var metric = ad.getStatsFor(last_touched_str, get_today_str)['get'+METRIC]();
      ad.pause();
      paused_ads.push({a : ad, m : metric});
    }
  }
  sendEmailForPausedAds(paused_ads);
}
  
// A function to send an email with an attached report of ads it has paused
function sendEmailForPausedAds(ads) {
  if(ads.length == 0) { return; } //No ads paused, no email
  var email_body = '"' + ['CampaignName','AdGroupName','Headline','Desc1','Desc2','DisplayUrl',METRIC].join('","') + '"\n';
  for(var i in ads) {
    var ad = ads[i].a;
    var metric = ads[i].m;
    email_body += '"' + [ad.getCampaign().getName(),
                         ad.getAdGroup().getName(),
                         ad.getHeadline(),
                         ad.getDescription1(),
                         ad.getDescription2(),
                         ad.getDisplayUrl(),
                         metric
                        ].join('","') +
                  '"\n';
  }
  var date_str = _getDateString(new Date(),'yyyy-MM-dd');
  var options = { attachments: [Utilities.newBlob(email_body, 'text/csv', "FinishedTests_"+date_str+'.csv')] };
  var subject = 'Finished Tests - ' + date_str;
  for(var i in TO) {
    MailApp.sendEmail(TO[i], subject, 'See attached.', options);
  }
}
  
//Given two lists of ads, this checks to make sure they are the same.
function sameAds(ads1,ads2) {
  for(var i in ads1) {
    if(ads1[i] != ads2[i]) { return false; }
  }
  return true;
}
  
//This reads the stored data from the spreadsheet
function readMapFromSpreadsheet() {
  var ad_map = {};
  var sheet = SpreadsheetApp.openById(findSpreadsheetId()).getActiveSheet();
  var data = sheet.getRange('A:C').getValues();
  for(var i in data) {
    if(data[i][0] == '') { break; }
    var [ag_id,last_touched,ad_ids] = data[i];
    ad_map[ag_id] = { ad_ids : (''+ad_ids).split(','), last_touched : new Date(last_touched) };
  }
  return ad_map;
}
  
//This will search for a label containing the spreadsheet id
//If one isn't found, it will create a new one and the label as well
function findSpreadsheetId() {
  var spreadsheet_id = "";
  var label_iter = AdWordsApp.labels().withCondition("Name STARTS_WITH 'history_script:'").get();
  if(label_iter.hasNext()) {
    var label = label_iter.next();
    return label.getName().split(':')[1]; 
  } else {
    var sheet = SpreadsheetApp.create('AdGroups History');
    var sheet_id = sheet.getId();
    AdWordsApp.createLabel('history_script:'+sheet_id, 'stores sheet id for adgroup changes script.');
    return sheet_id;
  }
}
  
//This will store the data from the account into a spreadsheet
function writeMapToSpreadsheet(ad_map) {
  var toWrite = [];
  for(var ag_id in ad_map) {
    var ad_ids = ad_map[ag_id].ad_ids;
    var last_touched = ad_map[ag_id].last_touched;
    toWrite.push([ag_id,last_touched,ad_ids.join(',')]);
  }
  writeToSpreadsheet(toWrite);
}

// Write the keyword data to the spreadsheet
function writeToSpreadsheet(toWrite) {
  var sheet = SpreadsheetApp.openById(findSpreadsheetId()).getActiveSheet();
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < toWrite.length) {
    sheet.insertRows(1,toWrite.length-numRows); 
  }
  var range = sheet.getRange(1,1,toWrite.length,toWrite[0].length);
  range.setValues(toWrite);
}
  
//This builds a map of the ads in the account so that it is easy to compare
function buildCurrentAdMap() {
  var ad_map = {}; // { ag_id : { ad_ids : [ ad_id, ... ], last_touched : date } }
  var ad_iter = AdWordsApp.ads().withCondition('Status = ENABLED').get();
  while(ad_iter.hasNext()) {
    var ad = ad_iter.next();
    var ag_id = ad.getAdGroup().getId();
    if(ad_map[ag_id]) {
      ad_map[ag_id].ad_ids.push(ad.getId());
      ad_map[ag_id].ad_ids.sort();
    } else {
      ad_map[ag_id] = { ad_ids : [ad.getId()], last_touched : new Date() };
    }
  }
  return ad_map;
}
  
//This takes the old ad map and the current ad map and returns an
//updated map with all changes.
function updatePreviousAdMap(prev_ad_map,ad_map) {
  for(var ag_id in ad_map) {
    var current_ads = ad_map[ag_id].ad_ids;
    var previous_ads = (prev_ad_map[ag_id]) ? prev_ad_map[ag_id].ad_ids : [];
    if(!sameAds(current_ads,previous_ads)) {
      prev_ad_map[ag_id] = ad_map[ag_id];
    }
  }
  return prev_ad_map;
}
  
//Helper function to format the date
function _getDateString(date,format) {
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format); 
}

Monday, June 17, 2013

Finding Anomalies In Your Keywords, Adgroups, and Ads

UPDATE 2013-07-23: Added the ability to add labels to your ads as well. This might be helpful for creative testing.

In a recent post from SearchEngineLand, Larry Kim wants PPC Managers to stop being lazy. Link baiting aside, he has some good points. I think much of the time spent pouring over client spreadsheets is really trying to answer the simple question of "Where Are The Anomalies?"

Anomalies are keywords or AdGroups that seem to be performing very differently than their brothers and sisters in the same AdGroup or Campaign. Maybe you have an AdGroup with 15 keywords and one or two keywords seem to be getting nearly all of the clicks. Or maybe you just have an AdGroup that seems to be eating up a good chunk of your budget and you want to have more control over it. In either of these cases, you need some way to quickly identify which Keywords or AdGroups you want to take action on.

So I created the following script to help me do just that. It has a little bit of statistics embedded in it to calculate the mean and standard deviation of a set of entities (AdGroups or Keywords). Then I apply a label to any entity that seems to be more than two standard deviations away from the mean, which would indicate it is performing much better or much worse than its siblings. That way, I can take action on those entities pretty easily in my account.

This script will also send you an email each day with a summary of the entities that it deems to be anomalies. Your goal is to fix these issues by moving them into their own Campaigns and AdGroups or maybe even getting rid of them altogether (possibly negatives).

Right now, the script will check every metric that is available in the AdWordsApp.stats object. It could be easily modified to check for things like cost per conversion or profit per impression. You will see in the code below where you can remove stats that don't interest you as well.

One note of warning is that I do not claim to be a statistics guy, so this may or may not be a valid way to look at things. Hopefully, it will help you locate the issues in an account quickly and fix them so that you can spend more time being lazy :)

Also, I am open to your comments and suggestions for this script. Is it useful to anyone else?

Thanks,
Russ

/**************************************
* Find the Anomalies
* Created By: Russ Savage
* Version: 1.2
* Changelog v1.2
*  - Fixed divide by 0 errors
*  - Changed SIG_FIGS to DECIMAL_PLACES
* Changelog v1.1
*  - Added ability to tag ad anomalies as well
* FreeAdWordsScripts.com
**************************************/
var DATE_RANGE = 'LAST_30_DAYS';
var DECIMAL_PLACES = 3;
var STANDARD_DEVIATIONS = 2;
var TO = ['you@your_domain.com'];
 
function main() {
  // This will add labels to and send emails about adgroups, keywords and ads. Remove any if you like.
  var levels_to_tag = ['adgroup','keyword','ad'];
  for(var x in levels_to_tag) {
    var report = getContentRows(levels_to_tag[x]);
    var entity_map = buildEntityMap(levels_to_tag[x]);
    for(var parent_id in entity_map) {
      var child_list = entity_map[parent_id];
      var stats_list = Object.keys(child_list[0].stats);
      for(var i in stats_list) {
        var mean = getMean(child_list,stats_list[i]);
        var stand_dev = getStandardDev(child_list,mean,stats_list[i]);
        var label_name = stats_list[i]+"_anomaly";
        report += addLabelToAnomalies(child_list,mean,stand_dev,stats_list[i],label_name,levels_to_tag[x]);
      }
    }
    sendResultsViaEmail(report,levels_to_tag[x]);
  }
}
  
//Takes a report and the level of reporting and sends and email
//with the report as an attachment.
function sendResultsViaEmail(report,level) {
  var rows = report.match(/\n/g).length - 1;
  if(rows == 0) { return; }
  var options = { attachments: [Utilities.newBlob(report, 'text/csv', level+"_anomalies_"+_getDateString()+'.csv')] };
  var email_body = "There are " + rows + " " + level + "s that have abnormal performance. See attachment for details.";
  var subject = 'Abnormal ' + _initCap(level) + ' Entities Report - ' + _getDateString();
  for(var i in TO) {
    MailApp.sendEmail(TO[i], subject, email_body, options);
  }
}
  
//Helper function to return a single row of the report formatted correctly
function toReportRow(entity,level,label_name) {
  var ret_val = [AdWordsApp.currentAccount().getCustomerId(),
                 entity.getCampaign().getName()];
  ret_val.push( (level == 'adgroup') ? entity.getName() : entity.getAdGroup().getName() );
  if(level == 'keyword') {
    ret_val = ret_val.concat([entity.getText(),entity.getMatchType()]); 
  } else if(level == 'ad') {
    ret_val = ret_val.concat([entity.getHeadline(),entity.getDescription1(),entity.getDescription2(),entity.getDisplayUrl()]); 
  }
  ret_val.push(label_name);
  return '"' + ret_val.join('","') + '"\n';
}
  
//Helper function to return the column headings for the report
function getContentRows(level) {
  var ret_val = ['AccountId','CampaignName','AdGroupName'];
  if(level == 'keyword') {
    ret_val = ret_val.concat(['KeywordText','MatchType']); 
  } else if(level == 'ad') {
    ret_val = ret_val.concat(['Headline','Description1','Description2','DisplayUrl']);
  }
  ret_val.push('LabelName');
  return '"' + ret_val.join('","') + '"\n';
}
  
//Function to add the labels to the entities based on the standard deviation and mean.
//It returns a csv formatted string for reporting
function addLabelToAnomalies(entites,mean,sd,stat_key,label_name,level) {
  createLabelIfNeeded(label_name);
  var report = '';
  for(var i in entites) {
    var entity = entites[i]['entity'];
    var deviation = Math.abs(entites[i]['stats'][stat_key] - mean);
    if(sd != 0 && deviation/sd >= STANDARD_DEVIATIONS) {
      entity.applyLabel(label_name);
      report += toReportRow(entity,level,label_name);
    } else {
      entity.removeLabel(label_name);
    }
  }
  return report;
}
  
//This is a helper function to create the label if it does not already exist
function createLabelIfNeeded(name) {
  if(!AdWordsApp.labels().withCondition("Name = '"+name+"'").get().hasNext()) {
    AdWordsApp.createLabel(name);
  }
}
  
//This function returns the standard deviation for a set of entities
//The stat key determines which stat to calculate it for
function getStandardDev(entites,mean,stat_key) {
  var total = 0;
  for(var i in entites) {
    total += Math.pow(entites[i]['stats'][stat_key] - mean,2);
  }
  if(Math.sqrt(entites.length-1) == 0) {
    return 0;
  }
  return round(Math.sqrt(total)/Math.sqrt(entites.length-1));
}
  
//Returns the mean (average) for the set of entities
//Again, stat key determines which stat to calculate this for
function getMean(entites,stat_key) {
  var total = 0;
  for(var i in entites) {
    total += entites[i]['stats'][stat_key];
  }
  if(entites.length == 0) {
    return 0;
  }
  return round(total/entites.length);
}
  
//This function returns a map of the entities that I am processing.
//The format for the map can be found on the first line.
//It is meant to work on AdGroups and Keywords
function buildEntityMap(entity_type) {
  var map = {}; // { parent_id : [ { entity : entity, stats : entity_stats } ], ... }
  var iter = getIterator(entity_type);
  while(iter.hasNext()) {
    var entity = iter.next();
    var stats = entity.getStatsFor(DATE_RANGE);
    var stats_map = getStatsMap(stats);
    var parent_id = getParentId(entity_type,entity);
    if(map[parent_id]) { 
      map[parent_id].push({entity : entity, stats : stats_map});
    } else {
      map[parent_id] = [{entity : entity, stats : stats_map}];
    }
  }
  return map;
}
  
//Given an entity type (adgroup or keyword) this will return the parent id
function getParentId(entity_type,entity) {
  switch(entity_type) {
    case 'adgroup' :
      return entity.getCampaign().getId();
    case 'keyword':
      return entity.getAdGroup().getId();
    case 'ad':
      return entity.getAdGroup().getId();
  }
}
  
//Given an entity type this will return the iterator for that.
function getIterator(entity_type) {
  switch(entity_type) {
    case 'adgroup' :
      return AdWordsApp.adGroups().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
    case 'keyword' :
      return AdWordsApp.keywords().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
    case 'ad' :
      return AdWordsApp.ads().forDateRange(DATE_RANGE).withCondition("Impressions > 0").get();
  }
}
  
//This returns a map of all the stats for a given entity.
//You can comment out the things you don't really care about.
function getStatsMap(stats) {
  return { // You can comment these out as needed.
          avg_cpc : stats.getAverageCpc(),
          avg_cpm : stats.getAverageCpm(),
          avg_pv : stats.getAveragePageviews(),
          avg_pos : stats.getAveragePosition(),
          avg_tos : stats.getAverageTimeOnSite(),
          bounce : stats.getBounceRate(),
          clicks : stats.getClicks(),
          cv : stats.getConversionRate(),
          conv : stats.getConversions(),
          cost : stats.getCost(),
          ctr : stats.getCtr(),
          imps : stats.getImpressions()
         };
}
  
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}
  
//Helper function to capitalize the first letter of a string.
function _initCap(str) {
  return str.replace(/(?:^|\s)\S/g, function(a) { return a.toUpperCase(); });
}

// A helper function to make rounding a little easier
function round(value) {
  var decimals = Math.pow(10,DECIMAL_PLACES);
  return Math.round(value*decimals)/decimals;
}

Thursday, April 18, 2013

Pause or Enable Campaigns, Keywords or Ads on a Specific Date

UPDATE 2013-12-13: Added the ability to work on Ads as well.

I've seen some questions posted around the forums asking about enabling or pausing keywords on a given day. It is pretty simple to do using labels and scripts. The following script will run through your account and look for keywords labeled with "Pause on " or "Enable on " and perform that action. If you would like to just use dates, you can set the two prefix values to be empty strings (""). The format of the date is YYYY-MM-DD (2013-05-01).

Thanks,
Russ

/**************************************************
* Pause or Enable Campaigns, Keywords or Ads on a Given Date
* Version 1.2
* Changelog v1.2 - Added ability to pause Campaigns
* Changelog v1.1 - Added ability to run on Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************************/
var ENTITY = 'Keyword'; //or Ad or Campaign
var PAUSE_PREFIX = "Pause on "; //look for labels "Pause on 2013-04-11"
var ENABLE_PREFIX = "Enable on "; //look for labels "Enable on 2013-04-11"

  
function main() {
  var todayStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
  var pauseStr = PAUSE_PREFIX+todayStr;
  var enableStr = ENABLE_PREFIX+todayStr;
  Logger.log("Looking for labels: " + [pauseStr,enableStr].join(' and '));
   
  var labelsArray = buildLabelArray(pauseStr,enableStr);
   
  if(labelsArray.length > 0) { 
    var labelsStr = "['" + labelsArray.join("','") + "']";
    var entityIter;
    if(ENTITY === 'Keyword') {
      entityIter = AdWordsApp.keywords().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Ad') {
      entityIter = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Campaign') {
      entityIter = AdWordsApp.campaigns().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else {
      throw 'Invaid ENTITY type. Should be Campaign, Keyword or Ad. ENTITY:'+ENTITY;
    }
     
    while(entityIter.hasNext()) {
      var entity = entityIter.next();
      pauseEntity(entity, pauseStr);
      enableEntity(entity, enableStr);
    }
  }
}
 
//Helper function to build a list of labels in the account
function buildLabelArray(pauseStr,enableStr) {
  var labelsArray = [];
  try {
    var labelIter = AdWordsApp.labels().withCondition("Name IN ['"+pauseStr+"','"+enableStr+"']").get();
    while(labelIter.hasNext()) {
      labelsArray.push(labelIter.next().getName());
    }
    return labelsArray;
  } catch(e) {
    Logger.log(e);
  }
  return [];
}
 
//Helper function to pause entities
function pauseEntity(entity, pauseStr) {
  var labelIter = entity.labels().withCondition("Name = '"+pauseStr+"'").get();
  if(labelIter.hasNext()) {
    entity.pause();
    entity.removeLabel(pauseStr);
  }
}
 
//Helper function to enable entities
function enableEntity(entity, enableStr) {
  var labelIter = entity.labels().withCondition("Name = '"+enableStr+"'").get();
  if(labelIter.hasNext()) {
    entity.enable();
    entity.removeLabel(enableStr);
  }
}

Wednesday, April 10, 2013

Report on Broken Urls In Your Account

Note: If you are looking for a version of this script to run at the MCC level, check out Monitor Broken Links Using MCC Level Scripts.

UPDATED: 2013-05-20: Based on a comment from a reader, the script now only checks active campaigns and adgroups and it also only checks each url once.

UPDATED: 2013-04-28: Based on a comment from a reader, I made some updates to this script which include adding the response code to the email and formatting the results as an attachment.

It happens to the best of us. Sometimes, we remove pages on our site or update links and forget to make the corresponding change our SEM accounts. So tonight I put together a quick script to run through all your ads and keywords and create an email report with any of them that return a 404 Not Found or a 500 Server Error response code. You can easily add more error codes to check for by adding them to the BAD_CODES array at the beginning of the script.

Thanks,
Russ

/****************************
* Find Broken Urls In Your Account
* Version 1.1
* ChangeLog v1.1
*  - Updated to only see Text Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************/
function main() {
  // You can add more if you want: http://goo.gl/VhIX
  var BAD_CODES = [404,500];
  var TO = ['email@example.com'/*,'email_address_2@example.com'*/];
  var SUBJECT = 'Broken Url Report - ' + _getDateString();
  var HTTP_OPTIONS = {
    muteHttpExceptions:true
  };
   
  //Let's look at ads and keywords for urls
  var iters = [
    //For Ad Level Urls
    AdWordsApp.ads()
      .withCondition("Status = 'ENABLED'")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .withCondition("Type = 'TEXT_AD'")
      .get(),
    //For Keyword Level Urls
    AdWordsApp.keywords()
      .withCondition("Status = 'ENABLED'")
      .withCondition("DestinationUrl != ''")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .get()
    ];
  
  var already_checked = {}; 
  var bad_entities = [];
  for(var x in iters) {
    var iter = iters[x];
    while(iter.hasNext()) {
      var entity = iter.next();
      if(entity.getDestinationUrl() == null) { continue; }
      var url = entity.getDestinationUrl();
      if(url.indexOf('{') >= 0) {
        //Let's remove the value track parameters
        url = url.replace(/\{[0-9a-zA-Z]+\}/g,'');
      }
      if(already_checked[url]) { continue; }
      var response_code;
      try {
        Logger.log("Testing url: "+url);
        response_code = UrlFetchApp.fetch(url, HTTP_OPTIONS).getResponseCode();
      } catch(e) {
        //Something is wrong here, we should know about it.
        bad_entities.push({e : entity, code : -1});
      }
      if(BAD_CODES.indexOf(response_code) >= 0) {
        //This entity has an issue.  Save it for later. 
        bad_entities.push({e : entity, code : response_code});
      }
      already_checked[url] = true;
    }
  }
  var column_names = ['Type','CampaignName','AdGroupName','Id','Headline/KeywordText','ResponseCode','DestUrl'];
  var attachment = column_names.join(",")+"\n";
  for(var i in bad_entities) {
    attachment += _formatResults(bad_entities[i],",");
  }
  if(bad_entities.length > 0) {
    var options = { attachments: [Utilities.newBlob(attachment, 'text/csv', 'bad_urls_'+_getDateString()+'.csv')] };
    var email_body = "There are " + bad_entities.length + " urls that are broken. See attachment for details.";
     
    for(var i in TO) {
      MailApp.sendEmail(TO[i], SUBJECT, email_body, options);
    }
  }  
}
 
//Formats a row of results separated by SEP
function _formatResults(entity,SEP) {
  var e = entity.e;
  if(typeof(e['getHeadline']) != "undefined") {
    //this is an ad entity
    return ["Ad",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getHeadline(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  } else {
    // and this is a keyword
    return ["Keyword",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getText(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  }
}
 
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}

Saturday, March 30, 2013

Automate Your Ads Based on Your Baseball Team's Schedule

I know everyone is focused on March Madness right now, but opening day for Baseball season is right around the corner. I wondered if it were possible to enable and disable specific ads based on the schedules of your local MLB team.

It turns out, the MLB has every team's full schedule in an easy to parse CSV format. All you need to do is search Google for " downloadable schedule" and look for the page that ends in "downloadable.jsp". It will look something like this:


The link you need is circled in the image above. You can see from the url what the team_id value should be in the code below. And if you right-click and download that .csv file, you will be able to see what value to use for the home_field as well. In the example below, I used my hometown teams of the Cubs and the White Sox, but you can use any teams you want.

The script itself will run through and look for a home game for your team. If the team is playing today and it is a home game, it will enable any ads that are tagged with the label you configured in the TEAM_INFO. If the team isn't playing a home game today, it will pause those same ads.

In the example below, I only used a portion of the data in the csv files. It would also be possible to enable ads only during the actual game, or enable ads for all game days, not just home games.

I encourage you to play around with the data and see what you can come up with. If you find anything useful, please leave a comment.

Thanks,
Russ


//-----------------------------------
// Enable/Disable Ads Based on the MLB Schedule
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var TEAM_INFO = [
    { team_id : 112, home_field : 'Wrigley Field', label : 'cubs' }, // Cubs
    { team_id : 145, home_field : 'U.S. Cellular Field', label : 'whitesox' } // White Sox
  ];
  //hopefully you've already created and tagged some ads with these labels
  //but just in case...
  createLabelsIfNeeded(TEAM_INFO); 
  
  var SEASON = (new Date()).getFullYear();
  var is_home = false, is_game_day = false;
  for(var t in TEAM_INFO) {
    var team = TEAM_INFO[t];
    var url = "http://mlb.mlb.com/soa/ical/schedule.csv?team_id="+team.team_id+"&season="+SEASON;
    var html = UrlFetchApp.fetch(url).getContentText();
    var date_list = html.split("\r\n");
    for(var i in date_list) {
      if(i == 0) {continue;}
      var [start_date,start_time,start_time_et,
           subject,location,description,
           end_date,end_date_et,end_time,end_time_et] = date_list[i].split(",");
      
      var today = new Date();
      var game_day = new Date();
      game_day.setFullYear(SEASON,parseInt(start_date.split("/")[0])-1,parseInt(start_date.split("/")[1]));
      
      is_home = (location == team.home_field);
      is_game_day = (diffDays(game_day,today) == 0);
      
      if(is_home && is_game_day) {
        enableBaseballAds(team.label);
        break;
      }
    }
    if(!(is_home && is_game_day)) {
      disableBaseballAds(team.label); 
    }
  }
  
}

function enableBaseballAds(label) {
  Logger.log("Enabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().enable(); 
  }
}

function disableBaseballAds(label) {
  Logger.log("Disabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().pause(); 
  }
}

function createLabelsIfNeeded(team_info) {
  var label_iter = AdWordsApp.labels().get();
  var label_list = [];
  while(label_iter.hasNext()) {
    label_list.push(label_iter.next().getName());
  }
  for(var i in team_info) {
    if(label_list.indexOf(team_info[i].label) == -1) {
      AdWordsApp.createLabel(team_info[i].label);
      label_list.push(team_info[i].label);
    }
  }
}

//A helper function to compare dates.
//Copied from: http://goo.gl/uW48a
function diffDays(firstDate,secondDate) {
  var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds
  return Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay))); 
}

Sunday, March 10, 2013

Manage Ads Based on Airport Delays

There are tons of APIs out on the internet today. Any of these can be incorporated into your AdWords scripts to automate some of your account management.

The script below uses the Airport Status Service from FAA.gov to enable and pause ads when there is a delay at a major airport. This would be useful for any business that is near an airport that may benefit from delays such as restaurants, bars, or hotels. Your ads could say things like:



You can add or remove airport codes from the list at the top. All you need to do is tag your ads with the label [airport_code]_delay ("ORD_delay","ATL_delay").

What other dynamic data could you use to optimize your ads? Movie times? Sports games? Let me know in the comments below and I'll see what I can come up with.

Thanks,
Russ

/*********************************************
* Pause/Enable Ads Based On Airport Delays
* Version 1.1
* ChangeLog v1.1 
*  - Added ability to completely pause non-delay ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
*********************************************/
// For this to work, you need to add a label to all
// the ads for each airport.  For example, PIT_normal
// or SFO_normal
var PAUSE_NORMAL_ADS_DURING_DELAY = false;
var DELAY_SUFFIX = '_delay'; //the suffix on the label for delayed ads
var NORMAL_SUFFIX = '_normal'; //the suffix on the label for normal ads

var AIR_CODES = ["ATL","ANC","AUS","BWI","BOS","CLT","MDW","ORD","CVG","CLE",
                 "CMH","DFW","DEN","DTW","FLL","RSW","BDL","HNL","IAH","HOU",
                 "IND","MCI","LAS","LAX","MEM","MIA","MSP","BNA","MSY","JFK",
                 "LGA","EWR","OAK","ONT","MCO","PHL","PHX","PIT","PDX","RDU",
                 "SMF","SLC","SAT","SAN","SFO","SJC","SNA","SEA","STL","TPA",
                 "IAD","DCA"];

function main() {
  var faaUrl = "http://services.faa.gov/airport/status/";
  var args = "?format=application/json";
  for(var i in AIR_CODES) {
    try{
      var resp = UrlFetchApp.fetch(faaUrl + AIR_CODES[i] + args);
      if( resp.getResponseCode() == 200 ) {
        var json = Utilities.jsonParse(resp.getContentText());
        if(json.delay == "false") {
          Logger.log("No delays at "+json.IATA+". Pausing delay ads if any are running.");
          turnOffDelayAds(json.IATA);
          if(PAUSE_NORMAL_ADS_DURING_DELAY) {
            turnOnNonDelayAds(json.IATA);
          }
        } else {
          Logger.log("Delays in "+json.IATA+" Reason: "+json.status.reason);
          Logger.log("Turning on delay ads if there are any.");
          turnOnDelayAds(json.IATA);
          if(PAUSE_NORMAL_ADS_DURING_DELAY) {
            turnOffNonDelayAds(json.IATA);
          }
        }
      }
    }
    catch(e) {
      Logger.log("Error: " + e.message);
    }
  }
}

function turnOffDelayAds(airportCode) {
  var labelName = airportCode + DELAY_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = ENABLED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().pause();
  }
}

function turnOffNonDelayAds(airportCode) {
  var labelName = airportCode + NORMAL_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = ENABLED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().pause();
  }
}

function turnOnDelayAds(airportCode) {
  var labelName = airportCode + DELAY_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = PAUSED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().enable();
  }
}

function turnOnNonDelayAds(airportCode) {
  var labelName = airportCode + NORMAL_SUFFIX;
  var adIter = AdWordsApp.ads()
    .withCondition("LabelNames CONTAINS_ANY ['"+labelName+"']")
    .withCondition("CampaignStatus = ENABLED")
    .withCondition("AdGroupStatus = ENABLED")
    .withCondition("Status = PAUSED")
    .get();
  while(adIter.hasNext()) {
    adIter.next().enable();
  }
}

Monday, March 4, 2013

Fixing Capitalization Errors in Your Ads

One of the issues that marketers face when they have systems for automatically building creatives from feeds is quality control. I recently ran into an issue where I created around 1,000 new ads where the city name was IN ALL CAPS. Google of course doesn't like that much and I didn't like the idea of having to manually update 1,000 ads, so I put together the following script.

The script runs through your account for all of the disapproved ads and tries to find ones with words in ALL CAPS. I'm not sure what Google's limit on caps is, but in the script below, it looks for anything with 3 or more capital letters in a row. Then it replaces them, creates a new ad, and if it was successful, deletes the old ad.

I ran into some trouble trying to identify the reasons the ads were disapproved. It is not yet available in the API, but it has been requested. The other issue was that sometimes, the new ad you create will fail, which will cause you to delete the old one and not create a new one. I solved that one by counting the ads in the adgroup before and after I created the new ad in order to make sure my ad had been created. Again, I posted the feature request in the adwords scripting forums.

Thanks,
Russ

//-----------------------------------
// Fix Ads with EXCESSIVE CAPITALIZATION
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var find_caps = /[A-Z]{3,}/g;
  var SEP = '~~@~~'; // this needs to be something you would never put in your ads.
  var ad_iter = AdWordsApp.ads().withCondition("ApprovalStatus = 'DISAPPROVED'").get();
  
  while(ad_iter.hasNext()) {
    var ad = ad_iter.next();
    var old_ad_cnt = get_ad_count(ad.getAdGroup());
    var old_ad_str = [ad.getHeadline(),ad.getDescription1(),ad.getDescription2(),ad.getDisplayUrl()].join(SEP);
    var new_ad_str = old_ad_str;
    Logger.log("Before:"+old_ad_str);
    var m = "";
    while((m = find_caps.exec(new_ad_str)) != null) {
      new_ad_str = replace_all(new_ad_str,m[0],init_cap(m[0]),false);
    }
    Logger.log("After:"+new_ad_str);
    if(old_ad_str != new_ad_str) {
      var [new_headline,new_desc1,new_desc2,new_disp_url] = new_ad_str.split(SEP);
      ad.getAdGroup().createTextAd(new_headline, new_desc1, new_desc2, new_disp_url, ad.getDestinationUrl());
      var new_ad_cnt = get_ad_count(ad.getAdGroup());
      if(new_ad_cnt == (old_ad_cnt+1)) {
        ad.remove();
      }
    } else {
      Logger.log("Skipping because no changes were made."); 
    }
  }
  
  function init_cap(s) {
    return s.charAt(0).toUpperCase() + s.slice(1).toLowerCase();
  }
  
  // This function was adapted from: http://dumpsite.com/forum/index.php?topic=4.msg8#msg8 
  function replace_all(original,str1, str2, ignore) {
    return original.replace(new RegExp(str1.replace(/([\/\,\!\\\^\$\{\}\[\]\(\)\.\*\+\?\|\<\>\-\&])/g,"\\$&"),(ignore?"gi":"g")),(typeof(str2)=="string")?str2.replace(/\$/g,"$$$$"):str2);
  }
  
  function get_ad_count(ad_group) {
    var ad_iter = ad_group.ads().get();
    var new_ad_cnt = 0;
    while(ad_iter.hasNext()) {
      ad_iter.next();
      new_ad_cnt++;
    }
    return new_ad_cnt;
  }
}

Monday, November 26, 2012

Update Ads for the New Year

This is a companion to one of my earlier posts about updating your keywords for the new year.  You can easily run through each ad you have and update them all for the new year using the script below.  It will also pause the old ad as well, making sure your staying relevant to your customers.  You should schedule this to run every year on January 1st if your advertising relies heavily on dated traffic.

Thanks,
Russ

//-----------------------------------
// Update Ads for 2012
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var OLD_YEAR = "2011";
  var NEW_YEAR = "2012";
  //You probably shouldn't update destination urls unless you know what you are doing.
  var FIELDS_CONTAINING_YEAR = ["Headline","Description1",
                                "Description2","DisplayUrl"
                                /*,"DestinationUrl"*/
                               ];
  
  for(i in FIELDS_CONTAINING_YEAR) {
    var field_iter = AdWordsApp.ads()
        .withCondition(FIELDS_CONTAINING_YEAR[i] + " CONTAINS " + OLD_YEAR)
        .withCondition("Status = ENABLED")
        .get();
    
    _iterateThroughAds(field_iter);
  }

  //---------------
  // Private Helper Functions
  //---------------  
  function _iterateThroughAds(ad_iter) {
    while (ad_iter.hasNext()) {
      var ad = ad_iter.next();
      var ag = ad.getAdGroup();
      _createNewAdFromOldAd(ag,ad);
    }
  }
  
  function _createNewAdFromOldAd(adgroup, old_ad) {
    //get the updated ad texts replacing all the old years with the new years
    var new_headline = old_ad.getHeadline().replace(OLD_YEAR,NEW_YEAR);
    var new_desc1 = old_ad.getDescription1().replace(OLD_YEAR,NEW_YEAR);
    var new_desc2 = old_ad.getDescription2().replace(OLD_YEAR,NEW_YEAR);
    var new_display_url = old_ad.getDisplayUrl().replace(OLD_YEAR,NEW_YEAR);
    var new_dest_url = old_ad.getDestinationUrl();/*.replace(OLD_YEAR,NEW_YEAR);*/
    
    //now create the new ad and pause the old one.
    adgroup.createTextAd(new_headline,new_desc1,new_desc2,new_display_url,new_dest_url);
    old_ad.pause();
  }
}

Thursday, November 22, 2012

Delete All Disapproved Ads in an Account

Again, another simple script that just focuses on account maintenance. Over time, large accounts might build up thousands of adgroups with ads that might be disapproved. Sometimes, it makes sense to remove those and then slowly build and deploy new creative (we will have a script to build new creative later). Hopefully this will work for you.

Thanks,
Russ
//-----------------------------------
// Delete Ads That Are Disapproved
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  // Let's start by getting all of the ad that are disapproved
  var ad_iter = AdWordsApp.ads()
  .withCondition("ApprovalStatus != APPROVED")
  .get();

  // Then we will go through each one
  while (ad_iter.hasNext()) {
    var ad = ad_iter.next();
    // now we delete the ad
    Logger.log("Deleteing ad: " + ad.getHeadline());
    ad.remove();
  }
}