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

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.

Leave a Reply

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