Link Prospecting with Pivot Tables

I have a lot of tools in my SEO toolbelt, but I only consider a few of them to be absolutely necessary – Link Prospector from Garrett French is one of them. LP takes all the grunt work out of advanced queries and scraping SERPs. But just like when you prospect by hand, you are going to find some garbage that you will need to remove from your data set.

By using Excel tables with filters and throwing the filtered data into a pivot tables  – you can create easily navigable documents filled with high value link opportunities.

Here’s a step by step process to cut down Link Prospector exports to just the meat and potatoes

Note: If you’ve never used Link Prospector before, here is an awesome intro post from Jon Cooper at PointBlank SEO.

I just started this blog, it still isn’t ranking well for my name and it’s pretty slow to index 🙁 So, I am going to use the guest post option in LP to help remedy this problem.

Let’s bust out some research phrases:

NO! this is wrong.

When it comes to prospecting for awesome guest posts, don’t type in your head terms. That is going to give you a bunch of sites that get spammed on a daily basis because they rank for really generic seo guest post terms – In order to find great guest posts you need to get creative and specific with your advanced queries/Link Prospector Research Phrases.  To find something a bit more up my alley and tailored to my interests, I went with these research phrases:

These research phrases are all pretty niche and specific. They are way more likely to find a site that I would actually be interested in contributing to.

Once the LP reports are done running, export the paths:

Step 1) Open the export in excel and save the file as .xlsx instead of .csv, .csvs don’t support tables or pivot tables.

Step 2) Copy all URLs from the LP export and in a new excel sheet, paste all URLs into column A.

Step 3) In the data tab in excel, select text to columns > delimited > and under other: put the forward slash.

Step 4) Adding the root domain column to your data

Now column A in excel is just your root domains, add a new column to the link prospector export data in excel and paste in the root domains.

Your data should look like this:

Step 5) Turn all this data into a table

you can now filter EVERY. SINGLE. COLUMN.

Step 6) Start Filterin’

Personally, I only want to guest post on sites that are .com, .net, .org. and .co.uk – and I also don’t want to guest post on anything below a PR 3.

Note: below the page rank 9 option in the filter, there is one below it called (blanks), these domains have n/a tool bar page rank, so make sure to deselect them so they don’t show up in your final data set.

Step 7) Prospect Evaluation

Now that you only have data that’s worth evaluating by hand, sort your root domains alphabetically and then sort your domain PR column from largest to smallest.

Now all the domains are grouped together and sorted by page rank.

Step 8) Delete Forums

Step 9) Delete domains that are irrelevant or out of reach.

Just do a quick visual scan and delete sites that aren’t a fit. TLC SEO isn’t super established yet, and I don’t think I have enough social proof/readership to swing a guest post on Search Engine Land, Search Engine Watch, Hubspot or Kiss Metrics, so I deleted them from the data set. These would be highly involved guest posts and I am going for strictly quick wins.Don’t spend to much time on this step, just delete what is obviously not a good fit – you will have time for more in depth evaluation later on.

Optional Protip: Hyper Qualify for Guest Posts

If you want to be efficient and only look at sites that are practically guaranteed to accept guest posts, filter the title tag column for the word “guest” or even more extreme would be “guest post”:

Note: the download at the end of this post does not include the hyper qualified guest post option.

Step 10) Copy all of the data in your data set into a new sheet

If you try and turn the current data into a pivot table, it will include the filtered data that is not currently showing – so you NEED to paste it into a new sheet.

Step 11) Turn this data into a pivot table

I know pivot tables freak some people out, but trust me, they aren’t so bad – and they are actually super handy!

Step 12) Set up your pivot table

Instead of doing this step by step, I am just going to show the final set up of in the pivot table field list, which should be easy enough to replicate on your own even with limited excel experience:

Note: Values automatically go in as sums, so you will need to go into the “Value Field Settings” menu for Page Rank )by right clicking on “Sum of Domain PR” and change it to Average.

This is what you should see:

Step 13) Sort by Page Rank

Now you have a fully set up Link Prospecting Pivot Table. When you go through the table, check out the title tags and meta descriptions. If they don’t seem like a fit, minimize the root domain and never look at that prospect again:

The goal of this isn’t to find every single guest post opportunity in your vertical, it’s to find quick wins and move on. When you find a good guest post opportunity, throw it into BuzzStream and let that software find the webmaster contact info for you, check out their last few posts via the Buzzstream RSS option and see if your content would be a strong fit.

This Link Prospecting Pivot Table is meant to be a living document that you can go back to again and again during an SEO campaign. When you need a guest post, just open it up and find the highest PR site (or AC Rank if you want to do a Majestic bulk look up and add it to the data set)

Download: Pivot tables can be confusing, so I’ve made the example Link Prospecting Pivot Table from this post available for download.

HOWEVER, by downloading this excel file, you solemnly swear that  you wont spam any of these sites with crappy guest post outreach – some of these prospects are top tier sites in our industry, if you hit one of them up for a guest post – please make sure you have something worth their time.

BONUS!!

Because Chris Dyson (of Triple SEO) is a generous and awesome bloke, he created a Link Prospecting Macro, that automates most of this process. Just make sure you have Marcos enabled in Excel, here’s how to use the Macro:

1. Import you data from link prospector into the rawdata sheet
2. Apply your advanced filters in the table on the filters sheet
3. Hit the Get Data button

Make sure to thank and follow Chris for this awesome Macro!