Basic SQL query<\/strong><\/h3>\n\n\n\nLet’s say I want to view a table that shows the URL, query (keyword), clicks, impressions, CTR and average position. In many respects, it’s similar to what I would see in Google Search Console. However, in this table, we can see the URL and query data together. That’s incredibly useful! We can even sort our results by URL and clicks, meaning that query data will show from most to least clicks while also being grouped by URL.<\/p>\n\n\n\n
In the screenshot below, I used a URL and impressions filter instead because my website hardly generates clicks from Google! The results would look untidy and depressing to look at.<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
And here is the code for you to copy\/paste:<\/p>\n\n\n\n
SELECT\n url,\n query,\n sum(impressions) as impressions,\n sum(clicks) as clicks,\n ROUND(sum(clicks) \/ sum(impressions),3) AS ctr,\n (ROUND((sum(sum_position) \/ sum(impressions))) + 1.0) AS avg_position,\nFROM `gsc-bulk-data-export.searchconsole.searchdata_url_impression`\nWHERE search_type = 'WEB'\n AND query IS NOT null\nGROUP BY url,query\nORDER BY url,clicks desc<\/code><\/pre>\n\n\n\nBranded vs non-branded search queries<\/strong><\/h3>\n\n\n\nOne of the most used search filters in Google Search Console is the branded vs non-branded search results. With BigQuery, we can find this type of information but on a much bigger scale. For the query below, we will add an additional column to our table that tells us whether the query is “branded” or “other”, alongside the usual clicks, impressions, CTR and average position.<\/p>\n\n\n\n
Don’t forget to change the “brandname” found in the CASE WHEN statement to your website’s brand name variants.<\/p>\n\n\n
\n
<\/figure><\/div>\n\n\n<\/div>\n\n\n\n
You can copy\/paste the code here:<\/p>\n\n\n\n
SELECT\r\n query,\r\n clicks,\r\n impressions,\r\n sum(clicks) \/ sum(impressions) AS ctr,\r\n (ROUND((sum(sum_position) \/ sum(impressions))) + 1.0) AS avg_position,\r\n case \r\n when regexp_contains(query, \"brandname1|brandname2|brandname3|brandname4\") then \"branded_queries\"\r\n else \"Other\"\r\n END AS navigational\r\nFROM\r\n `gsc-daily-export.searchconsole.searchdata_url_impression`\r\nWHERE query IS NOT null\r\nGROUP BY query, clicks, impressions\r\nORDER BY navigational DESC<\/code><\/pre>\n\n\n\nBranded vs non-branded total queries<\/strong><\/h3>\n\n\n\nLet’s take the “branded” vs “other” data from our last query and nest it in another table to get a high-level view of how many queries, clicks and impressions does each category receive from Google.<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
You can copy\/paste the code here:<\/p>\n\n\n\n
WITH\nGSC_Table AS (\nSELECT\n query,\n clicks,\n impressions,\n sum(clicks) \/ sum(impressions) AS ctr,\n (ROUND((sum(sum_position) \/ sum(impressions))) + 1.0) AS avg_position,\n case \n when regexp_contains(query, \"brandname1|brandname2|brandname3|brandname4\") then \"branded_queries\"\n else \"other\"\n END AS navigational\nFROM\n `gsc-daily-export.searchconsole.searchdata_url_impression`\nWHERE query IS NOT null\nGROUP BY query, clicks, impressions\n)\nSELECT\nnavigational,\nCOUNT(query) as total_query_count,\nsum(clicks) as total_clicks,\nsum(impressions) as total_impressions\nFROM GSC_Table\nGROUP BY navigational\nORDER BY total_query_count DESC<\/code><\/pre>\n\n\n\nThe results show us the following data:<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
Add a subdirectory column to your table<\/strong><\/h3>\n\n\n\nFor this SQL query, let’s pretend that we’re a big ecommerce website, and we would like to analyze data for one of our product categories. So, we’ll pretend that we’re working for ASOS, a leading fashion ecommerce brand, and we want to know what women’s shoe type generates the best results from Google organic search. We’ll look at the top navigation menu of the ASOS website and select the product categories highlighted in the red box. Thankfully, the categories use a logical URL subdirectory structure that we can use for our filtering.<\/p>\n\n\n\n
Because I don’t have access to ASOS data, I created a separate dummy database and added those subdirectories to my own website URLs. Weird, I know! But stay with me.<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
For our example below, we’ll create a table that includes the URL, total number of keywords, total clicks, total impressions, CTR, average position and category grouping based on the women’s shoe subdirectory structure.<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
And here is the code for you to copy\/paste:<\/p>\n\n\n\n
SELECT\n\t url,\n\t COUNT(DISTINCT query) as total_keywords,\n sum(clicks) AS clicks,\n\t sum(impressions) AS impressions,\n\t (sum(clicks) \/ sum(impressions)) AS ctr,\n\t ROUND((sum(sum_position) \/ sum(impressions)) + 1.0) AS avg_position,\n CASE\n\t\t WHEN url LIKE '%\/women\/shoes\/trainers\/%' THEN 'Trainers'\n\t\t WHEN url LIKE '%\/women\/shoes\/boots\/%' THEN 'Boots'\n\t\t WHEN url LIKE '%\/women\/shoes\/heels\/%' THEN 'Heels'\n\t\t WHEN url LIKE '%\/women\/shoes\/sandals\/%' THEN 'Sandals'\n\t\t WHEN url LIKE '%\/women\/shoes\/flat-shoes\/%' THEN 'Flat Shoes'\n\t\t WHEN url LIKE '%\/women\/shoes\/loafers\/%' THEN 'Loafers'\n\t\t WHEN url LIKE '%\/women\/sportswear\/shoes\/%' THEN 'Sportswear Shoes'\n\t\t WHEN url LIKE '%\/women\/shoes\/wide-fit-shoes\/%' THEN 'Wide Fit Shoes'\n\t\t WHEN url LIKE '%\/women\/shoes\/flat-sandals\/%' THEN 'Flat Sandals' \n ELSE\n 'Other'\n\tEND\n AS Category\n\t\t FROM `gsc-bulk-data-export.searchconsole.searchdata_url_impression`\n\t\t GROUP BY url\n ORDER BY clicks DESC<\/code><\/pre>\n\n\n\nWe use a CASE WHEN statement to filter our URLs by the different shoe types. The results are similar to those in an Excel Pivot table. Of course, for your data, you want to ensure that you change the subdirectory URL references found in the WHEN statements according to your website\u2019s structure.<\/p>\n\n\n\n
Best performing subdirectory<\/strong><\/h3>\n\n\n\nContinuing with our ASOS example, we’ve previously added an extra column showing each URL’s category. But what if we wanted to know what is the best-performing women’s shoe category? For that, we’ll wrap our last query in a nested table and create an additional SQL query to provide us with the answer.<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
Here is the code for you to copy\/paste:<\/p>\n\n\n\n
WITH\nGSC_Table AS (\nSELECT\n\t\t url,\n\t\t COUNT(DISTINCT query) as total_keywords,\n sum(clicks) AS clicks,\n\t\t sum(impressions) AS impressions,\n\t\t (sum(clicks) \/ sum(impressions)) AS ctr,\n\t\t ROUND((sum(sum_position) \/ sum(impressions)) + 1.0) AS avg_position,\n CASE\n WHEN url LIKE '%\/women\/shoes\/trainers\/%' THEN 'Trainers'\n WHEN url LIKE '%\/women\/shoes\/boots\/%' THEN 'Boots'\n WHEN url LIKE '%\/women\/shoes\/heels\/%' THEN 'Heels'\n WHEN url LIKE '%\/women\/shoes\/sandals\/%' THEN 'Sandals'\n WHEN url LIKE '%\/women\/shoes\/flat-shoes\/%' THEN 'Flat Shoes'\n WHEN url LIKE '%\/women\/shoes\/loafers\/%' THEN 'Loafers'\n WHEN url LIKE '%\/women\/sportswear\/shoes\/%' THEN 'Sportswear Shoes'\n WHEN url LIKE '%\/women\/shoes\/wide-fit-shoes\/%' THEN 'Wide Fit Shoes'\n WHEN url LIKE '%\/women\/shoes\/flat-sandals\/%' THEN 'Flat Sandals' \n ELSE\n 'Other'\n\tEND\n AS Category\n\t\t FROM `gsc-bulk-data-export.searchconsole.searchdata_url_impression`\n\t\t GROUP BY url\n)\nSELECT\nGSC_Table.Category,\nGSC_Table.total_keywords,\nsum(GSC_Table.clicks) as clicks,\nsum(GSC_Table.impressions) as impressions,\nFROM GSC_Table\nWHERE clicks > 10\nGROUP BY GSC_Table.Category,GSC_Table.total_keywords,\nORDER BY clicks DESC<\/code><\/pre>\n\n\n\nThe SQL query above provides us with the following results:<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
This is incredibly powerful! Now we can see how many keywords each category ranks for and the total number of clicks and impressions it gets from the Google search results.<\/p>\n\n\n\n
Add position grouping<\/strong><\/h3>\n\n\n\nUsing a similar methodology to what we did before to segment product categories of an ecommerce website, we can use the CASE WHEN statement to add an extra column to our table that shows the position grouping for each query.<\/p>\n\n\n\n
The SQL query below allows us to create a table that shows the query, URL, position grouping, clicks and impressions:<\/p>\n\n\n\n <\/figure>\n\n\n\n<\/div>\n\n\n\n
Here is the code for you to copy\/paste:<\/p>\n\n\n\n
WITH\n GSC_Table_Inner AS (\n SELECT\n url,\n query,\n sum(impressions) as impressions,\n sum(clicks) as clicks,\n sum(clicks) \/ sum(impressions) AS ctr,\n (ROUND((sum(sum_position) \/ sum(impressions))) + 1.0) AS avg_position\n FROM `gsc-bulk-data-export.searchconsole.searchdata_url_impression`\n WHERE query IS NOT null\n GROUP BY url,query\n )\n SELECT\n query,\n url,\n CASE\n WHEN avg_position > 1 AND avg_position < 4 THEN 'Positions 1 - 3'\n WHEN avg_position > 3 AND avg_position < 11 THEN 'Positions 4 - 10'\n WHEN avg_position > 10 AND avg_position < 21 THEN 'Positions 11 - 20'\n WHEN avg_position > 20 AND avg_position < 51 THEN 'Positions 20 - 50'\n WHEN avg_position > 50 AND avg_position < 101 THEN 'Positions 50 - 100'\n WHEN avg_position > 100 THEN 'Positions 100 +'\n END\n AS Position,\n clicks,\n impressions,\n FROM GSC_Table_Inner\n ORDER BY GSC_Table_Inner.clicks DESC<\/code><\/pre>\n\n\n\n