How to build SEO analytics for a multilingual website with the GSC API and BigQuery

Would you like to try Octo Browser at а discount?
Use the promo code OCTOBLOG to get 30% off any subscription. This offer is valid only for new users.

Why move away from web interfaces

A reliable SEO analytics system starts with eliminating manual exports. The Google Search Console interface is convenient for quickly checking certain metrics, but its capabilities are insufficient for in-depth product analytics.

The GSC API makes it possible to obtain data at a more granular level—by individual URLs, search queries, and other dimensions. This allows you to work not only with aggregated reports, but also with the underlying data from which you can build your own cuts and metrics.

To understand the advantages of this architecture, it is important to consider several key factors.

Data cardinality and web interface limitations

One of the main problems with the GSC interface is the limited amount of data it displays. This is especially critical for a multilingual project: when there are large numbers of pages, countries, devices, and queries, a significant portion of the information remains outside the standard report.

This is where cardinality is important—the number of unique combinations of parameters in a dataset. For example, if a website operates in 10 languages, receives traffic from 50 countries, uses 3 types of devices, and ranks for 10,000 search queries, the number of possible combinations can reach millions of rows.

Working with this volume of data through the web interface is practically impossible. The API allows you to retrieve considerably more data and split the export into separate segments. Through filtering and sequential processing, you can collect not only the top portion of the results, but also the long tail of queries and URLs that is usually lost in standard reports.

Data Lake and historical storage

Google Search Console stores historical data in its interface only for the last 16 months, which is insufficient for long-term SEO analytics.

A dedicated data warehouse, such as BigQuery, solves this problem. You can regularly save raw data obtained through the API there without being limited by the retention period in the GSC interface. This gives you a historical SEO database that can be used for long-term analysis, report building, and reprocessing data in any necessary dimension.

Dependence on external services and the cost of scaling

Ready-made ETL connectors can be used to automate data exports, for example, SaaS services such as Supermetrics or Fivetran. They allow you to set up data transfers quickly without in-house development, but as the project scales, this approach can become expensive and tie your infrastructure too closely to a particular service.

SaaS platforms charge for their services based on the volume of processed rows or the number of connectors. When your multilingual project starts generating gigabytes of raw SEO data per day, the cost of such a service can exceed the cost of storing the data in BigQuery and renting a small server for Python scripts several times over.

Furthermore, the deeper a service is integrated into the data collection and transformation process, the harder it becomes to replace it later: migration may require reconfiguring connectors, processing logic, and reporting.

Level of data granularity

When collecting data through the API, it is important to preserve as much detail as possible. If you combine data already at the export stage, you will not be able to reconstruct the original dimensions later.

For example, when designing an SEO database, you should store parameters such as Device type and Country separately. If the script requests data without a country breakdown, GSC will return the total number of clicks for the query. After that, it will be impossible to determine how many clicks came from Germany and how many came from France.

That's why it is better to store the data in detailed form and combine it and calculate final metrics only at the analysis or visualization stage. This preserves the ability to build any necessary data cuts in the future, even if they were not originally anticipated in the reports.

GSC API limitations when exporting large amounts of data

At first glance, working with the GSC API looks simple: authorize the script, retrieve the data, save it to the database, and move on to analysis. In practice, large projects quickly run into the API's technical limitations.

If you try to export large amounts of data without taking these limitations into account, you can encounter timeouts, 429 Too Many Requests errors, and incomplete exports. As a result, only part of the data will reach the data warehouse, and the system itself will become unstable.

That's why the pipeline should account for API limits, data update delays, query quotas, and retry mechanisms for failed requests in advance. Let's look at the main GSC API limitations and how to work with them correctly.

The 50,000-row limit and export segmentation

According to Google's documentation, the rowLimit parameter allows you to retrieve no more than 25,000 rows in a single request. The startRow parameter is used for pagination: you can initially request the first 25,000 rows and then the next 25,000.

However, there is an additional limitation: the sum of startRow + rowLimit cannot exceed 50,000. Therefore, for a single date and a selected combination of parameters, you cannot retrieve row 50,001 this way.

If the daily cardinality of your data exceeds 50,000 rows, you need to split the export into separate segments using Dimension Filters. For example, you can query data separately for language folders — /de/, /fr/, and so on, or additionally divide URLs by patterns using regular expressions.

This allows you to obtain the complete dataset through several independent requests targeting different data segments.

Data delay and working with dataState

The GSC API has a systematic data update delay of 48 to 72 hours. Therefore, when exporting data daily, it is important to distinguish between preliminary and final data.

The dataState parameter controls this. It has two values:

• "final" (default) — returns only fully aggregated and validated data.

