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

Start Guest Posting in 15 Minutes

Start Guest Posting in 15 Minutes

Welcome to Allbloggingtip Guest Posting is a good way to get backlinks from other websites. Today, we're going to create a link building template, where you can find prospects and valid email addresses in under 15 minutes.

What's up, Guys?

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 is to 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 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 well is 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.

What is a Guest Post?

Guest Post is nothing but an article that a person submits it to another website. People submit their article to promote their content, to get backlinks from another website, to share their views, etc. Most of the people do a guest post to get quality backlinks from other websites. Guest Post is a very good way to get quality backlinks from high domain authority websites.

Read here our article - Create Free Backlinks 2019 | Step By Step process to build high-quality backlinks for your website [Part 1]

Content Explorer

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, well 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 our pages. In fact, websites without write for us page 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 its 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. We now have our data so its time to move on to step 2, which is to create our template.

Formulas

Now, as I create the sheet, there are two functions that I'll use frequently. And these are IFERROR and ARRAY FORMULA. 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 the ARRAY FORMULA. 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, Ill wrap the formula with ARRAY FORMULA. 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? Well, 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 lets 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 the 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 well 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 well is 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 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 lets set up a new sheet and call it Master Guest Post. And within cell A1, Ill 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 Das 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 Ill 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 lets 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, Ill add…WHERE D is not null, meaning where column D from our raw data, which is the author's names, doesn't have a value.Looks much better. But if you look at this data again, you'll see someone-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 the last name to find a targeted email.

So well remove these by adjusting our query and adding to the WHERE clause. I'll type AND D contains a single quote, space, and close the single quote. And the reason why is because there's a space between the first and last names. Much better. Alright, the next thing we need to do is parse the author's name into two columns: their first and last names. 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 its 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, well use both the LEFT and FIND functions. And to find the last name, well use TRIM, RIGHT, SUBSTITUTE and REPT, which will grab the last word in the author's name.

And to avoid dragging down, well wrap the formula using ARRAY FORMULA, 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, well 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 HunterAdd the tool, and make sure you've signed up for an account. The 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. The reason being, Hunter will take the full URL and automatically use the root domain or subdomain when searching for an email address. And for the company name, just leave it blank since we already have the domain name column set. Once you're finished, click on the 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 these ones 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 were 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 a credit card. When its done verifying emails, click Download, then select All results. Finally, download the CSV file. Now, go back to your Email Validation sheet, and were going to import the file from NeverBounce here. So click on File Import Upload, and then lets drag and drop that file here. Now, well 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.

VLOOKUP

Now, were 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 value using a search key—you can then return a matching value from a specific cell in that range. So looking at the syntax, its 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, well type in 2, since its 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 ARRAY FORMULA. 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, well 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. So if you have an Ahrefs account, you can follow the instructions on the Instructions tab, make reference to this article for specific steps, and start building links fast. Now, if you found this article to be helpful, make sure to share it. And let me know in the comments if you want to see more articles like this one. So keep grinding away, work smarter and harder, and I'll see you in the next article.


We are open for Guest Post, to submit your Guest Post - Click Here

Post a comment

0 Comments