SEA specialists with an interest in scripts have probably seen the Brainlabs script. With this script you can make bid adjustments for every hour of the day, instead of the 6 standard blocks that you can create in the ad scheduling of a campaign. To do even more, we have written a new script.

Improvements

The idea of ​​the Brainlabs script is beautiful. It offers the possibility to place bid adjustments for every hour of every day. That means bidding on moments that, historically speaking, convert well and save costs on moments that are quieter. With the same idea, we have written a new script that contains a number of improvements compared to the Brainlabs script.

  • Bid adjustments per campaign
    In the original script you can define bid adjustments that are then the same for all campaigns. But not all campaigns perform the same at specific times. With this new script, the bid adjustments per hour are tailored to the campaign.
  • Search and Shopping
    Instead of having to choose Search or Shopping, or use the script twice, you can use this script to make bid adjustments per hour for both search and shopping campaigns.
  • Automatic calculation of bid adjustment
    In this new script, bid adjustments are automatically calculated based on the CPA. You no longer have to enter 168 bid adjustments in a sheet (or run the Heatmap script) and update it regularly. This new script combines both.

Calculation

In the script we look at the conversion data per hour of a day for the past twelve months. We compare this with the CPA campaign of the past 30 days. The bid adjustment is calculated based on this difference. To clarify how the bid adjustment is calculated, here are a few examples:

Example 1

CPA Campaign: € 20
Conversions hour of the day: 0
Cost hour of the day: € 30

Bid adjustment: MIN_BID (variable in the script).
If there are no conversions for that hour of the day, but the costs are lower than the CPA campaign, the bid adjustment is set to 0%.

Example 2

CPA Campaign: € 20
CPA hour of the day: € 25

Bid adjustment: 20/25 = 0.8 = -20%

The bid adjustment will never be more than MIN_BID (variable in the script). If the number of conversions is higher than average or the current-day conversion ratio is higher than average, we will halve the negative bid adjustment.

Example 3

CPA Campaign: € 20
CPA hour of the day: € 16

Bid adjustment: 20 / 16 = 1.25 = + 25%

The bid adjustment will never be higher than MAX_BID (variable in the script)

Reporting

But which bid adjustments did the script implement? Because you do not want to look into your account every 6 hours to see which bid adjustments are currently in, the adjustments are reported in a Google Sheet. After the script has run for a week, you will see the bid adjustments for the past 7 days in a heat map. A separate tab is created for each campaign.

Settings

  • LOG: Specify whether the script should report the intermediate steps, by adjusting the value to 'true'.
  • DATE_RANGE: The script determines the CPA campaign based on the date selection.
  • SPREADSHEET_URL: Make a copy of our spreadsheet to see which bid adjustments have been made in the last 7 days.
  • CAMPAIGN_LABEL: Do you not want to apply this script to all campaigns? Then label the campaigns.
  • INCLUDE_SEARCH: Allows you to indicate whether you want to include the search campaigns in the script.
  • INCLUDE_SHOPPING: Allows you to indicate whether you want to include the shopping campaigns in the script.
  • MIN_BID: The bid adjustment will not be lower than this set bid. 0.75 = -25% or 0.5 = -50%.
  • MAX_BID: The bid adjustment will not be higher than this set bid. 1.3 = + 30% or 2 = + 100%
Script
// Copyright 2019. Increase BV. All Rights Reserved.
//
// Created By: Tibbe van Asten
// for Increase B.V.
//
// Created 18-06-2018
// Last update: 04-04-2019
//
// ABOUT THE SCRIPT
// This scripts automatically ads a bidadjustment to every hour
// of the day, based of historical CPA. The CPA of the current and
// upcoming 5 hours will be compared to the campaign CPA and a 
// bidadjustment will be set accordingly.
//
////////////////////////////////////////////////////////////////////

