The 24 hour bidding schedule for AdWords

The 24 hour bidding schedule for AdWords

We’ve written an AdWords script to let you bid 24 times a day, 7 days a week

We often find that ROI and conversion rates are extremely variable at different times of the day and on different days of the week. However the standard Google Ad Scheduling only allows for six bidding windows in one day – not nearly enough if you’re as data-driven as we are.

Therefore we built a custom ad scheduling tool that changes bids every hour of every day – that’s four times more responsive that Adwords, Marin and Kenshoo. This ensures that we’re bidding more aggressively at optimal times and maximising the efficiency of our accounts.

If you’ve not run a script before please read our Introduction to AdWords Scripts. For more open-source fun check out our AdWords Scripts directory. And if you want to use some of our technology that’s arrived back from the future then sign up for one of our paid plans on the Brainlabs Tech Stack.

Before running it, you first need to make sure you’ve got the correct settings. There are a few things to configure:

(1) The bid multipliers run from a Google spreadsheet and the URL needs to be inserted into the code as spreadsheetUrl. We suggest you copy our template sheet and set your bids up in it. The doc should look something like this:

In cells B2 to H25, input the bid multipliers: a 0% bid multiplier doesn’t change any bids, and a 25%/-25% bid multiplier increases/decreases bids by 25%. -100%, or a blank cell, mean the ads will be turned off.

If you want to use mobile multipliers as well, they go in the second sheet of the same spreadsheet.

(2) You need to set the variable shoppingCampaigns to true if you want to use it on shopping campaigns, or to false to use it on Search or Display campaigns.

(3) Set runMobileBids to true if you want to change your mobile bid adjustments every hour, or set it to false if you only want to change the ad schedules.

(4) If you only want to use these multipliers on some campaigns, use excludeCampaignNameContains and includeCampaignNameContains to say what should or shouldn’t be in the campaign names.

You then need to set up a schedule so the script runs hourly. It will continually update the ad schedules, so the next few hours are always filled in.

And now all that’s left to do is sit back and see overall conversion rates rocket!

Follow us on Twitter and Facebook for more Brainlabs fun and updates.

/*
*
* Advanced ad scheduling
*
* This script will apply ad schedules to campaigns or shopping campaigns and set
* the ad schedule bid modifier and mobile bid modifier at each hour according to
* multiplier timetables in a Google sheet.
*
* This version creates schedules with modifiers for 4 hours, then fills the rest
* of the day and the other days of the week with schedules with no modifier as a
* fail safe.
*
* Version: 3.0
* Updated to allow -100% bids, change mobile adjustments and create fail safes.
* brainlabsdigital.com
*
*/

