Generate A List of the Top X results for a Project in your Nozzle Workspace

gistfile1.txt

-- Nozzle data top 20 results csv export

WITH
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.nozzle_nozzleofficial.rankings t
  WHERE requested >= '2022-01-01 00:00:00' AND requested < '2022-01-25 00:00:00'
  GROUP BY ranking_id
  ),
  
  -- apply any result level filters here, set group by, and get all metrics necessary for later calculations
latest_filtered_rankings_results AS (
  SELECT
    keyword_id,
    requested,
    
    phrase_id,
    phrase,
    locale_id,
    device,
    engine,
    language,
    location_id,
    location_type,
    location,
    country,
    
    result.title.text AS title,
    result.url.domain,
    result.url.url,
    result.rank,
    result.item_rank,
    result.paid_adjusted_rank,
    result.nozzle_metrics.click_through_rate,
    keyword_metrics.adwords_search_volume,
    keyword_metrics.adwords_cpc,
    result.nozzle_metrics.estimated_traffic,
    result.nozzle_metrics.ppc_value,
    result.measurements.pixels_from_top,
    result.measurements.pixel_height * result.measurements.pixel_width AS result_pixels_total,
    result.measurements.percentage_of_viewport,
    result.measurements.percentage_of_dom,
    result.description.text AS description,
    
    (ARRAY_TO_STRING((SELECT ARRAY_AGG(x IGNORE NULLS) 
     FROM UNNEST([
      IF(result.about.is_about, 'is_about', NULL),
      IF(result.according_to.is_according_to, 'is_according_to', NULL),
      IF(result.ad.is_ad, 'is_ad', NULL),
      IF(result.app.is_app, 'is_app', NULL),
      -- IF(result.article.is_article, 'is_article', NULL),
      IF(result.attribute.is_attribute, 'is_attribute', NULL),
      IF(result.available_on.is_available_on, 'is_available_on', NULL),
      IF(result.best.is_best, 'is_best', NULL),
      IF(result.bio.is_bio, 'is_bio', NULL),
      IF(result.book.is_book, 'is_book', NULL),
      -- IF(result.cast.is_cast, 'is_cast', NULL),
      IF(result.claim.is_claim, 'is_claim', NULL),
      IF(result.college.is_college, 'is_college', NULL),
      -- IF(result.complementary_result.is_complementary_result, 'is_complementary_result', NULL),
      IF(result.contact.is_contact, 'is_contact', NULL),
      -- IF(result.critic_review.is_critic_review, 'is_critic_review', NULL),
      -- IF(result.currency_converter.is_currency_converter, 'is_currency_converter', NULL),
      IF(result.destination.is_destination, 'is_destination', NULL),
      IF(result.dictionary.is_dictionary, 'is_dictionary', NULL),
      IF(result.direct_answer.is_direct_answer, 'is_direct_answer', NULL),
      IF(result.directory.is_directory, 'is_directory', NULL),
      IF(result.discover_more.is_discover_more, 'is_discover_more', NULL),
      -- IF(result.editorial_review.is_editorial_review, 'is_editorial_review', NULL),
      -- IF(result.episode.is_episode, 'is_episode', NULL),
      IF(result.event.is_event, 'is_event', NULL),
      IF(result.faq.is_faq, 'is_faq', NULL),
      IF(result.featured_snippet.is_featured_snippet, 'is_featured_snippet', NULL),
      IF(result.flight.is_flight, 'is_flight', NULL),
      IF(result.hotel.is_hotel, 'is_hotel', NULL),
      IF(result.how_to.is_how_to, 'is_how_to', NULL),
      -- IF(result.iframe.is_iframe, 'is_iframe', NULL),
      IF(result.image.is_image, 'is_image', NULL),
      IF(result.image.has_image, 'has_image', NULL),
      -- IF(result.map.is_map, 'is_map', NULL),
      -- IF(result.shopping.is_shopping, 'is_shopping', NULL),
      IF(result.job.is_job, 'is_job', NULL),
      IF(result.knowledge_graph.is_knowledge_graph, 'is_knowledge_graph', NULL),
      -- IF(result.latest_from.is_latest_from, 'is_latest_from', NULL),
      IF(result.local.is_local, 'is_local', NULL),
      IF(result.map_travel.is_map_travel, 'is_map_travel', NULL),
      IF(result.medical.is_medical, 'is_medical', NULL),
      -- IF(result.movie.is_movie, 'is_movie', NULL),
      -- IF(result.on_tv_soon.is_on_tv_soon, 'is_on_tv_soon', NULL),
      IF(result.organic.is_organic, 'is_organic', NULL),
      IF(result.people_also_ask.is_people_also_ask, 'is_people_also_ask', NULL),
      IF(result.people_also_search_for.is_people_also_search_for, 'is_people_also_search_for', NULL),
      IF(result.podcast.is_podcast, 'is_podcast', NULL),
      IF(result.product.is_product, 'is_product', NULL),
      IF(result.profile.is_profile, 'is_profile', NULL),
      IF(result.quote.is_quote, 'is_quote', NULL),
      -- IF(result.ratings.is_ratings, 'is_ratings', NULL),
      IF(result.recipe.is_recipe, 'is_recipe', NULL),
      IF(result.refine_by.is_refine_by, 'is_refine_by', NULL),
      IF(result.related_search.is_related_search, 'is_related_search', NULL),
      IF(result.research.is_research, 'is_research', NULL),
      IF(result.see_results_about.is_see_results_about, 'is_see_results_about', NULL),
      IF(result.showtimes.is_showtimes, 'is_showtimes', NULL),
      IF(result.sitelink.is_sitelink, 'is_sitelink', NULL),
      -- IF(result.sitelink.is_expanded, 'is_expanded', NULL),
      -- IF(result.stock_market.is_stock_market, 'is_stock_market', NULL),
      -- IF(result.tab.is_tab, 'is_tab', NULL),
      IF(result.top_rated.is_top_rated, 'is_top_rated', NULL),
      IF(result.top_result.is_top_result, 'is_top_result', NULL),
      IF(result.top_story.is_top_story, 'is_top_story', NULL),
      IF(result.twitter.is_twitter, 'is_twitter', NULL),
      -- IF(result.unit_converter.is_unit_converter, 'is_unit_converter', NULL),
      -- IF(result.user_review.is_user_review, 'is_user_review', NULL),
      IF(result.video.is_video, 'is_video', NULL),
      IF(result.amp.is_amp, 'is_amp', NULL),
      IF(result.review.is_review, 'is_review', NULL),
      IF(result.music.is_music, 'is_music', NULL),
      IF(result.table.has_table, 'has_table', NULL),
      IF(result.interactive.is_interactive, 'is_interactive', NULL),
      IF(result.address IS NOT NULL, 'address', NULL),
      IF(result.emphasis IS NOT NULL, 'emphasis', NULL),
      IF(result.forum IS NOT NULL, 'forum', NULL),
      IF(result.phone IS NOT NULL, 'phone', NULL),
      IF(result.price IS NOT NULL, 'price', NULL),
      IF(result.related_phrase IS NOT NULL, 'related_phrase', NULL)
    ]) AS x), '; ')) AS features,
    
  FROM latest_filtered_rankings
  JOIN nozzledata.nozzle_nozzleofficial.latest_keywords USING (keyword_id)
  JOIN UNNEST(results) AS result
  WHERE result.rank <= 20
  )
  
  SELECT * FROM latest_filtered_rankings_results ORDER BY keyword_id, requested

