[Adwords Script] Bid Based on Forex Rates

In the travel sector, foreign exchange rates affect the cost of travel and therefore have an effect on the number of holidays that are booked.

When the pound is strong compared to the Euro the cost of booking a holiday to Europe will decrease and sales will go up, this is a great opportunity for advertisers and they should look to take advantage of it by showing their ads more.

In contrast, when the pound is weak compared to the Euro the cost of travel will increase and conversion rates decline, here advertisers may want to look to decrease bids to reduce wasted spend.

Here at Clicteq we have recently created an Adwords Script to just that, allowing you to make programmatic changes to your bids in Adwords in real time based on exchange rates which you can find below.

Here are the different variables that you can set in the user area at the top of the script.

  • Base Currency: This is the three letter currency code in the country where people are traveling from, E.g. “GBP” and can be set on line 9
  • Base Currency: This is the three letter currency code of the destination. If the destination was America then you would use the code “USD” for example. This can be set on line 12
  • The number of days for comparison. This is the time period over which you want to make your comparison, it is set to a default of 30 days. This is set on line 15
  • Google Sheets URL (optional). If you want to create a log of all of your changes you can do this by entering the URL of a Google sheet where you want to record all of the changes the script makes on line 18.
  • Define campaigns to include or exclude. In lines 22 to 25, you have the option to define which campaigns you want to include or exclude. By default, all campaigns are included. To include only certain campaigns define these within the brackets on line 22. To exclude certain campaigns define these within the brackets on line 25.
  • Bid Modifier. The bid modifier can be set on line 29 and allows you to set how bigger change you would like to make to your bids based on the difference in exchange rate. If the exchange rate has dropped by 10% and you want to drop the bids by 10% proportionately then leave this at 1.00. If it has increased by 10% and you want to increase bids by 10% more set a modifier of 1.1o. To decrease them by 10% set a modifier of 0.9o. To increase them by 36% you would set a modifier of 1.36 and to decrease them by 36% you would set a modifier of 0.64.
  • Do you want to include paused ads? You can select whether or not to make changes to paused ad group within the script. You can either set this to True if you want to or False if not on line 35.

The script can be found below, you can add it to your account within the bulk operations section of your account.

Note. As exchange rates will only fluctuate slightly it generally makes sense to run this script weekly as opposed to hourly as we generally suggest.

/************************************
 * Exchange Rate Based Bidding Script
 * @version 2.1
 ***********************************/

///////////////////////// BEGIN USER EDIT AREA /////////////////////////////

//Base currency
var baseCurrency = 'GBP';

//Currency to compare the base currency to
var compareCurrency = 'EUR';

//Number of days to use when comparing currencies
var daysAgo = 30;

//Enter the url of the spreadsheet and the name of the sheet where you want the data to be added (optional)
var logSheetUrl = 'https://docs.google.com/spreadsheets/d/1ORaqDhRKyxrPV4NL7U-TLi9J6lULKdXA0piVqwK0R6E/edit#gid=0';
var sheetName = 'Sheet1'

//Only include campaigns with each of these phrases in the campaign name (leave blank for all) e.g. ["Brand", "Spain High Value"];
var includeCampaigns = []; 

//Exclude campaigns with any of these phrases in the campaign name (leave blank for all) e.g. ["Non Brand", "Shopping"];
var excludeCampaigns = [];

//Additional modifier - once a multiplier has been calculated based on exchange rate changes, it will be  multiplied by this modifier. The modifier 1.1 will increase bids by 10%; the modifier 0.9 will decrease by 10%
//Leave as 1.0 if you do not want to alter the
var modifier = 1.0; 

//Ad (Delivery) Network Options: https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report#adnetworktype1
var adNetworks = ["SEARCH", "CONTENT", "YOUTUBE_SEARCH", "YOUTUBE_WATCH", "MIXED"]

//filter to enabled Campaigns, AdGroups and Keywords. 
var enabledOnly = true; //Options: true, false (no quotes)

///////////////////////// END USER EDIT AREA ///////////////////////////////


function main() {
  
  log('Script started...');
  var now = new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd'));
  log('Fetching exchange rates...');
  var ratesArr = retrieveRates(now);
  var newRate = ratesArr[0];
  var oldRate = ratesArr[1];
  
  var percentChange = 1.0 + ((newRate - oldRate) / oldRate);
  var totalModifier = (modifier) * percentChange;
    
  log('Final modifier: ' + parseFloat(totalModifier, 2));  
  var keywords = AdWordsApp.keywords().withIds(getKeywordIds()).get();
  var numChanges = keywords.totalNumEntities();
  log('Updating Max CPC...');
  while(keywords.hasNext()) {
    var keyword = keywords.next();
    if (keyword.bidding().getStrategyType() == 'MANUAL_CPC') {
      var maxCpc = keyword.bidding().getCpc();
      keyword.bidding().setCpc(maxCpc * totalModifier);
    }
  }
  var logs = numChanges + " changes made."
  log(logs)
  writeOutLogs(totalModifier,logs,now)
}


