How To Automate And Speed Up Your SEO With The Serpstat API: A Step-By-Step Guide From Flatfy

The area of responsibility and diversity of tasks of an SEO specialist keeps expanding while the time remains limited. In this article, I will show you how to use the Serpstat API using real estate projects as an example. I'll also try to prove that even with a little knowledge of development and the ability to use Google and StackOverflow, you can simplify your daily routine work and gain an advantage over competitors.
Meet Flatfy
To work effectively on all of our projects, the team is continually testing various tools for digital marketing, especially those that have an API.
Serpstat now has a massive database of keywords and links, but most importantly - one of the most convenient APIs. This is necessary because working with a large number of sites requires a constant storing of a lot of data in one place and analyzing it.

What We Use Serpstat For
- Keyword research and clustering/filtering.
- Monitoring changes on the website, its backlink profile, SEO parameters, competitors.
- Generating texts and template pages.
- Work with the backlink profile, link-building strategy, outreach.
- Other routine tasks.
How To Use The Serpstat API In Google Spreadsheets Without Plugins
In this section, I will intentionally not mention anything that requires programming skills. All you need is the mastery of Google Search. I want to inspire beginners to learn and grow. So, let's start.
And what do SEOs love most? Right! Tables. In this article, we will use Google Spreadsheets.
To work with data in tables, we use functions. Since the "= serpstat (give me data)" function is missing, we will create our own.
If we google "google sheets custom function," we'll find a manual about App Script.
- token — your API key from Serpstat profile.

- query, se (search engine) — query parameters for this report.
Now, let's put it to practice.



How To Create A Dashboard For Competitor Analysis
The easiest way to use the API is to create various reports. Let's create a simple Dashboard using the "Formula 2" code to analyze competitors with the following parameters:
- the number of keywords for which they rank;
- website traffic;
- the number of ads in Google Ads.
This is what the mini-report looks like with the number of keywords, traffic, and ads in contextual advertising for competitors in real estate.

For instance, the variables would be sites (column A), API key Serpstat (cell G2), and search engine database (cell H2).
Now we need to make requests to one of the attributes from the JSON array. JSON has a nesting (tree structure), which we will share with slashes.
For example, you need to get the number of keywords on a site. To do this, we will use the formula = IMPORTJSON (F2, "result/keywords") (cell B1). If we want to get traffic, then change our request to = IMPORTJSON (F2, "result/traff").

How To Collect Data Quickly
Let's use the "formula 1" code.
It has several features that we can use. You can see all these features in the pop-up when entering the formula. A crucial feature for us is the ability to get several JSON keys at once and receive data on them.
So, let's say we would like to collect the top 100 search results for several search queries.
I personally do this very often for a quick check of the keyword clustering. There is a search query, and I don't know for which page it will work best. I gather the top of search results for several queries and compare their intersection directly in the spreadsheet. Thus, you don't need to run KeyAssort to cluster multiple requests, and you don't need to spend time opening numerous tabs with Google to compare the tops of SERPs.
Go to http://api.serpstat.com/v3/keyword_top and type in = ImportJSON (F2,"/result/top/position,/result/top/url") - note that we used two keys separated by commas: /result/top/position and /result/top/url.
Thus, we get two columns with data: positions from the top 100 and the URLs.

In this example, there's another detail — additional parameters. noHeaders allows us to remove the extra headers and get just the data. In column A, we need to insert the keywords of interest, and then "stretch" the formula further in column B.

Conclusion
Flatfy has a rule — if there's a repeating task and it can be automated, we automate it. Many companies buy new equipment for employees to increase their productivity, but few people think about how much time an employee can spend collecting data instead of analyzing it, and switching between tabs instead of having all the data in one place.
By automating data collection, we improve our performance and free up more time for high-priority tasks, and project development.
Using Serpstat API saves hours of work time. Using such simple automation, you can save at least one working hour per day for an SEO specialist.
Speed up your search marketing growth with Serpstat!
Keyword and backlink opportunities, competitors' online strategy, daily rankings and SEO-related issues.
A pack of tools for reducing your time on SEO tasks.
Discover More SEO Tools
Backlink Cheсker
Backlinks checking for any site. Increase the power of your backlink profile
API for SEO
Search big data and get results using SEO API
Competitor Website Analytics
Complete analysis of competitors' websites for SEO and PPC
Keyword Rank Checker
Google Keyword Rankings Checker - gain valuable insights into your website's search engine rankings
Recommended posts
Cases, life hacks, researches, and useful articles
Don’t you have time to follow the news? No worries! Our editor will choose articles that will definitely help you with your work. Join our cozy community :)
By clicking the button, you agree to our privacy policy.