Home | Excel for SEO

Archives For Excel for SEO

Excel is one of the sharpest knives in the SEO’s drawer, or at least it should be. I’ve developed these Excel templates to help you expedite your work. Everything from visualizing backlink profiles to segmenting link prospects by domain authority, you’ll find it here. Let me know what you want, and I’ll try to make it!

Tonight (March 20th) Distilled NYC is co-hosting a meetup in conjunction with iAcquire, another search agency here in New York City. The topic is Content Marketing vs Content Strategy.

My talk is on data driven content marketing. We believe that in order to know what content to create, you need to first know:

  • What content you have;
  • How that content is performing
  • What content your competitors have
  • How their content is performing

I will post the slidedeck this evening once the event has finished and I get back to my computer, but I built out a spreadsheet to give away to everyone. I figure I’ll give it away here and explain how to use both for the meetup goers as well as for all of you who read this post on its own.

Download the spreadsheet here.

Data-Driven Content Auditing

The first step before you do any work is to figure out what your goals are from the campaign. Why are you creating content and how are you going to get buy-in, and therefore budget, to create it once you have figured out what you need? Lucky for you I presented on this at SearchFest in February -

Your KPIs (Key Performance Indicators) could be many things, including:

  • Links
  • Traffic
  • Leads
  • ??

Settling on what you will be measured on first is the key to a successful campaign, or future successful campaigns as you learn and do better campaigns each time.

Pull Data

Once you have your goals in mind, you know what kind of data you need to gather. I always recommend gathering:

  • URLs
  • Content type or category (ie “Infographic” or “Marketing”). This can give insight into the kinds of content they create that you do not, and if it works for them.
  • SEOmoz Metrics for the site/page (Domain Authority, Page Authority, possibly Trust)
  • Number of linking root domains
  • Social Metrics (Twitter, FB, Google+, etc)
  • Traffic (for your own content) from Analytics

This data will be gathered from a multitude of places, including but not limited to:

Graph and Action

To keep all the data in one place, I’ve provided a spreadsheet here that you can download and use to audit up to 3 competitors and their types of content. Also, please customize it as you need (as it is impossible to meet everyone’s needs) and share with the class what you have done if you think it will be useful.

Here is a preview of the sheet:

excel-sheet

 

You’ll be given some charts as well to help you see visually what is working as well:

category-or-type-graph


You can download the spreadsheet here.

Here is my presentation from the meetup:

Back in April at Linklove, I presented on Tools of the SEO Trade. In that talk, I talked about somewhere in the neighborhood of 60 different tools available for use. I built out a couple for people to use as well (for free), and today I want to talk about one of them.

Introducing the Quora RSS Scraper Importer

One of the tools I built out, which is actually a tool that I built back in January or February for a client, is a Google Spreadsheet that imports the most recent Quora posts based on a Quora category. I’ll explain this further down, but if you’re impatient you can:

Download and Copy the Spreadsheet

Continue Reading…

Linkbuilding is always a hot topic within SEO, and different schools of thought exist. There’s the Throw Away Your Form Letters approach, and then there’s the school of form letters are great, just make them personalized. I did a lot of linkbuilding at my old job, and am doing some now for clients, and I prefer to take a more nuanced approach.

Different targets require different approaches. Let’s break the types of link prospects into three groups:

  • High level – these are the most important links. High quality sites.
  • Mid-level – these are valuable sites, but maybe not as hard to get.
  • Low-level – when you need mass.

Let’s examine the different approaches required for each.
Continue Reading…

While I’ve posted a few times about SEO Excel spreadsheets, and I’ve used link profiles a couple of times in blog posts, I’ve never been able to show what a backlink profile full of backlinks looks like.

Back to the beginning

A healthy backlink profile is one that has links of all types. It is usually nice bell curve, with medium amounts of low DA and PA backlinks and fewer super high quality backlinks (which makes sense), with a nice number in the middle as well. This makes sense, as super high authority sites are much less common that lower authority websites. You should expect links of all kinds.

One way that I think search engines could easily spot if a website is buying links is by running the site’s links through a process that then visualizes the distribution of Domain and Page Authority across those backlinks.

A Healthy-ish Profile

Let’s look at a few examples of strong and healthy backlink profiles. What we’re looking for is that nice bell curve that I talk about above.

Here is Distilled’s backlink profile:

A nice balanced bell curve is what we see

As you can see, that nice bell curve has taken shape. There are low authority links and high authority links. There’s nothing out of the ordinary here.

Here is SEO.com‘s backlink profile:

 

Also a nice balanced bell curve of authority.

Once again, we get that nice bell curve. SEO.com’s link profile looks pretty healthy to me!

*Remember*, this is not the whole story behind any backlink profile, but it is a good quick way to visualize if you need to dig deeper.

