Google Search console (GSC) is a collection of Google SEO tools and reporting. It offers data and configuration control for your website as well as a variety of visitor metrics.
As an online marketer, the GSC “search analytics” section is probably the most useful report for you. GSC “search analytics” delivers insights into how your website is performing in regards to the overall organic search traffic from Google. You can see what keyword searches on Google led to the site being listed in the SERPs, and the click through rates of such listings. You can also see the most popular queries, pages, countries and devices. Additionally for better understanding of your website’s performance in Google’s search results, you can use different filters to segment traffic even further.
The table in Google is queried once a day, but the data is updated on Google’s side in a 3 days latency. The data is stored in a table called google_search_console
Table Schema
Column Name | Type | Comments |
platform | STRING | |
siteUrl | STRING | |
searchTerm | STRING | |
device | STRING | |
countryCode | STRING | |
countryName | STRING | |
pageUrl | STRING | |
impressions | INTEGER | |
clicks | INTEGER | |
ctr | FLOAT | |
position | INTEGER | |
queryTime | TIMESTAMP | the date queried from the Google API |
insertTimeRecord | TIMESTAMP | The time the record was inserted in Cooladata |
Querying the Data
Querying the data can be done using CQL reports or KPI, querying the google_search_console table, like so:
select * from google_search_console
The following query returns data filtered by the date range picker:
select * from google_search_console where date(queryTime)(date_range_gcp(context)) order by queryTime