[Build Backlinks Online] How to Carve Out Marketing Strategies by Mining Your Competitors' Backlinks

Build Backlinks Online has posted a new item, 'How to Carve Out Marketing
Strategies by Mining Your Competitors' Backlinks'


Posted by Annie Cushing



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.


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten
hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think
of it as your exclusive digest of stuff you don't have time to hunt down but
want to read!






You may view the latest post at
http://feedproxy.google.com/~r/seomoz/~3/Q_v1wRZ71PM/how-to-carve-out-marketing-strategies-by-mining-your-competitors-backlinks

You received this e-mail because you asked to be notified when new updates are
posted.
Best regards,
Build Backlinks Online
peter.clarke@designed-for-success.com