function main() {
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //Options

  //The Google sheet to use
  //The default value is the example sheet
  var spreadsheetUrl = "https://docs.google.com/a/brainlabsdigital.com/spreadsheets/d/1JDGBPs2qyGdHd94BRZw9lE9JFtoTaB2AmlL7xcmLx2g/edit#gid=0";

  //Shopping or regular campaigns
  //Use true if you want to run script on shopping campaigns (not regular campaigns).
  //Use false for regular campaigns.
  var shoppingCampaigns = false;

  //Use true if you want to set mobile bid adjustments as well as ad schedules.
  //Use false to just set ad schedules.
  var runMobileBids = false;

  //Optional parameters for filtering campaign names. The matching is case insensitive.
  //Select which campaigns to exclude e.g ["foo", "bar"] will ignore all campaigns
  //whose name contains 'foo' or 'bar'. Leave blank [] to not exclude any campaigns.
  var excludeCampaignNameContains = [];

  //Select which campaigns to include e.g ["foo", "bar"] will include only campaigns
  //whose name contains 'foo' or 'bar'. Leave blank [] to include all campaigns.
  var includeCampaignNameContains = [];

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

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

  //Initialise for use later.
  var weekDays = ["MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY"];
  var adScheduleCodes = [];
  var campaignIds = [];

  //Retrieving up hourly data
  var scheduleRange = "B2:H25";
  var accountName = AdWordsApp.currentAccount().getName();
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheets = spreadsheet.getSheets();

  var timeZone = AdWordsApp.currentAccount().getTimeZone();
  var date = new Date();
  var dayOfWeek = parseInt(Utilities.formatDate(date, timeZone, "uu"), 10) - 1;
  var hour = parseInt(Utilities.formatDate(date, timeZone, "HH"), 10);

  var sheet = sheets[0];
  var data = sheet.getRange(scheduleRange).getValues();

  //This hour's bid multiplier.
  var thisHourMultiplier = data[hour][dayOfWeek];
  var lastHourCell = "I2";
  sheet.getRange(lastHourCell).setValue(thisHourMultiplier);

  //The next few hours' multipliers
  var timesAndModifiers = [];
  var otherDays = weekDays.slice(0);
  for (var h=0; h<5; h++) {
    var newHour = (hour + h)%24;
    if (hour + h > 23) {
      var newDay = (dayOfWeek + 1)%7;
    } else {
      var newDay = dayOfWeek;
    }
    otherDays[newDay] = "-";

    if (h<4) {
      // Use the specified bids for the next 4 hours
      var bidModifier = data[newHour][newDay];
      if (isNaN(bidModifier) || (bidModifier < -0.9 && bidModifier > -1) || bidModifier > 9) {
        Logger.log("Bid modifier '" + bidModifier + "' for " + weekDays[newDay] + " " + newHour + " is not valid.");
        timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], 0]);
      } else if (bidModifier != -1 && bidModifier.length != 0) {
        timesAndModifiers.push([newHour, newHour+1, weekDays[newDay], bidModifier]);
      }
    } else {
      // Fill in the rest of the day with no adjustment (as a back-up incase the script breaks)
      timesAndModifiers.push([newHour, 24, weekDays[newDay], 0]);
    }
  }

  if (hour>0) {
    timesAndModifiers.push([0, hour, weekDays[dayOfWeek], 0]);
  }

  for (var d=0; d<otherDays.length; d++) {
    if (otherDays[d] != "-") {
      timesAndModifiers.push([0, 24, otherDays[d], 0]);
    }
  }

  //Pull a list of all relevant campaign IDs in the account.
  var campaignSelector = ConstructIterator(shoppingCampaigns);
  for(var i = 0; i < excludeCampaignNameContains.length; i++){
    campaignSelector = campaignSelector.withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE "' + excludeCampaignNameContains[i] + '"');
  }
  campaignSelector = campaignSelector.withCondition("Status IN [ENABLED,PAUSED]");
  var campaignIterator = campaignSelector.get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();
    var includeCampaign = false;
    if(includeCampaignNameContains.length === 0){
      includeCampaign = true;
    }
    for(var i = 0; i < includeCampaignNameContains.length; i++){
      var index = campaignName.toLowerCase().indexOf(includeCampaignNameContains[i].toLowerCase());
      if(index !== -1){
        includeCampaign = true;
        break;
      }
    }
    if(includeCampaign){
      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){
    checkAndRemoveAdSchedules(campaignIds, []);
    return;
  }

  // Change the mobile bid adjustment
  if(runMobileBids){
    if (sheets.length < 2) {
      Logger.log("Mobile ad schedule sheet was not found in the Google spreadsheet.");
    } else {
      var sheet = sheets[1];
      var data = sheet.getRange(scheduleRange).getValues();
      var thisHourMultiplier_Mobile = data[hour][dayOfWeek];

      if (thisHourMultiplier_Mobile.length === 0) {
        thisHourMultiplier_Mobile = -1;
      }

      if (isNaN(thisHourMultiplier_Mobile) || (thisHourMultiplier_Mobile < -0.9 && thisHourMultiplier_Mobile > -1) || thisHourMultiplier_Mobile > 3) {
        Logger.log("Mobile bid modifier '" + thisHourMultiplier_Mobile + "' for " + weekDays[dayOfWeek] + " " + hour + " is not valid.");
        thisHourMultiplier_Mobile = 0;
      }

      var totalMultiplier = ((1+thisHourMultiplier_Mobile)*(1+thisHourMultiplier))-1;
      sheet.getRange("I2").setValue(thisHourMultiplier_Mobile);
      sheet.getRange("T2").setValue(totalMultiplier);
      ModifyMobileBidAdjustment(campaignIds, thisHourMultiplier_Mobile);
    }
  }

  // Check the existing ad schedules, removing those no longer necessary
  var existingSchedules = checkAndRemoveAdSchedules(campaignIds, timesAndModifiers);

  // Add in the new ad schedules
  AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns);

}

