September 13, 2012
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:
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.
Using Excel’s filtering capabilities, this advertiser can then create tight ad groups by filtering for keyword qualifiers like “madras”.
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:
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.
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)
2. High CPA Campaigns (Conversions & CPA)
3. Low Volume Ad Groups (Clicks & Impressions)
4. Top Performing Ads (CTR & 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.
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!