This is something of a spiritual successor to my previous post, Use One Huge Table In Excel. In that post I talked about the fact that to take advantage of the relationships between all of the elements you have in your spreadsheet, you really need to get them all in the same place. In this post I’d like to help you keep moving in the right direction. We’ll focus on segmenting and assessing data in ways that you may not have considered before.
To “keep moving in the right direction”, you need to install SeoTools for Excel. I’m not even going to qualify that admonition because once you do, you’ll see what I mean. Instead of trying to convince you here to install it, I will illustrate a way to use it which has been very powerful for me and hopefully helpful for you as well.
So lets talk about how SeoTools allows you more flexibility in filtering and pivoting your data.
SeoTools allows you to use Regular Expressions in Excel. If you know what Regex is, you probably downloaded SeoTools immediately after reading that statement. If not, hold on to your butts. It can be a little intimidating at first, but I promise you’ll get the hang of it with a little concerted effort.
The idea behind Regex is to match strings against a certain pattern. Say I have a list of keywords referring organic search traffic to my site:
If I want to understand whether the keyword in the first column is branded (for Distilled that would be the name of our company and of our conferences) I’d have a bit of a hard time. “Distilled” isn’t so hard to comprehend, but what about combining that with “searchlove”? What about alternative spellings, like “search love”? Excel can be painfully limited in some ways.
But with SeoTools we can use regular expressions. It’s way beyond the scope of this article to explain regular expressions, but suffice it to say if we take the reguler expression “distilled|search\s*love” an algorithm will match all of our keywords that contain “distilled” or “searchlove” or “search love”.
So with SeoTools installed, the formula that provides the “true/false” in the Branded column is:
Now we’ve successfully stored this “meta-data” about the keywords in the Branded column, and we can use it to manipulate them much more easily.
Filtering data in a table is—to my mind at least—the task which can be most directly improved through the application of Regex. And again, this is facilitated by having all of your data in One Huge Table.
Say we want to filter our keywords based on whether they are branded or non-branded. Excel has very limited ability to filter based on strings—at best you can explicitly include/exclude two terms in your filter. So the “search love” vs “searchlove” scenario described above would be stretching the current system to it’s limits, and adding “distilled” wouldn’t be easily doable.
But since we’ve used regular expressions to define whether each keyword in our list is branded, all we have to do is filter the Branded column and select either “false” or “true” depending on what we are looking for:
Hopefully that illustrates the simplicity of this scenario over the alternative of trying to define a filter with Excel’s built-in system.
The whole basis of pivot tables is that you are summarizing data in a way that relates to one variable. That’s a bit long winded so consider the table we dealt with above. It shows a column of keywords and indicates whether each of them is branded.
But if we wanted to reverse that relationship and see how much branded or non-branded traffic the site is receiving? That’s just a pivot table away now that those “true/false” Branded values have their own column in our table.
Our results might look something like this:
I’m not going to belabor this topic—if you are familiar with pivot tables then the power of combining them with regular expressions will be evident. If you’re new to Excel check out a tutorial on pivot tables or our Excel for SEO guide.
SeoTools for Excel has been something of a hot topic amongst Distillers lately. We’ve previously covered SeoTools on the Distilled blog. SeoTools is a plugin for Excel that adds a lot of useful functionality. Generally it’s the SEO-centric additions that get the most attention—things like pulling on-page elements, importing Google Analytics data (!), or social metrics for your site.
But to me the truly interesting things about SeoTools are those that allow you to manipulate data in ways which would be otherwise difficult or impossible. Do any of you have great ideas for how to apply this to your work?
Benjamin Estes SEO Consultant