• "all" — includes fresh data that has not yet undergone final processing.

This creates a trade-off between speed and accuracy when building the pipeline. Let's look at both scenarios.

• Using dataState: "final" (the default behavior). In this mode, data for the last 24 hours may not yet be available. If your script tries to export yesterday using the default value, the API will return an empty array. You need to apply a fixed offset of current_date — 3 days.

• Using dataState: "all". You will receive the required dataset for the previous 24 hours. However, Google warns that fresh data is preliminary. The system has not yet consolidated all duplicates, filtered out spam bots, or recalculated anomalies. After 2–3 days, these figures will change on Google's own servers.

How this can affect your storage architecture: if your Python script simply appends fresh raw data to BigQuery, your historical database will become distorted. When you write "fresh" metrics, you are storing a draft that will never exactly match the final reports in the GSC interface.

Therefore, for operational analytics, it is better to use a two-stage approach:

1. Export data for the previous day using dataState: "all".

2. At the same time, the script should re-export data for current_date — 4 days using dataState: "final".

3. In BigQuery, instead of simple Append, use the MERGE operator (or Upsert logic). The script should find the preliminary data from four days earlier in the database and overwrite it with the final, consolidated values.

This approach allows you to see fresh metrics on the dashboard while preserving a correct historical database after the final processing of the data.

API limits and exponential backoff

The GSC API limits the number of requests to protect its infrastructure from excessive load. The GSC API has strict quotas: 50 queries per second (QPS) and 1,200 queries per minute (QPM) per project. Therefore, these restrictions need to be taken into account in advance when performing large-scale exports.

The problem is especially noticeable when the data has to be split into hundreds of segments to work around the 50,000-row limit. To speed up the process, developers often use asynchronous requests (asyncio) or thread pools (ThreadPoolExecutor). But this can quickly exhaust the 50 QPS limit, and the API begins returning 429 Too Many Requests or 503 Service Unavailable errors.

A simple delay using time.sleep() does not work particularly well here. If several parallel threads receive an error at the same time and then sleep for the same period, they will resume almost simultaneously and create another traffic spike.

The correct script architecture should include an exponential backoff pattern with added random noise (Jitter). This causes the interval between retry requests to increase gradually. Not all threads will retry at the same time, reducing the chance that they will exceed the limits.

In Python, you do not necessarily need to implement this logic manually: you can use decorators from the tenacity library.

Collecting SEO data for subdomains and language folders

After taking GSC API limits and delays into account, the next important question is how exactly the multilingual website is structured. The project structure directly affects the logic for exporting, normalizing, and combining data.

In multilingual SEO, there are two polar approaches to website structure: national subdomains and language folders. For the user, the difference is minimal, but for building exports, the different structures can completely change the approach.

Subdomains and separate domains

If language versions are hosted on separate domains (site.de, site.fr) or subdomains (de.site.com, fr.site.com), the data for each version has to be collected as data from a separate resource.

Why is this useful for the business? Regional isolation allows you to control the crawl budget more strictly. A search engine will not spend the German bot's crawl budget scanning the French version of the website. From an SEO perspective, this is the safest route for scaling.

For the analytics pipeline, this creates two problems:

• More connection points. If the project has 10 language versions, the script needs to query multiple GSC resources sequentially or in parallel. The more sources there are, the more important API quotas, error handling, and the resilience of the entire export architecture become.

• URL normalization complexity (stitching data together). Pages serving the same purpose on different domains will have different addresses—for example, site.de/product and site.fr/product. To compare their performance as a single entity, their URLs need to be normalized.

In the Pandas library for Python, this normalization looks like this:

import pandas as pd
from urllib.parse import urlparse

# Keep only the path for stitching metrics across countries
df['normalized_url'] = df['page'].apply(lambda x: urlparse(x).path) 
# Result: /product

import pandas as pd
from urllib.parse import urlparse

# Keep only the path for stitching metrics across countries
df['normalized_url'] = df['page'].apply(lambda x: urlparse(x).path) 
# Result: /product

Without this normalization, the metrics for different localizations will remain separated across different URLs. This will make it harder to calculate the overall performance of the same page or template in different languages.

Language folders and data segmentation

If language versions are placed in folders, for example, site.com/de/ and site.com/fr/, the entire project remains within a single domain. This simplifies data collection: instead of making separate requests to multiple resources, you can work with a single Domain Property in Google Search Console.

Instead of 10 separate queries, you can make one large export, using filtering to work around the 50,000-row limit. This saves Google API quotas and reduces network load.

Large exports still need to be divided into segments. But the architecture itself becomes simpler: fewer connection points, fewer requests, and lower API load.

