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