How to Extract more than 1,000 Rows from Google Search Console with Online API

Google Search Console is probably the most used tool by all SEO professionals, it’s from GSC that we can see metrics such as clicks, impressions and positioning of all our keywords.

But, there is a huge problem in Search Console, and that is the fact that we can only see 1,000 rows of keywords or urls, unless we do the hard work of creating a lot of filters by keywords, URLs, devices and so son.

To bypass this 1,000 rows limits, we can use the Google Search Console API. If you already heard about the API, maybe you thought you had to know a lot of coding, programming and specially Python, and that is indeed some kind of truth, but there is an easier way to do that, and that is with the Google Search Console Online API, that is going to give us a 5,000 limit of lines in the report. You can have even more lines just by giving some easy filters.

Let’s get through it!

Advantages of using the Google Search Console API

• Extract more than 1,000 rows of keywords from Google Search Console;
• The possibility of having all of these data save month to month, so we don’t lose in thanks to the limit of 16 months from Google Search Console;
• Extract all the URLs from a website, along with all the keywords that position for that URL, without having to make any kind of filter;
• To extract all of these data directly from the browser. No need to know code, Python or anything like that;
• Much more depth for the analysis, possibility of discovering underexplored keywords.

How to use the Google Search Console Online API to Extract 1,000+ Rows of keywords

Login with your Google Search Console credentials on the API website: https://developers.google.com/webmaster-tools/search-console-api-original/v3/searchanalytics/query

In the tab that will open on the right, enter the domain exactly as it is registered in Google Search Console. That is, if it is the version with www, enter the www, the same thing if it is HTTP or HTTPS.

In the “Request Body” area, you will need to enter all the requests and metrics you want, such as the date of the report, whether you want keywords or URLs, devices, country, etc. You can click on the blue symbol, and the site will autofill the available metrics, and that should help you:

Choosing the desired date:

• Add the start date via the “startDate” attribute. The format must follow the YEAR-MONTH-DAY pattern, example: 2022-11-11;
• Add the end date of the report (“endDate”) in the same pattern. I always suggest concentrating this data by month and saving it month by month. Or, even better, depending on your website’s size, extract this data every day.

Add the desired dimensions:

• Query (keywords);
• Page;
• Device (Device, whether it is mobile, desktop or tablet);
• Country.

Then just check the first square where it says “Google OAuth 2.0”. Which is what will authorize the API to use your username to read Google Search Console data:

And then, just click “EXECUTE”. The API should bring all the requested data directly to the page:

The Finished API Code:

The request for all October 2022 data, with pages and keywords, with the API limit of 5,000, should look something like this:

{
  "startDate": "2022-10-01",
  "endDate": "2022-10-31",
  "rowLimit": 5000,
  "dimensions": [
    "PAGE",
    "QUERY"
  ]
}

In another example, if I wanted to request the date on which each URL had the number of clicks, this attribute would appear within the “dimensions” tab:

{
  "startDate": "2022-10-01",
  "endDate": "2022-10-31",
  "rowLimit": 5000,
  "dimensions": [
    "DATE",
    "PAGE",
    "QUERY"
  ]
}

Using filters to further deepen analysis:

Let’s suppose your site is very large and the limit of 5,000 rows is not enough, or that you just want to make a more segmented extraction, without the need to create filters in Excel, for example, it is possible to carry out these filters directly in the online API.

For this, you need to know how 3 main variables work:

• Dimension: the dimension to be filtered, in relation to the previously requested dimensions. It can be “page”, “query”, “device” or “country”;
• Operator: here the filter is very similar to that of Search Console, you must choose between “equal to”, “not equal to”, “contains” or “does not contain”;
• Expression: The expression to look for, based on the operator, within the dimension. This is basically your URL, keyword, or device filter.

If you have a website that sells beers, for example, and would like to extract only keywords that contain the term “craft”, your filter would look like this:

{
{
  "startDate": "2022-10-01",
  "endDate": "2022-10-31",
  "dimensions": [
    "QUERY",
    "PAGE"
  ],
  "dimensionFilterGroups": [
    {
      "filters": [
        {
          "dimension": "QUERY",
          "operator": "CONTAINS",
          "expression": "craft"
        }
      ]
    }
  ]
}
}

It sounds complicated at first, but notice that we basically told the API “query contains ‘craft'”. You could change the “dimension” to “device”, and the expression to “mobile” for example, and that way you would have all accesses only from mobile devices.

Convert API’s JSON data to Excel

If you’ve already tested it yourself, you must have noticed that the data brought in by the online API appears in JSON format, which makes any type of filter or analysis difficult, especially if you’re also very used to working with Excel.

To convert these JSON to an Excel spreadsheet is very simple:

• Click on the area where your website data is appearing, and with the keyboard shortcut “CTRL + A” select all the text in the area:

• Copy the entire content with the help of “CTRL + C” shortcut and go to any website that converts JSON to Excel. Very simple to find one by searching for “JSON to Excel converter” on Google.
• Now just add the content at the top, export to Excel and do all the filters and analyzes you want:

Isn’t it simple to extract 1000+ rows from Search Console? Hope this helps, and may you show good reports to your clients!