var config = {
  
  LOG : true,      

  // This daterange is used to calculate the campaign CPA.
  DATE_RANGE : "LAST_30_DAYS",
  
  // Google Sheet to report the bidadjustments that are set.
  // For every campaign in your account a new sheet will be created.
  // Make a copy from our spreadsheet here: https://docs.google.com/spreadsheets/d/1FtzNSE2bCBxYbswqMIAjGw2sgLZTTCN35_uYwOx8BUA/copy
  // Leave the 'Template' sheet in there
  SPREADSHEET_URL : "SPREADSHEET_URL",
  
  // Select which campaigns to include. For example: ["foo", "bar"] will include 
  // only campaigns with label 'foo' or 'bar'. Leave blank [] to include all campaigns.
  CAMPAIGN_LABEL : ["Hourly Bidding"],
  
  // If set to true, this script will run your search and/or shopping campaigns.
  INCLUDE_SEARCH : true,
  INCLUDE_SHOPPING : true,
  
  // The minimum and maximum for the bidmodifiers.
  // Minimal: 0.75 equals -25%. Maximum: 1.3 equals +30%
  MIN_BID : 0.75,
  MAX_BID : 1.3

}

////////////////////////////////////////////////////////////////////
// Do not make any changes below this line

function main() {
  
  var ss = connectSheet();

  // Selecting all search campaigns
  if(config.INCLUDE_SEARCH === true){
      
    var campaignSelector = AdsApp.campaigns().withCondition("Status = ENABLED");
    runningCampaigns(campaignSelector, ss);
    
  } // include search

  // Selecting all shopping campaigns
  if(config.INCLUDE_SHOPPING === true){
    
    var shoppingCampaignSelector = AdsApp.shoppingCampaigns().withCondition("Status = ENABLED");
    runningCampaigns(shoppingCampaignSelector, ss);
    
  } // include shopping
  
  Logger.log("Thanks for using this custom script by Tibbe van Asten. Winning!");

} // function main()

////////////////////////////////////////////////////////////////////
	
function runningCampaigns(campaignSelector, ss){
  
  // Filter campaigns if labels are defined
  if(config.CAMPAIGN_LABEL.length > 0){
    campaignSelector = campaignSelector.withCondition("LabelNames CONTAINS_ANY ['" + config.CAMPAIGN_LABEL + "']");
  }

  var campaignIterator = campaignSelector.get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    
    var sheet = checkSheet(ss, campaign);   

    // Calculating the campaign CPA to use in all functions
    var campaignCpa = campaign.getStatsFor(config.DATE_RANGE).getCost() / campaign.getStatsFor(config.DATE_RANGE).getConversions();

    // We only want to alter the campaigns with manual bidding strategies and actual conversions
    // All other campaigns are skipped
    if(isFinite(campaignCpa) == false && (campaign.getBiddingStrategyType() !== "MANUAL_CPC" || campaign.getBiddingStrategyType() !== "MANUAL_CPM" || campaign.bidding().getBiddingStrategyType() !== "MANUAL_CPV")) continue;

       	Logger.log("-----");
    	Logger.log("Campaign: " + campaign.getName());
        Logger.log("CPA: " + campaignCpa);
        Logger.log("-----");

    bidadjustmentAdschedule(campaign, campaignCpa, sheet);

  } // campaignIterator

} // function runningCampaings()

////////////////////////////////////////////////////////////////////

function connectSheet(){
  
  if(config.SPREADSHEET_URL == "SPREADSHEET_URL"){
    throw error("Define spreadsheet URL in config");
  } else{
    var ss = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
    return ss;
  }  
  
} // function connectSheet()

////////////////////////////////////////////////////////////////////

function checkSheet(ss, campaign){
  
  var sheet = ss.getSheetByName(campaign.getName());
  
  // If a sheet with the campaignname doesn't already exists
  // we will create a new sheet from the 'Template'-sheet in the spreadsheet.
  if (sheet == null) {
    var templateSheet = ss.getSheetByName("Template");
    ss.insertSheet(campaign.getName(), {template: templateSheet});
    var sheet = ss.getSheetByName(campaign.getName());
      
    if(config.LOG === true){
      Logger.log("New sheet created for " + campaign.getName());
    }
      
  } // if sheet doesn't exists
  
  return sheet;
  
} // checkSheet()

////////////////////////////////////////////////////////////////////

