link building with google sheets start guest posti with timestamps YoutubeScribe.com

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

本文档为站内渲染。原始文件本地路径：saas/source/seo-llm/raw-seo知识库-seo方法论-youtube-seo-scripts-ahrefs-scripts-link-bu-a216d6.txt（仅本地保留，不入库不部署）