Part 3: GSC Performance Metrics Deep Dive

« Learn SQL for SEO · Part 3 of 4

This series of videos lays the foundation for understanding and applying the GSC data.

The first video introduces the three main topics:

1. Overview of Search Metrics: A framework for understanding SEO metrics and levers
2. Defining Impressions and Position: Deep dive into impressions, positions, and clicks on real search pages
3. Site Impressions table vs URL impressions table: Let's build them in SQL to understand the differences

Introduction

Performance report metrics deep dive

This video explains the basics of Google Search Console metrics: impressions, positions, and clicks. We discuss how different search results, including AI-generated ones, affect these metrics. Then, dive into the details of the searchdata_site_impressions and searchdata_url_impressions tables.

0:00 Introduction to SEO Metrics and Levers 👉 Read the post about SEO Metrics and Levers
2:18 Defining Impressions and Positions
7:30 Defining search "elements"
29:08 Comparing searchdata_site_impressions and searchdata_url_impressions granularity
44:05 Conclusion and Next Steps

Defining GSC reporting tables in SQL

This video explains the differences between the two GSC tables in BigQuery by defining them in SQL from our fictional raw impression instances tables.

Example query: Synthesizing searchdata_raw_impressions

-- create-searchdata_raw_impressions_flat.sql
CREATE OR REPLACE TABLE your-project-name.searchconsole.searchdata_raw_impressions_flat AS
WITH ctr_curve AS (
  SELECT CAST(ROUND((sum_position / impressions) + 1) AS INT) AS position,
   SUM(clicks) AS total_clicks,
   SUM(impressions) AS total_impressions,
   SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr
  FROM `searchconsole.searchdata_url_impression`
  WHERE data_date > (
    SELECT MAX(data_date)
    FROM `searchconsole.ExportLog`
    WHERE namespace = 'SEARCHDATA_URL_IMPRESSION'
   ) - 7
   AND search_type = 'WEB'
  GROUP BY CAST(ROUND((sum_position / impressions) + 1) AS INT)
  ORDER BY position
 ),
 exploded_impressions AS (
  SELECT FARM_FINGERPRINT(
    CONCAT(
     url,
     site_url,
     IFNULL(query, CAST(RAND() AS STRING)),
     country,
     search_type,
     device,
     CAST(impression_instance AS STRING),
     CAST(data_date AS STRING),
     CAST(is_anonymized_query AS STRING),
     CAST(is_anonymized_discover AS STRING),
     CAST(is_amp_top_stories AS STRING),
     CAST(is_amp_blue_link AS STRING),
     CAST(is_job_listing AS STRING),
     CAST(is_job_details AS STRING),
     CAST(is_tpf_qa AS STRING),
     CAST(is_tpf_faq AS STRING),
     CAST(is_tpf_howto AS STRING),
     CAST(is_weblite AS STRING),
     CAST(is_action AS STRING),
     CAST(is_events_listing AS STRING),
     CAST(is_events_details AS STRING),
     CAST(is_search_appearance_android_app AS STRING),
     CAST(is_amp_story AS STRING),
     CAST(is_amp_image_result AS STRING),
     CAST(is_video AS STRING),
     CAST(is_organic_shopping AS STRING),
     CAST(is_review_snippet AS STRING),
     CAST(is_special_announcement AS STRING),
     CAST(is_recipe_feature AS STRING),
     CAST(is_recipe_rich_snippet AS STRING),
     CAST(is_subscribed_content AS STRING),
     CAST(is_page_experience AS STRING),
     CAST(is_practice_problems AS STRING),
     CAST(is_math_solvers AS STRING),
     CAST(is_translated_result AS STRING),
     CAST(is_edu_q_and_a AS STRING)
    )
   ) AS impression_id,
   *,
   -- Calculate position based on the sum_position and impressions
   CAST(
    ROUND(
     (sum_position / impressions) + 1 + (RAND() - 0.5)
    ) AS INT
   ) AS position
  FROM (
    SELECT url,
     site_url,
     IFNULL(query, 'PRIVACY_PROTECTED') AS query,
     country,
     search_type,
     device,
     impression_instance,
     data_date,
     sum_position,
     impressions,
     is_anonymized_query,
     is_anonymized_discover,
     is_amp_top_stories,
     is_amp_blue_link,
     is_job_listing,
     is_job_details,
     is_tpf_qa,
     is_tpf_faq,
     is_tpf_howto,
     is_weblite,
     is_action,
     is_events_listing,
     is_events_details,
     is_search_appearance_android_app,
     is_amp_story,
     is_amp_image_result,
     is_video,
     is_organic_shopping,
     is_review_snippet,
     is_special_announcement,
     is_recipe_feature,
     is_recipe_rich_snippet,
     is_subscribed_content,
     is_page_experience,
     is_practice_problems,
     is_math_solvers,
     is_translated_result,
     is_edu_q_and_a
    FROM `searchconsole.searchdata_url_impression`,
     UNNEST(GENERATE_ARRAY(1, impressions)) AS impression_instance
    WHERE search_type = 'WEB'
     AND data_date > (
      SELECT MAX(data_date)
      FROM `mvp-data-321618.searchconsole.searchdata_url_impression`
     ) - 7
   )
 ),
 searches_with_id AS (
  SELECT FARM_FINGERPRINT(
    CONCAT(
     impression_instance,
     site_url,
     IFNULL(query, CAST(RAND() AS STRING)),
     country,
     search_type,
     device,
     data_date
    )
   ) AS search_id,
   TIMESTAMP_ADD(
    TIMESTAMP(data_date),
    INTERVAL CAST(FLOOR(RAND() * 86400) AS INT64) SECOND
   ) AS search_timestamp,
   data_date,
   site_url,
   IFNULL(query, 'PRIVACY_PROTECTED') AS query,
   country,
   search_type,
   device
  FROM `searchconsole.searchdata_site_impression`,
   UNNEST(GENERATE_ARRAY(1, impressions)) AS impression_instance
  WHERE search_type = 'WEB'
   AND data_date > (
    SELECT MAX(data_date)
    FROM `mvp-data-321618.searchconsole.searchdata_url_impression`
   ) - 7
 ),
 search_impressions_cross AS (
  SELECT ei.impression_id,
   s.search_id,
   s.search_timestamp,
   s.query,
   ei.country,
   ei.search_type,
   ei.device,
   ei.site_url,
   ei.url,
   ei.is_anonymized_query,
   ei.is_amp_top_stories,
   ei.is_amp_blue_link,
   ei.is_job_listing,
   ei.is_job_details,
   ei.is_tpf_qa,
   ei.is_tpf_faq,
   ei.is_tpf_howto,
   ei.is_weblite,
   ei.is_action,
   ei.is_events_listing,
   ei.is_events_details,
   ei.is_search_appearance_android_app,
   ei.is_amp_story,
   ei.is_amp_image_result,
   ei.is_video,
   ei.is_organic_shopping,
   ei.is_review_snippet,
   ei.is_special_announcement,
   ei.is_recipe_feature,
   ei.is_recipe_rich_snippet,
   ei.is_subscribed_content,
   ei.is_page_experience,
   ei.is_practice_problems,
   ei.is_math_solvers,
   ei.is_translated_result,
   ei.is_edu_q_and_a,
   -- Determine if the impression was clicked based on CTR curve
   ei.position,
   IF (RAND() < cc.ctr, 1, 0) AS was_clicked
  FROM searches_with_id s
   JOIN exploded_impressions ei ON ei.site_url = s.site_url
   AND ei.query = s.query
   AND ei.country = s.country
   AND ei.search_type = s.search_type
   AND ei.device = s.device
   AND ei.data_date = s.data_date
   LEFT JOIN ctr_curve cc ON ei.position = cc.position
 ),
 search_impressions AS (
  SELECT *
  EXCEPT(impression_rn)
  FROM (
    SELECT *,
     ROW_NUMBER() OVER (
      PARTITION BY impression_id
      ORDER BY RAND()
     ) AS impression_rn
    FROM search_impressions_cross
    ORDER BY search_timestamp,
     search_id
   )
  WHERE impression_rn = 1
 )
