Here’s An AdWords Script That Lets You Optimize Bids Every Hour Of The Day

Columnist Daniel Gilbert shares Brainlabs' AdWords script that lets you set 24 different hourly bid multipliers, 7 days a week.

Chat with SearchBot

Capture2

Optimizing your keyword and ad group bids in order to maximize performance can be a tricky affair. Setting up a schedule to manage your bids is a great way to make sure that you’re not spending too much at the wrong times and more importantly that valuable traffic is getting to you at the right times.

AdWords’ built-in tool for modifying bids based on the time of day — ad scheduling — only allows you up to six bidding windows per day.

For large-scale accounts that demand a more granular approach, with bids that need to be changed every hour, the above limitations just won’t do. As an example, conversion rates for Domino’s vary dramatically during different hourly slots on different days; the company doesn’t want to bid at the same levels at 7:00 p.m., 9:00 p.m., and 11:00 p.m. on Wednesdays and Saturdays.

So the mathematical marketers at Brainlabs (my company) wrote an AdWords script — published in its entirety below — that gives you control of your bids for every hour of the day. If you’ve never used scripts, you can read our recent series on AdWords Scripts or just copy and paste the code into your account to get started.

The script works by altering the ad schedule bid modifier by referring to a central managing spreadsheet. Your first job will be to set up a correct spreadsheet. Copy and paste this generic ad schedule into a Google doc of your own. Be sure to rename the sheet to the actual account name from which you will be running the script.

Next, enter your own bid modifier values into the table. Entering 100% will leave the bid as is, while 150% will be a 1.5 multiplier.

Make a note of your spreadsheet URL because you’ll need it for the next section.

The next step is to copy and paste the script at the bottom of the article into your account. There are a few options to set. Have a look at the initial part of the script labeled Options.

  • Set the firstRun variable to true; as it’s the first the time script is being executed, there is an initialization procedure.
  • Enter the URL for the Google Sheet you used earlier for the spreadsheetUrl, replacing the value currently there.
  • excludeCampaignNameContains will exclude all campaigns which have this in their name. Leave blank not to exclude any campaigns.
  • includeCampaignNameContains will only include campaigns which have this in their name. Leave blank to include all campaigns.

Once the optional settings have been entered, run the script manually once, then change the firstRun variable to false.

Now you’re ready to set up a schedule for the script. We recommend you run the script hourly. If you’re not sure what your conversion rates are at different times, we’ll be following up with another article on how to calculate hourly bidding multipliers.

Note: For all affected campaigns, this script will delete all existing ad schedules.

/**
*
* Advanced ad scheduling
*
* This script will apply ad schedules and set the bid modifier for the schedule
* at each hour according to a multiplier timetable in a Google sheet.
*
* Version: 1.0
* brainlabsdigital.com
*
**/

function main() {

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //Options
  
  //Be sure to set firstRun to false after running the script for the first time
  var firstRun = true;
  
  //The google sheet to use
  //The default value is the example sheet linked to in the article
  var spreadsheetUrl = "https://docs.google.com/a/brainlabsdigital.com/spreadsheets/d/1JDGBPs2qyGdHd94BRZw9lE9JFtoTaB2AmlL7xcmLx2g/edit#gid=0";
  
  //Optional parameters for filtering campaign names. 
  //Leave blank to use filters. The matching is case insensitive.
  var excludeCampaignNameContains = ""; //Select which campaigns to exclude. Leave blank to not exclude any campaigns.
  var includeCampaignNameContains = ""; //Select which campaigns to include. Leave blank to include all campaigns.
  
  //When you want to stop running the ad scheduling for good, set
  //the lastRun variable to true to remove all ad schedules.
  var lastRun = false;
  
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  
  //Retrieving up hourly data
  var scheduleRange = "B2:H25";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName(accountName);
  var data = sheet.getRange(scheduleRange).getValues();
  
  var timeZone = AdWordsApp.currentAccount().getTimeZone();
  var date = new Date();
  var dayOfWeek = parseInt(Utilities.formatDate(date, timeZone, "uu")) - 1;
  var hour = parseInt(Utilities.formatDate(date, timeZone, "HH"));
  
  //This hour's bid multiplier.
  var thisHourMultiplier = data[hour][dayOfWeek];
  var lastHourCell = "I2";
  sheet.getRange(lastHourCell).setValue(thisHourMultiplier);
  
  //Initialise for use later.
  var weekDays = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];
  var adScheduleCodes = [];
  
  //Dummy name to exclude
  if(excludeCampaignNameContains === ""){
    excludeCampaignNameContains += "#@%" + date + "~};";
  }
  
  var campaignIds = [];
  
  //Pull a list of all relevant campaign IDs in the account.
  var campaignIterator = AdWordsApp.campaigns()
  .withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE "' + excludeCampaignNameContains + '"')
  .withCondition('Name CONTAINS_IGNORE_CASE "' + includeCampaignNameContains + '"')
  .get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    var campaignId = campaign.getId();
    campaignIds.push(campaignId);
  }
  
  //Return if there are no campaigns.
  if(campaignIds.length === 0){
    Logger.log("There are no campaigns matching your criteria.");
    return;
  }
  
  //Remove all ad scheduling for the last run.
  if(lastRun){
    RemoveAdSchedules(campaignIds);
    return;
  }
  
  //Remove all existing ad scheduling and add new schedules for the first run.
  if(firstRun){
    RemoveAdSchedules(campaignIds);
    AddAdSchedules(campaignIds, weekDays);
	Logger.log("Set the firstRun variable to false");
  }
  
  //Populate the adScheduleCodes array with the ad schedule ID corresponding to the weekDays array. 
  var campaignIterator = AdWordsApp.campaigns()
  .withIds(campaignIds)
  .withLimit(1)
  .get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    var adSchedules = campaign.targeting().adSchedules().get();
	if(adSchedules.totalNumEntities() === 0) {
      Logger.log("Some campaigns do not have ad scheduling, please re-do the first run");
      return;
    }
    while(adSchedules.hasNext()){
      var adSchedule = adSchedules.next();
      var adScheduleDay = adSchedule.getDayOfWeek();
      var adScheduleId = adSchedule.getId();
      adScheduleCodes[weekDays.indexOf(adScheduleDay)] = adScheduleId;
    }
  }
  
  var adScheduleCode = adScheduleCodes[dayOfWeek];
  
  //Apply the ad schedule bid modifier
  ModifyAdSchedule(campaignIds, adScheduleCode, thisHourMultiplier);
  
  //Set yesterday's ad schedule to a 0% bid modifier
  if(hour === 1){
    var yesterdayIndex = dayOfWeek - 1;
    if(yesterdayIndex === -1 ) yesterdayIndex = 6;
    var yesterdayAdScheduleCode = adScheduleCodes[yesterdayIndex];
    ModifyAdSchedule(campaignIds, yesterdayAdScheduleCode, 1);
  }
  
}

