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

Summary

This tutorial by Sam Oh from Ahrefs teaches SEOs how to quickly identify link-building prospects and collect their data using Google Sheets and Ahrefs' Content Explorer. It covers finding URLs, author names, and leveraging tools like Hunter for email discovery and NeverBounce for email validation. The video provides step-by-step instructions, including the use of Google Sheets formulas like IFERROR, ARRAYFORMULA, and QUERY, to streamline the process of creating a link-building campaign.

Takeaways

• 🔍 The video provides a tutorial on creating a link building template using Google Sheets and Ahrefs to find prospects and email addresses quickly.
• 📊 Ahrefs' Content Explorer is highlighted as a tool to search through content and gather SEO and social metrics, including author names for potential link building contacts.
• 💼 The script emphasizes the importance of not limiting the search to websites with 'write for us' pages, as these may receive fewer guest post pitches and thus less competition.
• 📝 The tutorial explains the use of Google Sheets formulas, such as IFERROR and ARRAYFORMULA, to handle errors and apply formulas across multiple rows efficiently.
• 📑 The process involves filtering and exporting data from Ahrefs' Content Explorer, then importing it into Google Sheets for further manipulation and analysis.
• 📋 The use of QUERY function in Google Sheets is demonstrated to parse specific columns of data from the raw dataset for a cleaner, more targeted list.
• 📝 The script introduces methods to split author names into first and last names using complex formulas in Google Sheets, which is essential for email finding tools.
• 🔎 The tutorial incorporates Hunter's Google Sheets add-on for finding email addresses based on first, last names, and domain, streamlining the email discovery process.
• ✉️ Email validation is discussed using NeverBounce to ensure the emails found are valid, improving the quality of the outreach list.
• 📊 The use of VLOOKUP function is shown to match email validation statuses with the master sheet, helping to identify which emails are confirmed as valid.
• 📈 The video concludes with tips on using filters and COUNTIF function in Google Sheets to organize and quantify the valid emails for link building outreach.

Q & A

What is the main purpose of the tutorial presented in the script?

The main purpose of the tutorial is to teach SEOs how to create a link building template using Google Sheets and Ahrefs' Content Explorer to find prospects and valid email addresses quickly.

What are the three data points that need to be found quickly for a link building campaign according to the script?

The three data points are the URL or domain of the prospect, someone's first name, and their last name.

Why is Ahrefs' Content Explorer useful for finding author names for link building?

Ahrefs' Content Explorer is useful because it allows users to search through over a billion pages of content and provides SEO and social metrics, including the names of authors, which is valuable for automation in link building.

What is the significance of not limiting guest-posting opportunities to websites with 'write for us' pages?

Websites without 'write for us' pages likely receive fewer guest post pitches, resulting in less competition and potentially a higher chance of acceptance for well-written content.

How does the script suggest filtering the search results in Ahrefs' Content Explorer for a guest-posting campaign?

The script suggests using filters such as 'One page per domain', 'English' language, 'Only live pages', and setting a 'Domain Rating' filter between 50 and 60.

What are IFERROR and ARRAYFORMULA functions in Google Sheets, and how are they used in the tutorial?

IFERROR is used to set a default value if a formula returns an error, preventing the display of error messages. ARRAYFORMULA allows a single formula to be applied across multiple rows without the need to drag it down.

How is the QUERY function used in the script to parse specific columns from the raw data?

The QUERY function is used to extract data from a specified range of cells with a query similar to SQL, allowing the selection of specific columns and the addition of WHERE conditions to filter the data.

What steps are taken to separate an author's first and last name into two separate columns in the script?

The script uses a combination of LEFT, FIND, TRIM, RIGHT, SUBSTITUTE, and REPT functions, along with ARRAYFORMULA and IFERROR, to parse the author's name into first and last name columns.

How does the script suggest finding email addresses for the authors using Google Sheets?

The script recommends using Hunter's Google Sheets add-on to find email addresses based on the first name, last name, and domain name provided in the spreadsheet.

What is the process for validating the found email addresses and integrating the validation status into the master sheet?

The process involves using the UNIQUE function to create a list of unique email addresses, uploading this list to NeverBounce for validation, and then downloading the results. The VLOOKUP function is then used to match the validation statuses with the master sheet.

What additional advice does the script provide for using the created link building template effectively?

The script advises to manually check sites for metrics like traffic and relevance before pitching, and to use the formulas provided in the pinned comment for further customization and efficiency.

Outlines

00:00

🔍 Link Building with Ahrefs and Google Sheets

This paragraph introduces a tutorial on creating a link building template using Google Sheets and Ahrefs. The goal is to quickly find link prospects and collect data such as URLs, first and last names of authors, within 15 minutes. The tutorial aims to teach both Google Sheets formulas and their practical application in link building. Ahrefs' Content Explorer is highlighted as a tool for finding relevant content and author information, with a focus on guest-posting opportunities. The process includes setting up filters for language, live pages, and domain ratings, and exporting the data in CSV format for further use in the template.

05:02

📝 Parsing Data and Crafting the Master Template

