Wednesday, January 16, 2013

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

12 comments:

  1. Hi Russ,

    This is a great help thank you.

    Can we set set these Ad Params at ad group level or are they applied to keywords only?

    Thanks again.
    Neilos

    ReplyDelete
  2. It should be pretty easy to modify the script above to update ad params based on ad group name. Follow these steps:

    1. First you need to update the spreadsheet. Currently we have keyword text and matchtype, but if you want to use adgroup instead, just update the column A header to be "adgroup", and keep column be the same. We will just ignore it later.

    2. Now, the logic is going to pull column A in the kw_text parameter. You can change the name if you like, but you don't have to. Now we need to update the Keyword Selector to find all the keywords in a given adgroup. So change the line:

    .withCondition("Text = '"+kw_text+"'")

    to

    .withCondition("AdGroupName = '"+kw_text+"'")

    3. So now you are pulling all the keywords in a given adgroup. The last update would be to remove the check for matchtype. So delete the line:

    if(kw.getMatchType() == mt) {

    And then also the closing } two lines down.

    You should end up with something like this:

    var kw_iter = AdWordsApp.keywords()
    .withCondition("AdGroupName = '"+kw_text+"'")
    .withCondition("Status = ENABLED")
    .get();
    while(kw_iter.hasNext()) {
    var kw = kw_iter.next();
    kw.setAdParam(1, param1);
    kw.setAdParam(2, param2);
    }


    Let me know if you have any issues.

    Thanks for checking out my blog,
    Russ

    ReplyDelete
    Replies
    1. I have edited the script to edit at the ad group level as above and it seems to keep throwing up 'script runtime errors'

      Any idea why?

      Delete
    2. Hi Ashley, Could you post your script here so that I could take a look at it?

      Thanks,
      Russ

      Delete
    3. Russell, thanks for this great collection of AW Scripts.

      I tried your suggestion above to update prices for all keywords within an ad group and it works fine.

      However, I noticed that it only works when I use a copy of your sample spreadsheet. The moment I create my own, the script generates this error: TypeError: Cannot call method "getRange" of null. (line 11)

      All that's different is the Google Drive URL.

      Do you use any special formatting in the spreadsheet?

      Cheers,
      Stefan

      Delete
    4. Make sure that you name the sheet in your new spreadsheet KeywordParams. By default, it is named Sheet1. Let me know if that solves your issue.

      Thanks for reading,
      Russ

      Delete
    5. That was it! Thanks again for the help, Russ.

      Delete
  3. Great stuff. Thanks!

    Could you help modify the script using regex so that it chooses ad groups containing the word/words in the spreadsheet?

    BR

    Mads

    ReplyDelete
  4. Hi Mads, I think all you need to do is modify the script using the comment above, but instead of saying:

    .withCondition("AdGroupName = '"+kw_text+"'")

    Change it to be

    .withCondition("AdGroupName CONTAINS_IGNORE_CASE '"+kw_text+"'")

    That will match any Ad Groups that contain the word from the spreadsheet. Let me know if that works for you. More information about the operators available for the .withCondition function can be found here: https://developers.google.com/adwords/scripts/docs/reference/adwordsapp_adgroupselector#withCondition_1.

    Thanks,
    Russ

    ReplyDelete
  5. Does this update business data feed or is it stored elsewhere?

    When i try to use the script i dont get any errors however I don't see it updating the feed either so how can see if and whats updating?

    I am trying to update these ad params hourly but appears I cant do it with bulk upload with csv or spreadsheet and only works if i manually do it from the schedule window of that specific data feed.

    Is there a way to update the feed hourly? Even 2 hours would be fine, 6 hours is too long for what I am hoping to achieve.

    ReplyDelete