Link Prospecting with Pivot Tables

January 21, 2013 by John-Henry Scherck 29 Comments

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!

Filed Under: Link Building

33 comments

Newest | Oldest

aesd124

Serena Hancurkan Raket Usai Raih Tiket Final

http://www.football-images.net/2014-10-27/47aac0a915783c2a69b925ca332056eb.jpg
<a href=http://indo11.com/>Agen Bola</a> Terpercaya indo11.com melaporkan, Keberhasilan Serena Williams menembus partai puncak Final WTA 2014 membuatnya meluapkan emosinya di atas lapangan Singapore Indoor Stadium, Sabtu (25/10). Petenis wanita nomor satu dunia itupun tak segan menghancurkan raket karena saking senangnya.

Serena berhasil melaju ke final usai bertarung sengit dengan Caroline Wozniacki. Serena dipaksa bermain tiga set sebelum menang 6-2, 3-6, 7-6 di babak semi final. Usai pertandingan, Serena yang senang bukan kepalang langsung menghancurkan raketnya ke lapangan. Dirinya pun menilai jika luapan emosinya itu patut di kenang.

"Saya tidak tahu berapa kali memukul, tapi raket ini tidak akan membuat saya melakukan kesalahan lagi," ucap juara bertahan Final WTA itu.

"Itu pasti legendaris. Saya agak kehilangan momen keren, tapi setidaknya Anda tahu saya begitu bersemangat, memberikan 200 persen. Ketika saya bermain, tidak peduli bagaimana perasaanku akan memberikan semua yang saya miliki untuk setiap pukulan dan angka," tandasnya.

wasignton124

Iannone Absen Setelah Ditabrak Marquez

http://www.football-images.net/2014-10-27/cdfbc67f993ab7e926c6b5d6370981b8.jpg
<a href=http://indo11.com/>Agen Bola</a> Terpercaya indo11.com melaporkan, Insiden senggolan Marc Marquez yang menyebabkan terjatuhnya Andrea Iannone membuat pembalap asal Italia itu gagal mengikuti Grand Prix Malaysia, Minggu (26/10). Iannone mengaku sangat kecewa, pasalnya akhir musim kali ini tak sesuai harapan.

"Pagi ini saya mencoba kembali ke jalur untuk melihat kondisi lengan saya, namun masih terlalu sakit jadi saya memutuskan untuk tidak mengambil risiko itu," ucap pembalap Pramac Ducati ini.

"Saya sangat menyesal untuk melewatkan balapan ini karena saya ingin menyelesaikan musim dengan baik. Namun Hal ini terjadi, kita akan mengambil kesempatan di balapan terakhir di Valencia," tandasnya.

Insiden tabrakan Iannone dan Marquez terjadi dalam sesi latihan bebas kedua kemarin, Jumat (24/10). Saat itu, Marquez yang tengah tampil ngotot malah menyenggol bagian belakang motor Iannone hingga lawannya terjatuh.

quess124

Halep Akui Serena Lebih Unggul Pengalaman

http://www.football-images.net/2014-10-27/a738a5a9a672c780bcf3576e699dc9a4.jpg
<a href=http://indo11.com/>Agen Bola</a> Terpercaya indo11.com melaporkan, Petenis Simona Halep menyebutkan calon lawannya Serena Williams lebih diunggulan pada laga final WTA 2014 di Singapore Indoor Stadium, Minggu (26/10). Menurutnya pengalaman petenis asal Amerika Serikat itu bakal jadi keunggulannya.

"Saya tidak diunggulkan, Dia (Serena) lebih memiliki pengalaman. Dia tahu bagaimana saya bermain beberapa hari yang lalu," cetusnya.

"Dia lebih tua dari saya dan dia tahu bagaimana bertanding di final besok. Ini pertama kalinya bagi saya. Tapi tetap saja saya punya kesempatan," tandasnya.

Sebelumnya, Halep dan Serena sudah bertemu di penyisihan Grup Merah beberapa waktu lalu. Saat itu, Halep menang dua set langsung 6-0, 6-2 dari petenis nomor satu dunia itu.

Hudders

Awesome, awesome post on link prospector. I've been using it for a few years now, but didn't start using it until months after getting it because it was so complicated. I talked about it in my post: http://imworks.co.uk/the-evolution-of-digital-marketing/ - Link Prospector and Buzzstream are the only two tools I would say are must haves right now for a white hat agency or SEO.

quess124

Isla: Premier League more competitive than Serie A

http://www.football-images.net/2014-10-03/d71431a04fb20978eda89a614d131125.jpg
Leading <a href=http://indo11.com/>Agen Bola</a> indo11.com highlighted, QPR defender Mauricio Isla is convinced that the Premier League is more competitive than and superior to Serie A.
The defender said: "It's a wonderful step in my career for many reasons. Coming to play here in the Premier League is good for me because it's really good football with many strong teams.”
He added: "I came into a club with many players with experience such as Rio Ferdinand, [Shaun] Wright-Phillips or [Adel] Taarabt. QPR are a respectful team, which is fine playing in the Premier League.
I have noticed the change [between Premier League and Serie A] in many ways. Here you can find seven or eight teams fighting for the title. The others are strong too.”

wasignton124

Simundza lavished praise on Maribor effort at Schalke

http://www.football-images.net/2014-10-03/75d9407e14b4520b6feee562c4a59b42.jpg
Famous <a href=http://indo11.com/>Agen Bola</a> indo11.com mentioned, Maribor coach Ante Simundza heaped praise on his side’s effort, feeling they could have won at Schalke on Tuesday, but he was still pleased with a point.
The coach said: "My first impression is that we could have taken more from this game, but we are very pleased with the point. The lads put in a very good performance and picking up a point is a very good result for us."
He added: "The equaliser was a present from Maribor to Schalke. Many things happen in football, but it is true that because of our mistake we conceded this goal."

aesd124

Totti regrets not beating Man City

http://www.football-images.net/2014-10-03/4e67abfbf7f1e03ddc56e7fe64822005.jpg
Famous <a href=http://indo11.com/>Agen Bola</a> indo11.com mentioned, Roma captain Francesco Totti felt it were unlucky for Roma not to beat Manchester City as the veteran enjoyed a record-breaking UEFA Champions League night and that they should have had more.
The captain said: "Looking at the whole 90 minutes, we had the clearer opportunities and could've won. We went down straight away to a penalty and came back into it, which wasn't easy against one of the strongest teams in Europe.”
He added: "We proved what we are worth and what we can do in an arena like this. Our objective is to do our best, try to get through the group knowing it is not easy against sides like Bayern Munich and Manchester City, who are on our level if not better. Playing like this, we can get there."

Mike Bonadio

This post is a huge help! Gonna try this out now, thanks!

spookseo

I use Link Prospector along with Google Alerts, BuzzStream, and Trackur, works pretty good together. I look for forums with Link Prospector then I use he other 3 to alert me whenever there's a new conversation also to see if my brand is positively mentioned.

AFreezee

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

Thanks!

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?

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.

besegal

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

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.

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?