Paid Product Report Query And Template

Paid Product Report Template:

https://datastudio.google.com/reporting/525f0853-3e56-46f7-98f3-1d0449f1da6c/page/p_j7laebl4jd

-- REI - Paid Product Report

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

  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