Showing posts with label google spreadsheet. Show all posts
Showing posts with label google spreadsheet. Show all posts

Thursday, July 11, 2013

Set AdParams at the AdGroup Level from a Google Spreadsheet

I've had a few people ask me how to update my previous script about Setting AdParams at the Keyword Level to be able to set them at the AdGroup level. Here is an update to that script that does just that.

To use it, you will need to create a new Google Spreadsheet and load it with the adgroup name, param 1 value, and param 2 value in columns A, B, and C respectively (with column headers). Then copy that url into the script and you should be good to go.

Thanks,
Russ

/************************************************
* Update Ad Params by Ad Groups
* 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 = "PUT YOUR SPREADSHEET URL HERE";
var SET_PARAM1 = true;
var SET_PARAM2 = false;
var DATA_RANGE = 'A:D'; // A - CampaignName, B - AdGroupName, 
                        // C - Param1, D - 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]].join('~~!~~');
    allDataHash[rowKey] = { param1 : row[2], param2: row[3] };
  }
  
  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].join('~~!~~');
    if(allDataHash[rowKey]) {
      if(SET_PARAM1) { kw.setAdParam(1, allDataHash[rowKey].param1); }
      if(SET_PARAM2) { kw.setAdParam(2, allDataHash[rowKey].param2); }
    }
  }
}

Monday, July 1, 2013

Campaign and Keyword Performance Reporting

Many of the daily or weekly reporting tasks that your boss asks you to put together can be automated using AdWords Scripts. One of my readers passed along the following request for a script that would save him time each day putting together some of his performance reports. Maybe it can save you some time as well.

The following script will build out a Google Spreadsheet with five tabs:
  1. Campaign Performance Summary for the Past 7 Days
  2. Campaign Performance Month to Date
  3. Campaign Performance for Last Month
  4. Keyword Performance Summary for the Past 7 Days
  5. Daily Keyword Performance for the Past 7 Days

All you need to do to get this up and running is to create a New Google Spreadsheet and paste the url into the script. Then you can schedule this script to run daily and when you get to work in the morning, all of your data will be refreshed.

Thanks,
Russ

/************************************
* Campaign and Keyword Summary Report
* Version: 1.2
* Changelog v1.2 - Fixed INVALID_PREDICATE_ENUM_VALUE
* ChangeLog v1.1 - Removed apiVersion from reporting call
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************/
var SPREADSHEET_URL = "PASTE GOOGLE SPREADSHEET URL HERE";

function main() {
  //These names are important. change them with caution
  var tabs = ['camp_perf_7_days','camp_perf_mtd','camp_perf_last_month','keyword_perf_7_days','keyword_perf_7_days_daily'];
  for(var i in tabs) {
    var results = runQuery(tabs[i]);
    writeToSpreadsheet(tabs[i],results);
  }
}

//Helper function to get or create the spreadsheet
function getSheet(tab) {
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet;
  try {
    sheet = s_sheet.getSheetByName(tab);
    if(!sheet) {
      sheet = s_sheet.insertSheet(tab, 0);
    }
  } catch(e) {
    sheet = s_sheet.insertSheet(tab, 0);
  }
  return sheet
}

//Function to write the rows of the report to the sheet
function writeToSpreadsheet(tab,rows) {
  var to_write = convertRowsToSpreadsheetRows(tab,rows);
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = getSheet(tab);
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < to_write.length) {
    sheet.insertRows(1,to_write.length-numRows); 
  }
  var range = sheet.getRange(1,1,to_write.length,to_write[0].length);
  range.setValues(to_write);
}

//A generic function used to build and run the report query
function runQuery(tab) {
  var API_VERSION = { includeZeroImpressions : false };
  var cols = getColumns(tab);
  var report = getReport(tab);
  var date_range = getDateRange(tab);
  var where = getWhereClause(tab);
  var query = ['select',cols.join(','),'from',report,where,'during',date_range].join(' ');
  var report_iter = AdWordsApp.report(query, API_VERSION).rows();
  var rows = [];
  while(report_iter.hasNext()) { 
    rows.push(report_iter.next());
  }
  return rows;
}
 
//This function will convert row data into a format easily pushed into a spreadsheet
function convertRowsToSpreadsheetRows(tab,rows) {
  var cols = getColumns(tab);
  var ret_val = [cols];
  for(var i in rows) {
    var r = rows[i];
    var ss_row = [];
    for(var x in cols) {
      ss_row.push(r[cols[x]]);
    }
    ret_val.push(ss_row);
  }
  return ret_val;
}

