Align your bids with your CPA within 5 minutes using AdWords Editor and Excel

By , guestblogger

63 SHARES

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.

Theory

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:


Campaigns, ad groups and keywords have been blurred on purpose in the screenshots.

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.

Get more weekly links with our Fast Five newsletter! Five Fast Links in Your Email Every Friday.

Also send me a daily RSS digest

Automation Toolkit

Twitter Facebook LinkedIn Google+ Email Print More
  • Koen

    Great article!

  • http://tessarexroat.com Tessa

    This can be a great starting-off point, and I’ve definitely used this strategy before, but it creates a slightly short-sighted model of bidding. Your overall goals are probably a CPA of $20, but if your biggest volume keyword has a $30 CPA and all the other keywords in the adgroup have $8 CPAs, your average might still be $20, in which case you wouldn’t necessarily want to lower that big keyword’s bid. That way you’re maintaining quantity as well as quality, otherwise you run the risk of getting a great CPA, but cutting the total number of acquisitions.

    • http://www.netsociety.nl/en/ Wijnand

      Hi all,

      Thanks for the great feedback!

      @ Tessa: you’re absolutely right. You always have to balance the trade-off between quantity (high volume keywords) and quality (low CPA keywords).
      But let’s say all the keywords in your example have an average position of 4, then aligning their bids with their conversion rate would lead to higher bids (and thus generally higher positions and more clicks) for the $8 CPA keywords, which would increase both their CPA and volume, and lead to a lower bid for the $30 CPA keyword, which would generally decrease both the CPA and volume for this keyword. The end-result then might very well be an improvement in both quality and quantity.

      Wijnand

  • http://www.certifiedknowledge.org brad

    Nice article.

    This is also a good method to set initial bids (which is often a struggle for many).

    You could take the same data; estimate a conversion rate – and then use the same steps to determine starting bids.

  • http://www.PainlessStopSmokingCure.com Eric E

    Hi Wijnand,

    Great article and great content!!

    Thank you.

    cheers

    Eric

  • Theresa

    This is a great article – a lot to think about and, yes, an easy way to test finding the right bid to produce your desired CPA.

  • http://www.alexlcohen.com Alex Cohen

    I’m pretty sure Google Alerts is the worst product ever or else I would have seen this a while ago. Thanks for the ClickEquation ebook shout out!

    -Alex

  • http://www.managed-ppc.co.uk David B

    Great article and definitely highlights the relationship between CPA, CPC and conversion rate but there is a missing factor here – daily budget.

    Ultimately whatever you bid must align with your daily budget so that you are using your daily budget but only just. You could be hitting your target CPA but blowing your daily budget in a few hours – this would indicate that you are missing out on a much lower CPA by bidding too high. Conversely you might fail to spend your daily budget and, as other posters point out, you’d therefore have cut volume in favour of margin….

    The other factor is that the CPA/CPC/Conversion rate formula is circular – ie CPC defines rank which massively defines conversion rate.

    My way of managing bids, at least at the beginning, is to make all opening bids the same or a factor of the first page bid estimate. This way all keywords are at least calibrated to supply/demand in some way. Then I look at the lost impressions share for budget and raise/lower the bids by a uniform % until this is near 0% and the budget is still being spent. After this I get a bit more granular and move the top performing terms into their own campaign, with their own budget and manage each keyword individually….

  • Melanie

    I realize this article is well over a year old now, but I
    imagine the logic is all the same. I’ll be honest, I am not at all following
    the idea that to calculate your CPA you multiply your CPC by the number of
    clicks you need to get a conversion. I have no idea how many clicks I need to
    get a conversion. And are you looking at this from the campaign level or for
    each individual keyword? If it’s the latter than you’ll need to calculate a
    different CPA for each individual keyword since the CPC varies. Could you
    please elaborate on this a bit more, I’m very intrigued!

    • Wijnand Meijer

      Hi Melanie,

      indeed the logic of the article still holds true as your CPA will always depend on your conversion rate and CPC.

      I’m looking at a keyword level, so you will need at least 1 conversion for each keyword you want to change the Max CPC for. 
      Once you have that conversion (preferably more), you ‘know’ how many clicks you needed to get that conversion. 
      Of course, this can change in the future, but then you can easily recalculate the bids.

      Another term for CPA is cost per conversion. So if it takes you a 100 clicks that cost $0.50 each to get 1 conversion, your cost per conversion = $50 = 100 x $0.50.

      Once you know what your conversion rate is for a certain keyword and you have a certain CPA you want to achieve, you can set your bid in such a way you only pay the ‘part’ that is converting as percentage of your CPA. So if a keyword converts 1% of the time, you pay 1% of your CPA for that word, if it converts 10% of the time, then you pay up to 10% of your CPA for that word. 
      In both cases the bid matches the value they deliver.

      I hope it makes a bit more sense now, otherwise re-reading the theory part of the article might be helpful.

  • Pingback: travel place

  • Pingback: yaeger-cpa-exam-review-materials

  • Pingback: Cost per Action Promoting Methods