{"id":29238,"date":"2024-02-12T15:02:19","date_gmt":"2024-02-12T15:02:19","guid":{"rendered":"https:\/\/www.searchenginejournal.com\/get-started-bigquery-queries\/505149\/"},"modified":"2024-02-12T15:02:19","modified_gmt":"2024-02-12T15:02:19","slug":"get-started-with-gsc-queries-in-bigquery","status":"publish","type":"post","link":"https:\/\/marketingnewsbox.com\/?p=29238","title":{"rendered":"Get Started With GSC Queries In BigQuery"},"content":{"rendered":"<p>BigQuery has a number of advantages not found with other tools when it comes to analyzing large volumes of <a href=\"https:\/\/www.searchenginejournal.com\/google-search-console-guide\/209318\/\">Google Search Console<\/a> (GSC) data.<\/p>\n<p>It lets you process billions of rows in seconds, enabling deep analysis across massive datasets.<\/p>\n<p>This is a <a href=\"https:\/\/www.searchenginejournal.com\/google-search-console-data-bigquery-enhanced-analytics\/496535\/\">step up from Google Search Console<\/a>, which only allows you to export 1,000 rows of data and may have <a href=\"https:\/\/support.google.com\/webmasters\/answer\/7576553\/#data_discrepancies\" rel=\"nofollow noopener noreferrer\" target=\"_blank\">data discrepancies<\/a>.<\/p>\n<p>You read all about why you should be using <a href=\"https:\/\/cloud.google.com\/bigquery?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">BigQuery<\/a> as an SEO pro. You figured out how to plug GSC with BigQuery. Data is flowing!<\/p>\n<p>Now what?<\/p>\n<p>It\u2019s time to start querying the data. Understanding and effectively querying the data is key to gaining actionable SEO insights.<\/p>\n<p>In this article, we\u2019ll walk through how you can get started with your queries.<\/p>\n<h2>Understanding GSC Data Structure In BigQuery<\/h2>\n<p>Data is organized in tables. Each table corresponds to a specific Google Search Console report. The <a href=\"https:\/\/support.google.com\/webmasters\/answer\/12917991?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">official documentation<\/a> is very extensive and clear.<\/p>\n<p>However, if you are reading this, it\u2019s because you want to understand the context and the key elements before diving into it.<\/p>\n<p>Taking the time to figure this out means that you will be able to create better queries more efficiently while keeping the costs down.<\/p>\n<h3>GSC Tables, Schema &amp; Fields In BigQuery<\/h3>\n<p>Schema is the blueprint that maps what each field (each piece of information) represents in a table.<\/p>\n<p>You have <a href=\"https:\/\/support.google.com\/webmasters\/answer\/12917991?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">three distinct schemas<\/a> presented in the official documentation because each table doesn\u2019t necessarily hold the same type of data. Think of tables as dedicated folders that organize specific types of information.<\/p>\n<p>Each report is stored separately for clarity. You\u2019ve got:<\/p>\n<ul>\n<li><strong>searchdata_site_impression<\/strong>: Contains performance data for your property aggregated by property.<\/li>\n<li><strong>searchdata_url_impression<\/strong>: Contains performance data for your property aggregated by URL.<\/li>\n<li><strong>exportLog<\/strong>: each successful export to either table is logged here.<\/li>\n<\/ul>\n<p>A few important notes on tables:<\/p>\n<ul>\n<li>You\u2019ll find in the <a href=\"https:\/\/support.google.com\/webmasters\/answer\/12917991?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">official documentation<\/a> that things don\u2019t run the way we expect them to: \u201cSearch Console exports bulk data once per day, though not necessarily at the same time for each table.\u201d<\/li>\n<li>Tables are retained forever, by default, with the GSC bulk export.<\/li>\n<li>In the URL level table (searchdata_url_impression), <a href=\"https:\/\/www.linkedin.com\/posts\/myriamjessier_bigquery-x-google-discover-nerd-moment-activity-7148946428562776064-TBC9?utm_source=share&amp;utm_medium=member_desktop\" target=\"_blank\" rel=\"noopener noreferrer\">you have Discover data<\/a>. The field is_anonymized_discover specifies if the data row is subject to the Discover anonymization threshold.<\/li>\n<\/ul>\n<p>Fields are individual pieces of information, the specific type of data in a table. If this were an Excel file, we\u2019d refer to fields as the columns in a spreadsheet.<\/p>\n<p>If we\u2019re talking about <a href=\"https:\/\/www.searchenginejournal.com\/google-analytics-4-guide\/407452\/\">Google Analytics<\/a>, fields are metrics and dimensions. Here are key data fields available in BigQuery when you import GSC data:<\/p>\n<ul>\n<li><strong>Clicks<\/strong> \u2013 Number of clicks for a query.<\/li>\n<li><strong>Impressions<\/strong> \u2013 Number of times a URL was shown for a query.<\/li>\n<li><strong>CTR<\/strong> \u2013 Clickthrough rate (clicks\/impressions).<\/li>\n<li><strong>Position<\/strong> \u2013 Average position for a query.<\/li>\n<\/ul>\n<p>Let\u2019s take the searchdata_site_impression table schema as an example. It contains 10 fields:<\/p>\n<div class=\"scrl-table\">\n<table>\n<tbody>\n<tr>\n<td><b>Field<\/b><\/td>\n<td><b>Explanation<\/b><\/td>\n<\/tr>\n<tr>\n<td>data_date<\/td>\n<td>The day when the data in this row was generated, in Pacific Time.<\/td>\n<\/tr>\n<tr>\n<td>site_url<\/td>\n<td>URL of the property, sc-domain:property-name or the full URL, depending on your validation.<\/td>\n<\/tr>\n<tr>\n<td>query<\/td>\n<td>The user\u2019s search query.<\/td>\n<\/tr>\n<tr>\n<td>is_anonymized_query<\/td>\n<td>If true, the query field will return null.<\/td>\n<\/tr>\n<tr>\n<td>country<\/td>\n<td>Country from which the search query originated.<\/td>\n<\/tr>\n<tr>\n<td>search_type<\/td>\n<td>Type of search (web, image, video, news, discover, googleNews).<\/td>\n<\/tr>\n<tr>\n<td>device<\/td>\n<td>The device used by the user.<\/td>\n<\/tr>\n<tr>\n<td>impressions<\/td>\n<td>The number of times a URL was shown for a particular search query.<\/td>\n<\/tr>\n<tr>\n<td>clicks<\/td>\n<td>The number of clicks a URL received for a search query.<\/td>\n<\/tr>\n<tr>\n<td>sum_top_position<\/td>\n<td>This calculation figures out where your website typically ranks in search results. It looks at the highest position your site reaches in different searches and calculates the average.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h3>Putting It Together<\/h3>\n<p>In BigQuery, the dataset for the Google Search Console (GSC) bulk export typically refers to the collection of tables that store the GSC data.<\/p>\n<p>The dataset is named \u201csearchconsole\u201d by default.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-505162 aligncenter b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/console-659bd408d0698-sej-480x204.png\" alt=\"BigQuery search console tables\" width=\"480\" height=\"204\"><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/console-659bd408d0698-sej-480x204.png\" alt=\"BigQuery search console tables\"><\/p>\n<p>Unlike the performance tab in GSC, you have to write queries to ask BigQuery to return data. To do that, you need to click on the \u201cRun a query in BigQuery\u201d button.<\/p>\n<div id=\"attachment_505164\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-505164 size-full b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/run-query-welcome-screen-659bd57012c0c-sej.png\" alt=\"Run SQL query option among three other options on the welcome screen\" width=\"2245\" height=\"802\"><span class=\"wp-caption-text\">Screenshot from Google Cloud Console, January 2024<\/span><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/run-query-welcome-screen-659bd57012c0c-sej.png\" alt=\"Run SQL query option among three other options on the welcome screen\"><\/div>\n<p>Once you do that, you should have access to the BigQuery Studio, where you will be creating your first SQL query. However,<strong> I don\u2019t recommend you click on that button yet<\/strong>.<\/p>\n<div id=\"attachment_505165\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-505165 size-full b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/welcome-to-big-query-studio-659bd5d15efb5-sej.png\" alt=\"access screen to the BigQuery Studio where you will be creating your first SQL query. \" width=\"2880\" height=\"1242\"><span class=\"wp-caption-text\">Screenshot of BigQuery Studio, January 2024<\/span><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/welcome-to-big-query-studio-659bd5d15efb5-sej.png\" alt=\"access screen to the BigQuery Studio where you will be creating your first SQL query. \"><\/div>\n<p>In Explorer, when you open your project, you will see the datasets; it\u2019s a logo with squares with dots in them. This is where you see if you have <a href=\"https:\/\/www.searchenginejournal.com\/ga4-pro-tips-even-if-youre-a-beginner-smb\/492526\/\">GA4<\/a> and GSC data, for instance.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-505167 size-content_large_1x b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/table-preview-659bd6b200c40-sej-768x357.png\" alt=\"data set for search impression table \" width=\"760\" height=\"353\"><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/table-preview-659bd6b200c40-sej-768x357.png\" alt=\"data set for search impression table \"><\/p>\n<p>When you click on the tables, you get access to the schema. You can see the fields to confirm this is the table you want to query.<\/p>\n<p>If you click on \u201cQUERY\u201d at the top of the interface, you can create your SQL query. <strong>This is better because it loads up some information you need for your query<\/strong>.<\/p>\n<p>It will fill out the FROM with the proper table, establish a default limit, and the date that you can change if you need to.<\/p>\n<div id=\"attachment_505168\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-505168 size-full b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/first-query-in-big-query-659bd72951bba-sej.png\" alt=\" If you click on \u201cQUERY\u201d at the top in the interface, you can create your SQL query. This is better because it loads up some information you need for your query.\" width=\"1600\" height=\"741\"><span class=\"wp-caption-text\">Screenshot from Google Cloud Console, January 2024<\/span><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/first-query-in-big-query-659bd72951bba-sej.png\" alt=\" If you click on \u201cQUERY\u201d at the top in the interface, you can create your SQL query. This is better because it loads up some information you need for your query.\"><\/div>\n<h2>Getting Started With Your First Query<\/h2>\n<blockquote class=\"twitter-quote\" id=\"tweet-1628044327057342465\" data-width=\"550\" data-dnt=\"true\">\n<p lang=\"en\" dir=\"ltr\">Search Console &gt; BigQuery export was previously only available to companies with devs\/ a super techy SEO. Now it&#8217;s available to everyone!<\/p>\n<p>Writing SQL is a more and more important skill for marketers &amp; I&#8217;m making something to help with that \u2013 if you&#8217;d like to test it DM me &#x1f642; <a href=\"https:\/\/t.co\/voOESJfo1e\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/t.co\/voOESJfo1e<\/a><\/p>\n<p>\u2014 Robin Lord (@RobinLord8) <a href=\"https:\/\/twitter.com\/RobinLord8\/status\/1628044327057342465?ref_src=twsrc%5Etfw\" target=\"_blank\" rel=\"noopener noreferrer\">February 21, 2023<\/a><\/p>\n<\/blockquote>\n<p>The queries we are going to discuss here are simple, efficient, and low-cost.<\/p>\n<p>Disclaimer: The previous statement depends on your specific situation.<\/p>\n<p>Sadly, you cannot stay in the sandbox if you want to learn how to use BigQuery with GSC data. You must enter your billing details. If this has you freaked out, fear not; <a href=\"https:\/\/cloud.google.com\/bigquery\/pricing\" target=\"_blank\" rel=\"noopener noreferrer\">costs should be low<\/a>.<\/p>\n<ul>\n<li>The first 1 TiB per month of query data is free.<\/li>\n<li>If you have a tight budget, you can set <a href=\"https:\/\/cloud.google.com\/billing\/docs\/how-to\/budgets\" target=\"_blank\" rel=\"noopener noreferrer\">cloud billing budget alerts<\/a> \u2014 you can set a BigQuery-specific alert and get notified as soon as data usage charges occur.<\/li>\n<\/ul>\n<p>In SQL, the \u2018SELECT *\u2019 statement is a powerful command used to retrieve all columns from a specified table or retrieve specific columns as per your specification.<\/p>\n<p>This statement enables you to view the entire dataset or a subset based on your selection criteria.<\/p>\n<p>A table comprises rows, each representing a unique record, and columns, storing different attributes of the data. Using \u201cSELECT *,\u201d you can examine all fields in a table without specifying each column individually.<\/p>\n<p>For instance, to explore a Google Search Console table for a specific day, you might employ a query like:<\/p>\n<p><code>SELECT *<\/code><\/p>\n<p><code>FROM `<b>yourdata<\/b>.searchconsole.searchdata_site_impression`<\/code><\/p>\n<p><code>WHERE data_date = '2023-12-31'<\/code><\/p>\n<p><code>LIMIT 5;<\/code><\/p>\n<p>You always need to make sure that the FROM clause specifies your searchdata_site_impression table. That\u2019s why it is recommended to start by clicking the table first, as it automatically fills in the FROM clause with the right table.<\/p>\n<p><strong>Important<\/strong>: We limit the data we load by using the data_date field. It\u2019s a good practice to limit costs (along with setting a limit).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-505169 size-content_large_1x b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/return-results-from-query-659bd7d6823d3-sej-768x397.png\" alt=\"results from the first query we made shown in a table format\" width=\"760\" height=\"393\"><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/return-results-from-query-659bd7d6823d3-sej-768x397.png\" alt=\"results from the first query we made shown in a table format\"><\/p>\n<h2>Your First URL Impression Query<\/h2>\n<p>If you want to see information for each URL on your site, you\u2019d ask BigQuery to pull information from the \u2018searchdata_url_impression\u2019 table, selecting the \u2018query\u2019 and \u2018clicks\u2019 fields.<\/p>\n<p>This is what the query would look like in the console:<\/p>\n<p><code>SELECT<\/code><\/p>\n<p><code> url,<\/code><\/p>\n<p><code> SUM(clicks) AS clicks,<\/code><\/p>\n<p><code> SUM(impressions)<\/code><\/p>\n<p><code>FROM<\/code><\/p>\n<p><code> `yourtable.searchdata_url_impression`<\/code><\/p>\n<p><code>WHERE<\/code><\/p>\n<p><code> data_date = \u20182023-12-25\u2019<\/code><\/p>\n<p><code>GROUP BY<\/code><\/p>\n<p><code> url<\/code><\/p>\n<p><code>ORDER BY<\/code><\/p>\n<p><code> clicks DESC<\/code><\/p>\n<p><code>LIMIT<\/code><\/p>\n<p><code> 100<\/code><\/p>\n<p>You always need to make sure that the FROM clause specifies your searchdata_url_impression table.<\/p>\n<p>When you export GSC data into BigQuery, the export contains partition tables. The partition is the date.<\/p>\n<p>This means that the data in BigQuery is structured in a way that allows for quick retrieval and analysis based on the date.<\/p>\n<p>That\u2019s why the date is automatically included in the query. However, you may have no data if you select the latest date, as the data may not have been exported yet.<\/p>\n<h4>Breakdown Of The Query<\/h4>\n<p>In this example, we select the URL, clicks, and impressions fields for the 25th of December, 2023.<\/p>\n<p>We group the results based on each URL with the sum of clicks and impressions for each of them.<\/p>\n<p>Lastly, we order the results based on the number of clicks for each URL and limit the number of rows (URLs) to 100.<\/p>\n<h2>Recreating Your Favorite GSC Report<\/h2>\n<p>I recommend you read the <a href=\"https:\/\/www.searchenginejournal.com\/google-search-console-data-bigquery-enhanced-analytics\/496535\/\">GSC bulk data export guide<\/a>. You should be using the export, so I will not be providing information about table optimization. That\u2019s a tad bit more advanced than what we are covering here.<\/p>\n<p>GSC\u2019s performance tab shows one dimension at a time, limiting context. BigQuery allows you to combine multiple dimensions for better insights<\/p>\n<p>Using SQL queries means you get a neat table. You don\u2019t need to understand the ins and outs of SQL to make the best use of BigQuery.<\/p>\n<p>This query is courtesy of <a href=\"https:\/\/uk.linkedin.com\/in\/chrisgreenseo\" target=\"_blank\" rel=\"noopener noreferrer\"><span data-rich-links=\"{&quot;per_n&quot;:&quot;Chris Green&quot;,&quot;per_e&quot;:&quot;christopher.green@torquepartnership.com&quot;,&quot;type&quot;:&quot;person&quot;}\">Chris Green<\/span><\/a>. You can find some of his <a href=\"https:\/\/github.com\/chr156r33n\/sql-for-seo\/blob\/main\/gsc-query-search-data-sql\" target=\"_blank\" rel=\"noopener noreferrer\">SQL queries in Github<\/a>.<\/p>\n<p><code>SELECT<\/code><\/p>\n<p><code> query,<\/code><\/p>\n<p><code> is_anonymized_query AS anonymized,<\/code><\/p>\n<p><code> SUM(impressions) AS impressions,<\/code><\/p>\n<p><code> SUM(clicks) AS clicks,<\/code><\/p>\n<p><code> SUM(clicks)\/NULLIF(SUM(impressions), 0) AS CTR<\/code><\/p>\n<p><code>FROM<\/code><\/p>\n<p><code> yourtable.searchdata_site_impression<\/code><\/p>\n<p><code>WHERE<\/code><\/p>\n<p><code> data_date &gt;= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)<\/code><\/p>\n<p><code>GROUP BY<\/code><\/p>\n<p><code> query,<\/code><\/p>\n<p><code> anonymized<\/code><\/p>\n<p><code>ORDER BY<\/code><\/p>\n<p><code> clicks DESC<\/code><\/p>\n<p>This query provides insights into the performance of user queries over the last 28 days, considering impressions, clicks, and CTR.<\/p>\n<p>It also considers whether the queries are anonymized or not, and the results are sorted based on the total number of clicks in descending order.<\/p>\n<p><strong>This recreates the data you would normally find in the Search Console \u201cPerformance\u201d report for the last 28 days of data<\/strong>, results by query, and differentiating anonymized queries.<\/p>\n<p>Feel free to copy\/paste your way to glory, but always make sure you update the FROM clause with the right table name. If you are curious to learn more about how this query was built, here is the breakdown:<\/p>\n<ul>\n<li><strong>SELECT<\/strong> clause:\n<ul>\n<li>query: Retrieves the user queries.<\/li>\n<li>is_anonymized_query AS anonymized: Renames the is_anonymized_query field to anonymized.<\/li>\n<li>SUM(impressions) AS impressions: Retrieves the total impressions for each query.<\/li>\n<li>SUM(clicks) AS clicks: Retrieves the total clicks for each query.<\/li>\n<li>SUM(clicks)\/NULLIF(SUM(impressions), 0) AS CTR: Calculates the Click-Through Rate (CTR) for each query. The use of NULLIF prevents division by zero errors.<\/li>\n<\/ul>\n<\/li>\n<li><strong>FROM<\/strong> clause:\n<ul>\n<li>Specifies the source table as mytable.searchconsole.searchdata_site_impression.<\/li>\n<\/ul>\n<\/li>\n<li><strong>WHERE<\/strong> clause:\n<ul>\n<li>Filters the data to include only rows where the data_date is within the last 28 days from the current date.<\/li>\n<\/ul>\n<\/li>\n<li><strong>GROUP<\/strong> BY clause:\n<ul>\n<li>Groups the results by query and anonymized. This is necessary since aggregations (SUM) are performed, and you want the totals for each unique combination of query and anonymized.<\/li>\n<\/ul>\n<\/li>\n<li><strong>ORDER BY<\/strong> clause:\n<ul>\n<li>Orders the results by the total number of clicks in descending order.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Handling The Anonymized Queries<\/h2>\n<p>According to <a href=\"https:\/\/www.youtube.com\/watch?v=_CxWC1_2mXA\" target=\"_blank\" rel=\"noopener noreferrer\">Noah Learner<\/a>, the Google Search Console API delivers 25 times more data than the GSC performance tab for the same search, providing a more comprehensive view.<\/p>\n<p>In BigQuery, you can also access the information regarding anonymized queries.<\/p>\n<p><a href=\"https:\/\/www.searchenginejournal.com\/google-search-console-data-bigquery-enhanced-analytics\/496535\/\">It doesn\u2019t omit the rows<\/a>, which helps analysts get complete sums of impressions and clicks when you aggregate the data.<\/p>\n<p>Understanding the volume of anonymized queries in your Google Search Console (GSC) data is key for SEO pros.<\/p>\n<p>When Google anonymizes a query, it means the actual search query text is hidden in the data. This impacts your analysis:<\/p>\n<ul>\n<li>Anonymized queries remove the ability to parse search query language and extract insights about searcher intent, themes, etc.<\/li>\n<li>Without the query data, you miss opportunities to identify new keywords and optimization opportunities.<\/li>\n<li>Not having query data restricts your capacity to connect search queries to page performance.<\/li>\n<\/ul>\n<h3>The First Query Counts The Number Of Anonymized Vs. Not Anonymized Queries<\/h3>\n<p><code>SELECT<\/code><\/p>\n<p><code> CASE<\/code><\/p>\n<p><code> WHEN query is NULL AND is_anonymized_query = TRUE THEN \"no query\"<\/code><\/p>\n<p><code> ELSE<\/code><\/p>\n<p><code> \"query\"<\/code><\/p>\n<p><code> END<\/code><\/p>\n<p><code> AS annonymized_query,<\/code><\/p>\n<p><code> count(is_anonymized_query) as query_count<\/code><\/p>\n<p><code>FROM<\/code><\/p>\n<p><code> `yourtable.searchdata_url_impression`<\/code><\/p>\n<p><code>GROUP BY annonymized_query<\/code><\/p>\n<h4>Breakdown Of The Query<\/h4>\n<p>In this example, we use a CASE statement in order to verify for each row if the query is anonymized or not.<\/p>\n<p>If so, we return \u201cno query\u201d in the query field; if not, \u201cquery.\u201d<\/p>\n<p>We then count the number of rows each query type has in the table and group the results based on each of them. Here\u2019s what the result looks like:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-505197 aligncenter b-lazy pcimg\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/anonymized-queries-659c17f4b5d51-sej-480x106.png\" alt=\"anonymized queries shown in results\" width=\"480\" height=\"106\"><\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.searchenginejournal.com\/wp-content\/uploads\/2024\/01\/anonymized-queries-659c17f4b5d51-sej-480x106.png\" alt=\"anonymized queries shown in results\"><\/p>\n<h2>Advanced Querying For SEO Insights<\/h2>\n<p>BigQuery enables complex analysis you can\u2019t pull off in the GSC interface. This means you can also create customized intel by surfacing patterns in user behavior.<\/p>\n<p>You can analyze search trends, seasonality over time, and keyword optimization opportunities.<\/p>\n<p>Here are some things you should be aware of to help you debug the filters you put in place:<\/p>\n<ul>\n<li>The date could be an issue. <a href=\"https:\/\/www.linkedin.com\/feed\/update\/urn:li:share:7102923187029499905\/\" target=\"_blank\" rel=\"noopener noreferrer\">It may take up to two days for you to have the data you want to query<\/a>. If BigQuery says on the top right corner that your query would require 0mb to run, it means the data you want isn\u2019t there yet or that there is no data for your query.<\/li>\n<li><a href=\"https:\/\/cloud.google.com\/bigquery\/docs\/samples\/bigquery-browse-table?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">Use the preview<\/a> if you want to see what a field will return in terms of value. It shows you a table with the data.<\/li>\n<li>The country abbreviations you will get in BigQuery are in a different format (<a href=\"https:\/\/support.google.com\/webmasters\/answer\/12917991?hl=en\" target=\"_blank\" rel=\"noopener noreferrer\">ISO-3166-1-Alpha-3 format<\/a>) than you are used to. Some examples: FRA for France, UKR for Ukraine, USA for the United States, etc.<\/li>\n<li>Want to get \u201cpretty\u201d queries? Click on \u201cmore\u201d within your query tab and select \u201cFormat query.\u201d BigQuery will handle that part for you!<\/li>\n<li>If you want more queries right away, I suggest you sign up for the <a href=\"https:\/\/seotistics.com\/best-seo-and-analytics-newsletter\/\" target=\"_blank\" rel=\"noopener noreferrer\">SEOlytics newsletter<\/a>, as there are quite a few SQL queries you can use.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>Analyzing GSC data in BigQuery unlocks transformative SEO insights, enabling you to track search performance at scale.<\/p>\n<p>By following the best practices outlined here for querying, optimizing, and troubleshooting, you can get the most out of this powerful dataset.<\/p>\n<p>Reading this isn\u2019t going to make you an expert instantly. This is the first step in your adventure!<\/p>\n<p>If you want to know more, check out <a href=\"https:\/\/gofishdigital.com\/blog\/how-to-use-bigquery-with-google-search-console\/\" target=\"_blank\" rel=\"noopener noreferrer\">Jake Peterson\u2019s blog post<\/a>, start practicing for free with Robin Lord\u2019s <a href=\"https:\/\/lost-at-sql.therobinlord.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Lost at SQL game<\/a>, or simply stay tuned because I have a few more articles coming!<\/p>\n<p>If you have questions or queries, do not hesitate to let us know.<\/p>\n<p>More resources:<\/p>\n<hr>\n<p><em>Featured Image: Tee11\/Shutterstock<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>BigQuery has a number of advantages not found with other tools when it comes to analyzing large volumes of Google Search Console (GSC) data. It lets you process billions of rows in seconds, enabling deep analysis across massive datasets. This is a step up from Google Search Console, which only allows you to export 1,000&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-29238","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/29238","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=29238"}],"version-history":[{"count":0,"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=\/wp\/v2\/posts\/29238\/revisions"}],"wp:attachment":[{"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=29238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=29238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/marketingnewsbox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=29238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}