SERPs Containing AI Overviews And My Domain Ranks Organically

Example Of This Report

https://docs.google.com/spreadsheets/d/1omXTbvOrg0zKGM_WQfJAMckp-geEA0u5BiibkrRSEQI/edit?gid=1209713781#gid=1209713781

WITH

latest_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.nozzle_reidemo.rankings t
  WHERE DATE(requested) >= '2025-06-01'
  GROUP BY ranking_id
),

asd AS (
  SELECT
    keyword_id,
    requested,

    IF(COUNTIF(result.rank = 1 AND result.layout.in_ai_overview IS TRUE) > 0, TRUE, FALSE) AS has_rank1_ai_overview,
    IF(COUNTIF(result.rank = 1 AND result.layout.in_ai_overview IS TRUE AND result.url.domain = 'rei.com') > 0, TRUE, FALSE) AS pinterest_in_rank1_ai_overview,
    IF(COUNTIF(result.layout.in_ai_overview IS NOT TRUE AND result.url.domain = 'pinterest.com') > 0, TRUE, FALSE) AS pinterest_ranks_outside_ai_overview,

  FROM latest_rankings
  JOIN UNNEST(results) AS result
  GROUP BY keyword_id, requested
  HAVING has_rank1_ai_overview IS TRUE
)

SELECT
  requested,
  keyword_id,
  phrase,
  device_code,
  country_code,
  language_code,
  asd.* EXCEPT(keyword_id, requested),
FROM asd
JOIN nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id k USING (keyword_id)
ORDER BY requested, phrase