[Adwords Scripts] Automate calculating and setting location bid modifiers

Following on from our first article in our series on automating device level bid modifiers, we know that calculating and setting location bid modifiers is also pretty tedious.

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

How the script works

This script makes bid adjustments based on the performance of the location that a user is browsing in. It will automatically calculate and increase bids if performance on a specific location is better than average, or decrease bids if performance in that location 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.

Modifier = (location CPA / average CPA for all locations) – 1 * 100

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 location 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 location, 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 the 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. []
/**
*
* location bid modifier script
*
* The script calculates location 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 Id, Cost, Conversions FROM CAMPAIGN_LOCATION_TARGET_REPORT DURING "}
  else if(reportType == 'campaign')
  {
    if(INCLUDEDCAMPAIGNS.length == 0 && EXCLUDEDCAMPAIGNS.length == 0)
    {
    var query = "SELECT Id,CampaignName, Cost,Conversions FROM CAMPAIGN_LOCATION_TARGET_REPORT DURING "
    
    }
    else if (INCLUDEDCAMPAIGNS.length >0)
    {
      var query = "SELECT CampaignName, Cost, Id,Conversions FROM CAMPAIGN_LOCATION_TARGET_REPORT WHERE CampaignName IN "+ INCLUDEDPARSED +" DURING "
    }
    else if (EXCLUDEDCAMPAIGNS.length >0)
    {
      var query = "SELECT CampaignName, Cost, Id,Conversions FROM CAMPAIGN_LOCATION_TARGET_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 modifiers = {}
  var totalCost = 0
  var totalConv = 0
  var foundIds = []
  var tempCpas = {}
  
  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
    var location = rowValues['Location']

    if(foundIds.indexOf(location) == -1)
    {
      foundIds.push(location)
      tempCpas[location] = {'cost':0, 'conversions':0}
      tempCpas[location]['cost'] = tempCpas[location]['cost'] + cost
      tempCpas[location]['conversions'] = tempCpas[location]['conversions'] + conversions
      
    }
    
    else
    {
      tempCpas[location]['cost'] = tempCpas[location]['cost'] + cost
      tempCpas[location]['conversions'] = tempCpas[location]['conversions'] + conversions
    
    }
   
  var accountCpa = (totalCost/totalConv).toFixed(2)  
    

  }

  for (var i = 0; i < foundIds.length ; i++)
    {
      var location = foundIds[i]
      var locCpa = tempCpas[location]['cost']/tempCpas[location]['conversions']
      if(tempCpas[location]['conversions'] > 0)
      {
      modifiers[location] = calcModifier(locCpa,accountCpa)
      }
      else
      {
        modifiers[location] = 'skip'
      }
      
    }
    Logger.log("ACCOUNT CONVERSIONS: " + totalConv)
    Logger.log("ACCOUNT COST: " + totalCost)
    
  Logger.log("ACCOUNT CPA: " + accountCpa)
    Logger.log("-----------------------------------")
  
  return modifiers
  }


function getCampaignCpas()
{
  
  //Gets campaign cpas segmented by device
  
  var rows = getRows('campaign') 
  var campaignCpas = {}
  var foundCampaigns = []
  
  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 location = rowValues['Location']
    
    var allLocations = {}
    var temp = {}
   	
    if(conversions > CONVERSIONSLIMIT)
    {
    var modifier = cost/conversions
    
    }
    
    else
    {

    var modifier = 'account'
    
    }
    
    if(foundCampaigns.indexOf(campaign) == - 1)
    {
      campaignCpas[campaign] = {}
      campaignCpas[campaign][location] = modifier
      foundCampaigns.push(campaign)
    }
    
    else
    {
    	campaignCpas[campaign][location] = modifier
    }
  }
  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)
{
 
    while(campaigns.hasNext())
  {
    var campaign = campaigns.next()
    var name = campaign.getName()
      
    var campaignCpa = campaign.getStatsFor(TIMERANGE).getCost()/campaign.getStatsFor(TIMERANGE).getConversions()
      
    Logger.log("CAMPAIGN NAME: " + name)
    Logger.log("")
    
    var locations = campaign.targeting().targetedLocations().get()
    
    while(locations.hasNext())
    {
      var location = locations.next()
      var id = location.getId().toString()
      var locName = location.getName()
      
      var currentBid = location.getBidModifier()
      
      if(campaignCpas[name][id] == 'account')
      {
        if(accountModifiers[id] == 'skip')
        {continue}
        
        else
        {
        var newModifier = parseInt(accountModifiers[id])/100
        }
        
      }
      
      else
          
      {
       var newModifier = calcModifier(campaignCpas[name][id],campaignCpa)/100
      }
      
      
      if(isNaN(newModifier))
      {continue}
      
      var newBid = currentBid + newModifier

      if(newBid < 0)
      {
        newBid = 0
      }
      
      if(newBid == currentBid)
      {
        continue
      }
        
      Logger.log("")
      Logger.log(locName)
      Logger.log("CURRENT BID: " + currentBid)
      Logger.log("NEW BID: " + newBid)
      location.setBidModifier(newBid)
      
    }

    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. Clicteq will not be liable for any loss caused due to use.

 

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

Leave a Reply

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