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

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:

Bad_queries

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:

good_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 Evalutation

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”:

hyper_qualify

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:

prospector_pivot

Step 13) Sort by Page Rank

sort_pivot_by_pr

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!

besegal

1 Like

John:

Nice work. I took the liberty of downloading the data file and then working up the raw data tab using Tableau. If other of our followers don't want to pay the fee for the paid version of Tableau, the free "Tableau Public Version" lets you do a fair amount of things.

I added in fields that identify if either the "Title" or "Detail" fields contain the words "guest post" or variants. I filtered out any forums too.

I also discovered that a handful of domains not only have above average PR scores but above average LTS (Link Target Scores). So those domains might be especially good targets. Just draw a square w/ your mouse around any of the circles in the scatter plot on the left. Then the bar graph on the right will be filtered to show just those domains.

Here's a link to the live and interactive data visualization:

http://public.tableausoftware.com/views/BESegalsAnalysisofJohnHenryShercksLinkProspector/LinkProspectorAnalysistoFindBestProspects?:embed=y

If you have any questions, feel free to reach out to me via Twitter @besegal

JHTScherck moderator

@besegal Wow, that is insane! Very cool set up. I'm not familiar with Tableau - but I am going to be digging into that software. Thanks for sharing!

23 comments

Newest | Oldest

AFreezee

Hey JH! I was super intimidated by this but I just went through it and it is super easy.

Thanks!

@AFreezee Glad its helpful Freeze :)

Miguel Salcido

@chrisdyson @JHTScherck This is SO sweet. But I can't get any of the filters to work in Chris' Macro sheet. None. I try to filter by PR, try to filter by LTS, etc. And nothing updates. Any advice?

@Miguel Salcido @chrisdyson @JHTScherck Hi Miguel, I actually haven't used Chris' Macro much, I prefer to sort through them by hand first. Sorry I can't be of more help.

HPhysiocSEO

Clever thinking here. I am very, very particular about who I reach out to and ask for the opportunity to guest blog. It's just as important to me who I blog for as it is to them who they select to publish on their site. But it's always nice to have a larger, better pool to pull from.

Justin McGill

Good stuff John-Henry. The macro that Chris put together is certainly helpful as well, but I always like to know what the macros are doing so certainly very helpful to have it all laid out.

@Justin McGill Thanks for stopping by Justin, glad you got some value out of the post!

chrisdyson

If any one has any pressing Excel queries then I am more than happy to run through them on my blog - tweet me or get at me tripleseo.com/contact-me/

hyderali_

Awesome post!

You know I always dreaded using Pivot tables. But you've provided step by step guide which i'll going to use. Excel is one of the powerful tool for SEO & its my bad that I don't use it fully. Would love to see more SEO for exce type of post, if you have any?

Thanks.

serfofthesoil

This is awesome. I am not super familiar with pivot tables so this is what I needed to get started on my own tables for prospecting and analysis. I appreciate you putting the download link in there.This is like prospecting 2.1, and I always enjoy finding more effective ways to get it done.

@serfofthesoil So happy to hear I could help, if you run into any snags - feel free to hit me up on twitter!

zach2

John-Henry,

Always insightful, but I would expect nothing less! Excel tips, tricks, hacks, and macros are always greatly appreciated, keep up the good work!

DholakiyaPratik

Gentleman, you nailed it in this post. Thanks for this detailed post, I'm going to share this with my team. I'd also love to hear your thoughts on my recent YouMoz. It's here - http://www.seomoz.org/ugc/the-ultimate-guide-to-advanced-guest-blogging

jo_pricey

Thanks for sharing your prospecting knowledge - will definitely be having a play with this tool later :-) You mention using Buzzstream to find the webmaster contact - out of interest how do you find the Link Prospector Tool compared to the Buzzstream prospecting tool?

@jo_pricey The buzzstream link prospecting tool is great if you have a solid handle on advanced queries - but I think that link prospector is personally more robust. That being said, Buzzstream actually has an import link prospector feature :)

@JHTScherck Thanks you, that's good to know - we're currently looking at Buzzstream for link prospecting, but it's good to have something to compare it to. However, Buzzstream does seem to offer a solid contact database in addition to link prospecting...decisions decisions!

@jo_pricey They both have free trials :)

Nice work... I think there's a few more Excel Hacks I can show you to speed this up a bit :)

@chrisdyson Please do!

@JHTScherck 1. You can use the filters in the Pivot table to select the PR and TLD no need to filter it and then copy into a new sheet?

2.Use the formula =LEFT(A1, FIND(“/”,A1,8)) to extract the domain name A1 is the cell containing the URL

For starters - if this is something people would use then I could create a spreadsheet to automate this :)

@chrisdyson Nice!