Link Building with Google Sheets: Start Guest Posting in 15 Minutes

By | October 19, 2019

Today, we’re going to create a link building
template like this, where you can find prospects and valid email addresses in under 15 minutes. Stay tuned. [music] What’s up SEOs? Sam Oh here with Ahrefs, the SEO tool that
helps you grow your search traffic, research your competitors and dominate your niche. Now, if you’ve never used Google Sheets or
if you’re a passive user, this tutorial is going to be a bit of a hybrid between learning Google Sheets formulas and how they can be practically applied to link building systems. Best of all, you can apply these to any sheet
you create in the future. With that said, let’s get to it. The first thing we need to find are link prospects
and collect some data. Now, our goal is to create a Google Sheet that will help us kick off a link building campaign in 15 minutes. And in order to do that, you’ll need to find three
data points fast. These are: The URL or domain of the prospect; Someone’s first name; and their last name. And the tool we’ll be using to find this information
is Ahrefs’ Content Explorer, which lets you search through over a billion pages of content
and get SEO and social metrics for every page. Now, something that’s often overlooked, is that Content Explorer also shows the names of the authors too. This is huge in my opinion when it comes
to automation. So let’s say we want to start a guest-posting campaign. I’ll start off by typing in a topic that’s
related to my niche, like “coffee.” And to ensure we’re finding relevant pages,
we’ll set this to a title search. This is going to show us all pages that contain
our keyword in the title. Now, it’s important to note that when you’re looking
for guest-posting opportunities, you don’t need to limit yourself to websites with “write for us” pages. In fact, websites without “write for us” pages
probably get fewer guest post pitches so there’s less noise to cut through. And why would they say no to free and
well-written content? Alright, so from the results page, I’ll click
on the One page per domain filter since we don’t need to contact the same site multiple times. Also, I’ll set this filter to English, since it’s
the only language I’ll be able to write in. Also, I’ll set the filter to Only live pages
to ensure all blogs are still alive. And finally, I’ll set a Domain Rating filter
to a minimum of 50 and a maximum of 60, which should give us a list of some good domains. Alright, so it looks like we have a good number
of results, so I’ll click on the export button, and choose the maximum number of exportable results. Finally, I’ll export the CSV. Cool. We now have our data so it’s time to move
on to step 2, which is to create our template. Now, as I create the sheet, there are two functions
that I’ll use frequently. And these are IFERROR and ARRAYFORMULA. IFERROR allows you to set a default value
if the formula returns an error. The syntax is basically saying… If this value returns an error, show a custom
error message. Or if you leave the custom error message blank, it’ll
return an empty cell instead of an ugly error message. For example, if I had a list of cells where I was
dividing value A into the corresponding value in column B, then this one would show an error because 5 can’t be divided into 0. So we can fix this by wrapping the formula with
IFERROR, which will then produce a null value. The other function is ARRAYFORMULA. The syntax looks like this, but it doesn’t
really say much. This function basically allows you to create
one formula and apply it across multiple rows without having to waste time dragging it down. So using our basic math example from earlier,
we can delete all of the formulas in the cells except the first one. Then in cell C1, I’ll wrap the formula with
ARRAYFORMULA. And instead of just looking at the A1 divided
by B1 cell, I’ll add “:A,” which will apply the colon to all cells in column A. And then I’ll do the same for column B. And if I press Return, you’ll see that the
formula gets applied to the entire column. But again, these errors look ugly, so what
do we do next? We’ll wrap the whole formula with IFERROR. This will allow us to add additional values in columns
A and B and the formula will automatically execute. Alright, so let’s actually build up our sheet by
adding a few more formulas to build our template. So first, you’ll need to import the file. So click on File>Import, and then Upload. Here, you can drag and drop the exported file
from Content Explorer. I’ll select “Replace current sheet” and then
complete the import. And I’ll change the sheet name to something
like “CE Import.” Now, this is the raw data we’ll be working with
and there’s no need to change anything here. Instead, we’re going to be parsing bits and
pieces of information from our raw data to have an untampered reference sheet. And the formula we’ll be using to do that is QUERY. The function works like this. You type in QUERY, then the range of cells you want
to extract data from. Then, add an actual query using a language
similar to SQL. So you can basically select specific columns
you want to extract, and add WHERE conditions to narrow in on your data. So let’s look at our raw data set and decide
which columns we want to parse. So for guest posting, I want to get the title,
URL, author’s name, and Domain Rating. So let’s take note of these columns in the
order we want them to appear. So B,C,E, and D. So let’s set up a new sheet and call it
“Master Guest Post.” And within cell A1, I’ll type “=QUERY” open bracket, then I’ll go back to our raw data sheet, click on
the B header and drag it over to column D as I won’t need any other information. Next, I’ll type a comma, and type two quotation
marks since the query needs to be wrapped in them. And I’ll type, “SELECT B,C,E,D”, and close the brackets. And there we have it. As you can see, a good chunk of the results
have author names, and a lot of them don’t. So let’s clean this list up a bit by adding
a WHERE clause to our SELECT statement. So I’ll click inside the box here and after
the SELECT portion, I’ll add… “WHERE D is not null,” meaning where column
D from our raw data, which is the author names, doesn’t have a value. Looks much better. But if you look at this data again, you’ll see some one-word author names like Sydney and
a double hyphen. If you’re not familiar with email finding tools, most of them need a first and a last name in order to find a targeted email. So we’ll remove these by adjusting our query
and adding to the WHERE clause. I’ll type “AND D contains” single quote, space,
and close the single quote. And the reason why is because there’s a space between the first and last name. Much better. Alright, the next thing we need to do is parse
the author’s name into two columns: their first and last name. So I’ll create new headers here called “First”
and “Last” in columns E and F. Now, in a world where everyone’s full name
was two words, we could simply do a function like SPLIT, where we could parse the first and
last name by looking for an empty space. But seeing as it’s not a reality, we have
to add slightly more complex formulas. And rather than explaining these ones to you, I’ll add them in the pinned comment so you can copy and paste them. So to find the first name, we’ll use both
the LEFT and FIND functions. And to find the last name, we’ll use TRIM,
RIGHT, SUBSTITUTE and REPT, which will grab the last word in the author’s name. And to avoid dragging down, we’ll wrap the
formula using ARRAYFORMULA, modifying the cell references to include the entire column,
and finally, I’ll add the IFERROR function so our results stay clean. And with the power of video, we’ll do the
same for the last name too. Alright, the next thing we need to do is find emails. For this, I use a tool called Hunter. They have a Google Sheets add-on which you can
get access to by going to Add-ons>Get add-ons, then search for Hunter Add the tool, and make sure you’ve signed
up for an account. Free account should come with 50 free searches
per month at the time of making this video, and by paying you’ll obviously get more lookups. Now, that we have it all set up, all you need to do is
click on Add-ons, hover over Hunter, and select Open. From here, select the Email Finder tab. Now, we need to map the columns. So as you can see, we have the first name
in column E, so let’s choose that. The last name is in F, and for the domain name
field, let’s choose column B, which is the URL. Reason being, Hunter will take the full URL
and automatically use the root domain or subdomain when searching for an email address. And for company name, just leave it blank since
we already have the domain name column set. Once you’re finished, click on “Find email
address” and wait for Hunter to finish the job. Alright, so we have a ton of emails, but which
ones are actually valid? To find this out, we need to get a clean list
of all email addresses Hunter found. So let’s create a new sheet called “Email validation.” Now, in cell A1, I’m going to use the UNIQUE function. And this one’s simple. Just type in UNIQUE, then within brackets,
choose the columns you want unique values from. So in our case, I’ll select the Email column. Now, I’ll click on File>Download as, and choose CSV. To validate these, we’re going to use a tool
called NeverBounce. Once you’re logged in, click Add List. Then upload your file there. Once it’s done, choose Clean my List, which
will then validate each email within your CSV. Choose to pay with your existing credits, and
I believe they give you a bunch of free ones when you first sign up. Or if you have a massive list, you can pay
using credit card. When it’s done verifying emails, click Download,
then select All results. Finally, download the CSV file. Now, go back to your Email Validation sheet, and
we’re going to import the file from NeverBounce here. So click on File>Import>Upload, and
then let’s drag and drop that file here. Now, we’ll select “Replace current sheet” and
import the data. The final step is to match the validation
statuses with our master sheet. So I’ll delete all of the columns Hunter generated
aside from the Email column. And I’ll create a new column called “Validation.” Now, we’re going to match up the email addresses
to our validation sheet to see which ones are actually valid, and which ones aren’t. To do this, we’ll use the VLOOKUP function. VLOOKUP allows you to lookup a value using
a search key—you can then return a matching value from a specific cell in that range. So looking at the syntax, it’s basically saying,
“Look for the search key within the first column of this range, then return the value in the index
key, which basically means column number.” To demonstrate, I’ll type in VLOOKUP, open
bracket, then I’ll click on the email address cell, since this is the value we want to search for. Then I’ll type in a comma and add a range. So I’ll go to the Email validation sheet and
select columns A and B. Then we need to add the index key. Since we’re looking for the email status, we’ll type in 2, since it’s the second column within our range. Finally, I’ll add FALSE, which will return
only exact matches to our search key. Now, before I hit the return key, let’s wrap
this in ARRAYFORMULA. And I’ll make sure to change the cell references
for the whole column. And then we’ll wrap this in IFERROR. And now we have the email statuses in
our master sheet. Last but not least, let’s add a filter to our table
by clicking on any cell within our table, and then clicking the filter icon up here. I’ll click on the Validation filter>Clear
all, then select only valid emails. And just for kicks, we’ll run a COUNTIF function
to see how many valid emails we have. And it looks like we have a good number of websites,
names, and email addresses we can add to our favorite outreach tool for more efficient link building. Now, you can easily just export massive
lists of websites from Content Explorer and find potentially thousands of emails in under an hour. But what I recommend is that you still look
through the site, check other metrics like traffic and relevance of the site before you
start pitching away. As for the emails that weren’t found with
automation, you’ll need to find them manually. Now, I’ve left most of the formulas in the
pinned comment so you can copy them. And I’ve also left a link in the description, where
you can copy the sheet to your own Google Drive. So if you have an Ahrefs account, you can
follow the instructions on the Instructions tab, make reference to this video for specific
steps, and start building links fast. Now, if you found this video to be helpful,
make sure to like, share and subscribe. And let me know in the comments if you
want to see more tutorials like this one. So keep grinding away, work smarter and harder,
and I’ll see you in the next tutorial.

