1. Support Home
  2. BigQuery
  3. Looker Studio Templates And Queries

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