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.