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 decreases and sales 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 increases and conversion rates decline; here, advertisers may want to look to decrease bids to reduce wasted spend.
At DemandMore, we’ve created a Google Ads script to do just that, allowing you to make programmatic changes to your bids in Google Ads in real time, based on exchange rates.
Here are the different variables that you can set in the user area at the top of the script.
- Base currency (of origin country): This is the three-letter currency code in the country where people are travelling from, such as “GBP”, and can be set on line 9.
- Base currency (of destination country): This is the three-letter currency code of the destination. For example, if the destination is America, use the code “USD”. This can be set on line 12.
- 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, and can be changed on line 15.
- Google Sheets URL (optional): If you want to create a log of all your changes, you can do this by entering the URL of a Google Sheet where you want to record all the changes the script makes. This is set 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 big a 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, leave it as 1.00. If it has increased by 10% and you want to increase bids by 10%, set a modifier of 1.10. To decrease them by 10%, set a modifier of 0.9. To increase them by 36%, set a modifier of 1.36, and to decrease them by 36%, set a modifier of 0.64.
- Include paused ads: You can select whether or not to make changes to a paused ad group within the script. You can either set this to True if you do want to or False if not. Do this 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 only fluctuate slightly, it generally makes sense to run this script weekly as opposed to hourly, as we usually 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);
}