Many digital marketers and analysts use BigQuery to bring marketing data sources together, like Google Analytics and Google Ads, to uncover insights about their marketing campaigns and websites. Google are excited to dive deeper into a new type of connection that adds Google Search data into this mix.
Earlier this year, Search Console announced bulk data exports, a new capability that allows users to export more Google Search data via BigQuery. This functionality allows you to analyze your search traffic in more detail, using BigQuery to run complex queries and create custom reports.
To create an export, you’ll need to perform tasks on both Cloud Console and Search Console.
Intro to Search performance data
The Performance data exported to BigQuery has three metrics that show how your search traffic changes over time:
- Clicks: Count of user clicks from Google Search results to your property.
- Impressions: Count of times users saw your property on Google search results.
- Position: The average position in search results for the URL, query, or for the website in general.
Each of those metrics can be analyzed for different dimensions. You can check how each of the queries, pages, countries, devices, or search appearances driving traffic to your website is performing.
If you’d like to learn more about the data schema, check out the table guidelines and reference in the Search Console help center.
Querying the data in BigQuery
If you need a little help to start querying the data, check the query guidelines and sample queries published in the help center, they can be handy to get up and running. Here’s one example, where we pull the USA mobile web queries in the last two weeks.
SELECT
query,
device,
sum(impressions) AS impressions,
sum(clicks) AS clicks,
sum(clicks) / sum(impressions) AS ctr,
((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
AND country = 'usa'
AND device = 'MOBILE'
AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000
Benefits of Bulk data exports
There are several benefits of exporting Search Console data to BigQuery:
- Analyze Google Search traffic in more detail. If you have a large website, this solution will provide more queries and pages than the other data exporting solutions.
- Run complex queries and create custom reports. While the Search Console interface allows you to perform simple analyses, it’s optimized for speed and for the average user. Using BigQuery will open many possibilities in data processing and visualization.
- Store data as long as you want. Search Console stores up to sixteen months of data; using BigQuery you can store as much data as it makes sense to your organization. Please note that by default data is kept forever in your BigQuery dataset, if you’d like to limit your storage costs you can update the default partition expiration times.
- Create and execute machine learning models. Machine learning on large datasets requires extensive programming and knowledge of frameworks; using BigQuery ML, you can increase development capabilities and speed with simple SQL.
- Apply pre-existing data security rules. If you use BigQuery data security and governance features, you can expand them to include your search data on BigQuery. This means you don’t need separate rules for separate products.
Google hope that this solution will help you store, analyze, and visualize your Search data in a more effective and scalable way. If you want to try out the Search Console export in BigQuery, you’ll need a billing account to do so. You can sign up for a free trial and add your billing account to get started analyzing Search Console data.