An Unhealthy Profile via Paid Links

This past week I was working on a site that has been buying links for years (not as a Distilled client). They have seen the error of their ways and are a brand new client of mine and will not be named. Part of my work with them is to help clean up their backlink profile as much as possible.

I used the Domain Authority spreadsheet that I have written about previously, and boy was I surprised at what I saw.

Here is your example of what a bad backlink profile looks like:

This is no bell curve. This is a sledding hill.

Remember that bad paid links often happen in bulk and are from low authority and low quality link networks or directories. They are built quickly and with reckless abandon. Since the easiest links to get are usually the low authority links, a site with a lot of paid links will have a lot of links from a lot of low authority sites.

How This is Valuable

These graphs can be valuable because they are actionable. Often lower authority sites are easier to get links from, so in Distilled’s case we may want to try to get links with targeted anchor text (as it is often easier to get this on lower authority sites than high authority).

Also, if we are able to visualize a site’s backlinks this easily, how easy would it be for a search engine to do it as well? I bet they do too. Depending on the industry and their tolerance for spam in that industry (and most SEOs agree that Google has different spam tolerances for different industries), I wouldn’t be surprised if they used a methodology like this as a signal in determining spam.

Finally, this methodology can be used to help qualify potential clients. Knowing what they have done before they you sign a contract, by doing your homework on them, can save you a lot of headaches in the long term.

I’d love to hear your thoughts.

Ladies and gentlemen, another awesome Excel spreadsheet for you to use. Today I have taken the SEOmoz SERP Analysis Tool export and modified it to spin you out a bunch of graphs that will show you where your site might be weak against your competitors. Huge!

Before further ado, you can download the sheet here. If you don’t want to read the background, skip to here.

A bit of background

When SEOmoz announced a few months ago that they were going to turn the SERP Analysis tool that was built by BusinessHut into a dynamic tool in the SEOmoz toolbox, you could say that I was stoked. Nay, I was overjoyed.

I love the tool, I really do. It may be a bit hidden in the toolbox, but I think that it is a thing of beauty and helps out a lot of people.

To me though, I was a little bit disappointed when I saw the tool. Yes, I love that you can plug in a site you want to compare the search results to, which is mega helpful. And the tool looks pretty. And yes, you can export it to CSV.

The feature I loved about the old tool, though, was how it would spin you out a bunch of graphs that would show you, visually, where your site stood against the competition, and it helped you understand how and why different sites were ranking for the term that you plugged in and where your site might be weak. Then you could take action and rank your site for awesome.

The SERP Analysis tool doesn’t have these graphs, though. Bummer, huh? I missed graphs like these:

Isn't that pretty? And helpful??

Choices

As far as I see it now, you have two choices:

1) Download the old spreadsheet and get all of the data manually, like I used to.

OR

2) Use this awesome spreadsheet that I’ve put together for you!

Making the SERP Analysis Tool even more awesome!

Today I decided that I was going to turn the SERP Analysis tool into a tool where you can plug in the full export that you just ran, using some of your monthly credits (I think you get 50?)

Here are the steps you should go through:

1. Download the Template for Moz SERP Analysis Awesome.

2. Run the SERP Analysis Tool for your keyword in question.

3. Run the full report using the button highlighted below:

 

4. Export to CSV.

Now, once you have exported to CSV, all you need to do is highlight ALL of the data that you have been given in your CSV and plug it into my Template for Moz SERP Analysis Awesome.

What will I see?

Now we get to the fun part! Now that you have inserted all of your data into the spreadsheet, you can click through the tabs and see images like these:

mozTrust compared to mozRank. How in line with your competitors is yours?

How well are your URLs and domain optimized for the term?

How about your domain mozRank? How does that compare?

You get your on-page optimization score as well. Bonus!

Yes, your social stats are there too.

Did you break them down by category/type?

You bet! Working from inspiration from the original document, I applied some conditional formatting to the sheet so that you can see when your onpage optimization is bad, like below:

Green is good, red is bad. Easy enough?

The different categories that you will receive graphs for are:

  • Ranking Page Link Metrics
  • Onpage Optimization
  • Domain Metrics
  • Social Metrics

Now what?

I’m an extremely visual person, so I find these graphs to be helpful to get an overall view of my site. For example, from running this sheet for my website and a term that I have been trying to rank it for, I see that my site is WAY behind in links, yet I’m still ranking pretty well for the term. If I needed to, and saw that my site is so far behind in domain metrics and links, I could try to outwork my competition by using videos, photos, and other types of media to get into the SERP and draw attention to my site.

Hmmmm….I might just do that.

Until later…happy optimizing!

And if you still haven’t gotten the spreadsheet, get it here.

As always, I’d love your feedback on how I could make this even cooler!

Enhanced by Zemanta