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.