Skip to content
Search engines 5511dd3

How to Carve Out Marketing Strategies by Mining Your Competitors' Backlinks

Annie Cushing

The author's views are entirely their own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.

Table of Contents

Annie Cushing

How to Carve Out Marketing Strategies by Mining Your Competitors' Backlinks

The author's views are entirely their own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.

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 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 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.

Back to Top
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.

Try our SEO Competitive Analysis tool

Get the data you need to outsmart your competitors and beat them in the SERPs.

Read Next

How to Actually Be Better Than Your Competitors in the Eyes of Choosy Customers

How to Actually Be Better Than Your Competitors in the Eyes of Choosy Customers

Jun 24, 2024
SEO Gap Analysis — Whiteboard Friday

SEO Gap Analysis — Whiteboard Friday

Dec 02, 2022
Daily SEO Fix: Competitive Link Research

Daily SEO Fix: Competitive Link Research

Oct 04, 2022

Comments

Please keep your comments TAGFEE by following the community etiquette

Comments are closed. Got a burning question? Head to our Q&A section to start a new conversation.