Update Ad Params from Google Spreadsheet

2013-07-11 - NOTE: For those wanting to update AdParams at an AdGroup level, check out an updated version of this script.

Today we have a little script to help adjust keyword ad params at a large scale.  This is an example of how you can use the integration with Google Spreadsheets to update param1 and param2 values for keywords.  I have provided a sample spreadsheet which you can make a copy of.

As long as the Adwords Account and the Google Docs account use the same login, it will be able to access any spreadsheet you have.  I suggest opening my sample spreadsheet and then making a copy if it in your account.  You can then replace the URL from the script below with the url of your copy of the spreadsheet.

Link to my sample spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodC1yVjUwc2Y1NDIzUVFLLThJdTFPUnc#gid=0

Thanks,
Russ

/************************************************
* Update Ad Params Using a Google Spreadsheet
* Version 1.1
* ChangeLog v1.1
*  - Added the ability to enable param1 or 2 individually
*  - Looks for Keywords on all sheets
*  - Runs much faster
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************************/
var SPREADSHEET_URL = "THE URL FOR YOUR GOOGLE SPREADSHEET GOES HERE";
var SET_PARAM1 = true;
var SET_PARAM2 = false;
var DATA_RANGE = 'A:E'; // A - CampaignName, B - AdGroupName, 
                        // C - Keyword, D - Param1, E - Param2

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var allSheets = spreadsheet.getSheets();
  var allData = [];
  for(var i in allSheets) {
    var sheet = allSheets[i];
    var data = sheet.getRange(DATA_RANGE).getValues();
    data.shift(); //get rid of headers
    allData = allData.concat(data);
  }
  
  var allDataHash = {};
  for(var i in allData) {
    var row = allData[i];
    if(row[0] === '') { continue; }
    var rowKey = [row[0],row[1],row[2]].join('~~!~~');
    allDataHash[rowKey] = { param1 : row[3], param2: row[4] };
  }
  
  var kwIter = AdWordsApp.keywords()
    .withCondition('CampaignStatus = ENABLED')
    .withCondition('AdGroupStatus = ENABLED')
    .withCondition('Status = ENABLED')
    .get();
  
  while(kwIter.hasNext()) { 
    var kw = kwIter.next();
    var campName = kw.getCampaign().getName();
    var adGroupName = kw.getAdGroup().getName();
    var rowKey = [campName,adGroupName,kw.getText()].join('~~!~~');
    if(allDataHash[rowKey]) {
      if(SET_PARAM1) { kw.setAdParam(1, allDataHash[rowKey].param1); }
      if(SET_PARAM2) { kw.setAdParam(2, allDataHash[rowKey].param2); }
    }
  }
}

Auto Add ValueTrack Params To All Destination URLs

Today we have a script that will help make sure all of your keywords are tagged with the appropriate ValueTrack parameters.  You can use this to ensure that all of your tracking params are added correctly to your urls.

You can add your own query string parameters to the mapping at the top of the script.  For example, if all the keywords in your account need to have the parameter "channel=sem" in their destination url, you could add that to the URL_PARAMS_TO_ADD mapping at the top of the file and it will be added to all your urls.

Also, I updated the script to allow you to add whatever values you want from the account into the url. See the examples for adding Campaign and AdGroup names to the url if you like.

Thanks,
Russ

