If you want to see how your competitors are gaining a strategic advantage, one of the best tactics to overtake them is to take a deep dive into their backlinks. They leave breadcrumbs behind that reveal their best tactics. Then pivot (no pun intended), glean ideas from their brilliance, and do it even better!

Required skill: pivot tables

If you don't know how to use pivot tables, you need to check out this video walkthrough. I teach you everything you need to know and then some.

Download example pivot table

I redacted my client's data from the pivot table in the Excel sheet, but you can get an idea of how I pulled together the data in the "Raw Data" tab and then see how I organized my pivot table in that tab. You can, of course, organize yours however you feel is best. But hopefully this will provide a good jumping-off point. I also sorted my pivot table by domain authority in descending order and then filtered out links from [free-subdomain].wordpress.com.

Step 2: Add another column and label it "Site." What I typically do is add the domain (without the http:// or www to minimize noise) and double-click the bottom-right corner of the cell to fill down to the bottom of the data set. Rinse and repeat each time you add a new batch of backlinks. When you finish, you'll have a single table that contains a mashup of backlinks.

Step 5: As a general rule, I don't like how Excel merely indents rows in the default, "Compact" pivot table format. In our data set here, where we have three different values pulled down in rows (Domain, Site, and URL). This can cause your rows to get really congested, and it can be hard to differentiate them. For this reason, if I pull multiple values into the Rows field list, I prefer the Outline layout. You can check it out in the Excel file download.

Step 6: I pulled individual observations into text boxes to the left of the pivot table. I used text boxes for a couple reasons:

I didn't want to mess up the heights of rows inside my pivot table.

I could attach links to the text boxes that linked to the individual cells in the pivot table.

Step 7: Dive into the data and start reverse-engineering strategies (using the observations I pulled out as a template, if you'd like). I can assure you there are many more to excavate from that data set!

Video tutorial

For you visual learners out there, I pulled together a 10-minute video walkthrough of some of the key steps I took to organize the data for analysis.

About Annie Cushing —
Because I love all things data visualization and analytics and my name is Annie, someone once dubbed me Annielytics. And it kinda stuck. My goal for my blog is to provide marketers with data visualization strategies in bite-sized posts that can be digested in less than five minutes each.

Get fresh SEO data, insights, and tracking

Comments
125

Thanks Annie for such help. It's always great to know what your competitors are into and you can develop your strategy accordingly.

Competitor's backlinks not only allows you to find where you can get backlinks from, but also where you should not get from. We had a client few years back for whom we did this activity, and found that their competitors have targeted mainly online web directories and article directories (around 80% of their total backlink portfolio) including some bookmarking websites. Their rankings were quite good at that time, but we knew this would become a nightmare for them in future. And then, Penguin hit and they were no where.

So, it teaches us not to blindly follow what your competitor is doing. :-)

Tip: if the only text boxes (or objects of any kind) that exist are those that you are trying to align, once you click on one (note: not simply inside to edit but outline to move), then all you have to do is click {ctrl-a} to select all. At that point it will only select the objects, not any of the cell text.

Yeahz, I discovered that years ago from an exported ranking report, I don't even remember which tool, that included a bunch of icons. It was meant to be a complete report, but I wanted to work with the data and of course once you started sorting and filtering the data, the icons were either no-longer accurate or a complete mess.

When you have to hand select 100+ tiny icons, you start trying LOTS of things... and here we are!

Annie - Excellent post. I'm always a fan of practical step-by-step tutorials, so the video and Excel download are a great bonus.

A tip for those who can't afford the Moz Pro membership: You can use a free community level Moz account to download data from Open Site Explorer. You miss out on some of the Pro details (like social stats), but you still get all the raw data that Annie used in this tutorial.

First, I just wanted to say that I love this post. It lead me to the other videos (the pivot table creation video and the domain name extraction video) and I'm super excited about putting all of this together. So, thank you!

Quick question, though. When I have a domain linking to a competitor from multiple URLs, my pivot table is adding those DAs and PAs, so that in each multiple linking URL case, the DA and PA in the 'site' row is showing a sum total of the DA and PA of all of the URLs that that particular domain links from. I noticed that doesn't happen in your example spreadsheet. Is there a step that I'm missing to stop the DA and PA columns from summing the numbers?

It's a little difficult to describe that clearly. Hopefully that makes sense! :)

But how we can determine that competitors backlinks are natural or effective for our site also? secondly, if someone have lot of backlinks and how we use them? like Google or other giant sites. Last not least, you share the stunning article and of course excel sheet.

That's why I sort my table by Domain Authority. I find that I spend so much time with the high authority links I rarely get to the bottom of the list where all the rubble is. There are times that you can't go after a particular search phrase b/c it's too competitive. I recently had a client ask what on-site and metadata changes I'd recommend they make to better compete for terms they weren't showing up on the first five pages of Google for. I told her absolutely none b/c they were way out of her league. Tools and strategies are intended to assist marketers refine their strategies.

I love any excuse to use Excel! What a fantastic guide and a very thorough method of seeing what competitors are doing. Have you used this method for anything other than studying backlink profiles? It looks like there are applications for anchor text variations, current ranking keywords and all sorts.

I believe a back link research is very important in SEO process. Seeing where other websites have back links and obtaining some of those back links for your own website, may be useful for better ranking in search engine. It’s a very elaborate work, that implies a lot of patience, but I believe the results worth it.

Really a Great Post Annie. You have mentioned a very good point to consider. People usually follow the competitors strategies in order to get success and also for better results and in my opinion also it is necessary to be smart before copying anyone's strategies. The video is quite good to understand your words. Thanks you for sharing your idea.

You couldn't be more right. I generally use competitors that are ranking on the first page of Google for a term we want to go for. Since Penguin started causing ruckus on the web, I don't find too many sites ranking at the top with really crappy backlink portfolios. But it still happens with some verticals that have managed to fly under the radar (like some travel queries). In doing one competitive analysis, I discovered a ring of prominent news sites that were selling links b/c they all used one of two URL structures.

It is the shortest way to find link opportunity rather than starting from the scratch. But it is better to filter out the penalized domains from the list. Many times it is seen that domains having high DA also penalized and removed from the index of Google. So while collecting competitors' backlinks, it is safe to remove such domains from your list.

Since I'm targeting sites that are ranking for keywords I'm going after, that filtering takes place automatically. If you're talking about filtering out backlinks from penalized domains, those links sink to the bottom of the pivot table if you sort the way I recommend.

Great post Annie. I love the competitive intelligence insights, downloadable excel workbook and video to tie it all together for us. Some great roll up your sleeve actionable advice. Thanks for sharing.

Thanks, this has given me some food for thought, but could ignorance be bliss?

I can see that it would definitely add more weight to competitor analysis, rather than just total backlink numbers as it's always difficult to determine the quality of their linking profile without seeing the raw data.

It would also be interesting to see the key differences in strategy from within a sector and compare these against keyword rankings to benchmark activity.

But, I do think that it could also lead to false interpretation and inaccurate judgement of what is a 'good' strategy.

Yes, the aim is to increase rankings and if used right this would help, but I think the risk is high for misinterpretation, or following a competitor down a rabbit hole rather than being a pioneer. It seems too easy that this could be adopted (maybe it already is) as a quick win to no longer be inventive in terms of content marketing strategies, by being blinkered to considering new possibilities and avenues?

This tool could have a great use for benchmarking, but I think it's important not to jump on too quickly to make sure you're not just covering competitor pre-covered ground while they forge on ahead.

The whole point of competitive analysis is to find out what your competitors are doing to outpace you in the marketing channel you want to dominate. So it's best not to make marketing strategies in a vacuum.

I agree about not making decisions in a vacuum, but equally there's a thin line between using the data intelligently to get ahead as you've suggested against using it to keep up/copy others where there may be hidden flaws. Great power of the pivot brings great responsibility! ;-)

