Data & BI
Data & BILooker Studio Templates

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, 20261 min read

Unpaid Product Report Template:

https://datastudio.google.com/reporting/dc1d99c7-b248-4c88-b3dd-9be9b087e2c8/page/p_j7laebl4jd

Merchant Analysis page of the unpaid product report, with a merchant table and a chart of keywords with at least one product

Historical Merchant Analysis page charting keywords with at least one product and daily estimated traffic over time

Product Pack and Product Count page with totals for product packs and products alongside weekly trend charts

Historical Merchant Analysis page charting keywords with at least one 360 result and at least one 3D result

-- 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 (

SELECTkeyword_id

FROMnozzledata.nozzle_reidemo.latest_keywords_by_keyword_id

JOINUNNEST(keyword_groups)ASkg

-- WHERE kg NOT IN ('- Keyword Source: Daily - US - Desktop -')

-- JOIN UNNEST(keyword_sources) as kw_source

-- WHERE kw_source.keyword_source_id IN (123)

GROUPBYkeyword_id

),

-- grabbing the latest version of each serp in case of reparse

latest_rankings AS (

SELECTASVALUE

ARRAY_AGG(tORDERBYinserted_atDESCLIMIT1)[OFFSET(0)]

FROMnozzledata.nozzle_reidemo.rankingst

JOINfiltered_keyword_idsUSING(keyword_id)

WHERErequested>='2024-05-01'

GROUPBYranking_id

),

all_keywords_by_requested AS (

SELECT

keyword_id,

requested,

FROM(SELECTDISTINCTkeyword_idFROMfiltered_keyword_ids)

CROSSJOIN(SELECTDISTINCTrequestedFROMlatest_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.requestedIGNORENULLS)OVER(PARTITIONBYkeyword_idORDERBYrequestedASCROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW),

FIRST_VALUE(d.requestedIGNORENULLS)OVER(PARTITIONBYkeyword_idORDERBYrequestedASCROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING)

))ASdata_from_requested,

FROMall_keywords_by_requesteda

LEFTJOIN(SELECTDISTINCTrequested, keyword_idFROMlatest_rankings)dUSING(keyword_id, requested)

),

fill_null_data AS (

SELECT

a.keyword_id,

a.requested,

a.data_from_requested,

r.*EXCEPT(keyword_id, requested)

FROMall_rankings_fill_nulla

LEFTJOINlatest_rankingsrONa.keyword_id=r.keyword_idANDa.data_from_requested=r.requested

),

filtered_results AS (

SELECT

requested,

keyword_id,

COALESCE(result.merchant.merchant_name, result.product.merchant)ASmerchant_name,

result.url.domain_id,

result.pack_rank,

result.rank,

DENSE_RANK()OVER(PARTITIONBYkeyword_id, requested, result.pack_rankORDERBYresult.item_rank)ASitem_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(PARTITIONBYkeyword_id, requested), TRUE, FALSE)ASis_first_product_pack,

FROMfill_null_datan

JOINUNNEST(results)ASresult

WHERErequestedISNOTNULL

ANDresult.paid.is_paidISNOTTRUE

ANDresult.product.is_productISTRUE

),

per_serp_data AS (

SELECT

requested,

keyword_id,

merchant_name,

is_first_product_pack,

ANY_VALUE(phrase)ASphrase,

ANY_VALUE(country)AScountry,

ANY_VALUE(location)ASlocation,

ANY_VALUE(language)ASlanguage,

ANY_VALUE((SELECTARRAY_AGG(kg)FROMUNNEST(keyword_groups)kgWHERENOTSTARTS_WITH(kg, '- ')))ASkeyword_groups,

MIN(rank)ASrank,

MIN(item_rank)ASitem_rank,

AVG(item_rank)ASitem_rank_avg,

CAST(SUM(country_adwords_search_volume*click_through_rate)ASINT64)ASestimated_traffic,

MIN(pixels_from_top)ASpixels_from_top,

SUM(percentage_of_viewport)ASabove_the_fold_percentage,

SUM(percentage_of_dom)ASserp_percentage,

COUNT(DISTINCTIF(is_packISTRUE, pack_rank, NULL))ASproduct_pack_count,

COUNT(DISTINCTIF(is_packISTRUEANDrankBETWEEN1AND3, pack_rank, NULL))ASproduct_pack_top_3_count,

COUNTIF(is_visibleISTRUE)ASvisible_product_count,

COUNTIF(has_3dISTRUE)AShas_3d_product_count,

COUNTIF(has_360ISTRUE)AShas_360_product_count,

COUNTIF(has_discountISTRUE)AShas_discount_count,

COUNT(*)ASproduct_count,

COUNTIF(is_visibleISTRUEANDdomain_idISNOTNULL)ASvisible_product_count_with_domain,

COUNTIF(has_3dISTRUEANDdomain_idISNOTNULL)AShas_3d_product_count_with_domain,

COUNTIF(has_360ISTRUEANDdomain_idISNOTNULL)AShas_360_product_count_with_domain,

COUNTIF(has_discountISTRUEANDdomain_idISNOTNULL)AShas_discount_count_with_domain,

COUNTIF(domain_idISNOTNULL)ASproduct_count_with_domain,

FROMfiltered_results

JOINnozzledata.nozzle_reidemo.latest_keywords_by_keyword_idkUSING(keyword_id)

GROUPBYkeyword_id, requested, merchant_name, is_first_product_pack

),

