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
11683 7 1
SEO 19 min read September 9, 2019

How To Collect A Semantic Core In 10 Minutes (And 5 More SEO Cases)

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.
And today I will tell you about our API console, which can become an excellent assistant for SEO and can automate routine tasks. I will also describe several non-standard ways of using it ;)

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.


Professional Keyword Research
And Competitor Analysis Is Now Easier With Serpstat API
Read more
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. Serpstat team does not support the product. 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 ;)
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 dataEvaluate 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.
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 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;)

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.

How To Avoid Unfair SEO Specialists Or Why A Business Owner Must Control The Work Of His Vendors
Read more
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.
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.
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.

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.

Keyword Research Without Agencies: Manual For Beginners And Online Shop Owners
Read more
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 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 SQLSQL 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?
    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.
    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.
    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.
    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:
    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.
    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 ;)

    Learn how to get the most out of Serpstat

    Want to get a personal demo, trial period or bunch of successful use cases?

    Send a request and our expert will contact you ;)

    Rate the article on a five-point scale

    The article has already been rated by 2 people on average 5 out of 5
    Found an error? Select it and press Ctrl + Enter to tell us

    Share this article with your friends

    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?

    Awesome!

    To complete your registration you need to enter your phone number

    Back

    We sent confirmation code to your phone number

    Your phone Resend code Queries left

    Something went wrong.

    Contact our support team
    Or confirm the registration using the Telegram bot Follow this link
    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

    Upgrade your plan

    Export is not available for your account. Please upgrade to Lite or higher to get access to the tool. Learn more

    Sign Up Free

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

    Invite
    View Editing

    E-mail
    Message
    Optional
    E-mail
    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