Because the API gives us a continuous stream of URLs, the script must assign country markers to the rows itself. This is done using regular expressions (RegEx).

Using the Pandas library, we can extract the language marker directly from the URL:

df['language_market'] = df['page'].str.extract(r'\.com/([a-z]{2})/')
# Exception handling: if RegEx returns NaN, this is the main version of the site
df['language_market'].fillna('en', inplace=True)

df['language_market'] = df['page'].str.extract(r'\.com/([a-z]{2})/')
# Exception handling: if RegEx returns NaN, this is the main version of the site
df['language_market'].fillna('en', inplace=True)

This allows you to extract the de marker from a URL such as site.com/de/product and use it for further analysis.

The main limitation of this approach is its dependence on the URL structure. The regular expression must exactly match the rules used to build language versions. If some pages use a different pattern, such as site.com/category-de/product, those URLs may be classified incorrectly or may not enter the required segment at all.

That's why, before configuring RegEx, it is important to check all possible language-URL patterns and handle exceptions separately.

Data transformation and metric calculation in Pandas

After collecting and normalizing the data, it needs to be combined and prepared for analysis. Pandas is convenient for this: the library makes it possible to work with large tables, combine sources, and calculate metrics at both row and group level.

Combining GSC and GA4 data by URL

Google Search Console displays search metrics—impressions, clicks, CTR, and positions. GA4 complements these with behavioral and business metrics, such as sessions and conversions.

To obtain a more complete picture of SEO traffic performance, GSC and GA4 data can be combined using a common key—the normalized landing-page URL.

In Pandas, this is done by joining the tables using a common key, the normalized URL:

import pandas as pd

# df_gsc — export from Search Console
# df_ga4 — export from GA4 (Sessions, Conversions)

# Join the data by landing page (Left Join so that pages without traffic are not lost)
merged_df = pd.merge(df_gsc, df_ga4, how='left', left_on='landing_page', right_on='page_path')

# We can now calculate the conversion rate of a specific SEO cluster:
merged_df['seo_conversion_rate'] = (merged_df['conversions'] / merged_df['clicks']) * 100

import pandas as pd

# df_gsc — export from Search Console
# df_ga4 — export from GA4 (Sessions, Conversions)

# Join the data by landing page (Left Join so that pages without traffic are not lost)
merged_df = pd.merge(df_gsc, df_ga4, how='left', left_on='landing_page', right_on='page_path')

# We can now calculate the conversion rate of a specific SEO cluster:
merged_df['seo_conversion_rate'] = (merged_df['conversions'] / merged_df['clicks']) * 100

Correct calculation of CTR and average position

When combining data from multiple language versions, you cannot calculate CTR and average position symply by using an arithmetic mean. This distorts the result because it does not account for different impression volumes.

For example:

• French subdomain: 2 clicks out of 4 impressions, CTR = 50%;

• German subdomain: 20 clicks out of 1,000 impressions, CTR = 2%.

If you simply average the CTR values, you get:

(50% + 2%) / 2 = 26%

But the actual CTR across the two subdomains is:

22 clicks / 1004 impressions = 2.19%

Therefore, when aggregating data from different localizations, metrics need to be recalculated from the underlying values:

• CTR is calculated as the ratio of total clicks to total impressions.

• Average position should be weighted by impressions: each row's position is multiplied by the number of impressions, and the sum of these values is then divided by total impressions.

In Pandas, this can be implemented as follows:

# Group the data by search query across all countries
def weighted_metrics(x):
    # Weighted average position = Sum (Position * Impressions) / Sum (Impressions)
    weighted_pos = (x['position'] * x['impressions']).sum() / x['impressions'].sum()
    # Actual CTR
    real_ctr = (x['clicks'].sum() / x['impressions'].sum()) * 100
    
    return pd.Series({
        'total_clicks': x['clicks'].sum(),
        'total_impressions': x['impressions'].sum(),
        'weighted_position': weighted_pos,
        'real_ctr': real_ctr
    })

# Apply the function to the grouped dataframe
final_cluster_data = merged_df.groupby('query').apply(weighted_metrics)

# Group the data by search query across all countries
def weighted_metrics(x):
    # Weighted average position = Sum (Position * Impressions) / Sum (Impressions)
    weighted_pos = (x['position'] * x['impressions']).sum() / x['impressions'].sum()
    # Actual CTR
    real_ctr = (x['clicks'].sum() / x['impressions'].sum()) * 100
    
    return pd.Series({
        'total_clicks': x['clicks'].sum(),
        'total_impressions': x['impressions'].sum(),
        'weighted_position': weighted_pos,
        'real_ctr': real_ctr
    })