function bidadjustmentAdschedule(campaign, campaignCpa, sheet) {

  // Set dates to retrieve 12 months of data
  // The script will always look at the last 12 months of data
  var today = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMMM dd, yyyy HH:mm:ss');
  var date = new Date();
  var firstDay = new Date(date.getFullYear(today), date.getMonth(today) - 12, 1);
  var lastDay = new Date(date.getFullYear(), date.getMonth(), 0);
  var firstMonth = firstDay.getMonth() + 1;
  var lastMonth = lastDay.getMonth() + 1;

    //Format months to make sure they will work in the selectors
    if(firstMonth < 10) {
      var firstMonth = "0" + firstMonth;
    }
    if(lastMonth < 10) {
      var lastMonth = "0" + lastMonth;
    }

  // We calculate the average campaign CPA and campaign conversions per hour to compare them
  // with stats per hour and day of the week
  var campaignCpa = campaign.getStatsFor(firstDay.getYear() + firstMonth + "01",lastDay.getYear() + lastMonth + lastDay.getDate()).getCost() / campaign.getStatsFor(firstDay.getYear() + firstMonth + "01",lastDay.getYear() + lastMonth + lastDay.getDate()).getConversions();
  var campaignConversions = campaign.getStatsFor(firstDay.getYear() + firstMonth + "01",lastDay.getYear() + lastMonth + lastDay.getDate()).getConversions() / (24*7);

  // Remove existing adSchedules to start clean
  var adScheduleIterator = campaign.targeting()
    .adSchedules()
    .get();

  while (adScheduleIterator.hasNext()) {
    var adSchedule = adScheduleIterator.next();
    adSchedule.remove();
  }
  
  var weekday = ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"]

  // Retrieving stats per hour and day of the week
  var report = AdsApp.report(
    "SELECT Conversions, Cost, CostPerConversion, AveragePosition, HourOfDay, SearchImpressionShare, DayOfWeek " +
    "FROM CAMPAIGN_PERFORMANCE_REPORT " +
    "WHERE CampaignId = " + campaign.getId() + " " +
    "DURING " + firstDay.getYear() + firstMonth + "01, " + lastDay.getYear() + lastMonth + lastDay.getDate());

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();

    var nextHour = parseInt(row["HourOfDay"]); nextHour = nextHour + 1;

    // The tricky part: we can only add six adschedules each day. So when running this script every hour
    // we will only add the upcoming 5 hour
    var date = Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), 'MMMM dd, yyyy HH:mm:ss');

    if(row["DayOfWeek"] == weekday[new Date(date).getDay()] && row["HourOfDay"] > (new Date(date).getHours() -1) && row["HourOfDay"] < (new Date(date).getHours() +6)){

      // With 0 conversions and costs higher then the campaign CPA average, we will set a negative bidadjustment.
      if(row["Conversions"] == 0 && row["Cost"] > campaignCpa){
        var bidModifier = config.MIN_BID;
        campaign.addAdSchedule(row["DayOfWeek"].toUpperCase(), parseInt(row["HourOfDay"]), 0, parseInt(nextHour), 0, bidModifier);
        setCell(row["HourOfDay"], date, bidModifier, sheet);
        
        	Logger.log(row["DayOfWeek"] + " " + row["HourOfDay"] + " Bidmodifier 1: " + config.MIN_BID);
      }

      // When the costs per conversion are higher then the campaign average, we will set a negative bidadjustment.
      // To make sure we don't lose all of our conversions, we check the number of conversions as well.
      // When the number of conversions is higher then (average / 2) we decrease the negative bidadjustment with 50%
      // We also take the conversionrate of the current day in consideration. If this is higher than the campaign average, we bisect the bidadjustment.

      else if(row["Conversions"] > 0 && row["CostPerConversion"] > campaignCpa){

        if(row["Conversions"] < (campaignConversions / 2)){
          var bidModifier = campaignCpa / row["CostPerConversion"];bidModifier = 1 - bidModifier; bidModifier = bidModifier / 2; bidModifier = 1 - bidModifier;
          if(bidModifier < config.MIN_BID){ bidModifier = config.MIN_BID; }
          
          if(campaign.getStatsFor("TODAY").getConversionRate > campaign.getStatsFor("LAST_30_DAYS").getConversionRate()){
            bidModifier = 1 - bidModifier; bidModifier = bidModifier / 2; bidModifier = 1 - bidModifier;
            
            	Logger.log("Good conversionrate");
          }
          
          campaign.addAdSchedule(row["DayOfWeek"].toUpperCase(), parseInt(row["HourOfDay"]), 0, parseInt(nextHour), 0, bidModifier);
          setCell(row["HourOfDay"], date, bidModifier, sheet);
        }
        
        if(row["Conversions"] > (campaignConversions / 2)) {
          var bidModifier = campaignCpa / row["CostPerConversion"]; bidModifier = 1 - bidModifier; bidModifier = bidModifier / 4; bidModifier = 1 - bidModifier;
          if(bidModifier < config.MIN_BID){ bidModifier = config.MIN_BID; }
          
          if(campaign.getStatsFor("TODAY").getConversionRate > campaign.getStatsFor("LAST_30_DAYS").getConversionRate()){
            bidModifier = 1 - bidModifier; bidModifier = bidModifier / 2; bidModifier = 1 - bidModifier;
            
            	Logger.log("Good conversionrate");
          }
          
          campaign.addAdSchedule(row["DayOfWeek"].toUpperCase(), parseInt(row["HourOfDay"]), 0, parseInt(nextHour), 0, bidModifier);
          setCell(row["HourOfDay"], date, bidModifier, sheet);
        }
      }

      // When the cost per conversion is lower then the campaign average, we can set a positive bidadjustment.
      // We also take average position and impression share in account. When both are better then the thresholds,
      // we won't set a positive bidadjustment, because there is too little to win.

      else if(row["CostPerConversion"] > 0 && row["CostPerConversion"] < campaignCpa && (parseInt(row["SearchImpressionShare"]) < 90 || row["AveragePosition"] > 1.5)){
        var bidModifier = campaignCpa / row["CostPerConversion"];
        if(bidModifier > config.MAX_BID){ bidModifier = config.MAX_BID; }
        
        campaign.addAdSchedule(row["DayOfWeek"].toUpperCase(), parseInt(row["HourOfDay"]), 0, parseInt(nextHour), 0, bidModifier);
        setCell(row["HourOfDay"], date, bidModifier, sheet);
      }

      // When no conversions are recorded, we still need to add the timeslot to make sure our ads are shown
      
      else{
        //(!(row["Conversions"] == 0 && row["Cost"] > campaignCpa) && !(row["Conversions"] > 0 && row["CostPerConversion"] > campaignCpa) && !(row["CostPerConversion"] > 0 && row["CostPerConversion"] < campaignCpa && (parseInt(row["SearchImpressionShare"]) < 90 || row["AveragePosition"] > 1.5)))
        var bidModifier = 1;
      	campaign.addAdSchedule(row["DayOfWeek"].toUpperCase(), parseInt(row["HourOfDay"]), 0, parseInt(nextHour), 0, bidModifier);
        setCell(row["HourOfDay"], date, bidModifier, sheet);
      }
      
        // Change the bidModifer, to make it more understandabe in the log
        if(bidModifier < 1){
          bidModifier = 1 - bidModifier; bidModifier = bidModifier * -1;
        } else if (bidModifier < 1.01 && bidModifier > 0.99){
          bidModifier = 0;
        } else{
          bidModifier = bidModifier - 1;
        }  
      
      	// Round up the bid for the Log, like the bidadjustment will be set in the campaign
        bidModifier = Math.round(bidModifier * 100);

      	Logger.log(row["DayOfWeek"] + " " + row["HourOfDay"] + " Bidmodifier: " + bidModifier);

    } // End of day + hour check

  } // End of row-iterator

  if(config.LOG === true) {
  	Logger.log(" ");
  }

} // function bidadjustmentAdschedule()

