Once you set up the Google Search Console (GSC) data bulk export in one of your properties, data is going to automatically flow into the Google Cloud project you chose during this process.
You can then start working with this data. This article gives you high level insights on how to proceed to do so.
Accessing the different tables
One super important thing you need to know is that the Google Search Console data bulk export automatically creates 3 tables in the Google Cloud project of your choice :
searchdata_site_impression, which provides data at the property level
searchdata_url_impression, which collects data for each of the property URL
ExportLog, with the export logs.
For more details, you can check the official documentation. You can also check our article regarding the data you will access to.
Why is this important? Because, when you access and use your data in BigQuery, you have to choose which table you want to query. For instance, if you want to get statistics about a specific page, you need to use the searchdata_url_impression table. If you want to analyze the export logs, you have to query ExportLog.
As you can see, each table provides different information, that’s why you need to familiarize yourselves with these.
Data availability
Also, keep in mind that the bulk export happens once a day and that it lags typically by 2-3 days. This is something you probably are used to as the UI has similar limitations.
Another limitation lies in the fact that the bulk export only starts from the day when you set it up. This means that it won’t export your historical data.
Querying your tables
Alright, now that all of those details are aside: how do we actually access this data?
BigQuery requires SQL to access your data. So, go in Google Cloud, make sure to select the right project (at the top left, you can switch between projects). Then, in the left menu, in the “BigQuery” section, click on “BigQuery studio”.
BigQuery studio is where you can navigate through your project datasets, tables and views, as well as see all the details you could want from this data. Tables and views are part of datasets. The Google Search Console bulk export automatically creates one called “searchconsole” by default (except if you chose another name when you set it up).
From here, the simplest way to query a table is to click on the three dots next to it and select “query”. This will preload a basic query structure with key elements such as the path to the table and required statements like “Select”.
Intro to an SQL query
We’re not going to go into details about SQL queries, you should definitely checkout out our course if you want a more in-depth intro to SQL for GSC data. But here is the basic information you need:
The SELECT statement allows you to choose the fields you will use as columns and, if needed, run calculations on those fields.
The FROM clause allows you to select from which table or view the data will come from.
The WHERE clause allows you to filter the rows (based on dates, for instance).
GROUP BY allows data aggregation (for each page and/or query, for instance).
Here is an example of query:
SELECT
query,
SUM(clicks),
SUM(impressions)
FROM
`biqquerytraining.test_data.searchdata_site_impression`
GROUP BY
query
Here, we:
Pick the query (the keywords) dimension with two metrics: clicks and impressions
We run calculations to get the sum for the clicks and impressions metrics suing the SUM() function
In the From clause, we specified the path to our searchdata_site_impression table
The last two lines allow us to tell BigQuery that we want to aggregate the sum of clicks and impressions for each query in the table.
See the query output
Once you’re done building your query, you can click to Run it. If there are no errors and that your query matches data you have in the table you have selected, you will get an output in the Query results section.
That’s where you can get the data you want to access. You can sort it and play with it here, and even export it as a CSV file, visualize it in Looker and more!
Go further
BTW, Google provides query samples and guidelines which can be useful.
Comments