The second paragraph delves into the process of parsing the raw data from Ahrefs' Content Explorer to create a master guest post template. It involves using Google Sheets functions like QUERY to select specific columns and WHERE clauses to filter out unwanted data. The paragraph explains how to separate author names into first and last names using a combination of LEFT, FIND, TRIM, RIGHT, SUBSTITUTE, and REPT functions, all wrapped in ARRAYFORMULA and IFERROR for clean results. It also introduces the use of Hunter's Google Sheets add-on for finding email addresses and NeverBounce for email validation, detailing the steps to integrate these tools into the template.

10:03

📧 Validating Emails and Finalizing the Link Building Template

The final paragraph focuses on the steps to validate email addresses found by Hunter and match them with the master sheet. It describes using the VLOOKUP function to search for email addresses in the validation sheet and return their status. The process includes ARRAYFORMULA for applying the formula across the column, IFERROR to handle errors, and adding a filter to the table to display only valid emails. The paragraph concludes with a COUNTIF function to count the number of valid emails and a suggestion to manually find emails not discovered through automation. It also encourages viewers to like, share, subscribe, and request more tutorials, providing a link in the description to copy the sheet to their Google Drive.

Keywords

💡Link Building

Link building is the process of acquiring hyperlinks from other websites to your own. It is a key aspect of SEO, as it helps to improve a website's visibility and ranking in search engine results. In the video, link building is the main theme, with the tutorial focusing on how to efficiently find prospects and email addresses for outreach in under 15 minutes.

💡Google Sheets

Google Sheets is a web-based spreadsheet program that is part of Google's online office suite. It is used for data manipulation, analysis, and visualization. In the context of the video, Google Sheets is utilized to create a template for link building, applying formulas and functions to automate the process of finding prospects and their contact information.

💡Ahrefs

Ahrefs is a platform that provides SEO tools to help users grow their search traffic, research competitors, and dominate their niche. The video mentions Ahrefs as the tool used to find link prospects, emphasizing its Content Explorer feature for searching through content and retrieving SEO and social metrics.

💡Content Explorer

Ahrefs' Content Explorer is a tool that allows users to search through over a billion pages of content and get SEO and social metrics for each page. It is highlighted in the script for its ability to show author names, which is crucial for the automation of the link building process described in the video.

💡Google Sheets Formulas

Google Sheets formulas are used to perform calculations and manipulate data within the spreadsheet. The video provides a tutorial on using specific formulas like IFERROR and ARRAYFORMULA, which are essential for creating a link building system that can handle errors gracefully and apply formulas across multiple rows efficiently.

💡IFERROR

IFERROR is a Google Sheets function that returns a specified value if a formula generates an error. In the video, it is used to handle errors in calculations by providing a default value or an empty cell, ensuring that the spreadsheet remains clean and error-free.

💡ARRAYFORMULA

ARRAYFORMULA is a Google Sheets function that applies a formula to an entire range of cells without the need to manually fill down the formula. The video script uses ARRAYFORMULA to streamline the process of applying formulas to data, such as dividing values or extracting information from a dataset.

💡QUERY Function

The QUERY function in Google Sheets is used to extract specific data from a range of cells using a language similar to SQL. In the video, QUERY is employed to parse raw data from the Content Explorer export, selecting specific columns and applying conditions to narrow down the data for the link building campaign.

💡Hunter

Hunter is an email verification service with a Google Sheets add-on that allows users to find email addresses based on first and last names and domain names. The video demonstrates using Hunter to find emails for the link building prospects identified through Ahrefs' Content Explorer.

💡Email Validation

Email validation is the process of checking if an email address is valid and capable of receiving emails. The video script describes using a tool called NeverBounce to validate the email addresses found by Hunter, ensuring that the outreach efforts are directed to active and legitimate email addresses.

💡VLOOKUP

VLOOKUP is a Google Sheets function used to look up values in a table based on a search key. In the context of the video, VLOOKUP is utilized to match email validation statuses with the master sheet, allowing the user to identify which emails are valid and which are not, thus refining the list for link building outreach.

Highlights

Creating a link building template to find prospects and valid email addresses in under 15 minutes.

Introduction to using Google Sheets and Ahrefs for SEO, competitor research, and niche domination.

Learning Google Sheets formulas for practical application in link building systems.

Finding link prospects by collecting the URL, domain, first name, and last name of prospects.

Utilizing Ahrefs' Content Explorer to search a billion pages for SEO and social metrics.

The importance of author names in Content Explorer for automation in link building.

Starting a guest-posting campaign with relevant topics and title search filters.

Filtering for guest-posting opportunities without limiting to 'write for us' pages.

Exporting search results from Ahrefs as a CSV for further analysis in Google Sheets.

Using IFERROR to handle errors and ARRAYFORMULA for applying formulas across multiple rows.

Importing and parsing data from the CSV into a new Google Sheet for reference.

Using QUERY function in Google Sheets to extract specific columns from raw data.

Cleaning the list by adding WHERE clauses to exclude one-word author names.

Parsing author names into first and last name columns with complex formulas.

Using Hunter's Google Sheets add-on to find email addresses based on first and last names and domain.

Validating email addresses with NeverBounce and downloading the results.

Matching email validation statuses with the master sheet using VLOOKUP function.

Filtering the table to show only valid emails and counting them with COUNTIF function.

Recommendation to manually find emails not discovered through automation.

Providing formulas in the pinned comment and a link to copy the Google Sheet template.

Encouraging viewers to like, share, subscribe, and request more tutorials.

Transcripts

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.