1. Support Home
  2. BigQuery
  3. Google Sheets/Excel Templates and Queries

Top Stories Ranking Report Query And Template

Top Stories Ranking Report Template:

https://docs.google.com/spreadsheets/d/1uRSA1jMSY6kp1uG2zNof9jIpVv5Lx8OUKbYabINlDw0/edit?gid=2052257031#gid=2052257031

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