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

 

 

 

Print Friendly, PDF & Email