Generic Query

WITH

-- grabbing the latest version of each serp in case of reparse
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]

  -- you'll insert the workspace/project slugs, so this will look like
  -- FROM nozzledata.myworkspace_myproject.rankings t
  FROM nozzledata._.rankings t

  -- ranking data is partitioned daily on the "requested" field, which is the timestamp based on the schedule.
  -- There is a BigQuery requirement to filter on this field, which will also help reduce query costs
  -- for most daily/weekly requests, the time component will be 00:00:00, but hourly schedules will be on the hour every hour.
  -- Here are a few common query patterns:
  WHERE DATE(requested) BETWEEN '2024-02-28' AND '2024-03-01'
  -- WHERE DATE(requested) >= '2024-01-01' -- to get 100% of all historical results, set this before you started tracking with Nozzle
  -- WHERE requested >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -30 DAY) -- last 30 days

  GROUP BY keyword_id, requested
),

latest_filtered_rankings_results AS (
  SELECT
    requested,
    keyword_id,

    phrase,
    device,
    language,
    country,
    location_type,
    location,

    -- this filters out built-in keyword groups like "- All Keywords -"
    (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,

    -- result data
    result.title.text as title,
    result.description.text as description,

    result.url.domain as domain,
    result.url.url as url,

    result.paid_adjusted_rank AS paid_adjusted_rank,
    result.rank AS rank,
    result.item_rank AS item_rank,

    -- calculate base rank (only featured snippets or 10 blue links)
    IF(result.organic.is_organic IS TRUE OR result.featured_snippet.is_featured_snippet IS TRUE,
      DENSE_RANK() OVER (PARTITION BY keyword_id, requested,
        result.organic.is_organic IS TRUE
        OR (result.featured_snippet.is_featured_snippet IS TRUE AND result.parent_item_rank IS NULL)
      ORDER BY result.rank),
      NULL
    ) AS base_rank,

    -- visibility (recursively checked in Chrome for CSS properties like display:none, opacity, visibility, etc)
    result.measurements.is_visible AS is_visible,

    -- calculated metrics
    result.nozzle_metrics.click_through_rate AS click_through_rate,
    result.nozzle_metrics.estimated_traffic AS estimated_traffic,

    result.measurements.pixels_from_top AS pixels_from_top,
    result.measurements.percentage_of_viewport AS above_the_fold_percentage, -- 100% = 1 | 5% = 0.05
    result.measurements.percentage_of_dom AS serp_percentage, -- 100% = 1 | 5% = 0.05

    -- some advanced aggregation examples. For aggregated metrics, we'd use a different query,
    -- these are primarily for isolating SERPs that match, then returning result level data

    -- how many product packs are on the SERP, and a breakout if they are in the top 3
    COUNTIF(result.product.is_product IS TRUE AND result.layout.is_pack IS TRUE) OVER (PARTITION BY keyword_id, requested) AS product_pack_count,
    COUNTIF(result.product.is_product IS TRUE AND result.layout.is_pack IS TRUE AND rank BETWEEN 1 AND 3) OVER (PARTITION BY keyword_id, requested) AS product_pack_top_3_count,

    -- how many visible products are on the SERP and how many total products (typically non-visible products are in a carousel)
    COUNTIF(result.product.is_product IS TRUE AND result.measurements.is_visible IS TRUE) OVER (PARTITION BY keyword_id, requested) AS visible_product_count,
    COUNTIF(result.product.is_product IS TRUE) OVER (PARTITION BY keyword_id, requested) AS product_count,

    -- this is a subset of SERP features. You will likely not care about many of these,
    -- and for some that match your business, you may want to get more granular
    (SELECT ARRAY_AGG(sf) FROM UNNEST([ -- for export to CSV, change "ARRAY_AGG(sf)" to "STRING_AGG(sf, '; ')"
      IF(result.layout.in_ai_overview IS TRUE, 'ai_overview', NULL),
      IF(result.ai.is_ai_generated IS TRUE, 'ai_generated', NULL),
      IF(result.direct_answer.is_direct_answer IS TRUE, 'direct_answer', NULL),
      IF(result.converter.is_unit_converter IS TRUE OR result.converter.is_currency_converter IS TRUE OR result.converter.is_exchange_rate IS TRUE, 'converter', NULL),
      IF(result.dictionary.is_dictionary IS TRUE, 'dictionary', NULL),
      IF(result.weather.is_weather IS TRUE, 'weather', NULL),
      IF(result.sport.is_sport IS TRUE, 'sport', NULL),
      IF(result.translate.is_translate IS TRUE, 'translate', NULL),
      IF(result.chart.is_chart IS TRUE OR result.chart.has_chart IS TRUE, 'chart', NULL),
      IF(result.table.is_table IS TRUE OR result.table.has_table IS TRUE, 'table', NULL),
      IF(result.list.is_bulleted IS TRUE OR result.list.is_numbered IS TRUE, 'list', NULL),
      IF(result.directory.is_directory IS TRUE, 'directory', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE, 'featured_snippet', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE AND (result.table.is_table IS TRUE OR result.table.has_table IS TRUE),  'featured_snippet_table', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE AND result.list.is_bulleted IS TRUE, 'featured_snippet_bulleted_list', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE AND result.list.is_numbered IS TRUE, 'featured_snippet_numbered_list', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE AND (result.image.is_image IS TRUE OR result.image.has_image IS TRUE),  'featured_snippet_image', NULL),
      IF(result.featured_snippet.is_featured_snippet IS TRUE AND (result.video.is_video IS TRUE OR result.video.has_video IS TRUE),  'featured_snippet_video', NULL),
      IF(result.according_to.is_according_to IS TRUE, 'according_to', NULL),
      IF(result.layout.is_pack AND result.image.is_image IS TRUE, 'image_pack', NULL),
      IF(result.layout.is_pack AND result.video.is_video IS TRUE, 'video_pack', NULL),
      IF(result.product.is_product IS TRUE AND NOT result.paid.is_paid IS TRUE, 'product', NULL),
      IF(result.product.is_product IS TRUE AND result.paid.is_paid IS TRUE, 'product_paid', NULL),
      IF(result.price.has_price IS TRUE OR result.price.has_price_range IS TRUE, 'price', NULL),
      IF(result.discount.has_discount IS TRUE OR result.discount.has_deal IS TRUE, 'discount', NULL),
      IF(result.best.is_best IS TRUE, 'best', NULL),
      IF(result.comparison.is_comparison IS TRUE, 'comparison', NULL),
      IF(result.top_rated.is_top_rated IS TRUE, 'top_rated', NULL),
      IF(result.destination.is_destination IS TRUE, 'destination', NULL),
      IF(result.hotel.is_hotel IS TRUE, 'hotel', NULL),
      IF(result.flight.is_flight IS TRUE, 'flight', NULL),
      IF(result.map_travel.is_map_travel IS TRUE, 'map_travel', NULL),
      IF(result.map.is_map IS TRUE, 'map', NULL),
      IF(result.busyness.is_busyness IS TRUE, 'busyness', NULL),
      IF(result.claim.is_claim IS TRUE, 'claim', NULL),
      IF(result.sitelink.is_sitelink IS TRUE, 'sitelink', NULL),
      IF(result.sitelink.is_expanded_sitelink IS TRUE, 'expanded_sitelink', NULL),
      IF(result.sitelink.is_sitelink IS TRUE AND result.paid.is_paid IS TRUE, 'sitelink_paid', NULL),
      IF(result.sitelink.is_expanded_sitelink IS TRUE AND result.paid.is_paid IS TRUE, 'expanded_sitelink_paid', NULL),
      IF(result.entertainment.is_movie_rating IS TRUE OR result.entertainment.is_on_tv_soon IS TRUE OR result.entertainment.is_cast IS TRUE OR result.entertainment.is_watch_movie IS TRUE OR result.entertainment.is_watch_show IS TRUE OR result.entertainment.is_episode IS TRUE OR result.entertainment.is_trailer_clip IS TRUE OR result.entertainment.is_based_on_the_book IS TRUE OR result.entertainment.is_top_voted_tag IS TRUE, 'entertainment', NULL),
      IF(result.podcast.is_podcast IS TRUE, 'podcast', NULL),
      IF(result.book.is_book IS TRUE OR result.book.is_ebook IS TRUE OR result.book.is_audiobook IS TRUE OR result.book.is_author IS TRUE OR result.book.is_book_preview IS TRUE OR result.book.is_borrow_ebook IS TRUE OR result.book.is_get_book IS TRUE, 'book', NULL),
      IF(result.available_on.is_available_on IS TRUE, 'available_on', NULL),
      IF(result.showtimes.is_showtimes IS TRUE, 'showtimes', NULL),
      IF(result.music.is_music IS TRUE OR result.music.is_lyrics IS TRUE OR result.music.is_other_recording IS TRUE OR result.music.is_listen IS TRUE OR result.music.is_song IS TRUE OR result.music.is_album IS TRUE, 'music', NULL),
      IF(result.app.is_app IS TRUE, 'app', NULL),
      IF(result.people_also_ask.is_people_also_ask IS TRUE, 'people_also_ask', NULL),
      IF(result.faq.is_faq IS TRUE, 'faq', NULL),
      IF(result.consideration.is_consideration IS TRUE, 'consideration', NULL),
      IF(result.things_to_know.is_things_to_know IS TRUE, 'things_to_know', NULL),
      IF(result.people_also_search_for.is_people_also_search_for IS TRUE, 'people_also_search_for', NULL),
      IF(result.refine_by.is_refine_by IS TRUE, 'refine_by', NULL),
      IF(result.related_search.is_related_search IS TRUE, 'related_search', NULL),
      IF(result.related_phrase.is_related_phrase_misc IS TRUE,  'related_phrase_misc', NULL),
      IF(result.forum.is_forum IS TRUE, 'forum', NULL),
      IF(result.question_and_answer.is_question_and_answer IS TRUE, 'question_and_answer', NULL),
      IF(result.news.is_news IS TRUE, 'news', NULL),
      IF(result.top_story.is_top_story IS TRUE, 'top_story', NULL),
      IF(result.latest_from.is_latest_from IS TRUE, 'latest_from', NULL),
      IF(result.news.is_news_misc IS TRUE, 'news_misc', NULL),
      IF(result.interesting_find.is_interesting_find IS TRUE, 'interesting_find', NULL),
      IF(result.discover_more.is_discover_more IS TRUE, 'discover_more', NULL),
      IF(result.found_on_the_web.is_found_on_the_web IS TRUE, 'found_on_the_web', NULL),
      IF(result.find_results_on.is_find_results_on IS TRUE, 'find_results_on', NULL),
      IF(result.event.is_event IS TRUE, 'event', NULL),
      IF(result.job.is_job IS TRUE, 'job', NULL),
      IF(result.college.is_college IS TRUE, 'college', NULL),
      IF(result.auto.is_auto IS TRUE, 'auto', NULL),
      IF(result.medical.is_medical IS TRUE, 'medical', NULL),
      IF(result.medical.is_related_condition IS TRUE, 'medical_related_condition', NULL),
      IF(result.medical.is_related_medication IS TRUE, 'medical_related_medication', NULL),
      IF(result.medical.is_may_treat IS TRUE, 'medical_may_treat', NULL),
      IF(result.medical.is_symptom IS TRUE, 'medical_symptom', NULL),
      IF(result.medical.is_treatment IS TRUE, 'medical_treatment', NULL),
      IF(result.about.is_about IS TRUE, 'about', NULL),
      IF(result.attribute.is_attribute IS TRUE, 'attribute', NULL),
      IF(result.knowledge_graph.is_knowledge_graph IS TRUE, 'knowledge_graph', NULL),
      IF(result.research.is_research IS TRUE, 'research', NULL),
      IF(result.cited_source.is_cited_source IS TRUE, 'cited_source', NULL),
      IF(result.bio.is_bio IS TRUE, 'bio', NULL),
      IF(result.profile.is_profile IS TRUE, 'profile', NULL),
      IF(result.quote.is_quote IS TRUE, 'quote', NULL),
      IF(result.recipe.is_recipe IS TRUE, 'recipe', NULL),
      IF(result.twitter.is_twitter IS TRUE, 'twitter', NULL),
      IF(result.finance.is_finance IS TRUE OR result.finance.is_stock_market IS TRUE OR result.finance.is_market_ticker IS TRUE OR result.finance.is_ticker_price_stats IS TRUE, 'finance', NULL),
      IF(result.story.is_story IS TRUE, 'story', NULL),
      IF(result.how_to.is_how_to IS TRUE, 'how_to', NULL),
      IF(result.organic.is_organic IS TRUE, 'organic', NULL)
    ]) sf WHERE sf IS NOT NULL) AS serp_features,

  FROM latest_filtered_rankings
  JOIN UNNEST(results) AS result

  -- this table has one row per unique keyword_id (phrase + locale + device), and all keyword groups
  -- aggregated, even if the same keyword belongs to multiple keyword sources, and shows the most
  -- current keyword configuration and keyword groups.
  JOIN nozzledata._.latest_keywords_by_keyword_id USING (keyword_id)

  -- here are common filters people use

  -- we use QUALIFY here instead of WHERE because of the window aggregation functions.
  -- if you use WHERE, it will filter before aggregating, which may impact the output
  QUALIFY product_pack_top_3_count > 0
      AND result.url.domain = 'chewy.com'
      AND result.featured_snippet.is_featured_snippet IS TRUE
      AND product_count > 30
)

SELECT * FROM latest_filtered_rankings_results
ORDER BY phrase, device, requested, paid_adjusted_rank, item_rank