Image from the National Archives
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.
You can download the Excel workbook from Dropbox.
Steps to pull data together
Step 1: Pull your site's (or client's) backlinks — using Open Site Explorer, Majestic SEO, ahrefs, or whatevs — as well as a few of your main competitors. Then pull them together into a formatted table.
Step 2: Add another column and label it "Site." What I typically do is add the domain (without the https:// 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 3: Extract the domains from the backlink URLs using the LEFT and SEARCH functions. If you haven't done this before, I demonstrate how in this video tutorial. (Or you can download the demo workbook from the post and just copy the formulas.)
Step 4: Create your pivot table using these settings:
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.
To set your pivot table to "Outline," click on any cell in the pivot table and go to Pivot Table Tools > Design tab > Layout > Report Layout > Show in Outline Form (Mac: PivotTable tab > Design > Layout > Outline Layout).
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.
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. :-)
This is a very good point. All too often people seem to blindly use 'techniques' to improve results without being wise/ smart enough about how they use them.
Thanks for the article Annie.
My pleasure!
So true! I've seen both clients and their competitors suffer similar fates.
Nice walk-thru Annie.
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.
For realz? I'm going to have to test that out because selecting them is a total pain.
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!
reverse engeneering - one of my favorites :)
Not for the faint of heart. :)
that maybe true...
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.
That is really good to know! Thanks, Wiz!
Love the step-by-step guide. Your excel tips are always the best, Annie!
Thankeww!
great post thanks!
You're welcome!
Great post.. But what will happen if the competitors back links are in 6 figures?? Can we use this tactic in that case?
Excel allows for a million rows of data, I believe. But if you're using a pivot table you can have even more b/c the data is stored in cache.
Thanks Annie ! We have many website and difficult to manage competitor's back links to analyze. A pivot table definitely helpfull, why this idea did't come in our mind? :-)
It will next time. :)
Hello Annie , I really like your post ("We help you do it" says the Home Page itself) so that is a given. :) Anyways thanks for sharing the post.
My pleasure. :)
Cute steps.Thank you Annie.For this detailed post on Competitors backlink analysis.
You're welcome!
Annie, I was watching your YoTube vídeos explaining some Exel aspecs. You've helped me a lot!
Wonderful! That always makes me so happy to hear. :)
Very useful post! I hadn't even heard of pivot tables before, but now I will definitely look into them.
Great! You will fall in love.
Great Post Annie,thank you for sharing.
You're welcome!
Hey Annie thanks for the tutorial. Got tons of value from your post yet again. Cheers!
You're very welcome!
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! :)
Yes, you can summarize values by average instead of sum. Just right-click on one of the cells in the level you want to average, and choose Summarize Values By > Average.
And thank you for the kind words!
Ahhhh....tried a number of things, but couldn't find a way to do that. Awesome! Thanks so much.
Very helpful...Thank you Annie!
You're welcome!
Hey Annie, nice work flow, I'm sure I'll be back to borrow from it when I do a competitor analysis. The video helps too.
Happy to help. :)
This is so sneaky, yet so obvious. Love it! Great tips.
Heh heh. Sneaky fits with my personal branding. :)
Hi Annie,
Your Goal accomplished. I am able to visualize data in a better way and your post is also easily digestible :)
Excellent! Always good to hear. Thank you. :)
It is an amazing explanation, by the way this post make my job much easier than before. thanks alot
Awesome! Then my job here is done. :)
Dominating a competitor analysis for a client using this process right now! Thanks again, Annie. My Excel skills have improved greatly thanks to your informative tutorials.
That's always so good to hear, Brady! Thanks!
I miss getting these tips in person! (was spoiled)
Aww! I miss sharing those in person! <3
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.
As always, great information Annie! :)
Thanks!
I have found three additional tutorials from this single guide (its not a mere post) 1)Creating Pivot Table 2)Working with Formatted Table 3)Extracting domain from the URLs.
Thanks for the great help and a great helping guide.
It's a buy-one-get-three-free deal! Seriously, happy to help. :)
Excellent Info Annie.
Thanks!
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 use these kinds of pivot tables to analyze Screaming Frog, analytics, and social data. This is why I push pivot tables so hard. They make your analysis options virtutally limitless.
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.
Great hints for back link research!
Thanks!
Great post...working thanks :)
My pleasure!
Hello Annie,
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.
Annie,
First, you are awesome--but that's redundant :) Thanks for giving us this amazing tool, one we can all easily access and make great use of. I cannot wait to put this to immediate and frequent use.
RS
Always a pleasure, Ronell! Really appreciate your consistent encouragement. :)
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.
You bet! I try to make sure all the learning types are satisfied. :)
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.
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! ;-)
Thanks Annie. Nice way to pulling our Competitors' Backlinks. This is very helpful for me.
You're very welcome! Glad it helps. :)
Great Post. Thanks for sharing easy way pulling out competitors backlinks. This is very helpful for me and others.
You're very welcome!
I'm new at pivot tables, so was looking forward to seeing your video walk-through. Unfortunately, I'm getting a 404 error with your link. Can this be fixed, please???
Sorry the link wasn't working! We're getting it fixed ASAP, and for now, you can use this link to check out Annie's video about creating pivot tables.
Yeah, sorry. It appears to be a server issue on GoDaddy's end. But it appears to be working now, with no intervention.
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.
https://www.searchmetrics.com/en/services/ranking-factors-2013/
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.
Excellent Post Annie.
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.
Absolutely. Unless you own the domain lemmingseo.com. :)
Simply Amazing !! Thanks for sharing..
You're welcome!
Thanks Annie I am just getting into the deep stuff you highlighted and Excel do you use the seo tool in Excel?
Yes, I love that plugin and use it all the time.
Thank you very much Annie for such a great post. This is at all times enormous to be familiar with what your competitors are interested in and you can expand your plan for that reason.
I'm for all time a follower of realistic step-by-step lessons, so the video and Excel download are a great way for easy understanding.
Great! I'm glad they helped. :)
My excel skills aren't very good so this will come in very handy. Thanks Annie!
Then do I have a blog for you. :)
Annie,
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!
Thank you so much! I really appreciate hearing success stories like this! Made my day, actually. :)
I read the first list of URLs as "Steamboat Sanchez" which would be a sweet name.
Thank you for this!
I'm giving you 5 mins to buy the domain before I go for it. :)
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.
Happy it helped, Kathryn! There is a treasure trove of marketing opportunities in every site's competitors' backlinks.
Good Marketing Strategies thanks for sharing this info
The video that you've shared with us on how to find competitors' backlinks by using excel pivot tables are very interesting. I really got a lot of useful information from it.
I never done experience it, i would do better now thank you so much for sharing very innovative idea.
Hey Annie,
This post was unbelievably helpful... one thing. The link in the article to the video on how to make pivot tables is returning a 404? any idea what's going on there?
Whoops! Sorry about that -- we're looking into what happened, and will update the link ASAP! In the meantime, here's a direct link to Annie's video.
Looks like it's back up, Thanks!
That's nice but I don't even really know how to go about getting backlinks in the first place.
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: https://backlinko.com/17-untapped-backlink-sources. Obviously they're "untapped" backlink sources, but you can get the point by just looking at this.
I love this post and have shared it multiple times. My favorite is the donation strategy because it is a win-win for you and the charity.
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.
Thnxxx Annie for this great help
You're welcome!
For the first time, the title of the post on MOZ was misleading.
The title made me believe that some marketing strategies would be discussed from analysis of competitors' backlinks and not how to present the data in good visual form.
Annie you might want to include a bit on the marketing strategies a bit on top of the good data visualization.
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.
Actually Moz stands only for the latter ("We help you do it" says the Home Page itself) so that is a given. :) Anyways thanks for sharing the post.
Annie@
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.
Annie
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.
No problem. You weren't the only one to think it was about mere link building. I'm not a link builder and really hate everything about it, so I'm never going to write a post on that topic.
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="https://www.facebook.com/xapkart.online">https://www.xapkart.com</a> before i mentioned my company competitor site backlinks. one is the bounce link. another one is a out marketting links.
Sorry - I'm not sure what you're saying here.
Sorry - 10 other guys don't really understand what you are saying here. But i don't give a thumb down for bad english - mine isn't better...