/**
* Function to add ad schedules for the campaigns with the given IDs, unless the schedules are
* referenced in the existingSchedules array. The scheduling will be added as a hour long periods
* as specified in the passed parameter array and will be given the specified bid modifier.
*
* @param array campaignIds array of campaign IDs to add ad schedules to
* @param array timesAndModifiers the array of [hour, day, bid modifier] for which to add ad scheduling
* @param array existingSchedules array of strings identifying already existing schedules.
* @param bool shoppingCampaigns using shopping campaigns?
* @return void
*/
function AddHourlyAdSchedules(campaignIds, timesAndModifiers, existingSchedules, shoppingCampaigns){
  // times = [[hour,day],[hour,day]]
  var campaignIterator = ConstructIterator(shoppingCampaigns)
  .withIds(campaignIds)
  .get();
  while(campaignIterator.hasNext()){
    var campaign = campaignIterator.next();
    for(var i = 0; i < timesAndModifiers.length; i++){
      if (existingSchedules.indexOf(
        timesAndModifiers[i][0] + "|" + (timesAndModifiers[i][1]) + "|" + timesAndModifiers[i][2]
          + "|" + Utilities.formatString("%.2f",(timesAndModifiers[i][3]+1)) + "|" + campaign.getId())
      > -1) {

        continue;
      }

      campaign.addAdSchedule({
        dayOfWeek: timesAndModifiers[i][2],
        startHour: timesAndModifiers[i][0],
        startMinute: 0,
        endHour: timesAndModifiers[i][1],
        endMinute: 0,
        bidModifier: Math.round(100*(1+timesAndModifiers[i][3]))/100
      });
    }
  }
}

/**
* Function to remove ad schedules from all campaigns referenced in the passed array
* which do not correspond to schedules specified in the passed timesAndModifiers array.
*
* @param array campaignIds array of campaign IDs to remove ad scheduling from
* @param array timesAndModifiers array of [hour, day, bid modifier] of the wanted schedules
* @return array existingWantedSchedules array of strings identifying the existing undeleted schedules
*/
function checkAndRemoveAdSchedules(campaignIds, timesAndModifiers) {

  var adScheduleIds = [];

  var report = AdWordsApp.report(
    'SELECT CampaignId, Id ' +
    'FROM CAMPAIGN_AD_SCHEDULE_TARGET_REPORT ' +
    'WHERE CampaignId IN ["' + campaignIds.join('","')  + '"]'
  );

  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 = 10000;

  for(var i = 0; i < adScheduleIds.length; i += chunkSize){
    chunkedArray.push(adScheduleIds.slice(i, i + chunkSize));
  }

  var wantedSchedules = [];
  var existingWantedSchedules = [];

  for (var j=0; j<timesAndModifiers.length; j++) {
    wantedSchedules.push(timesAndModifiers[j][0] + "|" + (timesAndModifiers[j][1]) + "|" + timesAndModifiers[j][2] + "|" + Utilities.formatString("%.2f",timesAndModifiers[j][3]+1));
  }

  for(var i = 0; i < chunkedArray.length; i++){
    var unwantedSchedules = [];

    var adScheduleIterator = AdWordsApp.targeting()
    .adSchedules()
    .withIds(chunkedArray[i])
    .get();
    while (adScheduleIterator.hasNext()) {
      var adSchedule = adScheduleIterator.next();
      var key = adSchedule.getStartHour() + "|" + adSchedule.getEndHour() + "|" + adSchedule.getDayOfWeek() + "|" + Utilities.formatString("%.2f",adSchedule.getBidModifier());

      if (wantedSchedules.indexOf(key) > -1) {
        existingWantedSchedules.push(key + "|" + adSchedule.getCampaign().getId());
      } else {
        unwantedSchedules.push(adSchedule);
      }
    }

    for(var j = 0; j < unwantedSchedules.length; j++){
      unwantedSchedules[j].remove();
    }
  }

  return existingWantedSchedules;
}

/**
* Function to construct an iterator for shopping campaigns or regular campaigns.
*
* @param bool shoppingCampaigns Using shopping campaigns?
* @return AdWords iterator Returns the corresponding AdWords iterator
*/
function ConstructIterator(shoppingCampaigns){
  if(shoppingCampaigns === true){
    return AdWordsApp.shoppingCampaigns();
  }
  else{
    return AdWordsApp.campaigns();
  }
}

/**
* Function to set a mobile bid modifier for a set of campaigns
*
* @param array campaignIds An array of the campaign IDs to be affected
* @param Float bidModifier The multiplicative mobile bid modifier
* @return void
*/
function ModifyMobileBidAdjustment(campaignIds, bidModifier){

  var platformIds = [];
  var newBidModifier = Math.round(100*(1+bidModifier))/100;

  for(var i = 0; i < campaignIds.length; i++){
    platformIds.push([campaignIds[i],30001]);
  }

  var platformIterator = AdWordsApp.targeting()
  .platforms()
  .withIds(platformIds)
  .get();
  while (platformIterator.hasNext()) {
    var platform = platformIterator.next();
    platform.setBidModifier(newBidModifier);
  }
}

Share this post