Unpaid Product Report Query And Template

A Looker Studio template and BigQuery query for unpaid product results in Nozzle SERP data, with merchant, product pack, 3D, 360, and discount metrics.
Updated March 30, 20265 min read
Unpaid Product Report Template:
https://datastudio.google.com/reporting/dc1d99c7-b248-4c88-b3dd-9be9b087e2c8/page/p_j7laebl4jd
-- REI Demo - SERP Product Report - With First Product Pack Flag
-- nozzledata.nozzle_reidemo

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-05-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,
    result.interactive.has_360,
    result.interactive.has_3d,
    result.discount.has_discount,

    -- flag for first product pack per SERP (filterable in Looker Studio)
    IF(pack_rank = MIN(pack_rank) OVER (PARTITION BY keyword_id, requested), TRUE, FALSE) AS is_first_product_pack,

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

per_serp_data AS (
  SELECT
    requested,
    keyword_id,
    merchant_name,
    is_first_product_pack,

    ANY_VALUE(phrase) AS phrase,
    ANY_VALUE(country) AS country,
    ANY_VALUE(location) AS location,
    ANY_VALUE(language) AS language,
    ANY_VALUE((SELECT ARRAY_AGG(kg) FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- '))) 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,
    SUM(percentage_of_dom) AS serp_percentage,

    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,

    COUNTIF(is_visible IS TRUE) AS visible_product_count,
    COUNTIF(has_3d IS TRUE) AS has_3d_product_count,
    COUNTIF(has_360 IS TRUE) AS has_360_product_count,
    COUNTIF(has_discount IS TRUE) AS has_discount_count,
    COUNT(*) AS product_count,

    COUNTIF(is_visible IS TRUE AND domain_id IS NOT NULL) AS visible_product_count_with_domain,
    COUNTIF(has_3d IS TRUE AND domain_id IS NOT NULL) AS has_3d_product_count_with_domain,
    COUNTIF(has_360 IS TRUE AND domain_id IS NOT NULL) AS has_360_product_count_with_domain,
    COUNTIF(has_discount IS TRUE AND domain_id IS NOT NULL) AS has_discount_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, is_first_product_pack
),

aggregate_by_requested AS (
  SELECT
    requested,
    merchant_name,
    is_first_product_pack,

    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(has_3d_product_count) AS has_3d_product_count,
    SUM(has_360_product_count) AS has_360_product_count,
    SUM(has_discount_count) AS has_discount_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(has_3d_product_count_with_domain) AS has_3d_product_count_with_domain,
    SUM(has_360_product_count_with_domain) AS has_360_product_count_with_domain,
    SUM(has_discount_count_with_domain) AS has_discount_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY requested, merchant_name, is_first_product_pack
),

aggregate_by_requested_by_keyword AS (
  SELECT
    keyword_id,
    requested,
    merchant_name,
    is_first_product_pack,
    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(has_3d_product_count) AS has_3d_product_count,
    SUM(has_360_product_count) AS has_360_product_count,
    SUM(has_discount_count) AS has_discount_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(has_3d_product_count_with_domain) AS has_3d_product_count_with_domain,
    SUM(has_360_product_count_with_domain) AS has_360_product_count_with_domain,
    SUM(has_discount_count_with_domain) AS has_discount_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY keyword_id, requested, merchant_name, is_first_product_pack
),

aggregate_by_keyword AS (
  SELECT
    keyword_id,
    merchant_name,
    is_first_product_pack,

    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(has_3d_product_count) AS has_3d_product_count,
    SUM(has_360_product_count) AS has_360_product_count,
    SUM(has_discount_count) AS has_discount_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(has_3d_product_count_with_domain) AS has_3d_product_count_with_domain,
    SUM(has_360_product_count_with_domain) AS has_360_product_count_with_domain,
    SUM(has_discount_count_with_domain) AS has_discount_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY keyword_id, merchant_name, is_first_product_pack
),

aggregate_total AS (
  SELECT
    merchant_name,
    is_first_product_pack,

    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(has_3d_product_count) AS has_3d_product_count,
    SUM(has_360_product_count) AS has_360_product_count,
    SUM(has_discount_count) AS has_discount_count,
    SUM(product_count) AS product_count,
    SUM(visible_product_count_with_domain) AS visible_product_count_with_domain,
    SUM(has_3d_product_count_with_domain) AS has_3d_product_count_with_domain,
    SUM(has_360_product_count_with_domain) AS has_360_product_count_with_domain,
    SUM(has_discount_count_with_domain) AS has_discount_count_with_domain,
    SUM(product_count_with_domain) AS product_count_with_domain,

  FROM per_serp_data
  GROUP BY merchant_name, is_first_product_pack
)

SELECT * FROM aggregate_by_requested_by_keyword
-- SELECT * FROM aggregate_total ORDER BY product_count DESC
Export Column DefinitionsA key to every column in the keyword table export from the Overview or Keywords dashboards, mapping raw field names to the metrics in the Nozzle UI.Video Pack YouTube Channel Report Query And TemplateA Looker Studio template and BigQuery query for tracking YouTube channel appearances in video packs, with channel rank, traffic, and video count metrics.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.