/**
 * Function to add ad schedules for all campaigns in the account. The scheduling will be 
 * added as a whole-day period for every day specified in the passed parameter array and will
 * be added with a bid modifier of 0%.
 *
 * @param array days the array of days for which to add ad scheduling
 * @return void
 */
function AddAdSchedules(campaignIds, days){
  
  var campaignIterator = AdWordsApp.campaigns()
  .withIds(campaignIds)
  .get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    for(var i = 0; i < days.length; i++){
      campaign.addAdSchedule({
        dayOfWeek: days[i],
        startHour: 0,
        startMinute: 0,
        endHour: 24,
        endMinute: 0,
        bidModifier: 1
      });
    }
  }
}

/**
 * Function to remove all ad schedules from all campaigns refernced in the passed array.
 *
 * @param array campaignIds array of campaign IDs to remove ad scheduling from
 * @return void
 */
function RemoveAdSchedules(campaignIds) {
  
  var adScheduleIds = [];
  
  var report = AdWordsApp.report(
    'SELECT CampaignId, Id ' + 
    'FROM CAMPAIGN_AD_SCHEDULE_TARGET_REPORT ' +
    'WHERE CampaignId IN ["' + campaignIds.join('","')  + '"] ' +
    'DURING YESTERDAY');
  
  var rows = report.rows();
  while(rows.hasNext()){
    var row = rows.next();
    var adScheduleId = row['Id'];
    var campaignId = row['CampaignId'];
    adScheduleIds.push([campaignId,adScheduleId]);
  }
  
  var chunkedArray = [];
  var chunkSize = 50000;
  
  for(var i = 0; i < adScheduleIds.length; i += chunkSize){
    chunkedArray.push(adScheduleIds.slice(i, i + chunkSize));
  }
  
  for(var i = 0; i < chunkedArray.length; i++){
    
    var adScheduleArray = [];
    
    var adScheduleIterator = AdWordsApp.targeting()
    .adSchedules()
    .withIds(chunkedArray[i])
    .get();
    while (adScheduleIterator.hasNext()) {
      var adSchedule = adScheduleIterator.next();
      adScheduleArray.push(adSchedule);
    }
    
    for(var j = 0; j < adScheduleArray.length; j++){
      adScheduleArray[j].remove();
    }
  }
  
}

/**
 * Function to set the bid modifier for a specific ad schedule period for a set of campaigns.
 *
 * @param array campaignIds the array of campaign IDs to have thier ad schedules modified
 * @param int adScheduleCode the ID of the adschedule to be modified
 * @param float bidModifier the multiplicative bid modifier
 * @return void
 */
function ModifyAdSchedule(campaignIds, adScheduleCode, bidModifier){
  
  var adScheduleIds = [];
  
  for(var i = 0; i < campaignIds.length; i++){
    adScheduleIds.push([campaignIds[i],adScheduleCode]);
  }
  
  var adScheduleIterator = AdWordsApp.targeting()
  .adSchedules()
  .withIds(adScheduleIds)
  .get();
  while (adScheduleIterator.hasNext()) {
    var adSchedule = adScheduleIterator.next();
    adSchedule.setBidModifier(bidModifier);
  }
}

 


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Daniel Gilbert
Contributor
Daniel Gilbert is the CEO at Brainlabs, the best paid media agency in the world (self-declared). He has started and invested in a number of big data and technology startups since leaving Google in 2010.

Get the must-read newsletter for search marketers.