aggregate_by_requested AS (

SELECT

requested,

merchant_name,

is_first_product_pack,

ROUND(AVG(rank), 2)ASrank,

ROUND(AVG(item_rank), 2)ASitem_rank,

ROUND(AVG(item_rank_avg), 2)ASitem_rank_avg_avg,

SUM(estimated_traffic)ASestimated_traffic,

CAST(AVG(pixels_from_top)ASINT64)ASpixels_from_top,

ROUND(AVG(above_the_fold_percentage), 4)ASabove_the_fold_percentage,

ROUND(AVG(serp_percentage), 4)ASserp_percentage,

SUM(product_pack_count)ASproduct_pack_count,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)]ASproduct_pack_count_min,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)]ASproduct_pack_count_p25,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)]ASproduct_pack_count_p50,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)]ASproduct_pack_count_p75,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)]ASproduct_pack_count_max,

SUM(product_pack_top_3_count)ASproduct_pack_top_3_count,

COUNT(DISTINCTCONCAT(keyword_id, requested))ASserps_with_at_least_1_product,

COUNT(DISTINCTkeyword_id)ASkeywords_with_at_least_1_product,

SUM(visible_product_count)ASvisible_product_count,

SUM(has_3d_product_count)AShas_3d_product_count,

SUM(has_360_product_count)AShas_360_product_count,

SUM(has_discount_count)AShas_discount_count,

SUM(product_count)ASproduct_count,

SUM(visible_product_count_with_domain)ASvisible_product_count_with_domain,

SUM(has_3d_product_count_with_domain)AShas_3d_product_count_with_domain,

SUM(has_360_product_count_with_domain)AShas_360_product_count_with_domain,

SUM(has_discount_count_with_domain)AShas_discount_count_with_domain,

SUM(product_count_with_domain)ASproduct_count_with_domain,

FROMper_serp_data

GROUPBYrequested, 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)ASphrase,

ANY_VALUE(country)AScountry,

ANY_VALUE(location)ASlocation,

ANY_VALUE(language)ASlanguage,

ANY_VALUE(keyword_groups)ASkeyword_groups,

ROUND(AVG(rank), 2)ASrank,

ROUND(AVG(item_rank), 2)ASitem_rank,

ROUND(AVG(item_rank_avg), 2)ASitem_rank_avg_avg,

SUM(estimated_traffic)ASestimated_traffic,

CAST(AVG(pixels_from_top)ASINT64)ASpixels_from_top,

ROUND(AVG(above_the_fold_percentage), 4)ASabove_the_fold_percentage,

ROUND(AVG(serp_percentage), 4)ASserp_percentage,

SUM(product_pack_count)ASproduct_pack_count,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)]ASproduct_pack_count_min,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)]ASproduct_pack_count_p25,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)]ASproduct_pack_count_p50,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)]ASproduct_pack_count_p75,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)]ASproduct_pack_count_max,

SUM(product_pack_top_3_count)ASproduct_pack_top_3_count,

COUNT(DISTINCTCONCAT(keyword_id, requested))ASserps_with_at_least_1_product,

COUNT(DISTINCTkeyword_id)ASkeywords_with_at_least_1_product,

SUM(visible_product_count)ASvisible_product_count,

SUM(has_3d_product_count)AShas_3d_product_count,

SUM(has_360_product_count)AShas_360_product_count,

SUM(has_discount_count)AShas_discount_count,

