International PPC: How to deal with currency fluctuations

If you run paid search campaigns in multiple countries, you'll need a good way to keep track of currency exchange rates. Columnist Daniel Gilbert has created a spreadsheet (and some code) to do this for you.

Chat with SearchBot

globe-world-spending-money-currency-ss-1920

One issue I’ve seen with international PPC is how to keep track of your spend and ROI abroad compared to at home. When setting up billing, you can either choose to use the target market currency or use your home currency. There are pros and cons to both, though I don’t intend to discuss them too much here, as most advertisers would have already made a decision a long time ago.

What is critical is that regardless of the currency you select, Google’s (and maybe Bing’s) internal auction is converted to USD in order for AdRank to be calculated. So without actually changing your bids abroad, you are bidding more and less aggressively based on currency fluctuations and competitor currencies.

Below, I’ll share a dashboard that we use for international PPC clients at Brainlabs (my employer) to track these movements and make adjustments where necessary.

This Google Sheets dashboard has all the currency information you will ever need to take back to your PPC reporting. Use this sheet to help keep currency conversions simple and up-to-date. There’s a brief explanation of how it works at the end of the article, along with a bit of code for those of you who want to convert currencies in JavaScript.

There are many reasons you may need currency data. Here are a few we thought of:

1. Improve your analysis and compare performance across markets

When analyzing your business across markets, it’s difficult to compare cost-related metrics unless everything is the same currency.

You might also want to check out the volatility of a currency before moving into a new market, especially if you’re going to need to convert money to make payments — if the exchange rate changes by 10 percent over the course of a month, you may end up paying much more at the end of the month than you would have at the start.

2. Use for reporting and billing

Like many of our clients, you may do the majority of your business in the country in which you’re based but have branched out to do some international business, too. Whether your operations are digital or otherwise, it’s usually preferable to have these international ventures use the local currency. For AdWords, this means setting up a different account in a different currency and getting billed for it at the end of the month in this currency.

It also makes reporting a bit of a nightmare — if you’re advertising in the US, Canada, Mexico, the Bahamas and Bermuda, you have to manually look up the exchange rate for each of these each day just to know how much you’re spending. Using a currency conversion function makes this automatic, keeping the exchange rate current, so that you always know your total spend in US dollars.

3. Bespoke paid-for currency conversion software can be expensive

Currency conversion software can cost anything from $5 to $1,000 a month, depending on how heavy your usage is, how frequently you want rates to update and how flexible you want to be with the currencies you use. In general, the more recent and accurate you want your data to be, the more you are going to pay.

Back in 2011, Google had its own finance service that retrieved exchange rates (and other financial data) for free, making it easy to perform currency conversions in scripts. The service itself has now been deprecated, but a method to access this data lives on through the GOOGLEFINANCE function in Google Sheets.

The Brainlabs Currency Converter

Now to the Brainlabs Currency Converter dashboard.

Brainlabs Currency Converter

To use it, click on the File menu, and then “Make a copy….” Save the copy to your Google Drive, and then it’s ready to use.

The sheet has four pages:

  • The Current Exchange Rate sheet. This is a table of the current exchange rates between various currencies, updated by Google every three to 20 minutes. Feel free to add in any currencies you are interested in; you can look up their three-letter currency code here. If for some reason it’s not providing you with the exchange rate, it could be that GOOGLEFINANCE doesn’t support exchange rates for that currency.
  • The Detailed Exchange Breakdown sheet. This is for looking more deeply into the exchange rate trends — how much has it changed over the last month? Over the last year? After choosing a lookback window, it gives you the exchange rates at close for each day, as well as graphing them and giving you the highest, lowest and average values in that time. If you want this detailed breakdown for several different currencies at once, you can duplicate the sheet and have a different conversion on each copy. To use, just enter a lookback window in days in cell B1 (or custom start and end dates in cells B2 and B3), then the three-letter code for the currency you’re converting FROM in B4, and TO in B5.
  • The Exchange Rate To Single Currency sheet. This sheet gives you a slightly less detailed look at the exchange rate to a single currency. Alter any of the red boxes to get exchange rates from many different currencies to just one, with the current exchange rate alongside the average, maximum and minimum exchange rates for a given time period.
  • The Useful Date Formulae sheet. A little bonus — this sheet contains some formulae for date periods you may want to use in the detailed exchange breakdown. These correspond to some of the date windows used in AdWords — useful if you’re in SEM and are using it to convert currencies.

If you’re more technically inclined and would like to implement currency conversion in code, here’s a JavaScript template to get you started:


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Daniel Gilbert
Contributor
Daniel Gilbert is the CEO at Brainlabs, the best paid media agency in the world (self-declared). He has started and invested in a number of big data and technology startups since leaving Google in 2010.

Get the must-read newsletter for search marketers.