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