How To Collect A Semantic Core In 10 Minutes (And 5 More SEO Cases)
Andrey Belousov
Growth Hacker at Serpstat
The work of an SEO specialist requires a large variety of tools, as it involves the fulfillment of many diverse tasks: collecting keywords, setting up linking, analyzing many SEO indicators. Today I will tell you about our API console, which can become an excellent assistant for SEO and can automate routine tasks. API saves 23 hours per month: now calculate how much your specialist's hour costs and how much money you'll save. Interesting? Let's see how to use it :)
Contents
- API console and its advantages
- How to find out how much junk traffic a domain has?
- How to see the share of commercial domain requests?
- How to find out the amount of branded traffic from a domain?
- How to collect a huge semantic core in 10 minutes?
- How to build an internal linking on the site?
- How to estimate SEO monetary value?
- How are the limits spent?
- Conclusion
API console and its advantages
Although Serpstat has many functions, solving daily SEO tasks can be very demanding. If you need a quick analysis of large amount of SEO data, then the Serpstat API is the perfect solution for you.
High-quality keyword analysis, checking results in seconds, fast competitive analysis and finding unusual solutions are just some of the advantages offered by the Serpstat API. All the functions are at your fingertips: from the analysis of a single site, to scanning involving complex estimates. You can find more information about using the interface in the manual.
High-quality keyword analysis, checking results in seconds, fast competitive analysis and finding unusual solutions are just some of the advantages offered by the Serpstat API. All the functions are at your fingertips: from the analysis of a single site, to scanning involving complex estimates. You can find more information about using the interface in the manual.
However, to make your work even easier, we have developed the Seprstat API Console. It is an easy-to-use tool that has new methods that are not available with standard use of the API.
Attention! This tool is on the Pre-Alpha development version. It is an example of using the Serpstat API. Each user can create a similar script for their individual needs.
Advantages of the console:
You do not need programming knowledge to use the API.
Batch data upload on multiple domains, URLs, keywords.
Lightning-fast data analysis and obtaining the result.
Automatic combining of analysis results across multiple reports.
The presence of convenient filters to sort the received data.
A lot of training materials on working with the console and use cases on our blog ;)
You can use the API as part of your plan with the following features:
I will show you some interesting SEO cases that you can implement with the help of our API console.
How to find out how much junk traffic a domain has?
Let's start with a simple case. Determining the amount of junk traffic will help you understand how many non-target audience visits your site. This will be useful, for example, if traffic to the site is growing, but conversions are not. Then you need to know why users visit your site, but do not perform targeted actions.
1
Go to the API console and enter your token from the Serpstat profile in the corresponding field.
2
Choose the database.
3
Select domain_keywords method.
4
Enter your domain address in Queries bar and press Load data.
5
Set positions to 20 (or 10) to save limits.
6
Press SQL → Snippets → Summary data → Evaluate SQL.
We get the total website traffic, Traff indicator. Write it down.
7
Then we add negative keywords and press firstly Apply Filters, then Evaluate SQL. We get the total website traffic without negative keywords. We subtract traffic without junk words from the total traffic and get junk traffic.
How to see the share of commercial domain requests?
Another way to evaluate the quality of traffic that goes to your site is to determine the proportion of commercial domain requests. Why do this? Let's say traffic to your site is growing, but there is no increase in conversions and profits. Perhaps your site is ranked by a large number of key information requests that do not drive targeted traffic. To check this:
1
Go to the API console and enter your token from the Serpstat profile in the corresponding field.
2
Choose the database.
3
Select domain_keywords method.
4
Enter your domain address in Queries bar and press Load data.
5
Set positions to 20 (or 10) to save limits.
6
Enter negative keywords and / or keywords. For example: buy, price. Click Apply Filters.
7
Click Evaluate SQL.
SELECT `url`,
sum(`raw_traff`) AS `raw_traff`,
sum(`filtered_traff`) AS `filtered_traff`,
sum(`raw_traff_cost`) AS `raw_traff_cost`,
sum(`filtered_traff_cost`) AS `filtered_traff_cost`,
100*sum(`filtered_traff`)/sum(`raw_traff`) AS `filtered_traff_procent`
FROM (SELECT
`raw_results`.`url` AS `url`,
`raw_results`.`_se` AS `_se`,
`raw_results`.`cost` AS `cost`,
`raw_results`.`position` AS `position`,
`raw_results`.`traff` AS `raw_traff`,
`results`.`traff` AS `filtered_traff`,
(`raw_results`.`traff` * `raw_results`.`cost`) AS `raw_traff_cost`,
(`results`.`traff` * `results`.`cost`) AS `filtered_traff_cost`
FROM raw_results() as `raw_results`
LEFT JOIN `results`
USING `keyword`,`_se`,`position`
)
GROUP BY `url`
We get this report:
Indicators:
raw_traff - traffic before applying the filter;
filtered_traff - traffic after applying the filter;
raw_traff_cost - the amount of CPC * traff before applying the filter;
raw_traff_cost - the amount of CPC * traff after applying the filter;
filtered_traff_procent - percentage of remaining traffic.
raw_traff - traffic before applying the filter;
filtered_traff - traffic after applying the filter;
raw_traff_cost - the amount of CPC * traff before applying the filter;
raw_traff_cost - the amount of CPC * traff after applying the filter;
filtered_traff_procent - percentage of remaining traffic.
8
If you want to get the total data, then delete the last line GROUP BY` url`.
Serpstat is an all-in-one SEO platform for SEO, PPC, Rank Tracking, competitor analysis, site audit, backlink analysis, and SEO keyword research.
Get a quick overview and save your time with a personal Serpstat demo! Want to get a trial or successful use cases? Send a request and our support team will contact you;)
Get a quick overview and save your time with a personal Serpstat demo! Want to get a trial or successful use cases? Send a request and our support team will contact you;)
How to find out the amount of branded traffic from a domain?
Brand traffic is not the merit of an SEO specialist. The serpstat.com domain will be the first on request "serpstat" without any SEO tricks. Moreover, the conversion of branded traffic is many times greater. This causes a problem with the motivation of an SEO specialist. He can do nothing and get undeserved bonuses.
On the other hand, brand traffic needs to be considered in attribution models. For example, a user visited us from contextual advertising. The next day he found the site by request "serpstat.com" and registered. In this case, the conversion should be counted as contextual advertising, not SEO. In other words, you need to treat brand traffic as well as direct referrals.
Using the API Console is a universal way to find out how much brand traffic a page has.
Using the API Console is a universal way to find out how much brand traffic a page has.
1
Go to the API console and enter your token from the Serpstat profile in the corresponding field.
2
Choose the database.
3
Select domain_keywords method.
4
Enter your domain address in Queries bar and press Load data.
5
Set positions to 20 (or 10) to save limits.
6
Open the SQL tab and enter the code:
SELECT `url`,
sum(`traff`) AS `traff`,
sum(`traff_cost`) AS `traff_cost`,
sum(`brand_traff`) AS `brand_traff`,
sum(`brand_traff_cost`) AS `brand_traff_cost`,
(100*sum(`brand_traff`)/sum(`traff`)) AS `brand_traff_prc`,
(100*sum(`brand_traff_cost`)/sum(`traff_cost`)) AS `brand_traff_cost_prc`
FROM(SELECT *,
(`is_brand` * `traff`) AS `brand_traff`,
(`is_brand` * `traff_cost`) AS `brand_traff_cost`
FROM (SELECT *,
(`traff` * `cost`) AS `traff_cost`,
1 * (
`keyword` like '%serpstat%' OR
`keyword` like '%serp stat%' OR
`keyword` like '%prodvigator%'
) as `is_brand`
FROM `results`
)
)
GROUP BY `url`
ORDER BY `brand_traff_prc` DESC
7
Replace "serpstat", "prodvigator" with your brand keywords and press Evaluate SQL.
We get a report on brand traffic for all URLs.
Indicators:
traff - all traffic;
traff_cost - traffic cost estimate (CPC * traff);
brand_traff - branded traffic;
brand_traff_cost - assessment of branded traffic;
brand_traff_prc - percentage of branded traffic.
traff - all traffic;
traff_cost - traffic cost estimate (CPC * traff);
brand_traff - branded traffic;
brand_traff_cost - assessment of branded traffic;
brand_traff_prc - percentage of branded traffic.
8
To calculate all site traffic, you need to remove the code line GROUP BY` url`.
The report shows that almost all brand traffic goes to the first 10 pages. For example, to the main page. Further, its percentage drops sharply.
We get a method for determining branded traffic with 90%: if among these 10 pages there are landing pages, then the traffic is branded, otherwise not.
We get a method for determining branded traffic with 90%: if among these 10 pages there are landing pages, then the traffic is branded, otherwise not.
How to collect a huge semantic core in 10 minutes?
Collecting the semantic core is one of the primary and most difficult tasks of an SEO specialist. Several methods are applicable here:
1
Analysis of direct competitors' site structure.
2
Brain storm.
3
Using special services.
All these methods are widely applicable and effective, however, they require a lot of time. Therefore, we decided to create a faster way using the API console. You can use any combination of API methods to collect and explore keywords.
1
We collect the keywords of competitors. You can do it in several ways:
Download competitor's keywords using the domain_keywords method.
Download the keywords of other competitors with the domain_intersection method.
Download the key combinations of other competitors using the domain_intersection method and select the Domain Combinations: 2 domains combination setting. The console will generate all combinations.
There is also a more advanced and expensive method - domains_keywords_match_sdk. Read more about the method in the article.
2
Collect keywords from advertisements of competitors.
Keywords competitors use for advertising. The method is ad_keywords.
Keywords several competitors use for advertising simultaneously - domains_ads_keywords_match_sdk. The method also expends a large number of API limits.
3
Collecting keyword sub-queries.
Use Wordstat syntax.
4
You can also upload keywords using other methods:
urls_keywords_match_sdk.
domains_uniq_keywords.
related_keywords
url_keywords.
url_missing_keywords.
url_keywords_and_missing.
keywords_and_suggestions.
The API console can combine the results of several queries. To do this, you need:
Go to the History tab.
Tick all the reports that you want to combine.
Choose Remove all domains data.
Press join selected data.
As a result, all keywords will be collected in one table.
They can be filtered by negative keywords.
If you have used multiple regions, you can combine them. To do this, on the tab SQL → SQL snippets select Group by keywords.
You can also add columns to the table with your URL, position and traffic by keyword. To do this:
1
Choose the domain_keywords method and enter the address of your domain in Queries. Download the report.
2
Next, when combining reports check this entry on the History tab, select Delete domain data not related to your domain, enter the address of your domain in the field Your domain.
Done! You can build a database of several dozens of thousands of keywords in just 10-30 minutes.
How to build an internal linking on the site?
Another use case that will help you quickly set the internal linking. Using URL aggregation, the API console allows you to create a list of links to insert into the site template. This will help you correctly distribute the link weight on the site. To do this:
1
Go to the API console and enter your token from the Serpstat profile in the corresponding field.
2
Choose the database.
3
Select domain_keywords method.
4
Enter your domain address in Queries and press Load data.
5
Set positions to 20 (or 10) to save limits.
6
Enter this code on the SQL tab:
SELECT ('<a href=”'+url+'”>'+FIRST(keyword)+'</a>') as link_code
FROM (SELECT * FROM results ORDER BY traff DESC)
GROUP BY url ORDER BY sum_traff DESC
This code groups keywords by URL, sorts them by traffic, finds the most frequent keyword of the page and uses it as an anchor (link text). As a result, we get a code that can be edited and inserted into the site template.
How to estimate SEO monetary value?
Sometimes it becomes necessary to evaluate the return of money on SEO. For example, to show the customer the effectiveness of their work. This can also be done using the API Console. To do this:
1
Go to the API console and enter your token from the Serpstat profile in the corresponding field.
2
Choose the database.
3
Select domain_keywords method.
4
Enter your domain address in Queries and press Load data.
5
Set positions to 20 (or 10) to save limits.
6
Choose Summary data on SQL tab.
In the report you will find the traff_cost column. This is an estimate of the cost of traffic: the amount of money you would have to spend on PPC to get the same amount of traffic that you get through SEO.
How is traff_cost estimated?
How is traff_cost estimated?
1
For each keyword, this is the combination of two components:
traff - approximate traffic for a keyword (depends on the position and frequency of the keyword).
cost - The average cost per click (CPC) according to Google Ads.
traff - approximate traffic for a keyword (depends on the position and frequency of the keyword).
cost - The average cost per click (CPC) according to Google Ads.
2
The cost of traffic is summed up for all keywords.
How are the limits spent?
Each month, Serpstat users get a certain number of API lines:
Lite Plan: 100.000.
Standard Plan: 500.000.
Advanced Plan: 1.000.000.
Enterprise Plan: 2.000.000.
Unused limits burn out at the end of the month. And you get new ones depending on the plan.
You will spend API lines for using the service. If you download a 1000-lined report, then 1000 API lines will be spent. Downloading starts after pressing the Load Data button.
After the data has been downloaded, the lines won't be used until you press the Load Data button again. That is, you can filter queries by negative keywords, download data and perform SQL filtering for free.
Data is not always downloaded from Serpstat. The console caches requests. In this case, the lines are not used. For example, if you change nothing, press the Load Data button a second time, then the report will download instantly and you will not spend anything.
If you have two regions and you click Load Data and then add a third region, then ⅔ of data will be obtained from the cache (fast and free). On the other hand, some advanced features may spend more lines. For example, we have already described how the domains_keywords_match_sdk method works.
Also, if you use part of the URL instead of a domain (for example, serpstat.com/en/blog/), the console will download keywords throughout the site and only then delete those that don't contain serpstat.com/en/blog/.
You can control the amount of downloaded data by setting Max. Rows. In most methods, it will be equal to Max. rows * number of domains * number of regions.
Lite Plan: 100.000.
Standard Plan: 500.000.
Advanced Plan: 1.000.000.
Enterprise Plan: 2.000.000.
Unused limits burn out at the end of the month. And you get new ones depending on the plan.
You will spend API lines for using the service. If you download a 1000-lined report, then 1000 API lines will be spent. Downloading starts after pressing the Load Data button.
After the data has been downloaded, the lines won't be used until you press the Load Data button again. That is, you can filter queries by negative keywords, download data and perform SQL filtering for free.
Data is not always downloaded from Serpstat. The console caches requests. In this case, the lines are not used. For example, if you change nothing, press the Load Data button a second time, then the report will download instantly and you will not spend anything.
If you have two regions and you click Load Data and then add a third region, then ⅔ of data will be obtained from the cache (fast and free). On the other hand, some advanced features may spend more lines. For example, we have already described how the domains_keywords_match_sdk method works.
Also, if you use part of the URL instead of a domain (for example, serpstat.com/en/blog/), the console will download keywords throughout the site and only then delete those that don't contain serpstat.com/en/blog/.
You can control the amount of downloaded data by setting Max. Rows. In most methods, it will be equal to Max. rows * number of domains * number of regions.
The console doesn't download more lines than the theoretical limit, but can download less if Serpstat databases don't have that much data.
If you enter into domain_keywords a small site with only 100 keywords, no matter what the limit is, no more than 100 lines will be downloaded.
After clicking Load Data, it will show how many rows there are in the Serpstat database (total) and how many are downloaded.
If you enter into domain_keywords a small site with only 100 keywords, no matter what the limit is, no more than 100 lines will be downloaded.
After clicking Load Data, it will show how many rows there are in the Serpstat database (total) and how many are downloaded.
To understand exactly how many lines will be spent, you need to set Max. rows = 1 and click on the Get Data button and look at Total. You'll see how many lines are in the Serpstat database, how many lines will be downloaded if you set a large number to Max. rows.
To save lines, you can set up the method. For example, set Position.To = 10 or add filter keywords. But not all methods support these settings.
To summarize:
To save lines, you can set up the method. For example, set Position.To = 10 or add filter keywords. But not all methods support these settings.
To summarize:
No more lines will be spent than written:
To the right of Max. rows
To the right of the Load Data button;
In the line Total rows in Serpstat;
In the line Downloaded rows.
To the right of Max. rows
To the right of the Load Data button;
In the line Total rows in Serpstat;
In the line Downloaded rows.
But less can be spent.
Conclusion
Serpstat API Console is a convenient solution for those who want to process huge amounts of data in seconds. This utility will help professionals who don't have programming skills, but want to use the API. The console has a lot of advantages:
You do not need programming knowledge to use the API.
Batch data upload on multiple domains, URLs, keywords.
Lightning-fast data analysis and obtaining the result.
Automatic combining of analysis results across multiple reports.
The presence of convenient filters to sort the received data.
A lot of training materials on working with the console and use cases on our blog ;)
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.
Found an error? Select it and press Ctrl + Enter to tell us
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
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.