[Adwords Scripts] Automate calculating device-level bid modifiers

With traffic for mobile and tablet devices now exceeding that of desktop devices, the importance of setting bids at device level is now more important than ever.

One of the most tedious tasks for an Account Manager is going through on a weekly basis and calculating, then setting, device-level bid modifiers for each campaign.

So we wrote a script that automatically goes through and calculates then sets device-level bids, significantly reducing the time required to do this task, and ensuring that budget is being distributed to the best-performing devices.

How the script works

This Adwords script makes bid adjustments based on the performance of the device that a user is browsing on. It will automatically calculate and increase bids if performance on a specific device is better than average, or decrease bids if performance on that device is below average.

The logic behind the script is as follows: it takes a look at your campaigns over the specified time period (e.g. a week, or a month), and then applies the following equation to calculate the bids.

By default, the script will iterate through campaigns one at a time and adjust bids at a campaign level.

To ensure statistical significance within the control panel, you can specify a minimum conversion number required per device at campaign level for the script to work.

If your number of conversions for that device is under your specified conversion limit, the modifiers calculated using data from the whole account will be applied, rather than modifiers calculated by looking at campaign-level data. Where there is already a modifier in place, the script will take this into account when calculating the new bids.

It is worth noting that if the campaign or account adjustment is set to -100% for a particular device, then the script will not make any changes to the modifier. Finally, when setting up the script, we would suggest that you set it to run monthly if you have set the lookback window to ‘month’, or weekly if you’ve set the lookback window to 7 days.

How to use the script

There are four variables to set in the user area at the top of the script:

  • Lookback window: this is time period during which you want the script to analyse. It can be set to ‘week’ (the last 7 days) or ‘month’ (the last 30 days).
  • Conversions limit: This is the number of conversions required for the account-level modifiers to be applied, as opposed to using the campaign-level (default). E.g. if the conversions limit is set to ‘5’, any campaigns with fewer than 5 conversions will have the account-level modifiers applied.
  • Campaigns to include: This is where you define which campaigns you want to be included, which are specified in a list. E.g. [“campaign1”,”campaign2”]. If you want all campaigns to be included, leave the brackets empty, i.e. []
  • Campaigns to exclude: This is where you define the list of campaigns you want to be excluded, e.g. [“campaign3”,”campaign4”]. If you don’t want any campaigns to be excluded, leave the brackets empty, i.e. []

 

/**
*
* Device bid modifier script
*
* The script calculates device level moodier based on the last 7 or 30 days data.
* The script will take into account the current device level modifiers.
*
* Version: 1.0
* maintained by Clicteq
*
**/

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

//Options

//Select look back window. Available values 'week' = last 7 days, 'month' = last 30 days.

USERLOOKBACK = 'month' 

//Select conversions limit. Each campaign with less conversions than conversions limit on device level will have the account-level modifiers applied.

CONVERSIONSLIMIT = 5 

//Select which campaigns to include. Put [] to include all campaigns. Pass a list, for example, ["campaign1","campaign2"] to include certain campaigns.

INCLUDEDCAMPAIGNS = [] 

//Select which campaigns to exclude. Put [] not to exclude any campaigns. Pass a list, for example, ["campaign1","campaign2"] to exclude certain campaigns.

EXCLUDEDCAMPAIGNS = []

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


function prepareInput()
{
  //Prepares input
  
  if(USERLOOKBACK == 'month')
  {
  TIMERANGE = 'LAST_30_DAYS'
  }
  
  else if(USERLOOKBACK = 'week')
  {
    TIMERANGE = 'LAST_7_DAYS'
  }
  
  
  for(var i = 0; i < INCLUDEDCAMPAIGNS.length; i ++)
  {
    INCLUDEDCAMPAIGNS[i] = "'" + INCLUDEDCAMPAIGNS[i] + "'"
    
  }
  
  for(var i = 0; i < EXCLUDEDCAMPAIGNS.length; i ++)
  {
    EXCLUDEDCAMPAIGNS[i] = "'" + EXCLUDEDCAMPAIGNS[i] + "'"
    
  }
  
  INCLUDEDPARSED = "[" + INCLUDEDCAMPAIGNS.toString() + "]"
  EXCLUDEDPARSED = "[" + EXCLUDEDCAMPAIGNS.toString() + "]"

}

