Benjamin Estes's Posts

There was a lot of hubbub yesterday about a dramatic increase in (not provided) traffic showing up in Analytics data. I’d say the most eye-opening part of this is the realization that the end of this upward trend is in sight: soon there will be effectively no keyword data readily available for organic search traffic from Google.

While Distilled hasn’t seen as dramatic an increase across our own accounts it has always been a matter of time. This was not unforeseen. Since Google announced the encryption of searches in October 2011 we’ve been considering this possibly. Lately it’s been seen as inevitable. So naturally Distilled has been thinking about and talking about this subject quite a bit.

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.

Sometimes we at Distilled go a little far out with our posts about statistics and data and all that, but not all of that is applicable to everyday work. Not all tasks are so complicated—some are fairly cut and dry, like figuring out which landing pages have received the most traffic over the last year. That’s straightforward, right? At some level this is clerical work—but that’s all the more reason to get it over with as quickly as possible.

Often we can slow ourselves down by organizing data in inefficient ways. These methods might seem smart or fast at the time, and yet they can often make it more difficult to get what we really need out of our data set. Maybe you are trying to figure out which page received the most traffic over the last year—and this is what your Excel file looks like:

Working according to a process means having real freedom to be creative and productive. What an odd statement, that a constraint on our actions would give us flexibility!

But think about it—in considering SEO or marketing, do we act with disregard or disdain for others? Do we allow ourselves to do whatever we like? No, we consider the needs of our customer or client or audience. We constrain our actions in ways that ensure we act in consonance with those needs. And we are free to act creatively within that context.

I’d like to take some time to walk you through my own experience with process at Distilled. This isn’t a philosophical discussion, it’s the real deal—the concept of the process is at the heart of effecting change or the ship it! mentality or any number of characteristics that make Distilled the way we are.

Just before last Christmas Google posted a method to download search queries from Webmaster Central to a CSV file using Python. Downloading a CSV files can of course be done from within Webmaster Central when you’re logged in, but by using Python and the Windows Task Scheduler it can be easily automated, which is very useful as Webmaster Central Data only goes back one month. With automation it is possible to archive this data and see trends over longer periods of time.

The original post does a reasonable job of describing the process but I’m going to try to go into more detail for those that aren’t as familiar with Python and all that jazz. And unlike the original post I’ll delve into how to use the Windows Task Scheduler for automation. I’m going to assume that you as a user know how to access the windows command line, but that’s about all that I’ll assume.