SUM(product_count)ASproduct_count,

SUM(visible_product_count_with_domain)ASvisible_product_count_with_domain,

SUM(has_3d_product_count_with_domain)AShas_3d_product_count_with_domain,

SUM(has_360_product_count_with_domain)AShas_360_product_count_with_domain,

SUM(has_discount_count_with_domain)AShas_discount_count_with_domain,

SUM(product_count_with_domain)ASproduct_count_with_domain,

FROMper_serp_data

GROUPBYkeyword_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)ASrank,

ROUND(AVG(item_rank), 2)ASitem_rank,

ROUND(AVG(item_rank_avg), 2)ASitem_rank_avg_avg,

SUM(estimated_traffic)ASestimated_traffic,

CAST(AVG(pixels_from_top)ASINT64)ASpixels_from_top,

ROUND(AVG(above_the_fold_percentage), 4)ASabove_the_fold_percentage,

ROUND(AVG(serp_percentage), 4)ASserp_percentage,

SUM(product_pack_count)ASproduct_pack_count,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)]ASproduct_pack_count_min,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)]ASproduct_pack_count_p25,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)]ASproduct_pack_count_p50,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)]ASproduct_pack_count_p75,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)]ASproduct_pack_count_max,

SUM(product_pack_top_3_count)ASproduct_pack_top_3_count,

COUNT(DISTINCTCONCAT(keyword_id, requested))ASserps_with_at_least_1_product,

COUNT(DISTINCTkeyword_id)ASkeywords_with_at_least_1_product,

SUM(visible_product_count)ASvisible_product_count,

SUM(has_3d_product_count)AShas_3d_product_count,

SUM(has_360_product_count)AShas_360_product_count,

SUM(has_discount_count)AShas_discount_count,

SUM(product_count)ASproduct_count,

SUM(visible_product_count_with_domain)ASvisible_product_count_with_domain,

SUM(has_3d_product_count_with_domain)AShas_3d_product_count_with_domain,

SUM(has_360_product_count_with_domain)AShas_360_product_count_with_domain,

SUM(has_discount_count_with_domain)AShas_discount_count_with_domain,

SUM(product_count_with_domain)ASproduct_count_with_domain,

FROMper_serp_data

GROUPBYkeyword_id, merchant_name, is_first_product_pack

),

aggregate_total AS (

SELECT

merchant_name,

is_first_product_pack,

ROUND(AVG(rank), 2)ASrank,

ROUND(AVG(item_rank), 2)ASitem_rank,

ROUND(AVG(item_rank_avg), 2)ASitem_rank_avg_avg,

SUM(estimated_traffic)ASestimated_traffic,

CAST(AVG(pixels_from_top)ASINT64)ASpixels_from_top,

ROUND(AVG(above_the_fold_percentage), 4)ASabove_the_fold_percentage,

ROUND(AVG(serp_percentage), 4)ASserp_percentage,

SUM(product_pack_count)ASproduct_pack_count,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(0)]ASproduct_pack_count_min,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(1)]ASproduct_pack_count_p25,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(2)]ASproduct_pack_count_p50,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(3)]ASproduct_pack_count_p75,

APPROX_QUANTILES(product_pack_count, 4)[OFFSET(4)]ASproduct_pack_count_max,

SUM(product_pack_top_3_count)ASproduct_pack_top_3_count,

COUNT(DISTINCTCONCAT(keyword_id, requested))ASserps_with_at_least_1_product,

COUNT(DISTINCTkeyword_id)ASkeywords_with_at_least_1_product,

SUM(visible_product_count)ASvisible_product_count,

SUM(has_3d_product_count)AShas_3d_product_count,

SUM(has_360_product_count)AShas_360_product_count,

SUM(has_discount_count)AShas_discount_count,

SUM(product_count)ASproduct_count,

SUM(visible_product_count_with_domain)ASvisible_product_count_with_domain,

SUM(has_3d_product_count_with_domain)AShas_3d_product_count_with_domain,

SUM(has_360_product_count_with_domain)AShas_360_product_count_with_domain,

SUM(has_discount_count_with_domain)AShas_discount_count_with_domain,

SUM(product_count_with_domain)ASproduct_count_with_domain,

FROMper_serp_data

GROUPBYmerchant_name, is_first_product_pack

)

SELECT * FROM aggregate_by_requested_by_keyword

-- SELECT * FROM aggregate_total ORDER BY product_count DESC

Related articles

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.