79 thoughts on “Link Building with Google Sheets: Start Guest Posting in 15 Minutes

  1. Emmanuel Asika Post author

    Can't wait for your Search Engine sir… Our entire blogger community here in Nigeria is in full support of what you're trying to accomplish.

  2. Ahrefs Post author

    Hey all. Hope you enjoyed the video. Here's a list of all formulas + the Google Sheet used in this tutorial. Enjoy 🙂

    Import just the title, URL, author name, and Domain Rating from a Content Explorer export

    =IFERROR(QUERY('CE Import'!B:P,"SELECT B,C,E,D WHERE D is not NULL AND D contains ' '"))

    Get the first word from a full author name


    Get the last word from a full author name

    =IFERROR(ArrayFormula(TRIM(RIGHT(SUBSTITUTE(D2:D," ",REPT(" ",100)),100))))

    Lookup email addresses to see if they're valid against your NeverBounce import

    =IFERROR(ArrayFormula(VLOOKUP(G2:G,'Email Validation'!A:B,2,FALSE)))

    NOTE: You may need to change cell references if any of the tools' export files changes over time.

    Copy the Google Sheet ►

  3. Sanjivan Biswas Post author

    Pls update link intersection, Not showing the full URL there, only shows the domain name.

  4. LOve Guru Post author

    Cool idea but Sounds like Spamming… doesn't it? We cannot get 100s of backlinks in a day and if we get then it will be the part of spamming..right ?

  5. Youngster Team Post author

    Plz make a free extension like keyword every where for more value because it creates more Trust on SAM😉

  6. Sagbee C Post author

    Thank you. We would love this kind of videos for sure.

  7. Riazuddin Jeelani Post author

    Just one word – GENIUSSSSSSSSSSSS. Thanks a TON

  8. Aryan Khan Post author

    Get Its save my A lot of time which I'm doing Manually <3
    I have a Question
    Is it Important in SEO to Add this Site Name (Brand Name) At the Start or End of Every Page, Post or Product etc

    I mean it is compulsory….!!!
    Example This is my first title – Mysitename OR Sitename | This is my 2nd title

  9. MrDyerN Post author

    Hey Sam, this is insanely helpful!! Thanks a bunch. Can you do a video on how to filter for quality websites quickly and efficiently?

  10. Deandre kaough Post author

    This is so useful. Thank you for bringing value. #teamgaryvee

  11. Skyler Reeves Post author

    Excellent video Sam. I don't know how I would get by without =QUERY() and =VLOOKUP(). 👍Sidenote: Excel supports =XLOOKUP() now! 😃

  12. S.M. Rakib Hasan Post author

    Awesome. This video will save my ton of time. Many thanks, Sam (y)

  13. Piyush Yadav - Exact Blogging Post author

    Hi Buddy, This is likely hard to understand can you make a another video on this topic in detail it's my request

  14. Charles Benninghoff Post author

    Excellent video. Thanks very much Mr. Oh. I admire your flexible mind able to grapple with these formulas. I'm an old, old hand at data manipulation and maybe some of my experience could help you & your followers. First and foremost – the method to collect the data is novel and unique. Kudos. After the information is assembled, though, much work can be eliminated by exporting the entire table to a CSV file. Then, find a cleaner service that will allow upload of ALL data. After cleaning, all data is maintained in downloadable format (including bad data should you wish) exactly as uploaded 'cept a new column is added to denote the Good, Bad & the Ugly. The resulting XL spreadsheet is identical to your product and the $$ cost probably the same.

    You have invigorated me to try this amazing link-building process. More kudos.

  15. Twok3Design Post author

    I've been doing a lot of work in Google Sheets and this just helped me fix a ton of stuff I was trying to figure out… plus, I now have an amazing link building sheet, thanks!

  16. Skuash Post author

    Honestly, this is what I call pure and real value. Learned a lot, hope to make it work, one reason why I failed so many time when trying to get guest posting opportunities is because I didn't have this.

  17. Narudol Pechsook Post author

    Thank you so much It useful to me very much.

  18. Praveen Kumar Post author

    hey, I have tried for guest blogging but not a single reply. And one more thing that your ahref account is very costly for new beginner blogger. Everyone can't afford this. Who had purchased a domain with a friend help? How he can buy your costly ahref account.

  19. Rory McCall Post author

    An incredible video . Your content here truly is wonderful. May I ask, I’m based in Europe. Last year when GDPR was introduced it has instilled a lot of fear pertaining to emails that you did not collect “legitimately”. Could this method be used safely in GDPR areas, would you think? Thanks

  20. eKelum SA Post author

    Wow.. Best and Effective way of doing link building. Amazing.

  21. hellocourage Post author

    Awesome Sam. Never know Excel sheet can be so powerful. After we are done with all the emails, do we send out using autoresponder like activecampaign or mailchimp?

  22. Style Points Post author

    Wow please do more vids like this. Learned a ton. thanks 🙂

  23. Arthur Post author

    Sam, so awesome! Thanks for the template. Is there a Sheets plugin for Ahrefs that lets you connect your API and pull in domain metrics? Also, this seems like a good process to use before uploading to a tool like buzzstream, since it's not the best at finding email addresses. Would you agree?

  24. WagnerMeters Post author

    Great video! Thanks for sharing. We would love to see more videos like this!

  25. Sony Max Movies Post author

    Siteground Vs Bluehost Vs Anything else. Your recommendation ?

  26. Sony Max Movies Post author

    How voice search will affect SEO's and Bloggers ?
    Will keyword targetting still work ?

  27. Matt Oney Post author

    Insane you much work you must put into these videos to make them so awesome. Thank and love you, Ahrefs

  28. Anatoli Gorianski Post author

    This is mind-blowing bro, the provided value bombs and practical knowledge are absurd.

    I'm going to buy hrefs right now.

  29. Arham Ali Post author

    Hi sam,

    Do you recommend reaching out to all those prospects without checking if they were guest post or not?

  30. Sumit Yadav Post author

    Guest post has become a big business now a days.

    I have sold close to 1300-1400USD per month.

  31. Sudhanshu Jha Post author

    yes Iwant this type of tutorial -sam you have changed my life with you tutorial

  32. Irvin Hector God Graded Success Post author

    EPIC! I learnt too much in this video thanks, thinks I can apply in more than just business. Much appreciated!

  33. Stephen Gaede Post author

    Very helpful! If you haven't already addressed the topic, could you do a tutorial on using Ahrefs to do link tracking? Other programs allow the creation of a list of URLs along with a target page/URL, with an automated way to see whether those URLs link to the target page. I'm pretty sure I could do this via a VLOOKUP and some other formulas if Ahrefs doesn't do it… and if you have such a solution it would be great to see.

  34. Kieran Proven Post author

    Sam Oh and the team at Ahrefs, you have blown my mind! The information that this one video provided is easily worth the monthly fee for your product alone. You have saved anyone who watches this tutorial massive amount of time and frustration. I wish I could suggest more video ideas but I haven't even scratched the surface Ahrefs functions. Easily the market-leading product. I also enjoy Tim Soulo's appearances on the Authority Hackers podcast👍

  35. Tecno Android Master Post author


  36. Sourodip Biswas Post author

    thanks for your video. Then I approach those people with the email address? what if they are the ones who guest posted on third party sites

  37. Brckata Post author

    This video shows why Ahrefs and your videos are the best there is online! Thanks!

  38. René Andreasi-Bassi Post author

    AMAZING! Bought the shotgun skyscraper course from Authority Hacker for a few hundred bucks, in which Mark explains a fairly similar approach. But this video seems less complex and frickin FREE! You’ve covered half of their course, the second part is more on outreach and I believe you’ve covered this already in another video. Love these advanced vids as they are more premium and really stand out from the hundreds of other seo channels that all say the same thing. Keep them coming! Thanks again! 🚀

  39. Marissa Williams Post author

    What is the best outreach tool you recommend?

  40. Marissa Williams Post author

    I only see one flaw in this. What if you accidently capture your competitors?

  41. Answar Mahmood Post author

    That was soooooo complicated…so wanted to do it but totally lost..cmon man we need to keep it real..

  42. Longisland Hub Post author

    Hey, I am trying to train my new employee who never heard of Backlinks before. I tried using the basketball backlink metaphor but seemed too complex. Is their a video you created that would explain Backlinks to someone who has never heard of them before? Thank you!

  43. Mohammed Ali Post author

    Is this legal? In terms of data protection.
    Would it be possible to get that many emails and send them marketing material from my website?

  44. Dima Makei Post author

    Great video! I'd love to see more videos like this. Like for every SEO process that could be automated or "SEO Automation with Google Sheets – Top 7 formulas for every SEO"

  45. Thietbi M5s Post author

    Hello I am using Ahref now, could you teach me why we have to the link and the email. What will we do with this information. ?

  46. Edouard Soutzo Post author

    It's a nice strategy, but how are you sure that those emails and site are relevant for guest posting?

  47. Mat Houchens Post author

    Great video but has this feedback been sent to the ahrefs dev team to provide this type of filtering and custom column ordering directly on the site to prevent these workflows from even being needed?

  48. Creativity Viral Post author

    This is amazing tutorial. Wow!!! What a genius. Like if you agree

  49. Sameer Khan Post author

    This is amazing information…. Let's try out this… Thanks for sharing ..

  50. Rahul V J Post author

    Epic guide. You guys always make the most actionable content!


Leave a Reply

Your email address will not be published. Required fields are marked *