//Based on the tab name, this returns the report type to use for the query
function getReport(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'CAMPAIGN_PERFORMANCE_REPORT';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'KEYWORDS_PERFORMANCE_REPORT';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the where clause for the query
function getWhereClause(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'where CampaignStatus = ENABLED';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'where CampaignStatus = ENABLED and AdGroupStatus = ENABLED and Status = ENABLED';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the columns to add into the report
function getColumns(tab) {
  var ret_array = [];
  if(tab.indexOf('daily') >= 0) {
    ret_array.push('Date');
  }
  ret_array.push('CampaignName');
  ret_array.push('CampaignStatus');
  
  if(tab.indexOf('keyword_') == 0) {
    ret_array = ret_array.concat(['AdGroupName',
                                  'AdGroupStatus',
                                  'Id',
                                  'KeywordText',
                                  'KeywordMatchType']);
  }
  return ret_array.concat(['Clicks',
                           'Impressions',
                           'Ctr',
                           'AverageCpc',
                           'Cost',
                           'AveragePosition',
                           'Conversions',
                           'ConversionRate',
                           'ConversionValue']);
}

//Based on the tab name, this returns the date range for the data.
function getDateRange(tab) {
  if(tab.indexOf('7_days') >= 0) {
    return 'LAST_7_DAYS';
  }
  if(tab.indexOf('mtd') >= 0) {
    return 'THIS_MONTH';
  }
  if(tab.indexOf('last_month') >= 0) {
    return 'LAST_MONTH';
  }
  throw new Exception('tab name not recognized: '+tab);
}

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, April 22, 2013

Store Account, Campaign, AdGroup, and Keyword Level Quality Score

UPDATE: 2014-02-15 - I updated the script to v2.0 which speeds the script up dramatically, includes the ability to store data into a CSV and use whatever date range you like for the stats.

I've had a lot of good feedback on my previous script: Store Account Level Quality Score. To that end, I've been asked a few times about storing Campaign and/or AdGroup level quality scores as well so I figured it would be a good time for an update.

Below is a script that will attempt to store Account, Campaign, and AdGroup level quality scores for the top 50000 keywords in your account. It will store the results in a google spreadsheet. In order for this to work, you will need to set up a new google spreadsheet. with three sheets named Account, Campaign, and AdGroup. You can simply make a copy of my spreadsheet found here (File > Make a copy...) : Account, Campaign, AdGroup Quality Score Spreadsheet (No longer needed as of v1.1)

Thanks,
Russ

Wednesday, March 20, 2013

Store Account Performance Report in a Google Doc

Day 2 and I'm still playing around with the new reporting API. I figured I'd post something that people started asking a little about from my previous post about Storing Account Level Quality Score which is storing all account level performance in a spreadsheet.

All you need to do is create a brand new Google Doc to store your data and paste that url into the script. The first time the script runs, it will create column headings. You can add, remove, or rearrange columns as you like by moving the column names around. You should reset your spreadsheet after doing this though.

Thanks,
Russ

/**************************************
* Store Account Performance Report in a Google Doc
* Version 1.1
* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
function main() {
  var spreadsheet_url = "Your Spreadsheet Url Goes Here";
  var date_range = 'YESTERDAY';
  var columns = ['Date',
                 'AccountCurrencyCode',
                 'AccountDescriptiveName',
                 'AccountId',
                 'AccountTimeZoneId',
                 'CustomerDescriptiveName',
                 'ExternalCustomerId',
                 'PrimaryCompanyName',
                 'PrimaryUserLogin',
                 'Device',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'SearchBudgetLostImpressionShare',
                 'SearchExactMatchImpressionShare',
                 'SearchImpressionShare',
                 'SearchRankLostImpressionShare',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
  
  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();
  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {
    sheet.clear();
    sheet.appendRow(columns);
  }
  
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING ' +date_range).rows();
  
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    sheet.appendRow(row_array); 
  }
}

Tuesday, February 26, 2013

Update Your Bids from a Google Spreadsheet

Today we have a little script to help adjust bids at a large scale.  This is an example of how you can use the integration with Google Spreadsheets to update some of your bids 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=0Aotb6eheEOpodEhDM2hqQmhfeTdHT3BLMl80aDRxTkE

Thanks,
Russ
/****************************************
 * Update Bids Using a Google Spreadsheet
 * Version 1.1
 * Created By: Russ Savage
 * FreeAdWordsScripts.com
****************************************/
function main() {
  var SPREADSHEET_URL = "Insert Url Here";
   
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('KeywordBids');
  var data = sheet.getRange("A:E").getValues();
  
  var kwBidHash = {};
  for(var i in data) {
    if(i == 0) { continue; }
    if(data[i][0] === '') { break; }
    var kwKey = Utilities.base64Encode([data[i][0],data[i][1],data[i][2]].join('~~!~~'));
    kwBidHash[kwKey] = data[i];
  }
  
  var kwIter = AdWordsApp.keywords()
    .withCondition("Status = ENABLED")
    .get();
  
  while(kwIter.hasNext()) {
    var kw = kwIter.next();
    var campName = kw.getCampaign().getName();
    var kwKey = Utilities.base64Encode([campName,kw.getText(),kw.getMatchType()].join('~~!~~'));
    if(kwBidHash[kwKey]) {
      if(kwBidHash[kwKey][3] === "FIXED") {
        kw.setMaxCpc(kwBidHash[kwKey][4]);
      }else{
        kw.setMaxCpc(kw.getMaxCpc() * (1+kwBidHash[kwKey][4]));
      }
    }
  }
}