Paid Product Report Query And Template

A Looker Studio template and BigQuery query for paid product results in Nozzle SERP data, with merchant-level rank, product pack, and traffic metrics.
Updated July 3, 20254 min read
Paid Product Report Template:
https://datastudio.google.com/reporting/525f0853-3e56-46f7-98f3-1d0449f1da6c/page/p_j7laebl4jd
-- REI - Paid Product Report

WITH

-- filter keywords early to reduce query execution time
filtered_keyword_ids AS (
  SELECT keyword_id
  FROM nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id
  JOIN UNNEST(keyword_groups) AS kg
  -- WHERE kg NOT IN ('- Keyword Source: Daily - US - Desktop -')
  -- JOIN UNNEST(keyword_sources) as kw_source
  -- WHERE kw_source.keyword_source_id IN (123)
  GROUP BY keyword_id
),


-- 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.nozzle_reidemo.rankings t
  JOIN filtered_keyword_ids USING (keyword_id)
  WHERE requested >= '2024-01-01'
  GROUP BY ranking_id
),

all_keywords_by_requested AS (
  SELECT
    keyword_id,
    requested,
  FROM (SELECT DISTINCT keyword_id FROM filtered_keyword_ids)
  CROSS JOIN (SELECT DISTINCT requested FROM latest_rankings)
),

-- first fill forwards, then backfill as necessary
all_rankings_fill_null AS (
  SELECT
    a.keyword_id,
    a.requested,
    IFNULL(d.requested, IFNULL(
      LAST_VALUE(d.requested IGNORE NULLS) OVER (PARTITION BY keyword_id ORDER BY requested ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
      FIRST_VALUE(d.requested IGNORE NULLS) OVER (PARTITION BY keyword_id ORDER BY requested ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    )) AS data_from_requested,

  FROM all_keywords_by_requested a
  LEFT JOIN (SELECT DISTINCT requested, keyword_id FROM latest_rankings) d USING (keyword_id, requested)
),

fill_null_data AS (
  SELECT
    a.keyword_id,
    a.requested,
    a.data_from_requested,

    r.* EXCEPT (keyword_id, requested)

  FROM all_rankings_fill_null a
  LEFT JOIN latest_rankings r ON a.keyword_id=r.keyword_id AND a.data_from_requested=r.requested
),

filtered_results AS (
  SELECT
    requested,
    keyword_id,

    COALESCE(result.merchant.merchant_name, result.product.merchant) AS merchant_name,
    result.url.domain_id,

    result.pack_rank,
    result.rank,
    DENSE_RANK() OVER (PARTITION BY keyword_id, requested, result.pack_rank ORDER BY result.item_rank) AS item_rank,
    result.layout.is_pack,

    keyword_metrics.country_adwords_search_volume,
    result.nozzle_metrics.click_through_rate,
    result.measurements.pixels_from_top,
    result.measurements.percentage_of_viewport,
    result.measurements.percentage_of_dom,
    result.measurements.is_visible,

  FROM fill_null_data n
  JOIN UNNEST(results) AS result
  WHERE requested IS NOT NULL
    AND result.paid.is_paid IS TRUE
    AND result.product.is_product IS TRUE
),

per_serp_data AS (
  SELECT
    requested,
    keyword_id,
    merchant_name,


    ANY_VALUE(phrase) AS phrase,
    ANY_VALUE(country) AS country,
    ANY_VALUE(location) AS location,
    ANY_VALUE(language) AS language,
    ANY_VALUE(keyword_groups) AS keyword_groups,





    MIN(rank) AS rank,
    MIN(item_rank) AS item_rank,
    AVG(item_rank) AS item_rank_avg,
    CAST(SUM(country_adwords_search_volume * click_through_rate) AS INT64) AS estimated_traffic,
    MIN(pixels_from_top) AS pixels_from_top,
    SUM(percentage_of_viewport) AS above_the_fold_percentage, -- 100% = 1 | 5% = 0.05
    SUM(percentage_of_dom) AS serp_percentage, -- 100% = 1 | 5% = 0.05

    -- how many product packs are on the SERP, and a breakout if they are in the top 3
    COUNT(DISTINCT IF(is_pack IS TRUE, pack_rank, NULL)) AS product_pack_count,
    COUNT(DISTINCT IF(is_pack IS TRUE AND rank BETWEEN 1 AND 3, pack_rank, NULL)) 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(is_visible IS TRUE) AS visible_product_count,
    COUNT(*) AS product_count,

    COUNTIF(is_visible IS TRUE AND domain_id IS NOT NULL) AS visible_product_count_with_domain,
    COUNTIF(domain_id IS NOT NULL) AS product_count_with_domain,

  FROM filtered_results
  JOIN nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id k USING (keyword_id)
  GROUP BY keyword_id, requested, merchant_name
),

aggregate_by_requested AS (
  SELECT
    requested,
    merchant_name,

    ROUND(AVG(rank), 2) AS rank,
    ROUND(AVG(item_rank), 2) AS item_rank,
    ROUND(AVG(item_rank_avg), 2) AS item_rank_avg_avg,
    SUM(estimated_traffic) AS estimated_traffic,
    CAST(AVG(pixels_from_top) AS INT64) AS pixels_from_top,
    ROUND(AVG(above_the_fold_percentage), 4) AS above_the_fold_percentage,
    ROUND(AVG(serp_percentage), 4) AS serp_percentage,

    SUM(product_pack_count) AS product_pack_count,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)] AS product_pack_count_min,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)] AS product_pack_count_p25,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)] AS product_pack_count_p50,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)] AS product_pack_count_p75,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)] AS product_pack_count_max,
    SUM(product_pack_top_3_count) AS product_pack_top_3_count,
    COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_product,
    COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_product,
    SUM(visible_product_count) AS visible_product_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY requested, merchant_name
),

