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!