PAA Report Query And Template

PAA Ranking Report Template:

https://lookerstudio.google.com/u/0/reporting/25688d09-231c-4b15-9073-506c80c03aad/page/p_gfuejdk4jd

--PAA Report: REI >> PAA Deep Analysis Query

WITH

-- this grabs the latest version of each SERP, in case we reparse
latest_filtered_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) BETWEEN '2024-07-01' AND '2025-01-31'
  GROUP BY ranking_id
),

raw_data AS (
  SELECT *
  FROM latest_filtered_rankings
  JOIN nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id USING (keyword_id)
),

paa AS (
  SELECT
    ranking_id,
    result.rank,
    result.item_rank,
    related_phrase,

  FROM raw_data
  JOIN UNNEST(results) AS result
  WHERE result.people_also_ask.is_people_also_ask IS TRUE
 
),

paa__featured_snippets AS (
  SELECT
    keyword_id,
    requested,
    phrase,
    (SELECT ARRAY_AGG(kg) FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- ')) AS keyword_groups,
    keyword_metrics.adwords_search_volume AS search_volume,
    device,
    paa.related_phrase,

    result.url.domain,
    result.url.url,
    result.title.text AS title,
    result.description.text AS description,


  FROM raw_data
  JOIN UNNEST(results) AS result
  JOIN paa ON paa.ranking_id = raw_data.ranking_id AND paa.rank = result.rank AND paa.item_rank = result.parent_item_rank
)

SELECT * FROM paa__featured_snippets