-- Nozzle data top 20 results csv export


WITH
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.nozzle_nozzleofficial.rankings t
  WHERE requested >= '2021-08-01 00:00:00' AND requested < '2021-12-31 00:00:00'
  GROUP BY ranking_id
),
  
-- apply any result level filters here, set group by, and get all metrics necessary for later calculations
latest_filtered_rankings_results AS (
  SELECT
    keyword_id,
    requested,
    
    phrase_id,
    phrase,
    locale_id,
    device,
    engine,
    language,
    location_id,
    location_type,
    location,
    country,
    
    result.title.text AS title,
    result.url.domain,
    result.url.url,
    result.rank,
    DENSE_RANK() OVER (PARTITION BY keyword_id, requested ORDER BY result.rank) AS no_feature_rank,
    result.item_rank,
    result.paid_adjusted_rank,
    result.nozzle_metrics.click_through_rate,
    keyword_metrics.adwords_search_volume,
    keyword_metrics.adwords_cpc,
    result.nozzle_metrics.estimated_traffic,
    result.nozzle_metrics.ppc_value,
    result.measurements.pixels_from_top,
    result.measurements.pixel_height * result.measurements.pixel_width AS result_pixels_total,
    result.measurements.percentage_of_viewport,
    result.measurements.percentage_of_dom,
    result.description.text AS description,
    
  FROM latest_filtered_rankings
  JOIN nozzledata.nozzle_nozzleofficial.latest_keywords USING (keyword_id)
  JOIN UNNEST(results) AS result
  WHERE result.rank <= 20
  AND (
    result.featured_snippet.is_featured_snippet = TRUE
    OR result.organic.is_organic = TRUE
  )
)
  
SELECT * FROM latest_filtered_rankings_results ORDER BY keyword_id, requested