# Apply the function to the grouped dataframe
final_cluster_data = merged_df.groupby('query').apply(weighted_metrics)

Automating hreflang and cannibalization monitoring

A dedicated data warehouse can be used not only for reporting, but also for automatically detecting technical SEO problems. Two types of issues are especially important for multilingual projects: broken localization relationships and internal competition between multiple pages for the same search demand.

Automated hreflang monitoring

Technical SEO optimization for international projects relies on the consistency and bidirectionality of localization tags. The hreflang tag works as a strict bidirectional cross-reference system. If, for example, a French page points to a German page as an alternative, the German page must contain a reciprocal link. Breaking this chain breaks the entire cluster in Google's eyes.

With a large project, manually checking these links is impossible, so monitoring should be based on comparing at least two data sources:

• Screaming Frog. Run the crawler on a schedule. It scans for the actual presence of tags in the HTML code and validates language codes (ISO 639-1) and country codes (ISO 3166-1 Alpha 2). The results are exported to a database.

• GSC API. The Search Console API provides an indexing report and shows how Google interpreted these relationships during its latest crawl.

Crawling results and Google data can be stored in the same database and then matched using SQL. For example, FULL OUTER JOIN can identify critical bugs in the aggregated data: pages that technically have correct tags in their code but have been ignored by the search engine.

There can be many reasons for this. One possibility is client-side rendering: JavaScript frameworks (React, Vue) take too long to render the tags in <head>, or performance metrics (Core Web Vitals) are so poor that Googlebot times out before it can read hreflang. Automation catches these issues before traffic starts to decline.

Traffic cannibalization and finding conflicting URLs

The second multilingual issue is internal competition. Cannibalization occurs when a search engine becomes confused about relevance and starts ranking, for example, the English version of a page for a query from Germany even though you have a dedicated German-language landing page.

Such overlaps are difficult to analyze at scale in the standard GSC interface. If raw data is stored in BigQuery, potential cannibalization can be detected automatically using SQL.

The cannibalization detection algorithm is as follows:

1. Group the data by two parameters: query and country.

2. Count the number of unique URLs (COUNT(DISTINCT page)) receiving impressions for this combination.

3. Filter the results using HAVING count > 1.

If multiple pages receive impressions for the same query + country combination, this is a signal of a potential relevance conflict.

GSC data can be complemented by checking the actual search results for the relevant regions. This is especially useful when analytics has already identified an anomaly, but the numbers alone do not make it clear what exactly the user sees and which version of the page Google displays in a particular country.

We have covered methods for automatically collecting SERPs in a separate article. And when you need to manually check individual queries and localizations in different regional environments, you can use an anti-detect browser such as Octo Browser.

Related article

Web scraping Google Search results: tutorial (2026)

How Octo Browser complements SEO analytics

GSC and BigQuery are well suited to finding problems across large volumes of data. They can help you notice, for example, that a German page has started losing impressions, that the wrong localization ranks for queries from a particular country, or that several URLs are competing with one another.

But after finding such an issue, the question usually arises: what does the user actually see in the search results?

GSC data helps identify an anomaly and understand its scale. To diagnose a specific case, it is useful to look at the search results from the required region and check how the website actually behaves.

This is where Octo Browser comes in. For different countries, you can create separate profiles, connect proxies with the required geolocation, and perform checks in isolated browser sessions. This is convenient when you regularly work with several geolocations and do not want to mix cookies, history, and other data between checks.

For example, our script detects that the English URL is receiving impressions for queries from Germany even though the website has a complete German version at /de/. This does not necessarily mean that the problem is definitely related to localization. First, it is worth checking what is happening in the actual search results.

Open an Octo profile with a German proxy and check which URL Google displays for the target query. At the same time, you can check whether the correct version of the website opens, whether there is an automatic redirect to another locale, and whether the page content matches the selected region.

You can use the same method to selectively check:

• the appearance of the wrong language version in the SERP;

• differences in search results across multiple regions;

• the operation of regional redirects;

• the localization of content, prices, and other page elements;

• changes after fixing hreflang, canonical, or internal relinking.

In this way, the GSC API and BigQuery help identify suspicious cases across the entire project, while Octo Browser helps investigate individual anomalies using the required geolocations in isolated environments.

Conclusion

Moving SEO analytics from web interfaces to a custom data collection and storage system is an upgrade to an entirely new level of project management.

At the start, such an architecture is technically demanding: you need to configure authorization, error handling, data normalization, regular exports, and process management. But as a result, you get a system that scales with the project and does not depend on the limitations of web interfaces.

Instead of manually collecting reports, you will have a single database with history where you can analyze all language versions, recalculate metrics correctly, identify technical problems, and build the data cuts you need without losing granularity.