We had a client few years to make my products on ap and now we came back for whom we did this activity, and found that their competitors have targeted mainly online web directories and article directories (around 80% of their total backlink portfolio) including some bookmarking websites onaplioa

Definitely beneficial to see what competitors are doing (although probably best to concentrate on those that are doing better than yourself!). Although, as mentioned here you definitely have to be careful about mimicking techniques used by competitors to avoid being penalised.

Nice post Annie! Though I have some doubts about backlinks building by means of looking where a competitor has his backlinks, since Google is moving in other directions. Links are becoming less of an importance it seems. Google is looking more of the entity and associations and cocitations which matter more and taking the social mentions very high up in the algorithms. See a great example here. Backlinks are great, but better if they come naturally...So focusing on great content which gets linked to is I think the ultimate goal.

Yeah, I'm glad Google is moving in this direction. I hated every minute of link building for the year I had to do it. But I'm all about diving into data and finding out what real marketing site owners have been doing and what's working.

You solved two of my biggest headaches in one amazing post!! First, I've always been terrified of pivot tables and in awe of anyone with the rare talent to build and modify them. Never in a zillion years would I have imagined I could do it myself, but thanks to your thorough explanation and video, I have now begun conquering my fear!

The other thing that continues to overwhelm me is the mandate I've been given by management to "find more backlinks," a process we all know is tedious and time-consuming and much more difficult than creating great content that attracts readers who want to share your stuff. Your post made doing competitive research so much easier and less intimidating because you showed me how to take all the info and organize it in a way that was useful. Tracking down the opportunities and trying to capitalize on them is still a huge endeavor, but you've made it accessible, and I appreciate that more than you know.

