Search Console Data Export to BigQuery
The Google Search Console is a great tool for accessing marketing analytics in terms of a website’s performance on Google Search. With Google BigQuery’s new feature, bulk data export, you can schedule a continuous export of your Search Console performance data to BigQuery. Once the data is in BigQuery, you’ll be able to run complex queries and unlock new opportunities from your performance data or export it to an external storage service.
What is BigQuery?
BigQuery is Google Cloud’s serverless data warehouse that allows users to query all types of data and do it across multiple clouds. With BigQuery, users can query data of any size without worrying about running out of resources since BigQuery scales to meet operational needs. It supports both batch loading and streaming data ingestion and subsequent processing of these datasets.
Being part of the Google Cloud Platform (GCP), BigQuery users leverage benefits such as infrastructure management, high scalability, interoperability, and high availability. On top of that, this Google Cloud service is built with solutions that make data processing more efficient, effective, and impactful such as:
Built-in machine learning – as a data engineer, you can train machine models using your data, evaluate their performance, and deploy them to production environments so that they make predictions on new data.
Built-in query acceleration – with BigQuery you can analyze data as it is generated without delay. Queries will perform better and it will take less time to retrieve data from the data warehouse.
What makes BigQuery perfect for business use cases?
BigQuery has an in-memory analysis service called BI Engine which helps businesses to perform interactive analysis of large data sets. It allows faster query processing by storing data in memory and this makes it useful for real-time data analysis. It also integrates with popular business intelligence tools like Tableau and Google’s Looker Studio which can be used to create interactive dashboards with real-time data. Using BI Engine, organizations can make faster and more informed decisions based on their data.
In addition, BigQuery also has a storage engine called BigLake that allows users to unify data warehouses and implement access controls. Organizations can merge all their data and govern it from a central place.
Use Case: Big Query in Marketing Analytics in Combination with Search Console
Search Console is an extremely useful marketing tool, providing users with information on their site’s performance on Google Search. This information typically comprises the number of impressions, clicks, and the average position in search results. This helps in identifying and fixing issues to improve your overall SEO ranking on Google Search and get more organic search traffic. Despite Search Console being very rich in marketing data, it only offers limited marketing analytics. Fortunately, you can export this data to BigQuery using bulk data export and utilize this Google Cloud service as a data analysis tool.
Step 1: Preparing your Cloud Project to receive Search Console data
From the Google Cloud Console, you will need to first select the Cloud project you want to receive this data. Next, enable the BigQuery API from the APIs & Services section.
With BigQuery enabled, grant Search Console permissions to export data into this project. Under IAM and Admin click the +GRANT ACCESS button which will open an Add Principal slider menu. Under New Principal add the name service account name “search-console-data-export@gserviceaccount.com” and assign the following roles:
BigQuery Job User
BigQuery Data Editor
Save your progress as soon as you are done and then move to the Search Console.
Step 2: Set a destination for your Search Console export
For your specific property, go to Settings > Bulk data export and make the following configurations:
Retrieve your Cloud project ID from Google Cloud Console and enter it into the Cloud Project ID field.
Name your dataset starting with the string “searchconsole…”. The default name is searchconsole and you can choose to leave it this way if you want to import from a single property per project ID. Otherwise, you should give each dataset a different name when importing from multiple properties per project ID.
Next, select a location in which Search Console will create a dataset with the first export.
Confirm your settings by clicking Continue and this will initiate the export. The first export will happen within 48 hours provided no error is encountered and this process will occur daily. If a non-persistent error is encountered, Search Console will attempt to re-export the data the following day. To finish up, you can set the expiration times once the tables have been created.
After the first export is complete log into your BigQuery account and you will have access to Search Console data in three tables:
searchdata_site_impression – this is a collection of data that keeps track of impressions related to site searches. The searchdata_site_impression table typically includes information such as the search term entered by the user, the number of times that term appeared in search results, and possibly additional details like the date and time of the search or the location of the user.
searchdata_site_url_impression – this refers to data that tracks the impressions of specific URLs in the context of a website search. This table typically contains information such as the URL or webpage that appeared as a search result, the number of times it was displayed to users, and potentially additional details like the search query that triggered the impression, the date and time of the search, or any user interactions with the URL.
ExportLog – a record of the successful exports of the tables mentioned above excluding failed exports.
Now you can start querying the data using SQL and generate more focused or specific datasets. For instance:
The top mobile web queries per region.
All web statistics by day for a given period
All search traffic by URL
And more.
Why this is important?
Bulk data export is particularly useful to websites that get tons of search queries daily or those that have numerous pages. These websites generate large volumes of data which require efficient data management and processing capabilities, which they may not find in the Search Console. BigQuery is well equipped to handle large volumes of data (over petabytes), allowing marketers to query the data instantly and quickly get new insights into their marketing analytics.
Adding BigQuery to your marketing stack is also useful in drawing more value from your Search Console data. BigQuery enables you to analyze the frequencies of particular search queries in specific regions. This helps in unlocking new content opportunities. Alternatively, if your business is venturing into a new region, this can be a basis for efficiently setting up your marketing strategy for this region.