PASF Report Template:
https://lookerstudio.google.com/u/0/reporting/2ddd991d-0016-4f88-b3be-a810a4b476c6/page/T6hmB
--PASF Report: Demo Site >> People Also Search For Report
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 '2025-01-05' AND '2025-01-05'
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_search_for.is_people_also_search_for 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
LEFT 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