Efficient bid management: spreadsheet fun!

Changing keyword bids is going to be a task that faces you with some regularity if you’re managing any PPC account. It would be fantastic if we could just manage ad group bids and never consider the cost of our individual keywords, but it doesn’t always work that way. Sometimes, you have fabulous keyword performers who need to be supported with better bids, hanging out in an ad group with massive overspenders who need to take a time out or at least a little dip in their ad ranking. And that’s all very well and good, if you have a very small account. But if you have a large PPC account- with lots of performers, and lots of overspenders, bid management can be very time-consuming if you don’t have access to, or would rather not use bid management software. Which is unfortunate, because there are a hundred other things you could do in your account, or to your landing pages, that might have a more substantial impact on your performance, and if you use all of your time changing bids, you’ll never get to them.

If you have a hundred keyword bids that need to be changed today, it’s going to be a general waste of your time to hang out in the AdWords interface or desktop editor and think: oh, this keyword is in position 3.4. Its bid is $0.24. I want to be in position 2. But its average cost per lead for the last 30 days is $4.68 and my maximum profitable CPL is $5. So what do I raise it to? $0.28? $0.50?  Don’t get me wrong- you have to consider all of those things, and more, but bid management- really, it’s not something that needs to consume your life. You can cut it down to something that’s both efficient and effective if you create a physical structure and some mental rules to support bid changing. So, having said that, here are the rules I follow when bid changing in Google. MSN/Bing would be similar due to the similarity of the AdWords and AdCenter desktop interfaces.

  1. Never ever even think about changing bids in the interface. Unless it’s like, two keywords. Instead, download account statistics in AdWords  Editor for whatever date range you need. The date range you need is going to depend on a lot of things, like how recently you’ve made bid changes, and the volume of your account. Don’t make bid changes based on 30 day performance, then go back 6 days later and make bid changes based on 30 day performance again- the impact of what you did 6 days before will be totally lost amongst the 30 day stats.
  2. Once you’ve downloaded statistics for the date range you want, just select all of the keywords for the account in Editor (or just for the campaign or ad group you want to change them for, if your review is limited to one), and paste them into Excel. There will be a lot of data there. Some if it is useless for your purposes. Destination URL, Comment? Avg CPM? Just delete those columns. Once you’ve just got the data you want to consider, highlight your first row (containing column names) and go to Data>Filter>AutoFilter. Now you can sort and sort to your heart’s desire and the data should stay with its proper keyword.
  3. Unless you’re using this method to determine if you have anything paused that you might want to turn back on, then set Campaign Status, Ad Group Status, and Keyword Status filters to show Active only. Paused stuff will just get in your way.
  4. Determine the goal of your bid changes. Do you need to lower your spend rate without lowering your leads too substantially? Is your CPL low and you have some room to increase cost to get more leads? This will determine what you need to focus on. Let’s say my main goal is to decrease CPL by lowering cost without lowering leads too much.  The first place to look is at anybody who has spent money but NOT converted. Major offenders. So set your Conversions filter to show only 0 conversion keywords, and sort by descending cost. Say my goal CPL for this account was $10. I need to decide whether keywords that have spent over that goal and have not converted should be paused or have their bids lowered. Before deciding to pause a keyword, check its longer-term performance. Just because something might be doing badly in the past 14 days doesn’t mean it always has, or always will.
  5. Make a notation of your intended action for the keyword. I generally use X for pause, D for decrease, and I for increase. You can color code instead if you’re not using Excel for Mac. 🙂 This just makes it easier later to track what I want to do to a keyword later when I am sorting by other things, like average position or total conversions.
  6. After you’ve worked out what you’re doing with your 0 converters, it’s time to start looking at those keywords which have converted for you, but whose performance can be optimized.  So just travel back to the Conversions filter, and make a custom filter that sets conversions not equal to 0.
  7. If I am trying to reduce CPL, a natural target is keywords whose CPL is too high. In general, this is how I begin: sort by cost per conversion. Add two columns next to “Max CPC”- “New Max CPC” and “% Difference”. If a keyword is well above the CPL goal for my account, I’m going to lower it more aggressively. As keyword costs get closer to my CPL goal, I’ll lower bids less aggressively. I fill in a “starting point” percent decrease in the % Difference column, and then calculate a New Max CPC based on that percent difference. The same applies in reverse for bid increases, which you can also perform within the same group of bid changes to balance the lead-decreasing potential of your bid decreases without increasing CPL, as long as you only increase bids on keywords with CPLs well under goal.
  8. Sorry, you’re not done! If only it were so easy. But there are other things to take into account- modifying the bid of a keyword with more spend and more conversions is going to impact your overall CPL more than modifying the bid of a keyword with lower spend and conversions, and not all keywords are in the same positions.  So you need to use the starting point you’ve developed with the percentage system and take a moment to consider the keywords individually. Sort by conversions, and make sure you make your most well thought out decisions about your high converters.  You should be conscientious about all of your bid changes, but it just makes sense to focus most of your energy where most of the impact will be. If increasing bids, remember that one conversion isn’t really enough data to go on when estimating that increasing a bid will get you more leads.  Then sort by position, and consider what your suggested bid change may do to the ad position. If it seems that it will drop it too extremely, you may want to be less aggressive. If a keyword whose bid you’re increasing is already in position 1.2, you can’t consider that increasing its bid 30% is really going to get you 30% more leads. Additionally, you should consider each keyword’s current average CPC. If a keyword is already spending near its maximum CPC on average, then a bid decrease is more likely to lower its position more substantially than a keyword that is not spending near its max CPC on average.
  9. After you’ve made all of the bid adjustments you need to make, set the filter on New Max CPC to “show nonblanks”.  You can get rid of most of the data now- you just need Campaign, Ad Group, Keyword, Keyword Type, and Max CPC to load your changes into AdWords Editor. So copy your New Max CPC values, paste “special” them into the Max CPC column as Values, copy the columns you need to make bulk keyword changes in Editor, and paste them in! Voila. Your bid changes, accomplished.

    It can sound complex at first, but starting with some ground rules applied across all keywords and then modifying based on individual factors for the highest impact keywords while keeping in mind the cautions listed above can seriously streamline the keyword bid management process while positively impacting your account performance. It’s a matter of practice and of actively monitoring the results you see to gain an understanding of the impact of your changes after you’ve made them. Like the rest of life, pay attention, know when to follow rules and when to break them and you’ll be just fine.