256 Kilobytes

Do you like “best SEO tools” roundups that are all the same? Then get the fuck off of my board, because this post isn’t garbage. While there are some good SEO and keyword research tools, like this one and this one, there are also many secret tools that you didn’t know could be used to find keywords. Assuming you know how to do keyword research, you can probably even use these to generate value.

Sitemaps

Using the process below, sitemaps can be used by even complete idiots to get a very solid idea of what keywords any site is targeting. As described in this more extensive thread, the general process is to:

  1. Get a site’s sitemap
  2. Fuck around with it until you have a plain list of URLs (this can be automated easily as described in the previously mentioned thread)
  3. Run the site through a tool like this one to generate a CSV with extracted URLs, title tags, and meta descriptions.
    • Note: You can also do this extraction with the IMPORTXML function in Google Sheets by writing a few XPath queries.
  4. Examine the data; there will usually be many easily identifiable keywords. For example, many sites will have their title tags set up as something along the lines of “Brand Name | My Keyword” or “Buy Keyword - It’s the #1 Thing You Can Buy!” which you can then split into separate cells with a spreadsheet function like SPLIT.
  5. Since these keywords might not be in quite the “right” format (e.g., “How You Can Buy Shoes” instead of “How to Buy Shoes”) you can also run these terms through something like Keyword Shitter to get variations that are more “keyword-y” that can then be parsed down by using something like searchvolume.io to remove terms that have no search volume.

And, since you’re analysing competitor sites already, you can also reference this video guide to competitor analysis for more about general competitor analysis. Or don’t; I’m not your dad.

Google Sheets

Google Sheets basically spits in the face of God. Kind of like how the company known as Google basically spits in the face of anyone who supports having laws and borders and sheet. Among other quality hacks, here are some of the ways that Google Sheets can be used for keyword research.

Extracting a Page’s Data

The IMPORTXML function has many uses and can be used to easily extract and import arbitrary data from webpages using arbitrary XPath queries. There are a variety of uses for this functionality in terms of keyword research including, but not limited to:

  • The <title> tag extraction mentioned above in the section on sitemaps:
    • =(A1, "//title")
  • Extracting meta descriptions:
    • =IMPORTXML(A6,"//meta[@name='description']/@content")
  • Extract all anchor texts from an arbitrary webpage (comma separated):
    • =JOIN(", ", IMPORTXML(A4, "//a"))

The process of using Google Sheets for data extraction and making URL requests is also discussed in more depth in this guide on how to use Google Sheets to scrape email addresses from lists of URLs.

Make HTTP Requests as Google

Note that when using IMPORTXML or similar functions, the HTTP request to the target URL is made from Google Drive, not from your own computer. To test and verify this, I made a test request to this URL:

Examining the server logs for this request show the following IP address and user agent for the IMPORTXML function:

  • 66.102.8.71
  • Mozilla/5.0 (compatible; GoogleDocs; apps-spreadsheets; +http://docs.google.com)

Other Tricks

There are a bunch more things that you can do with Google Sheets, various of which are discussed below in this post.

The Browser’s Developer Console

There are many tricks that you can do with the developer console, many of which you probably didn’t know about. Similarly to Google Sheets, the developer console is useful for web scraping. A similar setup is to install a plugin like Scraper, which can be used for basically the same thing.

D-Tier “Guest Post” Sites (PBNs)

Garbage “guest post” link farms are a gold mine for easy keyword ideas. As described by my homeboy, Hash Brown:

Also, if you're ever bored and looking for new niches to compete in crawling these shitty sites and pulling the anchor text is a great source of ideas.

- Hash Brown (My Home Boy), Reply to Identifying Link Farms 101

Hell yeah. And more importantly than anchor text, you can also take the sites’ actual URLs and then analyse them further by either using a tool like Ahrefs’ Site Explorer -> Organic Keywords or some other method, like the sitemap approach described above.

But, aren’t these sites fucking trash run my braincels in Pakistan?

- Probably Everyone

Yes. However, while a huge quantity of these terrible, spammy sites are fucking awful at ranking or making money, they often have found a fucking shit ton of keywords (of varying quality) that you can steal and then do a good job with. This is in contrast to many sites that are actually good, which despite having excellent content, may have very low search volume, since, you know, they’re actually trying to make quality content instead of focusing on keywords.

Your WordPress (Or Whatever) Spam Folder

Similarly to the process described above in regards to “D-Tier “Guest Post” Sites (PBNs),” you can also generate potential niche and keyword ideas through the spam that comes into your WordPress site’s spam/moderation folder. If you need more spam comments, then just fucking pay someone on SEOClerks $5 to GSA blast your site; 7,000 spam bots will follow the spam comments back to your site and start spamming your site comments in massive bulk.

Bing, Yahoo, MyWebSearch, and Non-Google Search Engines

There are a variety of pieces of data that can be scraped directly from search engine result pages for the purpose of, among other processes, keyword research. These pieces of data include, but are not limited to:

  • Number of results for a query
  • The list of pages ranking for a query
  • Searches related to a query

However, scraping Google search result pages is notoriously resource-intensive relative to scraping other search engines’ query results. As described by Hash Brown:

As you can see I have chosen:

  • Bing
  • Yahoo
  • MyWebSearch

> OMFG what a noob these are terrible Y no big G? AKA Google?

Google is hard to scrape. They are very good at detecting robots and it's not worth the effort. These search engines are way easier.

- Hash Brown, This Web 2.0 Guide

While it is easy to not even fucking bother scraping search engines and to rely on external sources like Ahrefs to gather SERP data, it’s substantially easier to scrape other search engines and much of this data extrapolates to Google easily.

Scraping Methods

This scraping can be done with something like Scrapebox (see Hash Brown’s guide above) for scraping in massive bulk.

For more targeted scraping, you can even use Google Sheets. This is what the demo code snippets below use, specifically for scraping Bing search results. These methods can be adjusted to scrape the other search engines mentioned. Interestingly, using IMPORTXML via Google Sheets actually works decently for scraping Google search results as well, probably because the HTTP request is actually being made from Google Sheets on your behalf, rather than from you directly.

Note that there are some edge cases (e.g., a search result where there are no “searches related to” showing), which will result in an “#N/A” error. If you want to have a blank cell instead, you can wrap any of these formulas in the IFERROR function as shown here:

  • =IFERROR([FORMULA], "")

Scraping Number of Results

In Google Sheets, this can be done as shown here:

  • Cell A2 contains a keyword. Ex:
    • “my dad’s car”
  • Cell B2 contains a formula that converts the keyword in A2 to a valid Bing URL:
  • Using the URL generated above, we can then use IMPORTXML and XPath queries to easily extract data. To extract the number of results:
    • =importxml(B2, "//*[@class='sb_count']")
    • 437,000,000 results
  • If you want to convert this from a string of text to a number, you can use this formula:
    • =VALUE(SUBSTITUTE(C2, " results", ""))
    • 437000000

Comparable XPath or other queries can be used with other scraping solutions.

Scraping SERP Results and Position

Doing this through Bing is fairly straightforward. To scrape the (regular/non-snippet or other) results, these formulas can be used:

  • Get titles of SERP positions in order, comma-separated:
    • =JOIN(", ", IMPORTXML(B29, "//*[@class='b_algo']/h2/a"))
  • Get URLs of SERP positions in order, comma-separated:
    • =JOIN(", ", IMPORTXML(B29, "//*[@class='b_algo']/h2/a/@href"))’

Depending on what you’re doing, you will probably want a slightly different format, plausibly involving the TRANSPOSE function.

Google Sheets implementation:

  • To get the “related searches for…” keywords for a Bing query into a cell and comma-separated, this formula can be used:
    • =Substitute(SUBSTITUTE(join(",", IMPORTXML(B2, "//*[@class='b_rs']//li//descendant::text()") ), ", ", " "), " ,", " ")
  • One limitation to doing this in Google Sheets is that--if you want the result to automatically be put into one cell--you do have to write an absolute abomination, crime against God hack involving a series of wrapper and helper functions.

Your Feels

Keyword research tools are fucking retarded. I mean, some of them are of excellent quality, but also, they are robots that are:

  • Attempting to classify an essentially infinite amount of data;
  • While also taking into account bullshit like:
    • Bot traffic;
    • Multiple variations on the same keyword;
    • Incomplete data sets; and
    • Other bullshit

They will often provide you with data that is incomplete or misleading. For example:

  • The keyword “what is the Antikythera Mechanism?” has an estimated 544,000 monthly searches according to Ahrefs. However, it actually has, like, fucking zero monthly searches; the data is entirely thrown off because it was the Google Doodle on the 17th of May, 2017 and got like fifty billion searches over the course of 24 hours.
  • Questions that are fucking impossible to phrase. Data for these types of searches can be substantially underrepresented, since they’re all like “how do you do that one fucking thing in the area” with people using drastically different phrasings. However, an article that covers the topic with some of the more obvious variations and Nice LSI Keywords can bring in more traffic than KW research tools may estimate, due to the way that tools may separately categorize those particularly large quantities of longtail variations.

Is there a search that is probably trash? Is there some shit that people probably are searching for that isn’t showing up in search engines? Don’t be fucking retarded.

Your Browser History

Your browser literally has probably years worth of browser history, including searches. This data is particularly valuable for keyword research because:

  • You probably know at least something about these topics and can put together quality content on them.
  • Many of these topics are likely related to sites that you run.
  • The searches that you’ve personally conducted are often underrepresented by keyword research tools (partially due to the “questions that are fucking impossible to phrase” section under “your feels”).

You can easily extract all of this data from your browser history. In my case, this ended up being around 9,000 searches.

Why don’t you just fucking export your search history directly from your Google account?

- Probably Someone

Because every time I’ve ever tried to export search history from a Google account I want to Buy Best Cheap Handgun in Dubai. You can export your Google data through this page for most Google products/platforms, but there seems to be no option to download specifically your search history. Anyway, here’s how you can get this data easily:

Find and Export Your Browser History

To find and export your browser history:

  • On Chrome, you would normally go to chrome://history/ in your address bar, but apparently Chrome’s lacks a built-in browser history export feature, so I GUESS INSTALL THIS GHETTO FUCKING PLUGIN
  • On FireFox, go to Library -> History -> Show All History -> Import and Backup -> Backup and then export the browser history as JSON

There are no other browsers that I know of.

[Optional] Convert from JSON to a List of URLs

If you need to convert the data from JSON to something that is easier to work with (such as a CSV or XLSX), there are a few fairly easy ways to do this:

  • Use an online “JSON to CSV/Whatever” converter. This one seems to work fairly well. Trying to convert JSON to a spreadsheet is basically a crime against God, since JavaScript Object Notation is used to store Fucking Objects, which can have child attributes, whereas spreadsheets aren’t designed to handle objects. Although this should basically work fine, since your browser history probably doesn’t even have child attributes. Which then raises the question of “why the fuck is the standard for browser history export formatting JSON,” since the average person probably doesn’t even need or want the fucking JSON file.
  • Use a “Extract URL from File” tool. This one seems to work fine. For larger files (e.g., 15MB+), it may lag or crash. You do lose the other metadata associated with your browser history with this method (title tags, timestamps, etcetera), but that data is fucking bullshit and irrelevant to extracting your search engine history anyway.
  • Use a regular expression (or similar). Since the “extract URLs from file” tool above was having a stroke with my 15MB+ search history file, I ended up using the Linux jq utility to extract the JSON “url” values with this command:
    • jq -r ".[] | .url" chrome_history_original.json > chrome-history-urls.txt
  • Use a regular expression (or similar). You could alternatively use a tool like Vim to do some tasks by using some hotkeys, although parsing URLs with regular expressions is notoriously obnoxious due to a massive quantity of edge cases.

How Query Parameters Work with Search Engine Result Pages

Search engine URLs generally have a URL parameter of “q” that indicates what a user searched for. Yahoo uses “p” instead. Amazon uses “k” for their parameter. For example, the search result page for “Some Bullshit” on Google and Bing is as follows:

There are also some other parameters, although they are basically irrelevant for our use case.

Extracting Search History From Query Parameters

To actually extract the “q” parameter from a list of URLs, the easiest method is to:

  1. Put your extracted list of newline-separated URLs into a text file
  2. Run this command from the Linux terminal:
    • cat chrome-history-urls.txt | grep -Po '[?&]q=.*?(&|$)' | perl -pe 's/.*[?&]q=(.*)?(&|$)/\1/' | perl -pe 's/&//s' | perl -pe 's/\+/ /g' | perl -pe 's/\%(\w\w)/chr hex $1/ge' | uniq
  3. An extracted list of all unique decoded “q” parameters are now in a new file called queries.txt. Any leading or trailing whitespace for any queries has also been trimmed.

In my case, this is 5,839 unique queries from my Google Chrome history. If you skip the “Convert from JSON to a List of URLs” step and just pipe the JSON file directly into the terminal command instead of the “chrome-history-urls.txt” file, the result seems to be basically identical (5,875 lines; possibly a few false positives or similar).

Alternate Abomination Method to Extract Query Parameters

Don’t fucking do this; this is AIDS, but it is an alternative.

  1. Paste your list of URLs into Google Sheets (or comparable)
  2. Go to Tools -> Script Editor, paste in this function, and then press CTRL+S to save the script:
    • function urldecode(url) { return decodeURI(url); }
  3. Go back to your spreadsheet, and run this function:
    1. =if(IFERROR(REGEXEXTRACT(substitute(A1, "+", " "), "[?&]q=(.*?)&"), "")="", "", urldecode(IFERROR(REGEXEXTRACT(substitute(A1, "+", " "), "[?&]q=(.*?)&"), "")))
  4. Then, drag the formula down the full column (CTRL+D is a quicker shortcut than manually dragging for something like 45,647 rows)

You might get an error because this absolute abomination hack has a custom function in it, and running a custom function in like fifty fucking thousand cells simultaneously tends to cause cells to throw errors. If that is the case, use some sorting or magic or otherwise to remove irrelevant URLs before running that function.

The Result

You should now have a list of all of your searches. For example, here is what I’ve extracted for some of my most recent searches:

  1. 470k ohm resistor
  2. al gore scandals
  3. brotli https
  4. browser parsing behavior security
  5. burn the coal pay the toll
  6. check da and pa
  7. check if number php
  8. free stock footage
  9. frying pans
  10. gangs in portland oregon
  11. italians
  12. japanese people bowing
  13. quotes about daytime tv
  14. rare clowns
  15. rare matt cutts
  16. rare steak png
  17. sat percentiles 1988
  18. sat score bell curve
  19. soyboys
  20. terry davis vlogs
  21. uhuru proud boys
  22. what percent of websites use https
  23. xpath match emails
  24. yellow tree air freshener racist
  25. Z go to bar 4 pm
  26. zalgo text

Once you have this list of queries, you can either grab whatever you want based on your feels, or do a more classic setup of taking these terms and running them through a combination of Keyword Shitter and searchvolume.io (or whatever) to gather variations and to determine search volume.

In Conclusion

Hell yeah. It’s time to use these secret keyword research tools to make best cheap money online. Regular keyword research tools are gay as fuck:

BROJOB BROJOB! CHOO CHOO

- Probably Someone Who Uses Regular Keyword Research Tools

Do you want to do some high quality keyword research? Then use these high quality secret keyword research tools. Or read other articles like this video guide to onpage SEO or this video guide on literally every link building method. Or alternatively register an account to leave hatemail in the comments.

Users Who Have Downloaded More RAM:
Hash Brown (2 months ago)
Huevos Rancheros (2 months ago)
Scuffed Dog (2 months ago)
yottabyte (2 months ago)
🐏 ⨉ 4
Posted by August R. Garcia 2 months ago

Edit History

• [2019-06-17 15:27 PDT] August R. Garcia (2 months ago)
• [2019-06-17 15:27 PDT] August R. Garcia (2 months ago)
• [2019-06-17 15:27 PDT] August R. Garcia (2 months ago)
🕓 Posted at 17 June, 2019 15:27 PM PDT

Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
🗎 187 🗨 919 🐏 273
Site Owner

Grahew Mattham

August Garcia is some guy who used to sell Viagra on the Internet. He made this website to LARP as a sysadmin while posting about garbage like user-agent spoofing, spintax, the only good keyboard, virtual assitants from Pakistan, links with the rel="nofollow" attributeproxiessin, the developer console, literally every link building method, and other junk.

Available at arg@256kilobytes.com, via Twitter, or arg.256kilobytes.com. Open to business inquiries based on availability.


Account created 9 months ago.
187 posts, 919 comments, and 273 RAMs.

Last active 12 hours ago:
Posted thread [Solved] Correct-Horse-Battery-Staple Implementations (Misc. Languages)

Profile Photo - Hash Brown Hash Brown Internet Activist &... United State of Euro...
🗎 61 🗨 430 🐏 204
Staff

The video was so good I shared it with my friend.

Users Who Have Downloaded More RAM:
August R. Garcia (2 months ago)
Huevos Rancheros (2 months ago)
Scuffed Dog (2 months ago)
🐏 ⨉ 3
Posted by Hash Brown 2 months ago 🕓 Posted at 17 June, 2019 16:38 PM PDT

"THAT DOG IS GETTING RAPED" - Terry A. Davis

Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
🗎 187 🗨 919 🐏 273
Site Owner

Also, most of the content that would be in a "real"/standard "top free SEO tools" guide is summarized here:

Best Free Search Volume Tools

  • SearchVolume.io is the only one to list here. It gives you Keyword Planner data for free with no account and the data is the actual KW Planner values, not the rounded ranges (ex: "100-100 searches"). Can run up to 800 keywords per request. 

Best Free Keyword Research Tools

  • Keyword Shitter is the main one that I use. Enter seed keyword and get basically infinite related suggestions.
  • Answer the Public is another decent option. Kind of like KW Shitter, but focused on clusters of keyword types, such as:
    • Question keywords (are/will/can/where/which/who/what/when/why/how)
    • Keywords by letter
    • "X vs. Y" keywords
    • "Preposition" keywords (is/to/near/without/can/for/with)

Using these Free Keyword Research Tools

The basic process for using these is disucssed more in the main/"big" keyword research guide.

Download more RAM. 🐏 ⨉ 0 Posted by August R. Garcia 2 months ago 🕓 Posted at 18 June, 2019 10:19 AM PDT

Sir, I can do you a nice SEO.

Post a New Comment

To leave a comment, login to your account or create an account.

Do you like having a good time?

Read Quality Articles

Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

View Articles →

Argue with People on the Internet

Use your account to explain why people are wrong on the Internet forum.

View Forum →

Vandalize the Wiki

Or don't. I'm not your dad.

View Wiki →

Ask and/or Answer Questions

If someone asks a terrible question, post a LMGTFY link.

View Answers →

Make Some Money

Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

Register an Account
You can also login to an existing account or recover your password. All use of this site is subject to terms outlined in the terms of service and privacy policy.