Google Search Console to Big Query Export - The Almost Free SEO Database Full of Gold

Delivered at Brighton SEO October 2026, this deck details why you should be taking full advantage of the Google Search Console
to Big Query Export

Transcript

1. Google Search Console Big Query Export - The Almost Free SEO Database Full of Gold @charlesmeaden #brightonseo
2. It Used To Be So Easy “Charles, can’t you just do one of the clever reports you used to run 15 years ago The one where we could match every purchase to a brand or generic term” @charlesmeaden #brightonseo
3. 15 Years Ago We Had This Every keyword inside Google Analytics @charlesmeaden #brightonseo
4. Then Google Took It All Away Septemer 2013 was a bad month for SEO @charlesmeaden #brightonseo
5. Along Came Google Search Console • 2015 we got 90 days of data • 2018 saw 16 months @charlesmeaden #brightonseo
6. Good Luck Getting Page and Query Together @charlesmeaden #brightonseo
7. You Don’t Get All The Queries • Take Total Clicks • Add this Regex Query .* • That’s What You’re Losing @charlesmeaden #brightonseo
8. Google on Anonymised Queries Anonymized queries are those that aren't issued by more than a few dozen users over a two-to-three month period. To protect privacy, the actual queries won't be shown in the Search performance data. This is why we refer to them as anonymized queries. While the actual anonymized queries are always omitted from the tables, they are included in chart totals, unless you filter by query. @charlesmeaden #brightonseo
9. The Web UI Limits You To 1000 Rows @charlesmeaden #brightonseo
10. The GSC API Gives You • Page and Query together • 100,000 of rows • Use Regular Expressions • Easy to access using tools such as Analytics Edge @charlesmeaden #brightonseo
11. Still Limited to 16 Months of Data Unless you’re archiving and storing it You lose it @charlesmeaden #brightonseo
12. The Big Query Export Rolled out in 2023, it automatically exports your data every day to a dedicated Big Query table @charlesmeaden #brightonseo
13. What Is Google Big Query? A cloud based SQL database that allows you to store terabytes of data and query in seconds @charlesmeaden #brightonseo
14. You May Get More Query Data Our ecommerce client gets 6X more unique queries @charlesmeaden #brightonseo
15. You Keep The Data The 16 month limit disappears @charlesmeaden #brightonseo
16. 30 Minutes To Setup • Owner or delegated owner • Have a credit card • Follow simple instructions @charlesmeaden #brightonseo
17. The Storage Costs Are Tiny • Standard Big Query is 2p per Gigabye • Monthly cost for this customer is £2.32 @charlesmeaden #brightonseo
18. Easy Exporting • Export CSV and JSON • Send to another BigQuery Table @charlesmeaden #brightonseo
19. Query Millions of Rows in Seconds • Queried 3 years of data across 616 million rows • To find any query with red in it • Good luck doing that in Excel @charlesmeaden #brightonseo
20. You Get Almost Everything Still don’t get all queries back, but you’ll know when they are missing @charlesmeaden #brightonseo
21. The Cost Is In The Querying • Each row contains 44 columns • You don’t need to include everything @charlesmeaden #brightonseo
22. Do I Need To Know SQL? • Just a little to get you started • If I can, you can • LLM’s are pretty good at helping • Ask them to comment the code @charlesmeaden #brightonseo
23. Query Just What You Need For our “red” query we only needed to query ¼ of the data @charlesmeaden #brightonseo
24. Lets Break Down A Basic Query Find all the unique queries for a specific time period sum the impresssions and clicks, calculate the CTR and average position SELECT WHERE query, SUM(impressions) AS total_impressions, SUM(clicks) AS total_clicks, -- Click-Through Rate (e.g. 0.0524 = 5.24%) SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr, -- Average Position (adds 1 to convert from 0-based to 1-based indexing) is_anonymized_query = false AND data_date >= '2023-01-01' AND data_date <= '2023-10-05' AND country = 'gbr' GROUP BY query ROUND(SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1.0, 2) AS avg_position ORDER BY FROM total_clicks DESC; `exampledata.searchconsole.searchdata_url_impression’ @charlesmeaden #brightonseo
25. SELECT Just the fields you need SELECT query, SUM(impressions) AS total_impressions, SUM(clicks) AS total_clicks @charlesmeaden #brightonseo
26. Calculated Fields Create new fields based on existing fields -- Click-Through Rate (e.g. 0.0524 = 5.24%) SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr, -- Average Position (adds 1 to convert from 0-based to 1-based indexing) ROUND(SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1.0, 2) AS avg_position @charlesmeaden #brightonseo
27. FROM The relevant Big Query table FROM exampledata.searchconsole.searchdata_url_impression @charlesmeaden #brightonseo
28. WHERE How we want to filter the data WHERE is_anonymized_query = false AND data_date >= ‘2026-01-01' AND data_date <= ‘2026-10-05' And country = 'gbr' @charlesmeaden #brightonseo
29. GROUP BY How do want to organise our data GROUP BY query @charlesmeaden #brightonseo
30. ORDER BY How do want to organise our data ORDER BY total_clicks DESC @charlesmeaden #brightonseo
31. The Final Result @charlesmeaden #brightonseo
32. Filter Using Regular Expressions Narrow down your search queries • Phrases – mens? blue jeans? • Groups (red|yellow|green) • Whole words \bcar\b • Word combos (\b(cottages?|ireland)\b.*){2} @charlesmeaden #brightonseo
33. N-grams At Scale N-grams are a sequence of words • @charlesmeaden “Red tennis shoes” contains • Three 1 word ngrams • Two 2 word “red tennis” and “tennis shoe” • One 3 word ngram #brightonseo
34. What Are N-grams Useful For They reveal recurring word patterns, exposing common themes • Modifiers – colours, locations • Topics – running shoes • Intent signals – buy, book, best @charlesmeaden #brightonseo
35. Before Big Query Desktop based tool that could take hours to run @charlesmeaden #brightonseo
36. Big Query & N-Grams ML.NGRAMS will find them for you • Find me all 4 and 5 word ngrams containing red wine • 12,369 found in 1.7 seconds @charlesmeaden #brightonseo
37. CASE Statements CASE Statements allow you to group pages and queries into useful clusters @charlesmeaden #brightonseo
38. Pesky Anonymised Queries CASE WHEN is_anonymized_query IS TRUE THEN 'anonymised' ELSE 'available' END AS query_status, @charlesmeaden #brightonseo
39. Categorise Pages SELECT -- 1. Categorize URL into site sections using Regex CASE WHEN REGEXP_CONTAINS(url, r'^https://www\.gosimpletax\.com/?$') THEN 'Homepage' WHEN REGEXP_CONTAINS(url, r'^https://www\.gosimpletax\.com/blog/') THEN 'Blog' WHEN REGEXP_CONTAINS(url, r'^https://www\.gosimpletax\.com/features/') THEN 'Features' WHEN REGEXP_CONTAINS(url, r'^https://www\.gosimpletax\.com/pricing/?$') THEN 'Pricing' ELSE 'Other' END AS site_section, @charlesmeaden #brightonseo
40. Group Queries into Ranking Buckets CASE WHEN ((sum_position / impressions) + 1.0) >= 1.0 AND ((sum_position / impressions) + 1.0) <= 3.0 THEN '1 to 3' WHEN ((sum_position / impressions) + 1.0) > 3.0 AND ((sum_position / impressions) + 1.0) <= 5.0 THEN '4 to 5' WHEN ((sum_position / impressions) + 1.0) > 5.0 AND ((sum_position / impressions) + 1.0) <= 10.0 THEN '6 to 10' ELSE '11+' END AS ranking_tier, @charlesmeaden #brightonseo
41. Lets Put It All Together • Calculate the % of anonymized queries • By site section and ranking buckets • Count unique queries • For 2026 @charlesmeaden #brightonseo
42. The End Result @charlesmeaden #brightonseo
43. AI Functions Built Into BigQuery Link Big Query with Gemini and define via prompts • AI.SCORE – Scoring system • AI.CLASSIFY – Classifications • AI.IF – Yes or No • AI.AGG – Aggregate Data @charlesmeaden #brightonseo
44. To Get 100 Free Google Scrapes The sky above the port was the color of television, tuned to a dead channel @charlesmeaden #brightonseo
45. Thank You Connect with me on LinkedIn https://speakerdeck.com/charlesmeaden/ @charlesmeaden #brightonseo