////////////////////////////////////////////////////////////////////

function setCell(hour, date, bid, sheet){
  
  var column = ["H","B","C","D","E","F","G"];

  var row = parseInt(hour) + 3;  	
  var col = column[new Date(date).getDay()];
  var cellBid = col + row;
  var cellDate = col + 2;
  
  // Change the bid, to make it more understandabe in the sheet
  if(bid < 1){
    bid = 1 - bid; bid = bid * -1;
  } else if (bid < 1.01 && bid > 0.99){
    bid = 0;
  } else{
    bid = bid - 1;
  }  
  
  // Round up the bid for the Google sheet, like the
  // bidadjustment will be set in the campaign
  bid = Math.round(bid * 100) / 100;
  
  sheet.getRange(cellBid).setValue(bid);
  sheet.getRange(cellDate).setValue(new Date());
  
} // function setCell()

Sharing knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Nils Rooijmans
Google Ads Scripter
Water Cooler Topics
Nils Rooijmans, Google Ads Scripter
Bas Baudoin
Teamleader PPC
Happy Leads
Bas Baudoin, Teamleader PPC
Martijn Kraan
Freelance PPC Specialist
Brightstep
Martijn Kraan, Freelance PPC Specialist
Tibbe van Asten
PPC Specialist
Founder Adsscripts
Tibbe van Asten, Senior PPC Automation Specialist