Mounds of data, MS Excel and large coffee; these are three things that I’ve come to know and love as a PPC manager. In fact, these things go hand-in-hand with each other (some would argue about the coffee) because Excel provides us with ways to manipulate and analyze all of the data we’re confronted with on a daily basis.

Perhaps this is why we’ve decided to pursue the topic of Excel yet again as our series for this month, covering some of the various ways it can help you optimize your accounts. For the purpose of this installment, we will focus on three ways to leverage Excel filters that can help us all save time.

Structuring Large Keyword Lists

One of the ways PPC managers can leverage Excel filters is for segmenting and structuring your accounts. This has come in handy for several of my clients by helping me structure and define tight ad groups from large unorganized keyword lists. Let’s take a look at an example:

Structuring Large Keyword Lists

Here we have an online clothing retailer who has developed a list of shirt-related keywords that need structuring. As you can see, there are several different types of keyword qualifiers, such as fabrics and gender.

Top Keyword Qualifiers

Using Excel’s filtering capabilities, this advertiser can then create tight ad groups by filtering for keyword qualifiers like “madras”.

Tight Ad Group Structure

By filtering out all the qualifiers, the advertiser doesn’t have to sift through each keyword one-by-one and is left with several tightly themed ad groups. Ideally, this would be a much longer list since that’s when the real time-saving benefits occur, but the example above demonstrates how Excel filters can help segment your keyword lists.

Eliminating Costly Search Query Themes

Excel filters can also be helpful for locating search query themes. This is similar to the keyword segmentation process in the section above, but now we’re concerned with negative keywords. I’ve found this to be particularly useful for clients that are new to AdWords with little-to-no negative keywords in their accounts. Let’s take a look at another example:

Finding Negative Keywords Using Filters

Going back to our online clothing retailer from the previous section, here we have a search query report (SQR) that shows us what users typed in order to trigger our keywords.

Finding Themes in Search Queries

After seeing “blog” several times, I could then set a filter for queries that contain “blog” in order to determine if this is a recurring theme. You can see that blog-related queries resulted in $17 spent without any conversions.

As you might imagine, online clothing retailers probably aren’t interested in serving ads to users looking for clothing blogs, so it would be smart of us to add “blog” to our list of negative keywords. Thanks to Excel’s handy filtering features, we can easily identify recurring themes in our SQRs rather than sifting through the queries one-by-one.

Analyzing Account Performance & KPI’s

Excel filters can also be useful when analyzing account performance and KPI’s. Oftentimes, we tend to make decisions and optimize based on multiple metrics/criteria. For example, pausing keywords that have spent a lot of money without converting, where we are concerned with both spend and conversions. Here are some examples:

1.      Non-Converting Keywords (Cost & Conversions)

Filtering by Cost and Conversion

2.     High CPA Campaigns (Conversions & CPA)

Filtering by Conversion and Cost per Conversion
3.     Low Volume Ad Groups (Clicks & Impressions)

Filtering by Clicks and Impressions
4.     Top Performing Ads (CTR & Impressions)

Filtering by CTR and Impressions

Of course this list isn’t exhaustive and your filters may look different based on your goals and KPI’s. In this way, however, Excel filters can also be a great way to locate and analyze performance based on multiple metrics. Although the AdWords interface has integrated filtering abilities as well, many account managers prefer to work in Excel instead. Regardless of where you decide to work, both allow for the filtering of multiple criteria.

Closing Thoughts

Excel Filters can be beneficial for any PPC account manager and help save you time working with all the data in the long run. Whether it’s structuring lists, identifying themes or analyzing performance, hopefully you’ve got a few ideas for ways in which to utilize them. Having said that, what are some other ways you have leveraged Excel Filters in your own accounts? Be sure to check back in throughout the rest of the week as our Excel series continues. As always, thanks for reading!