Align your bids with your CPA within 5 minutes using AdWords Editor and Excel
August 17, 2010
That’s right, changing your bids isn’t something you want to spend a lot of time on, and if your goal is to reach a certain CPA (or CPL, CPS, CPO, etc.) it doesn’t have to take you much more than a couple of minutes, even for large accounts.
But before giving you the theory and step by step instructions, there are a few things we should agree on about the kind of bid management we’re going to do:
- ‘Great bids can’t fix a lousy campaign’. This is actually the title of chapter 15 of the ClickEquations eBook (highly recommended) and it’s right on the money. It basically comes down to this: if the relationship between the search queries, your keywords, ads and landing pages isn’t thoughtfully managed or optimized, bidding will not solve your problems.
- CPA might not be the optimal KPI for you. Especially if you sell goods with greatly differing prices or margins, goals like ROAS or ROI are more suitable. This article however, is about reaching a certain CPA. You might also use different CPA’s for different campaigns, that is not a problem with this method.
If you measure your variable conversion value with the AdWords conversion tracker, then consider the method described by Brad Geddes in his article (Setting Max CPC) to align your bids with your ROI goals.
- The AdWords conversion tracker credits the last keyword (and corresponding ad) that was clicked before the conversion that followed. So when we use the AdWords conversion data, we’re also bidding based upon last click attribution. This might not be optimal, especially if you have a lot of keywords that often assist conversions. Luckily, you can find these keywords in your Search Funnels report and manually credit assisting keywords, if necessary.
Conversion attribution is a subject worth exploring. Don’t forget to read what Andrew Goodman (Traffick) and Adam Goldberg (ClearSaleing) have to say about it before you join this debate.
- Consider the bids that the discussed method will provide you as suggestions, based on historical data. Your past data might not be predictive of the future, and when we change bids based on your statistics, we assume just that. So if you know that this assumption isn’t valid, then use your own judgment to change bids.
- We are going to change bids on the keyword level. So it will not work for your campaigns on the Display Network or for search keywords that have no conversions (more about these later).
- Conversion rates do not vary by position. The debate is still out on this one, although according to Google it shouldn’t vary much. As always, you should test this for yourself, but I personally believe there are tons of things that influence conversion rate more than ad position.
And when changing bids based on conversation rates (which we will do), we simply have to assume these will not vary by ad position.
- You track conversions with the AdWords conversion tracker and are bidding manually. These are requirements for the method to work.
Maybe you use the Conversion Optimizer in some campaigns, needless to say that you can’t change bids manually in these campaigns. And if the conversion optimizer is giving you good results, you shouldn’t want to.
However, you can still use the method for campaigns that are not using automated bid management.
Before we go to the how-to section of this article, let me explain the simple theory it is based on:
Your CPA is the result of your CPC and conversion rate.
To calculate your CPA, you multiply your CPC by the number of clicks you need to get a conversion. If a click costs you $0.50 and you need a hundred clicks for a conversion, then your CPA will be $50.
You probably already knew that. And we also know that a lot of factors influence CPC (like quality score) and conversion rate (like your landing page) and that you should always keep optimizing these.
Now let’s turn the equation around: if you know that your target CPA is $40 and you need a hundred clicks for a conversion (i.e. your conversation rate is 1%), how much should you pay for that click?
Exactly, you should pay no more than $0.40 for that click, which happens to be 1% multiplied by $40. Consequently, if your conversion rate is 2% you can pay up to $0.80 to reach your target CPA of $40. And if your conversion rate is 100% you can bid your full CPA for that keyword. Just make sure your conversion tracker isn’t on the landing page instead of the thank you page first. 😉
Hence, to calculate the right bid to reach your CPA, you simply have to multiply the conversion rate of a keyword with its target (or max) CPA.
So we’ll consider conversion rate as a given and CPC as the variable we will change to reach our CPA.
So how do we quickly do this with AdWords Editor and Excel?
1) Open your account with AdWords Editor, get recent changes and download statistics for the date range you want to use to base your bid decisions on. I can’t tell you which date range is the right one. On the one hand you need enough conversions to be able to make decisions, on the other hand you don’t want to go too far back in time for the data to be representative. So choose a date range you’re comfortable with.
2) Select the keywords tab on the account level to see all keywords within your account.
3) Sort your keywords by conversions (make sure you have the conversions column in your view) and select all keywords that have at least 1 conversion:
4) Copy this selection.
5) Paste this selection into a new Excel file. The only columns we need are: Campaign, Ad Group, Keyword, Keyword Type, Max CPC and Conversion Rate (1-per-click, or many-per-click if you prefer to use many-per-click). You can safely remove all other columns.
6) Now, in an empty column to the right, insert a formula that multiplies the conversion rate with your target (or max) CPA. In the example below the conversion rate of 8.06% (in cell F2) is multiplied by the target CPA of $40
Important: we are changing your Max CPC’s. As you know, this isn’t the same as your actual CPC. So you can enter a number that is slightly higher than your target CPA into the formula, if you feel comfortable doing so.
Furthermore, if you have different CPA’s for different campaigns, it is best to make these changes one campaign at a time (by filtering out all the other campaigns in Excel).
7) Populate this column by double clicking the fill handle (the skinny plus sign that appears in the bottom right corner) in the cell in which you just entered the formula. Now all your new CPC’s are calculated:
8) Now that you have your CPC’s that are aligned with your CPA and conversion rate in this column, we just need copy and paste them (as values) back into to the Max CPC column. You can overwrite the existing Max CPC’s in this column.
9) If you want, you can format the Max CPC column to contain numbers and round off to two decimals. This isn’t necessary for the AdWords Editor import we’re going to do next.
However, AdWords Editor will not round up your CPC’s, but just use the first 2 decimals. So 3.536 will become 3.53. If you want this to become 3.54, format this column first.
10) Select and copy the Campaign, Ad Group, Keyword, Keyword Type and Max CPC (with your new CPC’s) columns in Excel.
11) Go back to AdWords Editor and select the Keywords view on the account level.
12) Click on the ‘Make Multiple Changes’ button and select ‘Add/Update Multiple Keywords’
13) Now paste the selection you just made in Excel into the Keywords area. Make sure the box ‘My keyword information below includes columns for campaign and ad group names.’ is selected.
14) When you click the Next button, it should say how many keywords will be updated. If you get the message that new Ad Groups or Campaigns will be created, something went wrong, so make sure you copied all the right columns.
If some keywords get ignored, that’s just because they already had the Max CPC you imported, so that’s fine.
15) Congratulations, you have now aligned your CPC’s with your CPA and conversion rate!
Before you post these changes consider a few things:
- Some keywords may have gotten a very high Max CPC. Theoretically these CPC’s are justified, but you may not want to to set your bids this high. To review these, just order your keywords by Max CPC in AdWords Editor and manually lower the bids that are too high for your taste.
- You didn’t change the bids of keywords with 0 conversions. This doesn’t mean that these should stay untouched, especially if they accrue a lot of costs.
You will have to manually adjust or even pause these keywords according to your CPA goals. For example, you could pause all keywords that have cost you (at least) 2 times your CPA and still didn’t convert. And you could lower bids to the first page bid estimates for all keywords that have cost you between 1 and 2 times your CPA and didn’t convert.
If they are early buying cycle keywords, you might want to set them apart, assign other goals to them and let the Budget Optimizer do the bidding, as Brad Geddes describes in this article.These are just suggestions to get you thinking in a methodical way about bid management, feel free to use any limits and rules that work for you.
Use the advanced search and advanced bid changes options in AdWords Editor to quickly find these keywords and make these bid changes or reorganizations.
- You didn’t change the bids in your campaigns on the Display Network (if any). However, you could apply the same logic on the ad group level instead of the keyword level and change bids on the ad group level accordingly.
The title of this article promised you to align your bids within 5 minutes. Maybe the first few times will take you a bit longer, but once you get the hang of it, you will be done in a couple of minutes.
For me personally, it certainly beats reviewing keywords one by one and increasing or decreasing bids based on CPA & position.
If you’re going to try this out for your account, I’d love to hear your experiences! And If you’re not, I’d love to hear why 😉
Guest Blogger Bio: Wijnand Meijer is Paid Search Strategist at Netsociety, an online media agency based in Amsterdam (the Netherlands) and Brussels (Belgium). He created his first AdWords campaigns in 2006 and is currently helping advertisers and coworkers alike to get their Paid Search to the next level.
Browse By Category
Smart RLSA Tips for the Holiday Season
The holiday season is just around the corner! Get tips on how to fine tune your RLSA audiences to capture additional conversions or revenue.
Our Attendees Have Spoken - Why You Can't Miss London
At Hero Conf London, 23-25 October, we've created a schedule that can be tailored to any PPC role. In-depth, actionable sessions for the PPC Specialist and high-level strategies and tactics for the Marketing Manager looking to generate new opportunities.
Using Google Sheets as a Script Controller
Learn how to use Google Sheets to make AdWords script adjustments via spreadsheet rather than through the code itself.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
How To Setup An Apple Search Ads Campaign
Apple Search Ads has become a huge force in the world of app downloads. Get tips on how to launch a campaign to promote an app!
Adding Facebook Ad Block Lists
Get tips on how to add a Facebook ad placement block list and start blocking your ads from appearing on certain websites.
Optimizing Display Advertising in an Omni-Channel World
We’re teaming up with DialogTech to tell you all about display tactics you can put in place for conversion success, both online and over the phone. DialogTech’s Blair Symes and Hanapin’s Stephanie White will show you the advanced strategies that will have a big impact...