Google Search Console (GSC)

Contents

1 Table Schema
2 Querying the Data

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

Table Schema

Querying the Data

Share