---
title: Unpaid Product Report Query And Template
description: Unpaid product report template in Looker Studio using Nozzle data in BigQuery.
---

[Skip to content](https://help.nozzle.io/unpaid-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)

# Unpaid Product Report Query And Template

Unpaid Product Report Template:

[https://lookerstudio.google.com/u/0/reporting/dc1d99c7-b248-4c88-b3dd-9be9b087e2c8/page/p\_j7laebl4jd](https://lookerstudio.google.com/u/0/reporting/dc1d99c7-b248-4c88-b3dd-9be9b087e2c8/page/p_j7laebl4jd)

![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-14-53-9977-PM.png?width=670&height=502&name=image-png-Jun-24-2025-06-14-53-9977-PM.png)

![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-23-48-2171-PM.png?width=670&height=502&name=image-png-Jun-24-2025-06-23-48-2171-PM.png)

![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-27-27-3707-PM.png?width=670&height=503&name=image-png-Jun-24-2025-06-27-27-3707-PM.png)

![](https://help.nozzle.io/hs-fs/hubfs/image-png-Jun-24-2025-06-26-51-2727-PM.png?width=670&height=503&name=image-png-Jun-24-2025-06-26-51-2727-PM.png)

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

- [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)

<https://twitter.com/nozzleio> <https://www.youtube.com/channel/UC6vTEcp-zzbgN2mJijLx6TA> <https://www.linkedin.com/company/nozzle/>

Copyright © 2026, Nozzle