PASF Report Query And Template

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