When you're trying to rank for a particular term or piecing together a competitor landscape, you might be looking at tens of thousands of backlinks when you want to understand only a small slice of the data. I don't know about you, but I weep a bit when I see tens of thousands of backlinks.
This is where pivot tables in Excel can come in handy. They are great at consolidating and slicing data into small, manageable chucks. When I say pivot tables and Excel, some people might begin to sweat and get tunnel vision, but fear not, it's worth learning -- especially if you can bring in more revenue for your client. I'm going to show you how to extract important, digestible chunks of data using pivot tables so you can better understand your competitor linking landscape and have solid PROOF that junk linking (e.g. directories, forums, blog comments, etc) still works!
Pulling Data
Let's begin with the data. You can either pull all of your client's competitor backlinks or focus on a given keyword. I like to focus on a keyword I'm trying to rank for because it's much more manageable and actionable to know exactly what it takes to rank in the top 3-5 positions.
Let's say I work for Javamo (I have no idea who these guys are, I just picked them randomly) and I want to rank in the top three spots for "online coffee beans."
We would have to jump over the following competitors to rank in the top position:
- www.coffeebeandirect.com/
- www.coffeereview.com/
- www.damicofoods.com/CoffeeBeansOnline.html
- www.sweetmarias.com/
- www.starbucksstore.com/
Now, let's pull their backlinks and the backlinks for Javamo so we can see where we need to get links. I use Open Site Explorer to download external backlinks to the root domain. If your client or competitors have millions of links you might want to consider using Majestic.
Note: I would recommend doing this on a PC or the most recent version of Excel for Mac. Older versions of Excel on Mac will frustrate you beyond belief because its not really made for using pivot tables and eliminating duplicates -- which is exactly what were doing here.
Stacking Data in Excel
Copy and paste the backlinks into column B in a new Excel workbook. Stack the data on top of each other. So we have www.coffeebeandirect.coms backlinks above www.damicofoods.coms backlinks, etc.
Cleaning Dirty Data
We now have a giant list of thousands of backlinks -- 18,283 to be precise. We want to find the highest quality page and domain for each competitor and eliminate duplicates. If we were to leave all linking pages on a domain, sitewide links would inflate our numbers.
You can do this by selecting column B through K, adding a filter (control + shift + L), then first sort page authority largest to smallest, then domain authority by largest to smallest. Remove the filter (control + shift + L).
Copy and paste column B into column A and column K into column L. Label column A "Clean URL" and column L "Clean Target URL." Hold control and click on columns A and L, then separately find and replace http://, then www., /* with nothing. Make sure you replace the http:// with nothing before /* because you'll erase your entire URL!
Eliminating Duplicates
This will give you clean URLs, and we can use the eliminate duplicate function to have the highest quality page on the highest quality domains. Eliminate the duplicates in the Clean URL and Clean Target URL column by selecting columns A through L and clicking the "Remove Duplicates" button. Unselect all columns but "Clean URL" and "Clean Target URL." Hit OK. Now you have the highest quality pages to the competitor root domains.
After you do this once and have it figured out, you might want to do it again and record it as a macro. For shortcut junkies, it will shave off 2-3 minutes and for non-shortcut junkies, 3-5 minutes. Definitely worth it if you're going to use this tool a lot.
Creating Pivot Tables
We can now slice and dice this data to find linking opportunities.
1) Choose Your Data -- Select columns A through L of your data by clicking on the column head (e.g. A, B, C, D, etc).
2) Create Your Pivot Table -- Click on the pivot table button and select OK.
3) Organizing Your Fields -- This should bring up a new sheet with the pivot table. Now we need to begin figuring out what we want to look at. To start, I want to figure out what types of anchor text my competitors are optimizing for. This can tell you whether being really aggressive with anchor text works. And it can also give you ideas for other keywords you might want to target.
In the "Pivot Table Field List," move "Anchor Text" to the "Row Label" and "Values" fields below. It should look like this:
Now you have to sort the count of anchor text to see what competitors are optimizing for. Decrease the size of your screen to 40% by clicking the minus button in the lower right hand corner of your screen. Drag the line between column A and B to the left to decrease the column width. Now increase the size of your sheet to 100%.
Right click one of the numbers under the "Count of Anchor Text" header. Hover over "Sort" and click "Largest to Smallest." You will have to decrease the size of your column again. See above.
In this particular space, the competitors are not being aggressive with exact match anchor text. However, in more "spammy" industries, such as bank loans, travel, etc, you might find competitors really optimizing for a keyword.
5) Analyzing Your Data for Link Strategies -- Now double click on one of the numbers under "Count of Anchor Text." This will blow out all of the data for that particular anchor text in a new sheet. Throw those URLs into Ontolos URL Reviewer and you can breeze through a ton of sites to find opportunities.
Just from looking at the images from Coffee Review, I know that they are rating coffee and posting reviews on roaster websites. If I were Javamo, I would consider creating an infographic that describes my beans (maybe include a darkness rating, acidic rating, ect), then reach out to cafe's that sell my beans to add the infographic.
I'm also noticing how Sweet Maria's has links from 7 domains with the anchor text "home coffee roasting." Not surprisingly, Sweet Maria's is ranking #1 for "home coffee roasting" with sitewide and one-off links from 7 sites with similar designs and 3 unique IPs -- maybe a small link network? Does that mean you buy some domains and create a link network of your own? Up to you.
In less than 5 minutes, we looked at one small slice of data to come up with a better idea of how to rank and even inspiration to rank for other terms (e.g. if Javamo could even get some low-quality, exact match anchor text links, they might be able to knock out Sweet Maria's #1 position for a keyword with 590 exact match monthly Google visits).
Other Ways to Slice Backlink Data
Looking at the anchor text is only one of many metrics we can analyze with our pivot tables. We can look at the domain and link types, where everyone is getting a link and we are not (this is great for startups that just launched their site / brand because you can get links from company profile pages).
We can extract the domain type (e.g. .edu, .gov., etc) and the type of link (e.g. blog post, forum, directory). I can see that Sweet Maria's and Coffee Review have a combined 23 .edus, so I would want to see how I could get on those pages or figure out a strategy to get on other .edus. You can use IF, FIND and IFERROR functions to extract this information from the linking URL. I'm not going to go in depth about how to do this, but you can review Distilled's Excel for SEOs blog post.
When extracting the type of links, it will not be exact. For instance, if "blog" appears in their URL, we assume it's a blog post. However, the URL could be www.seerintactive.com/blog-contributors. So it's not a blog post, but we are counting it as a blog post.
If we look at the competitor link types, we can see that Sweet Maria's has links in 58 forums, and over 200 blogs. So for the next month, we might want to test how forum links impacts our rankings.
Or we can get a look at the domains that link to our competitors that do not link to us. There are at least a hundred domains that link to competitors and not us, so we need to ask why. This can help us build strategies and get links.
Using This Tool To Justify Your SEO Strategies
Using this data-driven approach, you can form strategies around what is happening in the SERPs and be much more transparent with your clients. So if the top three competitors are really hitting it hard with directory links, you can tell your client that for the next month you are going to spend all of your time getting directory links. To take it a step further, you can pull revenue, transactions and goals to show them what it would mean to their bottom line to rank in the top three spots for that keyword. This certainly helps if you don't have a linking budget. Tying your strategy to the clients business with data can add value and validity to your SEO work.
It's important to note here that looking at backlinks is only part of the picture. You also have to consider domain authority, title tags, h1s and on-page content.
I hope you get a chance to sit down and play with this tool. Here is the "online coffee beans" example from above: https://seerinteractive.box.com/s/59v8kxhyfy0mjdda5c5b. Starting from scratch for an Excel nubie should take about an hour and for Excel wizards, maybe 30 minutes. Much of your time will be spent pulling data from Open Site Explorer.
If you do get a chance to play with this tool, please share stories about how this helped you and any other ways of slicing data not mentioned above.