function getRows(reportType)
{
  //Gets the correct report to calculate CPAs using user input (for campaigns
  
  if(reportType == 'account')
  {var query = "SELECT AccountDescriptiveName, Cost, Device,Conversions FROM ACCOUNT_PERFORMANCE_REPORT DURING "}
  else if(reportType == 'campaign')
  {
    if(INCLUDEDCAMPAIGNS.length == 0 && EXCLUDEDCAMPAIGNS.length == 0)
    {
    var query = "SELECT CampaignName, Cost, Device,Conversions FROM CAMPAIGN_PERFORMANCE_REPORT DURING "
    
    }
    else if (INCLUDEDCAMPAIGNS.length >0)
    {
      var query = "SELECT CampaignName, Cost, Device,Conversions FROM CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignName IN "+ INCLUDEDPARSED +" DURING "
    }
    else if (EXCLUDEDCAMPAIGNS.length >0)
    {
      var query = "SELECT CampaignName, Cost, Device,Conversions FROM CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignName NOT_IN "+ EXCLUDEDPARSED +" DURING "
    }
    
  }
  
  
  return AdWordsApp.report(query + TIMERANGE).rows()
}


function calcModifier(cpa,accountCpa)
{
  //Calcuates a bid modifier
  
  return (((accountCpa/cpa) - 1) * 100).toFixed(0)
}

function getAccountModifiers()
  {
    
    //Returns account-level bid modifiers
    
  var rows = getRows('account')
  
  var accountCpas = {}
  var totalCost = 0
  var totalConv = 0
  
  while(rows.hasNext())
  {
    var row = rows.next()
    rowValues = row.formatForUpload()
    var rowValues = row.formatForUpload()
    var cost = rowValues['Cost'].replace(",","")
    cost = parseFloat(cost)
    var conversions = rowValues['Conv. (opt.)'].replace(",","")
    conversions = parseFloat(conversions)
    totalCost += cost
    totalConv += conversions
    if(rowValues['Device'] == 'Other')
    {continue}
    accountCpas[rowValues['Device']] = (cost/conversions).toFixed(2)

    Logger.log(rowValues['Device'] + " COST: " + cost)
    Logger.log(rowValues['Device'] + " CONVERSIONS: " + conversions)
    Logger.log("")
    
  }
    
    Logger.log("ACCOUNT CONVERSIONS: " + totalConv)
    Logger.log("ACCOUNT COST: " + totalCost)
    
  var accountCpa = (totalCost/totalConv).toFixed(2)  
  
  Logger.log("ACCOUNT CPA: " + accountCpa)
    Logger.log("-----------------------------------")
  
  accountCpas['Total'] = accountCpa
    var devices = Object.keys(accountCpas)
    var modifiers = {}
    
for(var i  = 0; i < devices.length; i++)
{
  var device = devices[i]
  modifiers[device] = calcModifier(accountCpas[device],accountCpas['Total'])
  
}
    
  return modifiers
  }

function getCampaignCpas()
{
  
  //Gets campaign cpas segmented by device
  
  var rows = getRows('campaign') 
  var campaignCpas = {}
  
  while(rows.hasNext())
  {
    var row = rows.next()
    rowValues = row.formatForUpload()
    var rowValues = row.formatForUpload()
    var campaign = rowValues['Campaign']
    var cost = rowValues['Cost'].replace(",","")
    var conversions = rowValues['Conv. (opt.)'].replace(",","")
    
    cost = parseFloat(cost)
    conversions = parseFloat(conversions)
    
    var device = rowValues['Device']
    if( device == 'Other')
    {continue}
    
    var singleDevice = {}
    
    if(conversions > CONVERSIONSLIMIT)
    {
    var cpa = cost/conversions
    singleDevice = cpa
    }
    else
    {
    singleDevice = 'account'
    }
    if(Object.keys(campaignCpas).indexOf(campaign) == -1)
    {
    campaignCpas[campaign] = {'Computers':0, 'Mobile devices with full browsers':0, 'Tablets with full browsers':0}
    campaignCpas[campaign][device] = singleDevice
    }
    else
    {
      campaignCpas[campaign][device] = singleDevice
    }
    
    
  }
  
  return campaignCpas
}

function getCampaigns(campaignTypes)
{
  //Gets a campaign iterator using the conditions from user input
  
  if(campaignTypes == 'normal')
  {
  
      if(INCLUDEDCAMPAIGNS.length == 0 && EXCLUDEDCAMPAIGNS.length == 0)
    {
    var campaigns = AdWordsApp.campaigns().get()
    }
    else if (INCLUDEDCAMPAIGNS.length >0)
    {
      var campaigns = AdWordsApp.campaigns().withCondition("CampaignName IN "+ INCLUDEDPARSED).get()
      
    }
    else if (EXCLUDEDCAMPAIGNS.length >0)
    {
      var campaigns = AdWordsApp.campaigns().withCondition("CampaignName NOT_IN "+ EXCLUDEDPARSED).get()
    }
  }
  
  else if(campaignTypes == 'shopping')
  {
    if(INCLUDEDCAMPAIGNS.length == 0 && EXCLUDEDCAMPAIGNS.length == 0)
    {
    var campaigns = AdWordsApp.shoppingCampaigns().get()
    }
    else if (INCLUDEDCAMPAIGNS.length >0)
    {
      var campaigns = AdWordsApp.shoppingCampaigns().withCondition("CampaignName IN "+ INCLUDEDPARSED).get()
      
    }
    else if (EXCLUDEDCAMPAIGNS.length >0)
    {
      var campaigns = AdWordsApp.shoppingCampaigns().withCondition("CampaignName NOT_IN "+ EXCLUDEDPARSED).get()
    }
    
    
  }
  
  return campaigns
}