function getKeywordIds(){
  
  var ids = []
  var whereArray = [];
  whereArray.push("where AdNetworkType1 in ["+adNetworks+"]")
  if(typeof enabledOnly == "undefined" || !enabledOnly){
  } else {
    whereArray.push("and CampaignStatus = ENABLED ")
    whereArray.push("and AdGroupStatus = ENABLED ")
    whereArray.push("and Status = ENABLED ")
  }

  for (var i in includeCampaigns) {
    whereArray.push("and CampaignName CONTAINS_IGNORE_CASE '" + includeCampaigns[i].trim() + "'")
  }
  
  for (var i in excludeCampaigns) {
    whereArray.push("and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + excludeCampaigns[i].trim() + "'")
  }  
   
  var where = whereArray.join(" ");  
  var OPTIONS = { includeZeroImpressions : true };
  var cols = ['Id','AdGroupId'];
  var reportName = 'KEYWORDS_PERFORMANCE_REPORT';
  var query = ['select',cols.join(','),'from',reportName,
               where].join(' ');
  
  var reportIter = AdWordsApp.report(query, OPTIONS).rows();
  while(reportIter.hasNext()){
    var row = reportIter.next();
    ids.push([row.AdGroupId, row.Id])
  }
  return ids;
}

function retrieveRates() {
  
  var apiKey = '256107e251d2d66a03d67f1806dcf32b'; 
  var now = new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss'));
  now.setDate(now.getDate() - daysAgo);
  var dateQuery = '/' + now.toISOString().split('T')[0];
  
  var baseUrl = 'http://data.fixer.io/api';
  var apiKeyUrl = '?access_key=' + apiKey;
  var currentExchangeRateQuery = baseUrl + '/latest' + apiKeyUrl + '&base=EUR' + '&symbols=' + baseCurrency + ',' + compareCurrency;
  var oldExchangeRateQuery = baseUrl + dateQuery + apiKeyUrl + '&base=EUR' + '&symbols=' + baseCurrency + ',' + compareCurrency;
  
  try {
    var currentExRateResponse = UrlFetchApp.fetch(currentExchangeRateQuery, {validateHttpsCertificates: false});
    var oldExRateResponse = UrlFetchApp.fetch(oldExchangeRateQuery, {validateHttpsCertificates: false});
  } catch(e) {
    throw("Error: There was a problem fetching data from the fixer API. Fixer returned the following: " + e)
  }
  var currentData = JSON.parse(currentExRateResponse.getContentText());
  var oldData = JSON.parse(oldExRateResponse.getContentText());

  var currentRate = currentData.rates[compareCurrency] / currentData.rates[baseCurrency];
  var oldRate = oldData.rates[compareCurrency] / oldData.rates[baseCurrency];
  
  return [currentRate, oldRate];
}


function writeOutLogs(totalModifier,logs,now) {
  
  log('Writing logs...');
  var header = ['Base Currency', 'Comparison Currency', 'Days Ago', 'User Input Modifier', 'Final Modifier', 'Logs', 'Date Ran'];
  try{
    var outputSS = SpreadsheetApp.openByUrl(logSheetUrl).getSheetByName(sheetName)
    outputSS.getRange(1, 1, 1, header.length).setFontWeight('bold').setValues([header]);
    outputSS.setFrozenRows(1);
    var data = [baseCurrency, compareCurrency, daysAgo, modifier, parseFloat(totalModifier,2),logs, now];
    outputSS.getRange(outputSS.getLastRow() + 1, 1, 1, data.length).setValues([data]).setNumberFormat("#,##0.00");
    outputSS.getRange(outputSS.getLastRow(), outputSS.getLastColumn(), 1, 1).setNumberFormat("M/d/yyyy H:mm:ss");
    
  }catch(e){
   log(e)
   log("To output results to a sheet, please add a valid Google Spreadsheet URL to the script and ensure this user has access.") 
  }
  log("Finished")
}



/**
* Get AdWords Formatted date for n days back
* @param {int} d - Numer of days to go back for start/end date
* @return {String} - Formatted date yyyyMMdd
**/
function getAdWordsFormattedDate(d, format){
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}

function log(msg) {
  Logger.log(AdWordsApp.currentAccount().getName() + ' - ' + msg);
}

function round(num,n) {    
  return +(Math.round(num + "e+"+n)  + "e-"+n);
}

 

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 Search Engine Journal, Econsultancy and Certified Knowledge. You can follow him on Twitter or connect with him on Linkedin

One thought on “[Adwords Script] Bid Based on Forex Rates

Leave a Reply

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