CPA Optimised Bulk Keyword Bidding Tutorial

8 February, 2018

Last Updated 8th October 2018

Whether you’re working for an agency or in-house marketing department, you can bet efficiency of spend is a chief concern for you and your boss. This is especially true in the world of PPC, where direct investment is expected to yield immediate, measurable results. 

It’s this immediacy that makes PPC such an attractive and effective medium.

For many PPC-marketeers, the CPA (Cost Per Action) target is the key performance indicator, the holy grail. We live and die by our account efficiency.

What is manual CPA bidding?

The better your CPA is, the more profitable your account is to your business. This is why maintaining a consistently strong CPA for your account whilst scaling up volume of business is one of the principal headaches faced by PPC account managers.

Of course, smart keyword bidding based on performance is the first port of call here. Generally speaking, if a CPA is too high, we reduce bids. If the CPA is strong but we want to scale things up, we increase bids incrementally until we find the sweet spot.

But what if your account has 10,000+ keywords? It may only take a second to decide if your bid should be going up or down, but apply this manual process to a large-scale account and you might still be there next Christmas. And how do you know the right amount to increase or decrease by?

So, without further ado, here’s a little trick that will help you to apply large scale, calculated keyword bids to drive CPA efficiency, with just one formula.

Step 1: Download Your Keywords

Make sure you download all the keywords you wish to optimise, and make sure the following performance metrics are included:

  • Clicks
  • Goal Conversions
  • Cost
  • Cost Per Conversion (a.k.a. CPA)
  • Average Position

Important:  This method only works for keywords which have had at least one conversion. For best results, ensure the data you’re working with is statistically significant. Ideally you want around 5-10 conversions or 50-100 clicks, but this method can still work with smaller data sets.

The more data the better, so feel free to download a report for 30, 60 or even 90 days to gain as much detail as possible.

Step 2: Open and Prepare in Excel

For this example, we’ll be looking at some dummy data based on a purely made up premium laptop case manufacturer.

It should look something like this. There are plenty of sales here, and therefore plenty of data through which we can optimise our bids.

Step 3: Calculate New Max CPC

Insert a new column to the right of your existing Max CPC column, call this New Max CPC.

In your New Max CPC column, add the following formula:

=([Conversions]/[Clicks])*[Target CPA for account]= New Max CPC

Let’s suppose for this example our target CPA is £15. This means, using the data above, the formula would give the following results.

This formula calculates a new optimal max CPC based on current adherence of each keyword to your desired CPA target over the selected time period. Clever, huh?

As you can see, some have gone up and some have gone down in varying degrees. This is completely proportional to performance. You can paste this formula down for as many rows as you require too, even for thousands of keywords.

Step 4: Apply Bid Change Caps

Of course, we do not recommend you ever blindly trust a formula without reviewing and checking you’re happy with the resulting numbers and the potential impact this may have on performance.

Crucially, this formula does not cap recommended changes. In theory, if you have a keyword with a strong enough CPA, it may try to increase Max CPC by 50%, 100% 200% and beyond. Despite data suggesting such an increase would be appropriate, you do not know what effects such a drastic change would have. I recommend incremental changes.

To cap these changes, add a new column to the right of your New Max CPC column – call this column CHANGE and apply the following formula for each row:

=([New Max CPC]-[Old Max CPC)/[New Max CPC]

This will tell you how much the Max CPC has been altered as a percentage.

Suppose we wanted to ensure bids aren’t pushed too high at once; we could set a cap so that no bid would increase by more than… say… 20%.

To do this, simply place a filter on the CHANGE column, filter for values exceeding your desired caps and replace these with your desired overriding value. See below:

The two increases exceeding 20% have now been replaced with 20%. From here, you can apply the value of the CHANGE column to your New Max CPC column and override the formula. For a 20% cap, you would simply multiply the original Max CPC cell by 1.2.

This same method can be inverted and applied to prevent Max CPCs being reduced too drastically, which may be detrimental to overall traffic volume.

Other Things to Remember

  • If your keyword has zero conversions, the formula will suggest a -100% bid change, thereby turning it off. Remember to omit these from the process.
  • Check average positions. If your Average position is already 1.1, there will be little to gain from increasing bids beyond an inflated CPC.
  • Always SAVE a ‘before’ and ‘after’ version of your calculated bids, just in case you wish to revert back to initial bids.

Once you’re satisfied with your new calculated Max CPC bids, you’re ready to upload. You can do this through Bulk Uploads in the AdWords interface, or using Google AdWords Editor.

Thanks for reading. I hope this has been helpful. If you have a different way of manually optimising keyword bids en masse, please share!

Related articles

Written by Tom

Tom cites his key strengths as communication, writing and presenting. He enjoys working in digital due to the way it satisfies both the creative and logical sides of the brain.

Up next…
The Big Facebook Change – How Can You Still Reach Your Audience?
29 January, 2018


Leave a comment

Replying to: - Cancel