TLC SEO: The Linking Man's Blog

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

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.

wrong way

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:

 

export link prospector 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.

 

text_to_colums

 

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:

data_with_root_domians

 

Step 5) Turn all this data into a table

 

turn_into_table

 

you can now filter EVERY. SINGLE. COLUMN.

 

data_with_filters

 

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.

 

tlds_i_wantPR_exclusions

 

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.

 

roots_alpha pr_sort

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

organized_roots

 

Step 8) Delete Forums

 

remove_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
pivottable

 

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:

 

pivot_table_field_list

 

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.

 

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

 

minimized_prospects

 

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!

 

33 comments
aesd124
aesd124

Serena Hancurkan Raket Usai Raih Tiket Final

http://www.football-images.net/2014-10-27/47aac0a915783c2a69b925ca332056eb.jpg
<a href=http://indo11.com/><b>Agen Bola</b></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
wasignton124

Iannone Absen Setelah Ditabrak Marquez

http://www.football-images.net/2014-10-27/cdfbc67f993ab7e926c6b5d6370981b8.jpg
<a href=http://indo11.com/><b>Agen Bola</b></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
quess124

Halep Akui Serena Lebih Unggul Pengalaman

http://www.football-images.net/2014-10-27/a738a5a9a672c780bcf3576e699dc9a4.jpg
<a href=http://indo11.com/><b>Agen Bola</b></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
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
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/><b>Agen Bola</b></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
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/><b>Agen Bola</b></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
aesd124

Totti regrets not beating Man City
 
http://www.football-images.net/2014-10-03/4e67abfbf7f1e03ddc56e7fe64822005.jpg
Famous <a href=http://indo11.com/><b>Agen Bola</b></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
Mike Bonadio

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

spookseo
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
AFreezee

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


Thanks!

Miguel Salcido
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
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
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
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
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_
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
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
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!

jo_pricey
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?

chrisdyson
chrisdyson

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

JHTScherck
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!

JHTScherck
JHTScherck moderator

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

JHTScherck
JHTScherck moderator

 @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 :)

jo_pricey
jo_pricey

 @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!

chrisdyson
chrisdyson

 @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 :)

 

 

thonmey
thonmey

Mazzarri labels Inter sluggish and lazy
 
<b><a href="http://indo11.com">Agen Bola</a></b> terpercaya indo11.com reported,Inter head coach Walter Mazzarri slammed his ‘sluggish and lazy’ team after their 3-1 loss to Juventus on Sunday.
Stephan Lichtsteiner, Giorgio Chiellini and Arturo Vidal were on the scoresheet for the Serie A leaders at Juventus Stadium.
Like Rolando’s second-half effort for Inter, Chiellini and Vidal scored scrappy goals, with the former coming after the visitors failed to clear their lines.
Mazzarri, who was particularly critical of midfielder Mateo Kovacic, said his team failed to match Juve’s determination and aggression.
“I was annoyed at the way we conceded the second goal, as we had the opportunities to clear it and were too sluggish. Juventus were quicker to the ball, more aggressive and that made the difference,” the 52-year-old coach told Sky Sport Italia.
“We need to focus on every little detail with great determination, the way Juventus do. When the going gets tough, they get going. Juve were more determined and aggressive, whereas we were sluggish and lazy.as reported by <b><a href="http://indo11.com">Indo Eleven</a></b>.
“We created chances and let’s not forget Rodrigo Palacio twice could’ve made it 3-2 to end with a siege on their goal.”
Sourcehttp:// indo11.com

thonmey
thonmey

Huntelaar: It was harder against Wolfsburg
 
<b><a href="http://indo11.com">Agen Bola</a></b> terpercaya indo11.com reported,Schalke striker Klaas-Jan Huntelaar says he remains far from full fitness as he continues his recovery from a knee injury.
Last week, the prolific Dutchman played his first match since August last year, netting in a 3-0 win over Hamburg in the Bundesliga.
Huntelaar backed up that performance with 90 minutes on Saturday in Schalke’s important 2-1 victory over a 10-man Wolfsburg at Veltins-Arena.
The 30-year-old said his second match back was harder than his return and feels he still has plenty of work to do on his fitness.as reported by <b><a href="http://indo11.com">Indo Eleven</a></b>.
“I felt very good in our last game against HSV. It was a bit harder for me against Wolfsburg,” Huntelaar told the Bundesliga website.
“I played the full 90 minutes and maybe that was why. This game was certainly more demanding for me and I felt it more in my legs.
Sourcehttp:// indo11.com

homland
homland

Sir Bobby Charlton Khawatir Dengan Inggris 


Berita terbaru dan terkini bola Soccer dari <a href=http://indo11.com ><b>Agen Bola</b></a> Indo11.com –  Sir Bobby Charlton menyatakan bahwa dirinya “khawatir” dengan “kekurangan pemain kelas dunia” dalam skuad Piala Dunia Inggris.

Baru-baru ini Inggris melakukan dua pertandingan persahabatan melawan Ekuador dan Honduras sebelum pergi ke Brasil pekan depan.

Namun, pencetak gol terbanyak di tim nasional Inggris ini mengatakan bahwa Three Lions tidak cukup memiliki kualitas untuk memenangi turnamen.

“Satu hal yang membuat saya khawatir adalah kekurangan pemain kelas dunia,” kata Charlton.

“Saya tidak menentang pemain asing, tapi jika mereka bermain di Inggris, kami tidak cukup memiliki pemain muda. Kami harus memainkan pemain muda. Anda harus memberikan mereka kesempatan,”