Extreme Excel Awesomeness: PPC Bid Management Tool

As marketers, we are constantly inundated with data and analytics that are supposed to help us better perform our jobs. These reports, so we’re told, are designed to help us draw better insights from our campaigns and develop actionable strategies based on our performance metrics. The reports are supposed to make us more efficient, more tactical and more knowledgeable of our industry.

The problem with much of the information we receive from these data dumps is that it is often overwhelming, unwieldy or simply too difficult to understand. We’re forced to perform countless repetitive tasks just to get to a point where we understand what the data represents – before we can even attempt to draw any legitimate insights.

Excel, at its core, is a problem-solving tool. When used correctly, Excel can eliminate many of these tedious and repetitive steps from our analytic process and allow us to concentrate our efforts on what truly matters: drawing useful insights that affect strategy moving forward. This week I had the pleasure of speaking on the Extreme Excel Excellence panel at SMX Advanced in Seattle, with the overarching theme of embracing the opportunity that Excel presents for marketers to better analyze their data.

To illustrate this potential, I’ve developed a Bid Management Tool in Excel to help search engine marketers better evaluate their performance reports and make smarter decisions when optimizing campaigns. The Bid Management Tool is available for download here, and the following outlines its creation, from concept to completion.

 Identify the Problem

 

 Avinash Kaushik is credited with coining the 90/10 Rule for Magnificent Web Analytics Success, which essentially states that 10% of your resources should be allocated to tools, but the other 90% should be dedicated to the people who will wield these tools. Our people, not our tools, interpret and act upon data – and the tools only serve to help them accomplish this goal.

Our problem, one I’m sure we share with many in the marketing world, is that at Nebo we were not able to dedicate 90% of our time and resources to the human element of analysis. Our PPC team pours through thousands and thousands of rows of data to evaluate opportunity and optimize toward our campaign goals. They needed a more efficient way to prioritize their efforts, specifically in terms of managing keyword bids. To be clear, this tool was not intended to replace the human element but rather to empower it. Inspired by a clear understanding of the problem, we sought to eliminate the more monotonous tasks, thereby restoring time and resources to our most valuable assets: our people.

Determine the Criteria

 

Before we approach any problem, we need to determine the criteria most directly related to that problem. In the case of our Bid Management Tool, the most important factors were impressions, conversions and cost/conversion. These elements are highly indicative of campaign performance and influence what terms we bid on and how much we’re willing to spend. By understanding how these criteria are used to evaluate overall campaign performance, we were able to streamline our resources to identify and alter existing bids based on our individual thresholds.

Find the Key

 

Once our problem and criteria had been identified, it was time to put Excel to work. We first had to ascertain which formula or formulas would allow us to manipulate our criteria according to our needs. From there, we established the appropriate combination of nested formulas: multiple functions embedded within one another that allow us to execute multiple actions within the same cell. These nested functions would allow us to automate many of the protracted, repetitive tasks and translate our actual data into actionable data.

Build the Solution

 

Our Bid Management Tool was built using primarily IF(), AND() and OR() functions (for some background on these functions, check out Distilled’s Excel for SEO resource here). These functions operate on user-defined thresholds for our criteria, working together to classify our keywords based on whether or not these thresholds have been met.

 

For those keywords that meet the established criteria, the Bid Management Tool will furthermore increase or decrease our existing max CPC bids by the percentage indicated.

  

Every time they are run, these Excel functions transform our actual data into actionable data. As I mentioned above, these tools will never replace human analysis; but in a world with finite time and resources, these tools save countless hours that can be reallocated to the human endeavor.

  

Once again, the Bid Management Tool is available for download here. (Note: You may need to adjust some columns in the spreadsheet, as AdWords columns are likely different across different accounts.)

Feel free to share any questions or comments you might have!

Written by Brett Snyder on June 12, 2013

Comments

Add A Comment

Hi Brett,

Looks like a "oldie but goodie" blog post. Thanks for your clear and simple tool, really appriciated! I would like to use it for my campaigns but want to make sure I use at its best use. Do you have a sample of one of your campaign?

#HappyFriday

Jess

Brian says:

Hi Brett. Does this tool still work?

Brett Snyder says:

@Rick I'm a huge proponent of human campaign management actually. The reason I created this tool was to give our team MORE time for the human element. The tool helps streamline the process so that we know where to prioritize our efforts for campaign optimization. Thanks for reading!

Rick says:

I like your approach to making bid management a more efficient process. Thanks for sharing (especially for free)! Using it would definitely empower a campaign manager to more efficiently adjust bid levels. What are your general thoughts on human vs computer bid management? Thx again

Brett Snyder says:

@Andy Right now this is just formatted for AdWords and only for Impressions, Conversions, and Cost/Conversion but I have considered fleshing it out to more metrics/other engines (I talked through the principles of building these types of tools in my presentation, slideshare link here if you're interested: http://www.slideshare.net/BrettASnyder/smx-advanced-extreme-excel-excellence-brett-snyder-v-f-6613).

The big difference for me between this and the AdWords tool is I wanted this to be something that helps streamline your analysis rather than replace it. The idea is that you should be able to go in there and quickly identify what KWs need your attention and what the updated bids would look like...but then use your own qualitative analysis to determine if this is the most appropriate optimization change to make to the campaign. I'm a big believer in the 90/10 rule so I built this to help empower that 90% and give time back to folks who can use that time to apply their knowledge of the nuances to improve the overall campaign.

@Oleg Thanks!

oleg says:

As someone who is intimately involved with bid automation algorithms (and with excel being the ultimate tool of search marketing), I find both, your SMX presentation and Excel-based Bid Management Tool fascinating. Thank you for sharing!

Andy says:

Interesting tool, thank you for building and sharing. Does it work with multiple engines, or just with AdWords? And if just with AdWords, are there any key differences/benefits vs. the max CPC bidding tool that AdWords provides currently? (AdWords help doc link for reference https://support.google.com/adwords/answer/2472779?hl=en&ref_topic=1713955)

Written by
Brett Snyder