I am a devoted Annielytics fan, now. So grateful you're willing to share your passion with the rest of us!

Great post. I loved the video. I think it is very useful to monitor the competition. As you say, not to duplicate it, but to understand where they have success and to compare to your own (or your client's) online profile.

I work with many PR teams on the client side. I am finding that reviewing competitor's inbound links gives the PR team ideas for content themes and for placement. If we combine this type of competitive research with solid keyword research, we can help clients get the right messages in the best channels to build their presence.

I'm trying to help clients build their own "eco-system" or prospect universe (to Google-proof their online marketing efforts) and your presentation on analyzing the competitor's inbound links was very helpful. Thanks.

This is a whole other topic. There are tons of ways (and many not even created yet) to do this. I'd be biased if I only said guest blogging is a great way to do this, but one of my favorite link-building technique posts would be by my friend backlinkto. Here's the post: http://backlinko.com/17-untapped-backlink-sources. Obviously they're "untapped" backlink sources, but you can get the point by just looking at this.

Here's a video tutorial on how to use Open Site Explorer, the tool I used in the video. Blerg. I can't seem to insert a link in a comment. Are you a Moz member? If so, just search for how to use open site explorer. A Moz video shows up in search results with a great overview.

I'm sorry you misunderstood the title, but it wasn't misleading. It communicated that I would show readers how to mine marketing strategies from *their* competitors' backlinks. I could give you some fish or teach you to fish for yourself. I chose the latter.

before saying anything about the post, i will discuss something about common SEO myths - its very common in SEO, if a website is getting higher rank, all other website start following the trends and backlinks followed by that website without knowing the status of those backlinks. We only assume if one website is getting rank by using those backlinks, it will work for other websites also. Annie you have done a great job as you mentioned very practical points in your post and the most important is that how to use the backlinbks of our competitors in effective manner. I hope all the SEO Professionals who are following blindly to the competitors backlinks, will learn how to use them effectively...

This post isn't about link building (bleh) and doesn't encourage following a competitor's strategy blindly. It's more about being strategic and seeing where a competitor's marketing strategy can be pivoted upon and improved.

I did a mistake while commenting on your post. No doubt you have done a great job by making such a brilliant post. Its much practical and well described. Its an effective post. Thanks for sharing such brilliant information.

the curve out marketting we can find the websites from our competitors backlinks. this should be invalid backlinks are out of bounce backlinks. we can easily find out that. for examble <a href="http://www.facebook.com/xapkart.online">http://www.xapkart.com</a> before i mentioned my company competitor site backlinks. one is the bounce link. another one is a out marketting links.