How to find a shitload of sites quickly with Google sheets (and Bing)

How to find a shitload of sites quickly with Google sheets (and Bing)

I keep seeing people talk about how you can’t automate any part of link building strategy, so I wanted to write a quick thing about why that is nonsense.

I’ll start by saying that I understand the premise of such statements. You probably shouldn’t try to automate every aspect of link acquisition, at least not for any website that you care about long-term. There’s also absolutely zero substitute for highly targeted, and well researched link campaigns.

However, you can still get great links with relatively minimal work. Here’s just one way you can do that, using Google sheets and Bing (because Google are giant assholes sometimes, as you already know).

Let’s say we’re building links for an ecommerce store that sells dog toys. One of the first things that comes to my mind right off the bat is that there are tons of different breeds of dogs, which means that there are tons of different breeds of dog websites. Cool! Except maybe not so cool because it’s going to take me forever to comb through all of those, right? Nope.

Okay so first thing I’m gonna do is Google “list of dog breeds.” Then I’ll copy/paste the list of dog breeds I found into a Google doc.

screen-shot-2016-09-27-at-8-40-57-pm

Cool. Now I’ve got a list of 199 dog breeds (AKA keywords) in this sheet. Next thing I’m gonna do is highlight all of them again and click copy. Then delete them from the doc (because we’re going to reuse the cells they’re currently occupying). Next, right click on the A1 cell, hit paste special and then paste transpose.

screen-shot-2016-09-27-at-8-47-06-pm

This will paste that list of dog breeds – ahem, keywords – horizontally. Since I can’t screencap 199 columns, you’re just gonna have to believe me that this list extends all the way to column GQ.

screen-shot-2016-09-27-at-8-52-12-pm

Cool. Now what?

Well, here’s where it gets fun.

The following is a Google sheets function you can use to scrape Bing results for each of those dog breeds.

=importxml("https://www.bing.com/search?q=" & [cell #] & "+intitle%3alinks&count=50", "//h2/a/@href")

This will return the top 50 (because 100 doesn’t seem to work for some reason and not every query will return up to 50 results) Bing results for the query (dog breed) intitle:links. Why not inurl:? Well, because Bing sucks and intitle: works almost as well, at least for this purpose. Why not use Google results instead? Because somehow Google has managed to suck even more than Bing at something.

So what you’re gonna want to do with that function is replace the red text with the cell number of each cell containing a dog breed (keyword).

screen-shot-2016-09-27-at-9-11-48-pm

It will then take a second or so to load the results from that search.

screen-shot-2016-09-27-at-9-16-17-pm

Now, see that little blue square at the bottom right corner of the highlighted cell? Click and drag that all the way to column GQ.

screen-shot-2016-09-27-at-9-20-26-pm

It will probably take a while for every query to load so you might end up with something that looks like this for a while, but if you wait a few minutes (sometimes it can take an hour or more for really big jobs like this one) everything should load for you. I like to take off for lunch and when I come back all of the data has usually loaded. Even if some never populate, we already have a few hundred target sites to sort through.

screen-shot-2016-09-27-at-9-30-03-pm

At this point, it’s really up to you what you want to do with this data, but note that not every link we have here will be a viable target. You’re still going to need to examine the sites to determine which will be worthwhile prospects, but that can be done relatively quickly with some filtering in Google Sheets.

My first step would be combining all of the sites into one column and then I would set up a filter for all sites containing “links” in the URL. Then, sort A-Z to group all duplicate domains together. After that, it might take 20 minutes to scroll through, removing irrelevant URLs and duplicate results.

After I sorted through this particular batch of sites, I ended up with about 500 viable targets.

Of course, you’re still going to need to find contact info for these websites and how you choose to contact them is up to you, but there are ways to automate those as well. You’re also going to need to come up with at least a little bit of strategy on your own! 😉

Here is a quick function I use to pull email addresses from target pages (note: it pulls any email address off of a page which won’t necessarily be the email address you should be contacting so use wisely.):
=iferror(substitute(IMPORTXML([cell #],"//a[contains(@href, '@')]/@href"),"mailto:",""),"No Email Found")

So, there’s just one way you can automate an aspect of link building.

Here’s another way you could use this:

Let’s say I’m building links for a SaaS web application. I would go to a decent SaaS tools directory and copy all of their category titles much like I did with dog breeds. From there I would set up a similar Bing search for each of those categories. You can also play around with the search strings, of course. For the dog breeds, I could have added “breeder” to each of those search strings to come up with an entirely different list of results. For this SaaS example, you could add things like “startup” or “tools” or even “directory” to reveal more useful results, depending on what you’re after.

screen-shot-2016-09-27-at-10-32-15-pm

Look at all those keywords!

One final thing worth mentioning:
You can scrape Google results in a similar (although much slower, since you will need to do it one at a time) way with this chrome extension.


One Reply to “How to find a shitload of sites quickly with Google sheets (and Bing)”

Leave a Reply

Your email address will not be published. Required fields are marked *