aggregate_by_requested_by_keyword AS (
  SELECT
    keyword_id,
    requested,
    merchant_name,
    ANY_VALUE(phrase) AS phrase,
    ANY_VALUE(country) AS country,
    ANY_VALUE(location) AS location,
    ANY_VALUE(language) AS language,
    ANY_VALUE(keyword_groups) AS keyword_groups,


    ROUND(AVG(rank), 2) AS rank,
    ROUND(AVG(item_rank), 2) AS item_rank,
    ROUND(AVG(item_rank_avg), 2) AS item_rank_avg_avg,
    SUM(estimated_traffic) AS estimated_traffic,
    CAST(AVG(pixels_from_top) AS INT64) AS pixels_from_top,
    ROUND(AVG(above_the_fold_percentage), 4) AS above_the_fold_percentage,
    ROUND(AVG(serp_percentage), 4) AS serp_percentage,

    SUM(product_pack_count) AS product_pack_count,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)] AS product_pack_count_min,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)] AS product_pack_count_p25,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)] AS product_pack_count_p50,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)] AS product_pack_count_p75,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)] AS product_pack_count_max,
    SUM(product_pack_top_3_count) AS product_pack_top_3_count,
    COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_product,
    COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_product,
    SUM(visible_product_count) AS visible_product_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY keyword_id, requested, merchant_name
),

aggregate_by_keyword AS (
  SELECT
    keyword_id,
    merchant_name,

    ROUND(AVG(rank), 2) AS rank,
    ROUND(AVG(item_rank), 2) AS item_rank,
    ROUND(AVG(item_rank_avg), 2) AS item_rank_avg_avg,
    SUM(estimated_traffic) AS estimated_traffic,
    CAST(AVG(pixels_from_top) AS INT64) AS pixels_from_top,
    ROUND(AVG(above_the_fold_percentage), 4) AS above_the_fold_percentage,
    ROUND(AVG(serp_percentage), 4) AS serp_percentage,

    SUM(product_pack_count) AS product_pack_count,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)] AS product_pack_count_min,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)] AS product_pack_count_p25,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)] AS product_pack_count_p50,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)] AS product_pack_count_p75,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)] AS product_pack_count_max,
    SUM(product_pack_top_3_count) AS product_pack_top_3_count,
    COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_product,
    COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_product,
    SUM(visible_product_count) AS visible_product_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY keyword_id, merchant_name
),

aggregate_total AS (
  SELECT
    merchant_name,

    ROUND(AVG(rank), 2) AS rank,
    ROUND(AVG(item_rank), 2) AS item_rank,
    ROUND(AVG(item_rank_avg), 2) AS item_rank_avg_avg,
    SUM(estimated_traffic) AS estimated_traffic,
    CAST(AVG(pixels_from_top) AS INT64) AS pixels_from_top,
    ROUND(AVG(above_the_fold_percentage), 4) AS above_the_fold_percentage,
    ROUND(AVG(serp_percentage), 4) AS serp_percentage,

    SUM(product_pack_count) AS product_pack_count,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)] AS product_pack_count_min,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)] AS product_pack_count_p25,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)] AS product_pack_count_p50,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)] AS product_pack_count_p75,
    APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)] AS product_pack_count_max,
    SUM(product_pack_top_3_count) AS product_pack_top_3_count,
    COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_product,
    COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_product,
    SUM(visible_product_count) AS visible_product_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY merchant_name
)

SELECT * FROM aggregate_by_requested_by_keyword
-- SELECT * FROM aggregate_total ORDER BY product_count DESC
PASF Report Query And TemplateA Looker Studio template and BigQuery query for People Also Search For results in Nozzle SERP data, with related phrases and ranking URLs.Looker Studio Templates With BigQuery QueriesAn index of Looker Studio templates and BigQuery queries for Nozzle data, including PAA, PASF, product, video pack, and geo ranking reports.PAA Report Query And TemplateA Looker Studio template and BigQuery query for reporting on People Also Ask results in Nozzle SERP data, with the URLs and titles behind each question.