/******************************************
* Auto Add ValueTrack (and other) Params If Not There
* Created By: Russ Savage
* Version 1.1
* ChangeLog v1.1
*  - Added the ability to add function calls into the parameters to add
* FreeAdWordsScripts.com
******************************************/
function main() {
   
  var URL_PARAMS_TO_ADD = { 
    "kw" : "{keyword}",
    "crid" : "{creative}",
    "mt" : "{matchtype}",
    "ntwk" : "{network}",
    "ap" : "{adposition}",
    "dv" : "{device}",
    "dvm" : "{devicemodel}",
    //"camp" : getCampaignName,  <----- This function is defined below
    //"ag" : getAdGroupName  <----- and so is this one
  };
   
  var kw_iter = AdWordsApp.keywords()
                  .withCondition("DestinationUrl != ''")
                  .get();
  
  while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    var destUrl = kw.getDestinationUrl();
    if(!destUrl || destUrl === '') { continue; }
    var toAppend = [];
    for(var key in URL_PARAMS_TO_ADD) {
      if(destUrl.indexOf(key) >= 0) { continue; }
      var value = URL_PARAMS_TO_ADD[key];
      
      //check to see if we should call a function
      if(typeof(value) === 'function') {
        value = encodeURI(value(kw));
      }
      toAppend.push(key+"="+value);
    }
    if(destUrl.indexOf('?') >= 0) {
      kw.setDestinationUrl(destUrl + "&"+toAppend.join('&'));
    } else {
      kw.setDestinationUrl(destUrl + "?"+toAppend.join('&'));
    }
    Logger.log(kw.getDestinationUrl());
  }
}

// As long as the function call is passed a kw object, you can insert
// whatever value you want into the url
function getCampaignName(kw) {
  return kw.getCampaign().getName();
}

function getAdGroupName(kw) {
  return kw.getAdGroup().getName();
}

Automating Maintenance Tasks with AdWords Scripting Part 3

In our final installment of automating the monthly maintenance tasks found in AdWords for Dummies with AdWords scripting, let's end with the end of the month clean up of your account.

Declutter your account by shedding keywords that don't have any conversions.

Similar to the previous post, you will need to have set up conversion tracking for all of this to work.  We can actually use a variation of the previous script to accomplish this.  Really we are doing the same thing just increasing bids instead of decreasing them.

//-----------------------------------
// Pause Keywords That Are Not Performing
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var THE_VALUE_OF_ONE_CONVERSION = 10;
  var DECREASE_BIDS_BY_PERCENTAGE = .5;
  
  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) {
      if(THE_VALUE_OF_ONE_CONVERSION * 5 > cost) {
        kw.pause();
      }
      else if(THE_VALUE_OF_ONE_CONVERSION * 2 > cost) {
        kw.setMaxCpc(kw.getMaxCpc() * (1-DECREASE_BIDS_BY_PERCENTAGE)); 
      }
    }else{
      //no conversions on this keyword
      //we will deal with that later
      continue;
    }
  }
}

Thanks,
Russ

Automating Maintenance Tasks with AdWords Scripting Part 2

Let's continue with automating the maintenance tasks found in AdWords for Dummies with AdWords scripting. Let's continue with the maintenance that is run on the 15th of the month.

Reward keywords that are bringing in cheap traffic.

Similar to the previous post, you will need to have set up conversion tracking for all of this to work.  We can actually use a variation of the previous script to accomplish this.  Really we are doing the same thing just increasing bids instead of decreasing them.

//-----------------------------------
// Increase Bids Cheap Conversion Keywords
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  //For keywords with less than $5 CPC, let's pump those bids up by 35%
  var AMAZING_COST_PER_CONV = 5;
  var AMAZING_BID_INCREASE_AMOUNT = .35;
  
  //For keywords with between $5 and $8 CPCs, we will only increase the bids by 20%
  var GREAT_COST_PER_CONV = 8;
  var GREAT_BID_INCREASE_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));
      
      if(cost_per_conversion <= AMAZING_COST_PER_CONV) {
        kw.setMaxCpc(kw.getMaxCpc() * (1+AMAZING_BID_INCREASE_AMOUNT)); 
      }
      else if(cost_per_conversion <= GREAT_COST_PER_CONV) {
        kw.setMaxCpc(kw.getMaxCpc() * (1+GREAT_BID_INCREASE_AMOUNT));
      }
    }else{
      //no conversions on this keyword
      //we will deal with that later
      continue;
    }
  }
}

Stay tuned for the next and final installment where we de-clutter our accounts by deleting the keywords with no conversions.

Thanks,
Russ