--- title: Paid Product Report Query And Template description: Paid product report template in Looker Studio using Nozzle data in BigQuery. --- [Skip to content](https://help.nozzle.io/paid-product-report-query-and-template#main-content) [Customer portal](https://help.nozzle.io/tickets?hsLang=en) ![logo-blue copy 2.png\]](https://help.nozzle.io/hs-fs/hubfs/logo-blue%20copy%202.png?height=33&name=logo-blue%20copy%202.png) - [Tickets](https://help.nozzle.io/tickets) Open main navigation Close main navigation - [Tickets](https://help.nozzle.io/tickets) - [Customer portal](https://help.nozzle.io/tickets) - [Nozzle - Enterprise Keyword Rank Tracker](https://nozzle.io/) [Nozzle - Enterprise Keyword Rank Tracker](https://nozzle.io/) How can we help you? - There are no suggestions because the search field is empty. 1. [Support Home](https://help.nozzle.io/?hsLang=en) 2. [BigQuery](https://help.nozzle.io/bigquery?hsLang=en) 3. [Looker Studio Templates And Queries](https://help.nozzle.io/bigquery?hsLang=en#looker-studio-templates-and-queries) # Paid Product Report Query And Template Paid Product Report Template: [https://lookerstudio.google.com/u/0/reporting/525f0853-3e56-46f7-98f3-1d0449f1da6c/page/p\_j7laebl4jd](https://lookerstudio.google.com/u/0/reporting/525f0853-3e56-46f7-98f3-1d0449f1da6c/page/p_j7laebl4jd) ![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-30-24-8996-PM.png?width=688&height=516&name=image-png-Jun-24-2025-06-30-24-8996-PM.png) ![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-31-47-1722-PM.png?width=688&height=517&name=image-png-Jun-24-2025-06-31-47-1722-PM.png) ``` -- REI - Paid Product ReportWITH-- 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 reparselatest_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 necessaryall_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 ```   - [Getting Started](https://help.nozzle.io/getting-started?hsLang=en) - [Keyword Management](https://help.nozzle.io/keyword-management?hsLang=en) - [Brand Management](https://help.nozzle.io/brand-management?hsLang=en) - [Reputation Management](https://help.nozzle.io/reputation-management?hsLang=en) - [Account Management](https://help.nozzle.io/account-management?hsLang=en) - [Segments](https://help.nozzle.io/segments?hsLang=en) - [BigQuery](https://help.nozzle.io/bigquery?hsLang=en#main-content) - [Looker Studio Templates And Queries](https://help.nozzle.io/bigquery?hsLang=en#looker-studio-templates-and-queries) - [Google Sheets/Excel Templates and Queries](https://help.nozzle.io/bigquery?hsLang=en#google-sheets-excel-templates-and-queries) - [Dashboards](https://help.nozzle.io/dashboards?hsLang=en#main-content) - [Exports](https://help.nozzle.io/dashboards?hsLang=en#exports) - [Keyword Clustering](https://help.nozzle.io/keyword-clustering?hsLang=en) - [Troubleshooting](https://help.nozzle.io/troubleshooting?hsLang=en) - [Data Engineering](https://help.nozzle.io/data-engineering?hsLang=en) - [API](https://help.nozzle.io/api?hsLang=en#main-content) - [cURL](https://help.nozzle.io/api?hsLang=en#curl) - [BI](https://help.nozzle.io/bi?hsLang=en) - [Metrics](https://help.nozzle.io/metrics?hsLang=en#main-content) - [Share Of Voice %](https://help.nozzle.io/metrics?hsLang=en#share-of-voice) - [Metric Picker](https://help.nozzle.io/metrics?hsLang=en#metric-picker) [![SMX\_weblogo.png](https://help.nozzle.io/hs-fs/hubfs/SMX_weblogo.png?width=159&height=85&name=SMX_weblogo.png "SMX_weblogo.png")](https://help.nozzle.io/?hsLang=en) Copyright © 2026, Nozzle