Sarah Crooke - Deeper Dives Into Search: BigQuery Meets Google Search Console

Описание: From day 2 of Search With Sean Live.

Sarah is a digital expert with over 15 years' experience spanning development, project management, and account management, now focused on implementing analytics code and delivering data-driven insights with a passion for uncovering opportunities through her keen eye and digital expertise.

Sarah explores how to maximise the power of Search Console and BigQuery, showcasing real-life examples of using data via Google Cloud. She explains how to leverage these tools effectively and build a strong business case for BigQuery adoption.

Connect with Sarah -   / sarahcrooke  

Watch the full day 2 event (11 speakers included) -    • Search With Sean Live - Day 2  

0:00 – Intro: BigQuery + Google Search Console (GSC) with SQL
0:22 – About Sarah (Digital Analyst at Murum; 20 yrs digital; 10 yrs analytics)
1:00 – Wine course story → you don’t need to be an expert to benefit from SQL
1:58 – Why export GSC to BigQuery? (limits of the GSC interface)
2:43 – GSC UI limits: 1k rows, 16-month window, no flexible comparisons
3:28 – Bulk export to BigQuery: benefits & “set it up now” for retention
4:09 – Unlimited retention*, no 1k row limit, richer fields, joins, SQL power
4:49 – Join with GA4, Merchant Center, Google Ads & other data sources
5:02 – What you can do: seasonality, zero-click pages, unique query counts
5:51 – Demo goal: branded vs non-branded queries (quick analysis)
6:13 – Tables overview: site_impressions vs url_impressions (field tour)
7:32 – Previewing the data: anonymised queries, device/search type flags
8:23 – Why not export to Sheets? Use queries instead
8:52 – Writing your first query: SELECT + WHERE date filter
10:15 – Watching query cost (processed data) to stay within free tier
11:55 – Add clicks & impressions; handle NULLs; filter by date
14:40 – Aggregate with SUM + GROUP BY; ORDER BY impressions (DESC)
16:56 – Remove NULL queries with WHERE … AND query IS NOT NULL
17:42 – Add CTR and average position calculations (including rounding)
19:58 – Case statement: classify branded vs non-branded (LIKE with wildcards)
21:59 – Group and compare: branded vs non-branded performance output
22:55 – Cost control tips: test on single day; avoid SELECT *; billing alerts
24:29 – Building the business case: retention, low costs, cross-team value
26:02 – Learning & help: prompt ChatGPT for BigQuery+GSC SQL; “Lost at SQL”
27:48 – Course invite: BigQuery Pro for SEO (register interest)
28:17 – Wrap-up & thanks