Video Pack YouTube Channel Report Template:
https://lookerstudio.google.com/u/0/reporting/9fb96291-8a1b-4dce-8d9c-77b3e8b0b7bc/page/p_j7laebl4jd
-- Rei Demo - youtube channel appearances in video packs
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 >= '2025-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,
video.account AS video_channel,
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 NOT TRUE
AND result.video.is_video IS TRUE
AND rank BETWEEN 1 AND 5
),
per_serp_data AS (
SELECT
requested,
keyword_id,
video_channel,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(country) AS country,
ANY_VALUE(location) AS location,
ANY_VALUE(language) AS language,
ANY_VALUE((SELECT ARRAY_AGG(kg) FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- '))) 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 video 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 video_pack_count,
COUNT(DISTINCT IF(is_pack IS TRUE AND rank BETWEEN 1 AND 3, pack_rank, NULL)) AS video_pack_top_3_count,
-- how many visible videos are on the SERP and how many total videos (typically non-visible videos are in a carousel)
COUNTIF(is_visible IS TRUE) AS visible_video_count,
COUNT(*) AS video_count,
COUNTIF(is_visible IS TRUE AND domain_id IS NOT NULL) AS visible_video_count_with_domain,
COUNTIF(domain_id IS NOT NULL) AS video_count_with_domain,
FROM filtered_results
JOIN nozzledata.nozzle_reidemo.latest_keywords_by_keyword_id k USING (keyword_id)
GROUP BY keyword_id, requested, video_channel
),
aggregate_by_requested AS (
SELECT
requested,
video_channel,
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(video_pack_count) AS video_pack_count,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(0)] AS video_pack_count_min,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(1)] AS video_pack_count_p25,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(2)] AS video_pack_count_p50,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(3)] AS video_pack_count_p75,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(4)] AS video_pack_count_max,
SUM(video_pack_top_3_count) AS video_pack_top_3_count,
COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_video,
COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_video,
SUM(visible_video_count) AS visible_video_count,
SUM(video_count) AS video_count,
SUM(visible_video_count_with_domain) AS visible_video_count_with_domain,
SUM(video_count_with_domain) AS video_count_with_domain,
FROM per_serp_data
GROUP BY requested, video_channel
),
aggregate_by_requested_by_keyword AS (
SELECT
keyword_id,
requested,
video_channel,
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(video_pack_count) AS video_pack_count,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(0)] AS video_pack_count_min,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(1)] AS video_pack_count_p25,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(2)] AS video_pack_count_p50,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(3)] AS video_pack_count_p75,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(4)] AS video_pack_count_max,
SUM(video_pack_top_3_count) AS video_pack_top_3_count,
COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_video,
COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_video,
SUM(visible_video_count) AS visible_video_count,
SUM(video_count) AS video_count,
SUM(visible_video_count_with_domain) AS visible_video_count_with_domain,
SUM(video_count_with_domain) AS video_count_with_domain,
FROM per_serp_data
GROUP BY keyword_id, requested, video_channel
),
aggregate_by_keyword AS (
SELECT
keyword_id,
video_channel,
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(video_pack_count) AS video_pack_count,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(0)] AS video_pack_count_min,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(1)] AS video_pack_count_p25,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(2)] AS video_pack_count_p50,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(3)] AS video_pack_count_p75,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(4)] AS video_pack_count_max,
SUM(video_pack_top_3_count) AS video_pack_top_3_count,
COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_video,
COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_video,
SUM(visible_video_count) AS visible_video_count,
SUM(video_count) AS video_count,
SUM(visible_video_count_with_domain) AS visible_video_count_with_domain,
SUM(video_count_with_domain) AS video_count_with_domain,
FROM per_serp_data
GROUP BY keyword_id, video_channel
),
aggregate_total AS (
SELECT
video_channel,
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(video_pack_count) AS video_pack_count,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(0)] AS video_pack_count_min,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(1)] AS video_pack_count_p25,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(2)] AS video_pack_count_p50,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(3)] AS video_pack_count_p75,
APPROX_QUANTILES(video_pack_count, 4)[OFFSET(4)] AS video_pack_count_max,
SUM(video_pack_top_3_count) AS video_pack_top_3_count,
COUNT(DISTINCT CONCAT(keyword_id, requested)) AS serps_with_at_least_1_video,
COUNT(DISTINCT keyword_id) AS keywords_with_at_least_1_video,
SUM(visible_video_count) AS visible_video_count,
SUM(video_count) AS video_count,
SUM(visible_video_count_with_domain) AS visible_video_count_with_domain,
SUM(video_count_with_domain) AS video_count_with_domain,
FROM per_serp_data
GROUP BY video_channel
)
SELECT * FROM aggregate_by_requested_by_keyword
-- SELECT * FROM aggregate_total ORDER BY video_count DESC