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

If a couple of years ago it was possible to solve all problems through Excel, now it's worth paying attention to studying JS, Python, and basic knowledge of HTML, CSS, command line, HTTP. In our work, we try to automate:

My name is Vlad Morgun, and I'm the SEO Team Lead at Flatfy. Our platform helps millions of users from 30 countries find accommodations. The primary vector of development is secondary residential real estate in mega-cities. We are working on creating a fast search engine taking into account all the wishes of users to provide a relevant answer.

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.

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.

An SEO specialist must always strive to self-improvement since this is a dynamic field of activity that requires multilateral knowledge, often heavily tied to data analysis and experience. The more and faster you gain experience, the better, and therefore it is vital to be able to automate processes to free up time.

Further, in this section, we will do everything as if I didn't know about development, but could search on Google. I want to inspire beginners to develop and learn. So, let's go:

Let's figure out what it is:

From the definition, we already see that we will interact with Serpstat service without an interface via another program. This will allow you not to waste time logging into Serpstat, selecting the necessary reports and filters, uploading them, and only then working with them.

And what do SEOs love most? Correctly! Tables. In this article, we will use Google Spreadsheets.

To work with data in tables, we use functions, but in the function "= serpstat (give me data)" is missing, and therefore we create our own.

If we google "google sheets custom function," we'll find a manual that talks about App Script.

Next, we move on to the concept of endpoint API.

In our case, this is just a link with get parameters. For example, api.serpstat.com/v3/domain_info?query=example.com&token=XXX&se=XXX, where:

Using this URL, you can get the necessary data in JSON format.

Let's take a ready-made script for working with JSON in Google Spreadsheet (more precisely, google "google spreadsheet JSON import"):

Code "Formula 1":

Or a simpler "Formula 2" code:

I take someone's ready-made scripts to show that with a great desire, you can automate SEO tasks and with minimal programming knowledge, using other people's experience.

We put that theory to the test.

Every SEO should know what his/her competitors do and how the niche is developing. If you don't follow this, then you can miss the moment when the rules in the niche become different, and you have to change a lot of things. Because the sooner you notice changes in tops, on competitors' websites, the better.

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:

Even such simple indicators can show us how much our project differs from competitors, and if you save data, for example, by months, you can track the dynamics of the project.

This is how the mini-report looks like with the number of keywords, traffic, and ads in contextual advertising for competitors in real estate.

Next, we collect the URLs in column F, which we will use with the formula = CONCATENATE ("http://api.serpstat.com/v3/domain_info?query=",A2,"&token=",G$2,"& se=", $2). The $ sign in front of the cell number means "don't change the number".

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").

To solve your tasks, you can look in Serpstat help for possible keys (in our example keywords, traff, ads) from answers to requests and find out what values you can get.

Now let's look at the task when you need to get data quickly, but not use the interface. For example, get tops on demand or the frequency of specific keywords.

For example, take the code "formula 1".

It has several features that we will need. You can see all these features in the pop-up when entering the formula and in the comments of the code developer.
An important feature for us is the ability to get several JSON keys at once and receive data on them.

We want to find out the top 100 for several queries quickly. For example, I do this very often for a quick check for the clustering of queries. There is a request, and I don't know which page it will work best for. I get tops 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 tops.

Go to http://api.serpstat.com/v3/keyword_top and make the formula = 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.

Perhaps you want to check the frequency of requests quickly. Everything is similar here, but we use the request https://api.serpstat.com/v3/keyword_info and the formula = ImportJSON(F2,"/result/region_queries_count","noHeaders").

In this example, there is another feature - additional parameters. noHeaders allows us to remove the extra headers and get just the data. We write in column A the keys we need for verification, and "stretch" the formula further in column B.

By the way, for those who want to use the API, but don't have programming skills, the guys from Serpstat also developed the API Console, with which you can easily and quickly get all the necessary data. See the articles on the Console below.

Thus, with the help of Google, minimal knowledge in development, excellent support from Serpstat, and, most importantly, desire, you can get any data very quickly.

Flatfy has a rule - if a task arises several times, and it can be automated, we automate it. Many companies buy new equipment for employees to increase productivity, but few people think how much time an employee can spend collecting data instead of analyzing it and switching tabs instead of viewing all the data in one place.

By automating the processes of data collection, we improve the quality of tasks, get more time for 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.