![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){kind=logo}
Unpaid Product Report Template:
https://lookerstudio.google.com/u/0/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 (
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