SELECT *
FROM search_impressions

Example query: Building searchdata_site_impressions

WITH ordered_impressions AS (
 SELECT *,
  ROW_NUMBER() OVER (
   PARTITION BY search_id
   ORDER BY position
  ) rn
 FROM `mvp-data-321618.searchconsole.searchdata_raw_impressions_flat`
),
top_impressions AS (
 SELECT DATE(search_timestamp) data_date,
  i.*
 EXCEPT(impression_id, rn)
 FROM ordered_impressions i
 WHERE rn = 1
),
searchdata_site_impressions AS (
 SELECT data_date,
  site_url,
  query,
  country,
  search_type,
  device,
  COUNT(search_id) impressions,
  SUM(position) sum_top_position,
  SUM(was_clicked) clicks -- each impression row has a value of 1 if it was clicked
 FROM top_impressions
 GROUP BY ALL
)
SELECT data_date,
 site_url,
 query,
 country,
 search_type,
 device,
 impressions,
 sum_top_position,
 clicks
FROM searchdata_site_impressions
GROUP BY ALL

Example query: Building searchdata_url_impressions

WITH ordered_impressions AS (
 SELECT *,
  ROW_NUMBER() OVER (
   PARTITION BY search_id,
   url
   ORDER BY position
  ) rn -- THE BIG DIFFERENCE
 FROM `mvp-data-321618.searchconsole.searchdata_raw_impressions_flat`
),
top_impressions AS (
 SELECT DATE(search_timestamp) data_date,
  i.*
 EXCEPT(impression_id, rn)
 FROM ordered_impressions i
 WHERE rn = 1
)
SELECT data_date,
 site_url,
 query,
 country,
 search_type,
 device,
 url,
 is_anonymized_query,
 is_amp_top_stories,
 is_amp_blue_link,
 is_job_listing,
 is_job_details,
 is_tpf_qa,
 is_tpf_faq,
 is_tpf_howto,
 is_weblite,
 is_action,
 is_events_listing,
 is_events_details,
 is_search_appearance_android_app,
 is_amp_story,
 is_amp_image_result,
 is_video,
 is_organic_shopping,
 is_review_snippet,
 is_special_announcement,
 is_recipe_feature,
 is_recipe_rich_snippet,
 is_subscribed_content,
 is_page_experience,
 is_practice_problems,
 is_math_solvers,
 is_translated_result,
 is_edu_q_and_a,
 COUNT(search_id) impressions,
 SUM(position) sum_top_position,
 SUM(was_clicked) clicks -- each impression row has a value of 1 if it was clicked
FROM top_impressions
GROUP BY ALL

---

« Part 2: Intro to Data Warehousing · Next: Part 4: SEO Analytics SQL »