function setBids(campaigns,campaignCpas, accountModifiers)
{
  
  
  var computer = 'Computers'
  var mobile = 'Mobile devices with full browsers'
  var tablet = 'Tablets with full browsers'
  
    while(campaigns.hasNext())
  {
    var campaign = campaigns.next()
    var name = campaign.getName()
    
    var campaignCpa = campaign.getStatsFor(TIMERANGE).getCost()/campaign.getStatsFor(TIMERANGE).getConversions()
    
    var currentComputer = campaign.targeting().platforms().desktop().get().next().getBidModifier()
    var currentMobile = campaign.targeting().platforms().mobile().get().next().getBidModifier()
    var currentTablet = campaign.targeting().platforms().tablet().get().next().getBidModifier(
    
    var newComputer = calcModifier(campaignCpa,campaignCpas[name][computer])
    var newMobile = calcModifier(campaignCpa,campaignCpas[name][mobile])
    var newTablet = calcModifier(campaignCpa,campaignCpas[name][tablet])
    
    var accountComputer = accountModifiers[computer]/100
    var accountMobile = accountModifiers[mobile]/100
    var accountTablet = accountModifiers[tablet]/100
    
    Logger.log("CAMPAIGN NAME: " + name)
    Logger.log("CURRENT DESKTOP MODIFIER " + currentComputer)
    Logger.log("CURRENT MOBILE MODIFIER " + currentMobile)
    Logger.log("CURRENT TABLET MODIFIER " + currentTablet)
    Logger.log("")
    
    //desktop---------------------------------------
    
    if(currentComputer != 0)
    {
  if(!isNaN(newComputer))
    {
     var newBidComputer = currentComputer + (newComputer/100)
    }
    
    else
    {
      var newBidComputer = currentComputer + accountComputer
    }
      if(newBidComputer < 0)
      {newBidComputer = 0}
      
      Logger.log("NEW DESKTOP MODIFIER: " + newBidComputer)
      campaign.targeting().platforms().desktop().get().next().setBidModifier(newBidComputer)
    }
    
    //mobile---------------------------------------
    

    if(currentMobile != 0)
    {
  if(!isNaN(newMobile))
    {
      var newBidMobile = currentMobile + (newMobile/100)
      
    }
    
    else
    {
      var newBidMobile = currentMobile + accountMobile
      
    }
      
      if(newBidMobile < 0)
      {newBidMobile = 0}
      Logger.log("NEW MOBILE MODIFIER: " + newBidMobile) 
      campaign.targeting().platforms().mobile().get().next().setBidModifier(newBidMobile)
    }
    
    //tablet---------------------------------------
    
    
    if(currentTablet != 0)
    {
  if(!isNaN(newTablet))
    {
      var newBidTablet = currentTablet + (newTablet/100)
    }
    
    else
    {
      var newBidTablet = currentTablet + accountTablet
      
    }
      if(newBidTablet < 0)
      {newBidTablet = 0}
      Logger.log("NEW TABLET MODIFIER: " + newBidTablet)
      campaign.targeting().platforms().tablet().get().next().setBidModifier(newBidTablet)
    }
    Logger.log("")
    Logger.log("#############################")
    
  }
  
}


function main() 
{
  //Combines all functions and modifies bids
  
  prepareInput()
  var accountModifiers = getAccountModifiers()
  var campaignCpas = getCampaignCpas()

  
  var campaigns = getCampaigns('normal')
  var shoppingCampaigns = getCampaigns('shopping')
  
  setBids(shoppingCampaigns,campaignCpas, accountModifiers)
  setBids(campaigns,campaignCpas, accountModifiers)
  
}

Disclaimer: this script is provided without guarantee or liability.

wesley parker
About wesley parker

Wesley is Founder and CEO at Clicteq. He currently manages a £6 Mil Adwords portfolio across a range of different sectors. He regulally features in leading search publications such as Econsultancy, Campaign Magazine and Search Engine Land. You can follow him on Twitter or connect with him on Linkedin

6 thoughts on “[Adwords Scripts] Automate calculating device-level bid modifiers

  • Hey Wesley,
    just tried to run your script but it throws a syntax error in line 252 and I’m not able to find out why… any idea?
    thank you & kind regards,
    André

  • Hi Wesley,

    This script seems really interesting. However, for someone not fully able to read Javascript, it’s a bit unclear what the formula or calculation is to determine the actual bid modifier %. Could you elaborate on this?

    thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *