Skip to content

Instantly share code, notes, and snippets.

@boydnorwood
Last active March 21, 2024 08:28
Show Gist options
  • Save boydnorwood/e19086c77c477b8ad32f00d0c1247add to your computer and use it in GitHub Desktop.
Save boydnorwood/e19086c77c477b8ad32f00d0c1247add to your computer and use it in GitHub Desktop.
SQL for generating a basic rankings report in Data Studio with data from your Nozzle workspace
--Top Ranking URLs Report for Nozzle.io
--Data Studio Template can be found here: https://datastudio.google.com/u/1/reporting/359d4414-0cd6-4da1-8df5-2c6908e0ddec/page/pyxcB
WITH
-- find the latest versioned keyword data
-- this can also be used to pin a query to an older version, good for static reports
latest_keyword_source_versions AS (
SELECT keyword_source_id, MAX(keyword_source_version_id) AS keyword_source_version_id
FROM nozzledata.nozzle_nozzleofficial.keywords
WHERE keyword_source_id=930701976723823
GROUP BY keyword_source_id
),
-- filter keyword groups first to avoid accidentally aggregating
-- on multiple rows based on keyword groupings
filtered_keywords AS (
SELECT
keyword_id,
ANY_VALUE(phrase_id) AS phrase_id,
ANY_VALUE(phrase) AS phrase,
ANY_VALUE(locale_id) AS locale_id,
ANY_VALUE(device) AS device,
ANY_VALUE(engine) AS engine,
ANY_VALUE(language) AS language,
ANY_VALUE(location_id) AS location_id,
ANY_VALUE(location_type) AS location_type,
ANY_VALUE(location) AS location,
ANY_VALUE(country) AS country,
ANY_VALUE(ad_words_criteria_id) AS ad_words_criteria_id,
ANY_VALUE(keywords.groups) AS keyword_groups,
FROM nozzledata.nozzle_nozzleofficial.keywords
JOIN latest_keyword_source_versions USING (keyword_source_id, keyword_source_version_id)
GROUP BY keyword_id
),
-- filter rankings by keyword ids + search level fields
latest_filtered_rankings AS (
SELECT AS VALUE
ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
FROM nozzledata.nozzle_nozzleofficial.rankings t
JOIN filtered_keywords USING (keyword_id)
WHERE requested >= '2021-08-01' AND requested <= '2021-12-31'
GROUP BY ranking_id
),
-- apply any result level filters here, set group by, and get all metrics necessary for later calculations
latest_filtered_rankings_results AS (
SELECT
keyword_id,
requested,
keyword_metrics.adwords_search_volume AS search_volume,
FIRST_VALUE(result.url.url) OVER (PARTITION BY keyword_id, requested ORDER BY rank, item_rank) AS ranking_url,
result.url.url_id,
result.rank,
result.item_rank,
result.paid_adjusted_rank,
result.nozzle_metrics.click_through_rate,
result.nozzle_metrics.estimated_traffic,
result.nozzle_metrics.ppc_value,
result.measurements.pixels_from_top,
result.measurements.pixel_height * result.measurements.pixel_width AS result_pixels_total,
result.measurements.percentage_of_viewport,
result.measurements.percentage_of_dom,
FROM latest_filtered_rankings
JOIN filtered_keywords USING (keyword_id)
JOIN UNNEST(results) AS result
WHERE (result.paid IS NULL OR result.paid.is_paid=FALSE)
AND (result.url.domain='nozzle.io')
),
-- this doesn't fill null on days where there was no keyword data
distinct_keyword_ids_per_requested AS (
SELECT
keyword_id,
requested,
FROM latest_filtered_rankings_results
GROUP BY keyword_id, requested
),
distinct_group_bys AS (
SELECT
keyword_id,
FROM latest_filtered_rankings_results
GROUP BY keyword_id
),
-- for accurate calculations, we have to fill all requested periods for all keywords
fill_nulls AS (
SELECT
keyword_id,
requested,
FROM distinct_keyword_ids_per_requested
),
-- calculate min metrics per keyword_id, group_by, and requested
per_serp_metrics AS (
SELECT
requested,
keyword_id,
ANY_VALUE(ranking_url) AS ranking_url,
ANY_VALUE(search_volume) AS search_volume,
MIN(rank) AS top_rank_ignore_nonranking,
IFNULL(MIN(rank), 101) AS top_rank,
MIN(paid_adjusted_rank) AS top_paid_adjusted_rank_ignore_nonranking,
IFNULL(MIN(paid_adjusted_rank), 101) AS top_paid_adjusted_rank,
MIN(pixels_from_top) AS top_pixels_from_top_ignore_nonranking,
IFNULL(MIN(pixels_from_top), 30000) AS top_pixels_from_top,
SUM(click_through_rate) AS click_through_rate_ignore_nonranking,
IFNULL(SUM(click_through_rate), 0) AS click_through_rate,
SUM(percentage_of_viewport) AS percentage_of_viewport_ignore_nonranking,
IFNULL(SUM(percentage_of_viewport), 0) AS percentage_of_viewport,
SUM(percentage_of_dom) AS percentage_of_dom_ignore_nonranking,
IFNULL(SUM(percentage_of_dom), 0) AS percentage_of_dom,
HLL_COUNT.INIT(url_id, 24) AS unique_urls__count__total,
HLL_COUNT.INIT(IF(rank > 0 AND rank <= 3, url_id, NULL), 24) AS unique_urls__count__rank_group_1_3,
HLL_COUNT.INIT(IF(rank = 1, url_id, NULL), 24) AS unique_urls__count__rank_group_1,
HLL_COUNT.INIT(IF(rank = 2, url_id, NULL), 24) AS unique_urls__count__rank_group_2,
HLL_COUNT.INIT(IF(rank = 3, url_id, NULL), 24) AS unique_urls__count__rank_group_3,
HLL_COUNT.INIT(IF(rank > 3 AND rank <= 6, url_id, NULL), 24) AS unique_urls__count__rank_group_4_6,
HLL_COUNT.INIT(IF(rank > 6 AND rank <= 10, url_id, NULL), 24) AS unique_urls__count__rank_group_7_10,
HLL_COUNT.INIT(IF(rank > 11 AND rank <= 15, url_id, NULL), 24) AS unique_urls__count__rank_group_11_15,
HLL_COUNT.INIT(IF(rank > 16 AND rank <= 20, url_id, NULL), 24) AS unique_urls__count__rank_group_16_20,
HLL_COUNT.INIT(IF(rank > 21 AND rank <= 50, url_id, NULL), 24) AS unique_urls__count__rank_group_21_50,
HLL_COUNT.INIT(IF(rank > 51, url_id, NULL), 24) AS unique_urls__count__rank_group_51,
HLL_COUNT.INIT(IF(rank IS NOT NULL, keyword_id, NULL), 24) AS unique_keywords__count__total,
HLL_COUNT.INIT(IF(rank > 0 AND rank <= 3, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_1_3,
HLL_COUNT.INIT(IF(rank = 1, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_1,
HLL_COUNT.INIT(IF(rank = 2, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_2,
HLL_COUNT.INIT(IF(rank = 3, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_3,
HLL_COUNT.INIT(IF(rank > 3 AND rank <= 6, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_4_6,
HLL_COUNT.INIT(IF(rank > 6 AND rank <= 10, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_7_10,
HLL_COUNT.INIT(IF(rank > 11 AND rank <= 15, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_11_15,
HLL_COUNT.INIT(IF(rank > 16 AND rank <= 20, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_16_20,
HLL_COUNT.INIT(IF(rank > 21 AND rank <= 50, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_21_50,
HLL_COUNT.INIT(IF(rank > 51, keyword_id, NULL), 24) AS unique_keywords__count__rank_group_51,
COUNTIF(rank IS NOT NULL) AS unique_results__count__total,
SUM(IF(rank > 0 AND rank <= 3, 1, 0)) AS unique_results__count__rank_group_1_3,
SUM(IF(rank = 1, 1, 0)) AS unique_results__count__rank_group_1,
SUM(IF(rank = 2, 1, 0)) AS unique_results__count__rank_group_2,
SUM(IF(rank = 3, 1, 0)) AS unique_results__count__rank_group_3,
SUM(IF(rank > 3 AND rank <= 6, 1, 0)) AS unique_results__count__rank_group_4_6,
SUM(IF(rank > 6 AND rank <= 10, 1, 0)) AS unique_results__count__rank_group_7_10,
SUM(IF(rank > 11 AND rank <= 15, 1, 0)) AS unique_results__count__rank_group_11_15,
SUM(IF(rank > 16 AND rank <= 20, 1, 0)) AS unique_results__count__rank_group_16_20,
SUM(IF(rank > 21 AND rank <= 50, 1, 0)) AS unique_results__count__rank_group_21_50,
SUM(IF(rank > 51, 1, 0)) AS unique_results__count__rank_group_51,
SUM(estimated_traffic) AS estimated_traffic__sum__total,
SUM(IF(rank > 0 AND rank <= 3, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_1_3,
SUM(IF(rank = 1, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_1,
SUM(IF(rank = 2, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_2,
SUM(IF(rank = 3, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_3,
SUM(IF(rank > 3 AND rank <= 6, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_4_6,
SUM(IF(rank > 6 AND rank <= 10, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_7_10,
SUM(IF(rank > 11 AND rank <= 15, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_11_15,
SUM(IF(rank > 16 AND rank <= 20, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_16_20,
SUM(IF(rank > 21 AND rank <= 50, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_21_50,
SUM(IF(rank > 51, estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_51,
SUM(ppc_value) AS ppc_value__sum__total,
SUM(IF(rank > 0 AND rank <= 3, ppc_value, 0)) AS ppc_value__sum__rank_group_1_3,
SUM(IF(rank = 1, ppc_value, 0)) AS ppc_value__sum__rank_group_1,
SUM(IF(rank = 2, ppc_value, 0)) AS ppc_value__sum__rank_group_2,
SUM(IF(rank = 3, ppc_value, 0)) AS ppc_value__sum__rank_group_3,
SUM(IF(rank > 3 AND rank <= 6, ppc_value, 0)) AS ppc_value__sum__rank_group_4_6,
SUM(IF(rank > 6 AND rank <= 10, ppc_value, 0)) AS ppc_value__sum__rank_group_7_10,
SUM(IF(rank > 11 AND rank <= 15, ppc_value, 0)) AS ppc_value__sum__rank_group_11_15,
SUM(IF(rank > 16 AND rank <= 20, ppc_value, 0)) AS ppc_value__sum__rank_group_16_20,
SUM(IF(rank > 21 AND rank <= 50, ppc_value, 0)) AS ppc_value__sum__rank_group_21_50,
SUM(IF(rank > 51, ppc_value, 0)) AS ppc_value__sum__rank_group_51,
FROM latest_filtered_rankings_results
RIGHT JOIN fill_nulls n USING (keyword_id, requested)
GROUP BY keyword_id, requested
),
-- get the average of all the minimum values
per_serp_metrics_aggregation AS (
SELECT
requested,
keyword_id,
ANY_VALUE(ranking_url) AS ranking_url,
ANY_VALUE(search_volume) AS search_volume,
APPROX_QUANTILES(top_rank_ignore_nonranking, 4) AS top_rank_ignore_nonranking__quantiles,
AVG(top_rank_ignore_nonranking) AS top_rank_ignore_nonranking__avg__total,
APPROX_QUANTILES(top_rank, 4) AS top_rank__quantiles,
AVG(top_rank) AS top_rank__avg__total,
APPROX_QUANTILES(top_paid_adjusted_rank_ignore_nonranking, 4) AS top_paid_adjusted_rank_ignore_nonranking__quantiles,
AVG(top_paid_adjusted_rank_ignore_nonranking) AS top_paid_adjusted_rank_ignore_nonranking__avg__total,
APPROX_QUANTILES(top_paid_adjusted_rank, 4) AS top_paid_adjusted_rank__quantiles,
AVG(top_paid_adjusted_rank) AS top_paid_adjusted_rank__avg__total,
APPROX_QUANTILES(top_pixels_from_top_ignore_nonranking, 4) AS top_pixels_from_top_ignore_nonranking__quantiles,
AVG(top_pixels_from_top_ignore_nonranking) AS top_pixels_from_top_ignore_nonranking__avg__total,
APPROX_QUANTILES(top_pixels_from_top, 4) AS top_pixels_from_top__quantiles,
AVG(top_pixels_from_top) AS top_pixels_from_top__avg__total,
APPROX_QUANTILES(click_through_rate_ignore_nonranking, 4) AS click_through_rate_ignore_nonranking__quantiles,
AVG(click_through_rate_ignore_nonranking) AS click_through_rate_ignore_nonranking__avg__total,
APPROX_QUANTILES(click_through_rate, 4) AS click_through_rate__quantiles,
AVG(click_through_rate) AS click_through_rate__avg__total,
APPROX_QUANTILES(percentage_of_viewport_ignore_nonranking, 4) AS percentage_of_viewport_ignore_nonranking__quantiles,
AVG(percentage_of_viewport_ignore_nonranking) AS percentage_of_viewport_ignore_nonranking__avg__total,
APPROX_QUANTILES(percentage_of_viewport, 4) AS percentage_of_viewport__quantiles,
AVG(percentage_of_viewport) AS percentage_of_viewport__avg__total,
APPROX_QUANTILES(percentage_of_dom_ignore_nonranking, 4) AS percentage_of_dom_ignore_nonranking__quantiles,
AVG(percentage_of_dom_ignore_nonranking) AS percentage_of_dom_ignore_nonranking__avg__total,
APPROX_QUANTILES(percentage_of_dom, 4) AS percentage_of_dom__quantiles,
AVG(percentage_of_dom) AS percentage_of_dom__avg__total,
HLL_COUNT.MERGE(unique_urls__count__total) AS unique_urls__count__total,
HLL_COUNT.MERGE(unique_urls__count__rank_group_1_3) AS unique_urls__count__rank_group_1_3,
HLL_COUNT.MERGE(unique_urls__count__rank_group_1) AS unique_urls__count__rank_group_1,
HLL_COUNT.MERGE(unique_urls__count__rank_group_2) AS unique_urls__count__rank_group_2,
HLL_COUNT.MERGE(unique_urls__count__rank_group_3) AS unique_urls__count__rank_group_3,
HLL_COUNT.MERGE(unique_urls__count__rank_group_4_6) AS unique_urls__count__rank_group_4_6,
HLL_COUNT.MERGE(unique_urls__count__rank_group_7_10) AS unique_urls__count__rank_group_7_10,
HLL_COUNT.MERGE(unique_urls__count__rank_group_11_15) AS unique_urls__count__rank_group_11_15,
HLL_COUNT.MERGE(unique_urls__count__rank_group_16_20) AS unique_urls__count__rank_group_16_20,
HLL_COUNT.MERGE(unique_urls__count__rank_group_21_50) AS unique_urls__count__rank_group_21_50,
HLL_COUNT.MERGE(unique_urls__count__rank_group_51) AS unique_urls__count__rank_group_51,
HLL_COUNT.MERGE(unique_keywords__count__total) AS unique_keywords__count__total,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_1_3) AS unique_keywords__count__rank_group_1_3,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_1) AS unique_keywords__count__rank_group_1,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_2) AS unique_keywords__count__rank_group_2,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_3) AS unique_keywords__count__rank_group_3,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_4_6) AS unique_keywords__count__rank_group_4_6,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_7_10) AS unique_keywords__count__rank_group_7_10,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_11_15) AS unique_keywords__count__rank_group_11_15,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_16_20) AS unique_keywords__count__rank_group_16_20,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_21_50) AS unique_keywords__count__rank_group_21_50,
HLL_COUNT.MERGE(unique_keywords__count__rank_group_51) AS unique_keywords__count__rank_group_51,
SUM(unique_results__count__total) AS unique_results__count__total,
SUM(unique_results__count__rank_group_1_3) AS unique_results__count__rank_group_1_3,
SUM(unique_results__count__rank_group_1) AS unique_results__count__rank_group_1,
SUM(unique_results__count__rank_group_2) AS unique_results__count__rank_group_2,
SUM(unique_results__count__rank_group_3) AS unique_results__count__rank_group_3,
SUM(unique_results__count__rank_group_4_6) AS unique_results__count__rank_group_4_6,
SUM(unique_results__count__rank_group_7_10) AS unique_results__count__rank_group_7_10,
SUM(unique_results__count__rank_group_11_15) AS unique_results__count__rank_group_11_15,
SUM(unique_results__count__rank_group_16_20) AS unique_results__count__rank_group_16_20,
SUM(unique_results__count__rank_group_21_50) AS unique_results__count__rank_group_21_50,
SUM(unique_results__count__rank_group_51) AS unique_results__count__rank_group_51,
SUM(estimated_traffic__sum__total) AS estimated_traffic__sum__total,
SUM(estimated_traffic__sum__rank_group_1_3) AS estimated_traffic__sum__rank_group_1_3,
SUM(estimated_traffic__sum__rank_group_1) AS estimated_traffic__sum__rank_group_1,
SUM(estimated_traffic__sum__rank_group_2) AS estimated_traffic__sum__rank_group_2,
SUM(estimated_traffic__sum__rank_group_3) AS estimated_traffic__sum__rank_group_3,
SUM(estimated_traffic__sum__rank_group_4_6) AS estimated_traffic__sum__rank_group_4_6,
SUM(estimated_traffic__sum__rank_group_7_10) AS estimated_traffic__sum__rank_group_7_10,
SUM(estimated_traffic__sum__rank_group_11_15) AS estimated_traffic__sum__rank_group_11_15,
SUM(estimated_traffic__sum__rank_group_16_20) AS estimated_traffic__sum__rank_group_16_20,
SUM(estimated_traffic__sum__rank_group_21_50) AS estimated_traffic__sum__rank_group_21_50,
SUM(estimated_traffic__sum__rank_group_51) AS estimated_traffic__sum__rank_group_51,
SUM(ppc_value__sum__total) AS ppc_value__sum__total,
SUM(ppc_value__sum__rank_group_1_3) AS ppc_value__sum__rank_group_1_3,
SUM(ppc_value__sum__rank_group_1) AS ppc_value__sum__rank_group_1,
SUM(ppc_value__sum__rank_group_2) AS ppc_value__sum__rank_group_2,
SUM(ppc_value__sum__rank_group_3) AS ppc_value__sum__rank_group_3,
SUM(ppc_value__sum__rank_group_4_6) AS ppc_value__sum__rank_group_4_6,
SUM(ppc_value__sum__rank_group_7_10) AS ppc_value__sum__rank_group_7_10,
SUM(ppc_value__sum__rank_group_11_15) AS ppc_value__sum__rank_group_11_15,
SUM(ppc_value__sum__rank_group_16_20) AS ppc_value__sum__rank_group_16_20,
SUM(ppc_value__sum__rank_group_21_50) AS ppc_value__sum__rank_group_21_50,
SUM(ppc_value__sum__rank_group_51) AS ppc_value__sum__rank_group_51,
FROM per_serp_metrics
GROUP BY requested, keyword_id
),
group_by_totals AS (
SELECT
requested,
keyword_id,
phrase_id,
phrase,
locale_id,
device,
engine,
language,
location_id,
location_type,
location,
country,
ad_words_criteria_id,
keyword_groups,
ranking_url,
search_volume,
top_rank__avg__total AS top_rank__avg__total__value,
top_rank__avg__total - FIRST_VALUE(top_rank__avg__total) OVER (change) AS top_rank__avg__total__change,
top_pixels_from_top__avg__total AS top_pixels_from_top__avg__total__value,
top_pixels_from_top__avg__total - FIRST_VALUE(top_pixels_from_top__avg__total) OVER (change) AS top_pixels_from_top__avg__total__change,
click_through_rate__avg__total AS click_through_rate__avg__total__value,
click_through_rate__avg__total - FIRST_VALUE(click_through_rate__avg__total) OVER (change) AS click_through_rate__avg__total__change,
percentage_of_viewport__avg__total AS percentage_of_viewport__avg__total__value,
percentage_of_viewport__avg__total - FIRST_VALUE(percentage_of_viewport__avg__total) OVER (change) AS percentage_of_viewport__avg__total__change,
percentage_of_dom__avg__total AS percentage_of_dom__avg__total__value,
percentage_of_dom__avg__total - FIRST_VALUE(percentage_of_dom__avg__total) OVER (change) AS percentage_of_dom__avg__total__change,
estimated_traffic__sum__total AS estimated_traffic__sum__total__value,
estimated_traffic__sum__total - FIRST_VALUE(estimated_traffic__sum__total) OVER (change) AS estimated_traffic__sum__total__change,
estimated_traffic__sum__rank_group_1 AS estimated_traffic__sum__rank_group_1__value,
estimated_traffic__sum__rank_group_1 - FIRST_VALUE(estimated_traffic__sum__rank_group_1) OVER (change) AS estimated_traffic__sum__rank_group_1__change,
estimated_traffic__sum__rank_group_2 AS estimated_traffic__sum__rank_group_2__value,
estimated_traffic__sum__rank_group_2 - FIRST_VALUE(estimated_traffic__sum__rank_group_2) OVER (change) AS estimated_traffic__sum__rank_group_2__change,
estimated_traffic__sum__rank_group_3 AS estimated_traffic__sum__rank_group_3__value,
estimated_traffic__sum__rank_group_3 - FIRST_VALUE(estimated_traffic__sum__rank_group_3) OVER (change) AS estimated_traffic__sum__rank_group_3__change,
estimated_traffic__sum__rank_group_4_6 AS estimated_traffic__sum__rank_group_4_6__value,
estimated_traffic__sum__rank_group_4_6 - FIRST_VALUE(estimated_traffic__sum__rank_group_4_6) OVER (change) AS estimated_traffic__sum__rank_group_4_6__change,
estimated_traffic__sum__rank_group_7_10 AS estimated_traffic__sum__rank_group_7_10__value,
estimated_traffic__sum__rank_group_7_10 - FIRST_VALUE(estimated_traffic__sum__rank_group_7_10) OVER (change) AS estimated_traffic__sum__rank_group_7_10__change,
estimated_traffic__sum__rank_group_11_15 AS estimated_traffic__sum__rank_group_11_15__value,
estimated_traffic__sum__rank_group_11_15 - FIRST_VALUE(estimated_traffic__sum__rank_group_11_15) OVER (change) AS estimated_traffic__sum__rank_group_11_15__change,
estimated_traffic__sum__rank_group_16_20 AS estimated_traffic__sum__rank_group_16_20__value,
estimated_traffic__sum__rank_group_16_20 - FIRST_VALUE(estimated_traffic__sum__rank_group_16_20) OVER (change) AS estimated_traffic__sum__rank_group_16_20__change,
estimated_traffic__sum__rank_group_21_50 AS estimated_traffic__sum__rank_group_21_50__value,
estimated_traffic__sum__rank_group_21_50 - FIRST_VALUE(estimated_traffic__sum__rank_group_21_50) OVER (change) AS estimated_traffic__sum__rank_group_21_50__change,
estimated_traffic__sum__rank_group_51 AS estimated_traffic__sum__rank_group_51__value,
estimated_traffic__sum__rank_group_51 - FIRST_VALUE(estimated_traffic__sum__rank_group_51) OVER (change) AS estimated_traffic__sum__rank_group_51__change,
ppc_value__sum__total AS ppc_value__sum__total__value,
ppc_value__sum__total - FIRST_VALUE(ppc_value__sum__total) OVER (change) AS ppc_value__sum__total__change,
ppc_value__sum__rank_group_1 AS ppc_value__sum__rank_group_1__value,
ppc_value__sum__rank_group_1 - FIRST_VALUE(ppc_value__sum__rank_group_1) OVER (change) AS ppc_value__sum__rank_group_1__change,
ppc_value__sum__rank_group_2 AS ppc_value__sum__rank_group_2__value,
ppc_value__sum__rank_group_2 - FIRST_VALUE(ppc_value__sum__rank_group_2) OVER (change) AS ppc_value__sum__rank_group_2__change,
ppc_value__sum__rank_group_3 AS ppc_value__sum__rank_group_3__value,
ppc_value__sum__rank_group_3 - FIRST_VALUE(ppc_value__sum__rank_group_3) OVER (change) AS ppc_value__sum__rank_group_3__change,
ppc_value__sum__rank_group_4_6 AS ppc_value__sum__rank_group_4_6__value,
ppc_value__sum__rank_group_4_6 - FIRST_VALUE(ppc_value__sum__rank_group_4_6) OVER (change) AS ppc_value__sum__rank_group_4_6__change,
ppc_value__sum__rank_group_7_10 AS ppc_value__sum__rank_group_7_10__value,
ppc_value__sum__rank_group_7_10 - FIRST_VALUE(ppc_value__sum__rank_group_7_10) OVER (change) AS ppc_value__sum__rank_group_7_10__change,
ppc_value__sum__rank_group_11_15 AS ppc_value__sum__rank_group_11_15__value,
ppc_value__sum__rank_group_11_15 - FIRST_VALUE(ppc_value__sum__rank_group_11_15) OVER (change) AS ppc_value__sum__rank_group_11_15__change,
ppc_value__sum__rank_group_16_20 AS ppc_value__sum__rank_group_16_20__value,
ppc_value__sum__rank_group_16_20 - FIRST_VALUE(ppc_value__sum__rank_group_16_20) OVER (change) AS ppc_value__sum__rank_group_16_20__change,
ppc_value__sum__rank_group_21_50 AS ppc_value__sum__rank_group_21_50__value,
ppc_value__sum__rank_group_21_50 - FIRST_VALUE(ppc_value__sum__rank_group_21_50) OVER (change) AS ppc_value__sum__rank_group_21_50__change,
ppc_value__sum__rank_group_51 AS ppc_value__sum__rank_group_51__value,
ppc_value__sum__rank_group_51 - FIRST_VALUE(ppc_value__sum__rank_group_51) OVER (change) AS ppc_value__sum__rank_group_51__change,
unique_results__count__total AS unique_results__count__total__value,
unique_results__count__total - FIRST_VALUE(unique_results__count__total) OVER (change) AS unique_results__count__total__change,
unique_results__count__rank_group_1 AS unique_results__count__rank_group_1__value,
unique_results__count__rank_group_1 - FIRST_VALUE(unique_results__count__rank_group_1) OVER (change) AS unique_results__count__rank_group_1__change,
unique_results__count__rank_group_2 AS unique_results__count__rank_group_2__value,
unique_results__count__rank_group_2 - FIRST_VALUE(unique_results__count__rank_group_2) OVER (change) AS unique_results__count__rank_group_2__change,
unique_results__count__rank_group_3 AS unique_results__count__rank_group_3__value,
unique_results__count__rank_group_3 - FIRST_VALUE(unique_results__count__rank_group_3) OVER (change) AS unique_results__count__rank_group_3__change,
unique_results__count__rank_group_4_6 AS unique_results__count__rank_group_4_6__value,
unique_results__count__rank_group_4_6 - FIRST_VALUE(unique_results__count__rank_group_4_6) OVER (change) AS unique_results__count__rank_group_4_6__change,
unique_results__count__rank_group_7_10 AS unique_results__count__rank_group_7_10__value,
unique_results__count__rank_group_7_10 - FIRST_VALUE(unique_results__count__rank_group_7_10) OVER (change) AS unique_results__count__rank_group_7_10__change,
unique_results__count__rank_group_11_15 AS unique_results__count__rank_group_11_15__value,
unique_results__count__rank_group_11_15 - FIRST_VALUE(unique_results__count__rank_group_11_15) OVER (change) AS unique_results__count__rank_group_11_15__change,
unique_results__count__rank_group_16_20 AS unique_results__count__rank_group_16_20__value,
unique_results__count__rank_group_16_20 - FIRST_VALUE(unique_results__count__rank_group_16_20) OVER (change) AS unique_results__count__rank_group_16_20__change,
unique_results__count__rank_group_21_50 AS unique_results__count__rank_group_21_50__value,
unique_results__count__rank_group_21_50 - FIRST_VALUE(unique_results__count__rank_group_21_50) OVER (change) AS unique_results__count__rank_group_21_50__change,
unique_results__count__rank_group_51 AS unique_results__count__rank_group_51__value,
unique_results__count__rank_group_51 - FIRST_VALUE(unique_results__count__rank_group_51) OVER (change) AS unique_results__count__rank_group_51__change,
unique_keywords__count__total AS unique_keywords__count__total__value,
unique_keywords__count__total - FIRST_VALUE(unique_keywords__count__total) OVER (change) AS unique_keywords__count__total__change,
unique_keywords__count__rank_group_1 AS unique_keywords__count__rank_group_1__value,
unique_keywords__count__rank_group_1 - FIRST_VALUE(unique_keywords__count__rank_group_1) OVER (change) AS unique_keywords__count__rank_group_1__change,
unique_keywords__count__rank_group_2 AS unique_keywords__count__rank_group_2__value,
unique_keywords__count__rank_group_2 - FIRST_VALUE(unique_keywords__count__rank_group_2) OVER (change) AS unique_keywords__count__rank_group_2__change,
unique_keywords__count__rank_group_3 AS unique_keywords__count__rank_group_3__value,
unique_keywords__count__rank_group_3 - FIRST_VALUE(unique_keywords__count__rank_group_3) OVER (change) AS unique_keywords__count__rank_group_3__change,
unique_keywords__count__rank_group_4_6 AS unique_keywords__count__rank_group_4_6__value,
unique_keywords__count__rank_group_4_6 - FIRST_VALUE(unique_keywords__count__rank_group_4_6) OVER (change) AS unique_keywords__count__rank_group_4_6__change,
unique_keywords__count__rank_group_7_10 AS unique_keywords__count__rank_group_7_10__value,
unique_keywords__count__rank_group_7_10 - FIRST_VALUE(unique_keywords__count__rank_group_7_10) OVER (change) AS unique_keywords__count__rank_group_7_10__change,
unique_keywords__count__rank_group_11_15 AS unique_keywords__count__rank_group_11_15__value,
unique_keywords__count__rank_group_11_15 - FIRST_VALUE(unique_keywords__count__rank_group_11_15) OVER (change) AS unique_keywords__count__rank_group_11_15__change,
unique_keywords__count__rank_group_16_20 AS unique_keywords__count__rank_group_16_20__value,
unique_keywords__count__rank_group_16_20 - FIRST_VALUE(unique_keywords__count__rank_group_16_20) OVER (change) AS unique_keywords__count__rank_group_16_20__change,
unique_keywords__count__rank_group_21_50 AS unique_keywords__count__rank_group_21_50__value,
unique_keywords__count__rank_group_21_50 - FIRST_VALUE(unique_keywords__count__rank_group_21_50) OVER (change) AS unique_keywords__count__rank_group_21_50__change,
unique_keywords__count__rank_group_51 AS unique_keywords__count__rank_group_51__value,
unique_keywords__count__rank_group_51 - FIRST_VALUE(unique_keywords__count__rank_group_51) OVER (change) AS unique_keywords__count__rank_group_51__change,
unique_urls__count__total AS unique_urls__count__total__value,
unique_urls__count__total - FIRST_VALUE(unique_urls__count__total) OVER (change) AS unique_urls__count__total__change,
unique_urls__count__rank_group_1 AS unique_urls__count__rank_group_1__value,
unique_urls__count__rank_group_1 - FIRST_VALUE(unique_urls__count__rank_group_1) OVER (change) AS unique_urls__count__rank_group_1__change,
unique_urls__count__rank_group_2 AS unique_urls__count__rank_group_2__value,
unique_urls__count__rank_group_2 - FIRST_VALUE(unique_urls__count__rank_group_2) OVER (change) AS unique_urls__count__rank_group_2__change,
unique_urls__count__rank_group_3 AS unique_urls__count__rank_group_3__value,
unique_urls__count__rank_group_3 - FIRST_VALUE(unique_urls__count__rank_group_3) OVER (change) AS unique_urls__count__rank_group_3__change,
unique_urls__count__rank_group_4_6 AS unique_urls__count__rank_group_4_6__value,
unique_urls__count__rank_group_4_6 - FIRST_VALUE(unique_urls__count__rank_group_4_6) OVER (change) AS unique_urls__count__rank_group_4_6__change,
unique_urls__count__rank_group_7_10 AS unique_urls__count__rank_group_7_10__value,
unique_urls__count__rank_group_7_10 - FIRST_VALUE(unique_urls__count__rank_group_7_10) OVER (change) AS unique_urls__count__rank_group_7_10__change,
unique_urls__count__rank_group_11_15 AS unique_urls__count__rank_group_11_15__value,
unique_urls__count__rank_group_11_15 - FIRST_VALUE(unique_urls__count__rank_group_11_15) OVER (change) AS unique_urls__count__rank_group_11_15__change,
unique_urls__count__rank_group_16_20 AS unique_urls__count__rank_group_16_20__value,
unique_urls__count__rank_group_16_20 - FIRST_VALUE(unique_urls__count__rank_group_16_20) OVER (change) AS unique_urls__count__rank_group_16_20__change,
unique_urls__count__rank_group_21_50 AS unique_urls__count__rank_group_21_50__value,
unique_urls__count__rank_group_21_50 - FIRST_VALUE(unique_urls__count__rank_group_21_50) OVER (change) AS unique_urls__count__rank_group_21_50__change,
unique_urls__count__rank_group_51 AS unique_urls__count__rank_group_51__value,
unique_urls__count__rank_group_51 - FIRST_VALUE(unique_urls__count__rank_group_51) OVER (change) AS unique_urls__count__rank_group_51__change,
FROM per_serp_metrics_aggregation
JOIN filtered_keywords USING(keyword_id)
WINDOW change AS (PARTITION BY keyword_id ORDER BY requested)
)
SELECT * FROM group_by_totals
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment