This site uses cookies and other tracking technologies to make possible your usage of the website, assist with navigation and your ability to provide feedback, analyse your use of our products and services, assist with our promotional and marketing efforts, and provide better user experience.

By using the website, you agree to our Privacy policy

Accept and continue

Report a bug

Cancel
7642   25   16
SEO 7 min read March 22, 2017

APIcal Way To Collect All Relevant Keywords In Your Niche

How To Set Up An Affiliate Marketing System For eCommerce Store

Ivan Kutas
Deputy Head of SEO at Netpeak
How to expand your keyword list quickly and easily? It's a question that every SEO who works on major projects asks himself sooner or later. My team and I hate wasting our time. Thus we developed a unique script for Google Spreadsheets based on Serpstat's API. In this article, we're sharing this script with you to make your life a bit easier. All you need is Google Spreadsheets and API access.
This script will be useful for PPC specialists, who are searching for new keywords for their ad campaigns, as well. Copy this spreadsheet to your Google Drive to start using it.

What data can you obtain via this script?

This script includes only those Serpstat API methods that are required for our goal. Here's what data you can get using this script:

  • keywords that URL ranks for in Google search results;
  • keywords that competitors' URLs rank for in top-10 but that are missing from the queried URL;
  • results of full-text keyword search;
  • results of full-text search suggestions search;
  • related keywords (is available for all tariff plans except plan A).
The whole list of reports you can get via API with request examples is listed here.

Why Google Apps Script?

Google Apps Script – is user-friendly and easy-to-use. Let's cover its pros and cons.

Pros:

It's a cloud-based tool;

Independence of OS;

It's shareable, just copy it to your Google Drive.

Cons:

The script's working time is limited (no more than 6 minutes);

Data transfer capacity is also limited. However, this limitation affects only the method of getting keywords' info.

How to use this script?

1
Open the script and copy it to your Google Drive.
2
Open the first sheet called "Settings" and enter your API token, which can be found in your profile. Then choose the database you need.
3
Add the list of keywords and/or URL you want to get data for. Don't leave blank lines as the script stops working if it finds the gaps.
4
Set the filters to get the desired results.
5
Choose the method you need.
Click on Serpstat menu item and choose the method you need. Moreover, you can run multiple methods.

What methods are covered in this script?

Check queries quantity

Use this method to check the number of queries left for today. You'll get such a pop-up:

Get keys by URLs

This method returns keywords that URL ranks for in Google search results. As a result, you'll get the table with the following data on "Res by URL" sheet:

  • found keywords;
  • proper spelling if there are typos;
  • PPC competition;
  • volume;
  • toponyms;
  • SERP elements;
  • URL that is used for this query.

Get missing keys by URLs

Shows a list of keywords that competitors' URLs rank for in top-10 but that are missing from the queried page. You'll get the following list of data on "Res Missing by URL" sheet:

  • found keywords;
  • PPC competition;
  • volume;
  • toponyms;
  • SERP elements;
  • connection strength, which shows how many competitors from top-20 search results have this keyword.

Get keys by keyword

This method uses a full-text search to find all keywords that match the queried term. For every keyword found you'll see its volume, CPC, and level of competition. Here's what you'll get on "Res by Key" sheet:

  • found keywords;
  • proper spelling if there are typos;
  • PPC competition;
  • volume;
  • toponyms;
  • SERP elements;
  • social networks in search results;
  • the keyword that was analyzed.

Get suggestions by keyword

This report lists autocomplete suggestions for the keyword you requested (full-text search). Here's what data you'll find on "Res Suggestion" sheet:

  • found keywords;
  • toponyms;
  • the keyword that was analyzed.

Get related keys by keyword

This report gives you a comprehensive list of related keywords whose SERP is similar to the one the requested keyword has (only for B and C plans).

Finally, you'll get the following data on "Res Related" sheet:

  • found keywords;
  • proper spelling if there are typos;
  • PPC competition;
  • volume;
  • toponyms;
  • SERP elements;
  • connection strength, which shows how many mutual pages from top-30 search results the initial and the found keyword have;
  • the keyword that was analyzed.

Get keywords info

This report provides you with the keyword overview showing its volume, CPC, and level of competition. This script can process just 280 keywords at a time because of Google Apps Script limitations.

The data you'll find on "Key Info" sheet:

  • keywords;
  • PPC competition;
  • volume;
  • toponyms;
  • SERP elements;
  • social networks in search results;
  • CPC;
  • the number of found results.
As I've already said you can run multiple methods, but mind that it's better not to run more than 2, because of API's limitation of 10 simultaneous requests/second.
Let's analyze the results. For 12 URLs and 25 keywords, I got 2 352 relevant keywords for 2 minutes. The filter setting that I used is on the right. As original keywords and URLs are listed as well, we can easily cluster the gained keywords.

How to improve this script?

This script isn't perfect, it may be further developed and optimized, while even now it works smoothly. If you have any ideas how to improve this script, share your ideas in comments. Contact me via Facebook if you want to add other parameters to this script.
Check out this video vlog to learn more about Serpsta's API:
Found an error? Select it and press Ctrl + Enter to tell us

Recommended posts

Subscribe to our newsletter
Keep up to date with our latest news, events and blog posts!
Sign In Free Sign Up

You’ve reached your query limit.

Or email
Forgot password?
Or email
Back To Login

Don’t worry! Just fill in your email and we’ll send over your password.

Are you sure?
Please pick the project to work on

Personal demonstration

Serpstat is all about saving time, and we want to save yours! One of our specialists will contact you and discuss options going forward.

These may include a personal demonstration, a trial period, comprehensive training articles & webinar recordings, and custom advice from a Serpstat specialist. It is our goal to make you feel comfortable while using Serpstat.

Name
Email
Phone
We are glad of your comment

Upgrade your plan

Sign Up Free

Спасибо, мы с вами свяжемся в ближайшее время

Invite
E-mail
Role
Message
Optional

You have run out of limits

You have reached the limit for the number of created projects. You cannot create new projects unless you increase the limits or delete existing projects.

I want more limits

Christmas is a time for miracles.

You are almost on the finish line of our Christmas quest. The last brick of your lego-promocode is left on the way up 55% discount.

Did not find previous lego-bricks? Fill the form anyway.

Name
Email
Phone