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

Automating Maintenance Tasks With AdWords Scripting Part 1

I'm not ashamed.  I read "for Dummies" books.  And one of my favorites is AdWords for Dummies. Say what you want about these books, but they are easy to read and have great info for getting started quickly.

One of the chapters in the book describes a monthly routine for maintaining your AdWords account.  Using AdWords scripts is a good way to automate these tasks.  Let's start with the first of the month.

"On the first day of the month, deal with all the keywords that are converting too expensively."

Sounds simple enough.  Of course, you will need to have set up conversion tracking for all of this to work. They say pull the last 30 days, which is what we have done here, but you can pull any time range you like.  This follows the example from the book.  You should of course replace the values at the top with ones that make sense for your business.

//-----------------------------------
// Reduce Bids on High Cost per Conversion Keywords
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  //Let's reduce keywords with a CPC greater than $15 by 35%
  var WAY_TOO_HIGH_COST_PER_CONV = 15;
  var WAY_TOO_HIGH_BID_REDUCTION_AMOUNT = .35;
  
  //And keywords with CPC between $10 and $15 by 20%
  var TOO_HIGH_COST_PER_CONV = 10;
  var TOO_HIGH_BID_REDUCTION_AMOUNT = .20;
  
  var kw_iter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .get();
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var kw_stats = kw.getStatsFor("LAST_30_DAYS");
    var cost = kw_stats.getCost();
    var conversions = kw_stats.getConversions();
    if(conversions > 0) {
      var cost_per_conversion = (cost/(conversions*1.0));
      //Here is the magic.  If it is way too high, reduce it by the way too high amount
      if(cost_per_conversion >= WAY_TOO_HIGH_COST_PER_CONV) {
        kw.setMaxCpc(kw.getMaxCpc() * (1-WAY_TOO_HIGH_BID_REDUCTION_AMOUNT)); 
      }
      //otherwise, if it is still too high, reduce it with just the too high amount
      else if(cost_per_conversion >= TOO_HIGH_COST_PER_CONV) {
        kw.setMaxCpc(kw.getMaxCpc() * (1-TOO_HIGH_BID_REDUCTION_AMOUNT));
      }
    }else{
      //no conversions on this keyword
      //we will deal with that later
      continue;
    }
  }
}

And that's it. Stay tuned for the next installment where we tackle the keywords that are performing well by increasing their bids.

Thanks,
Russ

Getting Started with Using These Scripts

Much like the movie that shows the opening title fifteen minutes into the feature, today I'm going to talk a little about actually using the scripts that are posted on this site.  Think of it as a getting started guide for using the information presented on this blog.

Most of the information here comes from working through the developer documentation for AdWords scripting, which can be found here.

How do I actually get to the place where I enter the scripts?

The easiest way I've found to get to the place to enter these scripts is to select the "Create and Manage Scripts >>" option from the "Automate" button on the campaigns tab.

That will land you on a page that allows you to create a new script.

Once you have created a new script, you should be able to copy any of the scripts on this blog directly into the text box (after deleting what is in there for starters).

Google has an awesome feature that allows you to preview the results of the script before actually running it on your account.  In order for anything to run, you must "authorize" the script to access your account.  Just click the "Authorize now" button and follow the prompts.

Once you preview the script, you can name it, save it, and run it for real on your account.  Scheduling it to run is also just a simple matter of clicking on the "Create schedule" link on the scripts page.

And there you have it.  Let me know if you have any trouble setting up one of my scripts by commenting on the post.  If you are having trouble, likely someone else is as well.

Thanks,

Russ