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

Image via Flickr user Paul Keller
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.

Image via Flickr user CarbonNYC
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.

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


Step 6) Start Filterin’


Step 7) Prospect Evalutation


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.
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”:
Step 10) Copy all of the data in your data set into a new sheet
Step 11) Turn this data into a pivot table

Step 12) Set up your pivot table


Step 13) Sort by Page Rank

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!