Top Stories Ranking Report Template:
WITH
-- grabbing the latest version of each serp in case of reparse
latest_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM nozzledata.static_topstories.rankings t
WHERE requested >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY) -- last 7 days
GROUP BY keyword_id, requested
),
latest_rankings_results AS (
SELECT
requested,
keyword_id,
phrase,
keyword_metrics.adwords_search_volume AS search_volume,
ARRAY(
SELECT AS STRUCT
rank,
item_rank,
url.url
FROM UNNEST(results) AS result
WHERE result.top_story.is_top_story IS TRUE
AND result.measurements.is_visible IS TRUE
AND url.url IS NOT NULL
) AS top_stories
FROM latest_rankings
JOIN nozzledata.static_topstories.latest_keywords_by_keyword_id USING (keyword_id)
),
top_stories_pivoted AS (
SELECT
requested,
phrase,
search_volume,
top_stories[SAFE_ORDINAL(1)].rank AS top_story_rank,
top_stories[SAFE_ORDINAL(1)].url AS top_story_1,
top_stories[SAFE_ORDINAL(2)].url AS top_story_2,
top_stories[SAFE_ORDINAL(3)].url AS top_story_3,
top_stories[SAFE_ORDINAL(4)].url AS top_story_4,
top_stories[SAFE_ORDINAL(5)].url AS top_story_5,
top_stories[SAFE_ORDINAL(6)].url AS top_story_6,
top_stories[SAFE_ORDINAL(7)].url AS top_story_7,
FROM latest_rankings_results
)
SELECT *,
NET.REG_DOMAIN(top_story_1) top_story_1_domain,
NET.REG_DOMAIN(top_story_2) top_story_2_domain,
NET.REG_DOMAIN(top_story_3) top_story_3_domain,
NET.REG_DOMAIN(top_story_4) top_story_4_domain,
NET.REG_DOMAIN(top_story_5) top_story_5_domain,
NET.REG_DOMAIN(top_story_6) top_story_6_domain,
NET.REG_DOMAIN(top_story_7) top_story_7_domain
FROM top_stories_pivoted
ORDER BY requested desc, phrase