boydnorwood · May 3, 2022 19:17
diff --git a/Nozzle's Local SEO Dashboard SQL b/Nozzle's Local SEO Dashboard SQL
 --For Local Pack Dashboard: For generating Pages 1, 2, and 4 on the template
 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.localseodemocompany_localseodemoco.keywords
  GROUP BY keyword_source_id
 ),

 -- get the latest keywords from the latest keyword sources, merging keyword groups and keyword source ids
 latest_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(device_code) AS device_code,
    ANY_VALUE(engine) AS engine,
    ANY_VALUE(engine_code) AS engine_code,
    ANY_VALUE(language) AS language,
    ANY_VALUE(language_code) AS language_code,
    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(country_code) AS country_code,
    ANY_VALUE(ad_words_criteria_id) AS ad_words_criteria_id,
    ARRAY_CONCAT_AGG(keywords.groups) AS keyword_groups,
    ARRAY_AGG(STRUCT(
      workspace_id,
      workspace_slug,
      team_id,
      team_slug,
      keyword_source_id,
      keyword_source_version_id,
      keyword_source_name
    )) AS keyword_sources,
  FROM nozzledata.localseodemocompany_localseodemoco.keywords
  JOIN latest_keyword_source_versions USING (keyword_source_id, keyword_source_version_id)
  GROUP BY keyword_id
 ),

 -- dedupe the merged keyword group list and sort it
 latest_keywords_deduped_groups AS (
  SELECT
    keyword_id,
    phrase_id,
    phrase,
    locale_id,
    device,
    device_code,
    engine,
    engine_code,
    language,
    language_code,
    location_id,
    location_type,
    location,
    country,
    country_code,
    ad_words_criteria_id,
    (SELECT ARRAY_AGG(keyword_group IGNORE NULLS ORDER BY keyword_group) FROM (SELECT DISTINCT keyword_group FROM UNNEST(keyword_groups) AS keyword_group)) AS keyword_groups,
    keyword_sources,
  FROM latest_keywords
 ),

 -- only grab the most recently updated copy for each ranking id and do partition filtering on requested
 latest_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.localseodemocompany_localseodemoco.rankings t
  JOIN latest_keywords_deduped_groups USING (keyword_id)
  WHERE requested >= '2021-09-01 00:00:00' AND requested <= '2022-10-19 23:59:59'
  GROUP BY ranking_id
 ),

 -- this doesn't fill null on days where there was no keyword data
 distinct_keywords_by_requested AS (
  SELECT DISTINCT requested, keyword_id FROM latest_rankings
 ),

 distinct_keywords AS (
  SELECT DISTINCT keyword_id FROM latest_rankings
 ),

 distinct_keyword_count AS (
  SELECT COUNT(*) AS keyword_count FROM distinct_keywords
 ),

 distinct_requested AS (
  SELECT DISTINCT requested FROM latest_rankings
 ),

 all_keywords_by_requested AS (
  SELECT
    keyword_id,
    requested,
    keyword_count,
  FROM distinct_keywords
  CROSS JOIN distinct_requested
  CROSS JOIN distinct_keyword_count
 ),

 -- 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,
    keyword_count,
  from all_keywords_by_requested a
  left join distinct_keywords_by_requested d using (keyword_id, requested)
 ),

 -- used for pagination
 latest_requested AS (
  SELECT
    MAX(requested) AS requested
  FROM latest_rankings
 ),

 -- used for pagination
 earliest_requested AS (
  SELECT
    MIN(requested) AS requested
  FROM latest_rankings
 ),

 earliest_and_latest_requested as (
  SELECT * FROM earliest_requested
  UNION ALL
  SELECT * FROM latest_requested
 ),

 -- apply any result level filters here, set group by, and get all metrics necessary for later calculations
 latest_filtered_rankings_results AS (
  SELECT
    requested,
    keyword_id,
    phrase,
    location,
    device_code,

    result.title.text AS group_by,
    result.title.text,
    result.url.url,
    
    result.local.*,

    result.rank AS result__rank,
    result.measurements.pixels_from_top AS result__measurements__pixels_from_top,
    result.nozzle_metrics.click_through_rate AS result__nozzle_metrics__click_through_rate,
    result.measurements.percentage_of_viewport AS result__measurements__percentage_of_viewport,
    result.measurements.is_visible AS result__measurements__is_visible,
    result.measurements.percentage_of_dom AS result__measurements__percentage_of_dom,
    result.nozzle_metrics.estimated_traffic AS result__nozzle_metrics__estimated_traffic,
    result.nozzle_metrics.ppc_value AS result__nozzle_metrics__ppc_value,
    result.url.url_id AS result__url__url_id,

    result.review.review_count AS result__review__review_count,
    result.rating.normalized AS result__rating__normalized,
    
    regexp_extract_all(result.description.text, (select concat('(', string_agg(category, '|'), ')') from `nozzle-app.reference.google_my_business_categories` where instr(category, '(') = 0)) AS business_categories,


  FROM latest_rankings
  JOIN latest_keywords_deduped_groups USING (keyword_id)
  JOIN UNNEST(results) AS result
  WHERE (result.paid IS NULL OR result.paid.is_paid=FALSE) AND result.local.is_local=TRUE AND ((1=1) AND (result.url.domain IS NOT NULL) AND (LOWER(result.url.domain)!=LOWER('')))
  AND result.title.text NOT IN ('10+ more stories', 'Images', 'More places', 'Open now', 'Top rated', 'View all', ':')
 ),

 -- apply any result level filters here, set group by, and get all metrics necessary for later calculations
 latest_rankings_results AS (
  SELECT
    a.keyword_id,
    a.requested,
    a.data_from_requested,

    group_by,

    location,
    device_code,

    keyword_count,
    
    phrase,
    
    has_phone_button,
    has_directions_button,
    has_save_button,
    has_website_button,


    business_categories,

    result__rank,
    result__measurements__pixels_from_top,
    result__nozzle_metrics__click_through_rate,
    result__measurements__percentage_of_viewport,
    result__measurements__is_visible,
    result__measurements__percentage_of_dom,
    result__nozzle_metrics__estimated_traffic,
    result__nozzle_metrics__ppc_value,
    result__url__url_id,

    result__review__review_count,
    result__rating__normalized,


  FROM latest_filtered_rankings_results r
  RIGHT JOIN all_rankings_fill_null a ON r.keyword_id=a.keyword_id AND r.requested=a.data_from_requested
  WHERE group_by IS NOT NULL
 ),

 -- calculate min metrics per keyword_id, group_by, and requested
 per_serp_metrics_main AS (
  SELECT
    keyword_id,
    requested,
    group_by,
    ANY_VALUE(keyword_count) AS keyword_count,
    location,
    device_code,
    
    ARRAY_AGG(DISTINCT phrase) AS phrases,
    ARRAY_CONCAT_AGG(business_categories) AS business_categories,


    IFNULL(MIN(result__rank), 101) AS top_rank,

    IFNULL(MIN(result__measurements__pixels_from_top), 30000) AS top_pixels_from_top,

    IFNULL(SUM(result__nozzle_metrics__click_through_rate), 0) AS click_through_rate,

    IFNULL(AVG(result__review__review_count), 0) AS review_count,
    IFNULL(AVG(result__rating__normalized), 0) AS rating,

    IFNULL(SUM(IF(result__measurements__is_visible=TRUE, result__measurements__percentage_of_viewport, 0)), 0) AS percentage_of_viewport,

    IFNULL(SUM(result__measurements__percentage_of_dom), 0) AS percentage_of_dom,

    SUM(result__nozzle_metrics__estimated_traffic) AS estimated_traffic__sum__total,
    SUM(IF(result__rank = 1, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_1,
    SUM(IF(result__rank = 2, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_2,
    SUM(IF(result__rank = 3, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_3,
    SUM(IF(result__rank > 3 AND result__rank <= 6, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_4_6,
    SUM(IF(result__rank > 6 AND result__rank <= 10, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_7_10,
    SUM(IF(result__rank > 11 AND result__rank <= 15, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_11_15,
    SUM(IF(result__rank > 16 AND result__rank <= 20, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_16_20,
    SUM(IF(result__rank > 21 AND result__rank <= 50, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_21_50,
    SUM(IF(result__rank > 51, result__nozzle_metrics__estimated_traffic, 0)) AS estimated_traffic__sum__rank_group_51,

    SUM(result__nozzle_metrics__ppc_value) AS ppc_value__sum__total,
    SUM(IF(result__rank = 1, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_1,
    SUM(IF(result__rank = 2, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_2,
    SUM(IF(result__rank = 3, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_3,
    SUM(IF(result__rank > 3 AND result__rank <= 6, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_4_6,
    SUM(IF(result__rank > 6 AND result__rank <= 10, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_7_10,
    SUM(IF(result__rank > 11 AND result__rank <= 15, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_11_15,
    SUM(IF(result__rank > 16 AND result__rank <= 20, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_16_20,
    SUM(IF(result__rank > 21 AND result__rank <= 50, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_21_50,
    SUM(IF(result__rank > 51, result__nozzle_metrics__ppc_value, 0)) AS ppc_value__sum__rank_group_51,

    HLL_COUNT.INIT(keyword_id, 24) AS unique_keywords__count__total,
    HLL_COUNT.INIT(IF(result__rank = 1, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_1,
    HLL_COUNT.INIT(IF(result__rank = 2, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_2,
    HLL_COUNT.INIT(IF(result__rank = 3, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_3,
    HLL_COUNT.INIT(IF(result__rank > 3 AND result__rank <= 6, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_4_6,
    HLL_COUNT.INIT(IF(result__rank > 6 AND result__rank <= 10, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_7_10,
    HLL_COUNT.INIT(IF(result__rank > 11 AND result__rank <= 15, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_11_15,
    HLL_COUNT.INIT(IF(result__rank > 16 AND result__rank <= 20, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_16_20,
    HLL_COUNT.INIT(IF(result__rank > 21 AND result__rank <= 50, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_21_50,
    HLL_COUNT.INIT(IF(result__rank > 51, keyword_id, NULL), 16) AS unique_keywords__count__rank_group_51,

    HLL_COUNT.INIT(result__url__url_id, 24) AS unique_urls__count__total,
    HLL_COUNT.INIT(IF(result__rank = 1, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_1,
    HLL_COUNT.INIT(IF(result__rank = 2, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_2,
    HLL_COUNT.INIT(IF(result__rank = 3, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_3,
    HLL_COUNT.INIT(IF(result__rank > 3 AND result__rank <= 6, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_4_6,
    HLL_COUNT.INIT(IF(result__rank > 6 AND result__rank <= 10, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_7_10,
    HLL_COUNT.INIT(IF(result__rank > 11 AND result__rank <= 15, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_11_15,
    HLL_COUNT.INIT(IF(result__rank > 16 AND result__rank <= 20, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_16_20,
    HLL_COUNT.INIT(IF(result__rank > 21 AND result__rank <= 50, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_21_50,
    HLL_COUNT.INIT(IF(result__rank > 51, result__url__url_id, NULL), 16) AS unique_urls__count__rank_group_51,

    COUNTIF(result__rank IS NOT NULL) AS unique_results__count__total,
    COUNTIF(result__rank = 1) AS unique_results__count__rank_group_1,
    COUNTIF(result__rank = 2) AS unique_results__count__rank_group_2,
    COUNTIF(result__rank = 3) AS unique_results__count__rank_group_3,
    COUNTIF(result__rank > 3 AND result__rank <= 6) AS unique_results__count__rank_group_4_6,
    COUNTIF(result__rank > 6 AND result__rank <= 10) AS unique_results__count__rank_group_7_10,
    COUNTIF(result__rank > 11 AND result__rank <= 15) AS unique_results__count__rank_group_11_15,
    COUNTIF(result__rank > 16 AND result__rank <= 20) AS unique_results__count__rank_group_16_20,
    COUNTIF(result__rank > 21 AND result__rank <= 50) AS unique_results__count__rank_group_21_50,
    COUNTIF(result__rank > 51) AS unique_results__count__rank_group_51,

    COUNTIF(has_phone_button) AS has_phone_button__count__total,
    COUNTIF(has_directions_button) AS has_directions_button__count__total,
    COUNTIF(has_save_button) AS has_save_button__count__total,
    COUNTIF(has_website_button) AS has_website_button__count__total,

  FROM latest_rankings_results
  GROUP BY keyword_id, requested, group_by, location, device_code
 ),

 -- get the average of all the minimum values
 per_serp_metrics_aggregation_main AS (
  SELECT
    requested,
    group_by,
    location, 
    device_code,
    ANY_VALUE(keyword_count) AS keyword_count,
    
    ARRAY_CONCAT_AGG(phrases) AS phrases,
    ARRAY_CONCAT_AGG(business_categories) AS business_categories,


    APPROX_QUANTILES(top_rank, 4) AS top_rank__quantiles,
    (SUM(top_rank) + (ANY_VALUE(keyword_count) - COUNT(DISTINCT keyword_id)) * 101) / ANY_VALUE(keyword_count) AS top_rank__avg__total,

    APPROX_QUANTILES(top_pixels_from_top, 4) AS top_pixels_from_top__quantiles,
    (SUM(top_pixels_from_top) + (ANY_VALUE(keyword_count) - COUNT(DISTINCT keyword_id)) * 30000) / ANY_VALUE(keyword_count) AS top_pixels_from_top__avg__total,

    APPROX_QUANTILES(click_through_rate, 4) AS click_through_rate__quantiles,
    SUM(click_through_rate)/ANY_VALUE(keyword_count) AS click_through_rate__avg__total,

    APPROX_QUANTILES(review_count, 4) AS review_count__quantiles,
    AVG(review_count) AS review_count__avg__total,

    APPROX_QUANTILES(rating, 4) AS rating__quantiles,
    AVG(rating) AS rating__avg__total,

    APPROX_QUANTILES(percentage_of_viewport, 4) AS percentage_of_viewport__quantiles,
    SUM(percentage_of_viewport)/ANY_VALUE(keyword_count) AS percentage_of_viewport__avg__total,

    APPROX_QUANTILES(percentage_of_dom, 4) AS percentage_of_dom__quantiles,
    SUM(percentage_of_dom)/ANY_VALUE(keyword_count) AS percentage_of_dom__avg__total,

    SUM(estimated_traffic__sum__total) AS estimated_traffic__sum__total,
    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) 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,

    HLL_COUNT.MERGE(unique_keywords__count__total) AS unique_keywords__count__total,
    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,

    HLL_COUNT.MERGE(unique_urls__count__total) AS unique_urls__count__total,
    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,

    SUM(unique_results__count__total) AS unique_results__count__total,
    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(has_phone_button__count__total) AS has_phone_button__count__total,
    SUM(has_directions_button__count__total) AS has_directions_button__count__total,
    SUM(has_save_button__count__total) AS has_save_button__count__total,
    SUM(has_website_button__count__total) AS has_website_button__count__total,


  FROM per_serp_metrics_main
  GROUP BY requested, group_by, location, device_code
 ),

 -- calculate min metrics per keyword_id, group_by, and requested
 group_by_totals_main AS (
  SELECT
    requested,
    group_by,
    location, 
    device_code,
    keyword_count,
    
    (SELECT ARRAY_AGG(phrase IGNORE NULLS ORDER BY phrase) FROM (SELECT DISTINCT phrase FROM UNNEST(phrases) AS phrase)) AS phrases,
    (SELECT STRING_AGG(phrase, '; ' ORDER BY phrase) FROM (SELECT DISTINCT phrase FROM UNNEST(phrases) AS phrase)) AS phrases_concat,
    (SELECT ARRAY_AGG(business_category IGNORE NULLS ORDER BY business_category) FROM (SELECT DISTINCT business_category FROM UNNEST(business_categories) AS business_category)) AS business_categories,


    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_rank__quantiles[OFFSET(0)] AS top_rank__min__total__value,
    top_rank__quantiles[OFFSET(0)] - FIRST_VALUE(top_rank__quantiles[OFFSET(0)]) OVER (change) AS top_rank__min__total__change,


    top_rank__quantiles[OFFSET(1)] AS top_rank__p25__total__value,
    top_rank__quantiles[OFFSET(1)] - FIRST_VALUE(top_rank__quantiles[OFFSET(1)]) OVER (change) AS top_rank__p25__total__change,


    top_rank__quantiles[OFFSET(2)] AS top_rank__p50__total__value,
    top_rank__quantiles[OFFSET(2)] - FIRST_VALUE(top_rank__quantiles[OFFSET(2)]) OVER (change) AS top_rank__p50__total__change,


    top_rank__quantiles[OFFSET(3)] AS top_rank__p75__total__value,
    top_rank__quantiles[OFFSET(3)] - FIRST_VALUE(top_rank__quantiles[OFFSET(3)]) OVER (change) AS top_rank__p75__total__change,


    top_rank__quantiles[OFFSET(4)] AS top_rank__max__total__value,
    top_rank__quantiles[OFFSET(4)] - FIRST_VALUE(top_rank__quantiles[OFFSET(4)]) OVER (change) AS top_rank__max__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,


    top_pixels_from_top__quantiles[OFFSET(0)] AS top_pixels_from_top__min__total__value,
    top_pixels_from_top__quantiles[OFFSET(0)] - FIRST_VALUE(top_pixels_from_top__quantiles[OFFSET(0)]) OVER (change) AS top_pixels_from_top__min__total__change,


    top_pixels_from_top__quantiles[OFFSET(1)] AS top_pixels_from_top__p25__total__value,
    top_pixels_from_top__quantiles[OFFSET(1)] - FIRST_VALUE(top_pixels_from_top__quantiles[OFFSET(1)]) OVER (change) AS top_pixels_from_top__p25__total__change,


    top_pixels_from_top__quantiles[OFFSET(2)] AS top_pixels_from_top__p50__total__value,
    top_pixels_from_top__quantiles[OFFSET(2)] - FIRST_VALUE(top_pixels_from_top__quantiles[OFFSET(2)]) OVER (change) AS top_pixels_from_top__p50__total__change,


    top_pixels_from_top__quantiles[OFFSET(3)] AS top_pixels_from_top__p75__total__value,
    top_pixels_from_top__quantiles[OFFSET(3)] - FIRST_VALUE(top_pixels_from_top__quantiles[OFFSET(3)]) OVER (change) AS top_pixels_from_top__p75__total__change,


    top_pixels_from_top__quantiles[OFFSET(4)] AS top_pixels_from_top__max__total__value,
    top_pixels_from_top__quantiles[OFFSET(4)] - FIRST_VALUE(top_pixels_from_top__quantiles[OFFSET(4)]) OVER (change) AS top_pixels_from_top__max__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,


    click_through_rate__quantiles[OFFSET(0)] AS click_through_rate__min__total__value,
    click_through_rate__quantiles[OFFSET(0)] - FIRST_VALUE(click_through_rate__quantiles[OFFSET(0)]) OVER (change) AS click_through_rate__min__total__change,


    click_through_rate__quantiles[OFFSET(1)] AS click_through_rate__p25__total__value,
    click_through_rate__quantiles[OFFSET(1)] - FIRST_VALUE(click_through_rate__quantiles[OFFSET(1)]) OVER (change) AS click_through_rate__p25__total__change,


    click_through_rate__quantiles[OFFSET(2)] AS click_through_rate__p50__total__value,
    click_through_rate__quantiles[OFFSET(2)] - FIRST_VALUE(click_through_rate__quantiles[OFFSET(2)]) OVER (change) AS click_through_rate__p50__total__change,


    click_through_rate__quantiles[OFFSET(3)] AS click_through_rate__p75__total__value,
    click_through_rate__quantiles[OFFSET(3)] - FIRST_VALUE(click_through_rate__quantiles[OFFSET(3)]) OVER (change) AS click_through_rate__p75__total__change,


    click_through_rate__quantiles[OFFSET(4)] AS click_through_rate__max__total__value,
    click_through_rate__quantiles[OFFSET(4)] - FIRST_VALUE(click_through_rate__quantiles[OFFSET(4)]) OVER (change) AS click_through_rate__max__total__change,





    review_count__avg__total AS review_count__avg__total__value,
    review_count__avg__total - FIRST_VALUE(review_count__avg__total) OVER (change) AS review_count__avg__total__change,


    review_count__quantiles[OFFSET(0)] AS review_count__min__total__value,
    review_count__quantiles[OFFSET(0)] - FIRST_VALUE(review_count__quantiles[OFFSET(0)]) OVER (change) AS review_count__min__total__change,


    review_count__quantiles[OFFSET(1)] AS review_count__p25__total__value,
    review_count__quantiles[OFFSET(1)] - FIRST_VALUE(review_count__quantiles[OFFSET(1)]) OVER (change) AS review_count__p25__total__change,


    review_count__quantiles[OFFSET(2)] AS review_count__p50__total__value,
    review_count__quantiles[OFFSET(2)] - FIRST_VALUE(review_count__quantiles[OFFSET(2)]) OVER (change) AS review_count__p50__total__change,


    review_count__quantiles[OFFSET(3)] AS review_count__p75__total__value,
    review_count__quantiles[OFFSET(3)] - FIRST_VALUE(review_count__quantiles[OFFSET(3)]) OVER (change) AS review_count__p75__total__change,


    review_count__quantiles[OFFSET(4)] AS review_count__max__total__value,
    review_count__quantiles[OFFSET(4)] - FIRST_VALUE(review_count__quantiles[OFFSET(4)]) OVER (change) AS review_count__max__total__change,



    rating__avg__total AS rating__avg__total__value,
    rating__avg__total - FIRST_VALUE(rating__avg__total) OVER (change) AS rating__avg__total__change,


    rating__quantiles[OFFSET(0)] AS rating__min__total__value,
    rating__quantiles[OFFSET(0)] - FIRST_VALUE(rating__quantiles[OFFSET(0)]) OVER (change) AS rating__min__total__change,


    rating__quantiles[OFFSET(1)] AS rating__p25__total__value,
    rating__quantiles[OFFSET(1)] - FIRST_VALUE(rating__quantiles[OFFSET(1)]) OVER (change) AS rating__p25__total__change,


    rating__quantiles[OFFSET(2)] AS rating__p50__total__value,
    rating__quantiles[OFFSET(2)] - FIRST_VALUE(rating__quantiles[OFFSET(2)]) OVER (change) AS rating__p50__total__change,


    rating__quantiles[OFFSET(3)] AS rating__p75__total__value,
    rating__quantiles[OFFSET(3)] - FIRST_VALUE(rating__quantiles[OFFSET(3)]) OVER (change) AS rating__p75__total__change,


    rating__quantiles[OFFSET(4)] AS rating__max__total__value,
    rating__quantiles[OFFSET(4)] - FIRST_VALUE(rating__quantiles[OFFSET(4)]) OVER (change) AS rating__max__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_viewport__quantiles[OFFSET(0)] AS percentage_of_viewport__min__total__value,
    percentage_of_viewport__quantiles[OFFSET(0)] - FIRST_VALUE(percentage_of_viewport__quantiles[OFFSET(0)]) OVER (change) AS percentage_of_viewport__min__total__change,


    percentage_of_viewport__quantiles[OFFSET(1)] AS percentage_of_viewport__p25__total__value,
    percentage_of_viewport__quantiles[OFFSET(1)] - FIRST_VALUE(percentage_of_viewport__quantiles[OFFSET(1)]) OVER (change) AS percentage_of_viewport__p25__total__change,


    percentage_of_viewport__quantiles[OFFSET(2)] AS percentage_of_viewport__p50__total__value,
    percentage_of_viewport__quantiles[OFFSET(2)] - FIRST_VALUE(percentage_of_viewport__quantiles[OFFSET(2)]) OVER (change) AS percentage_of_viewport__p50__total__change,


    percentage_of_viewport__quantiles[OFFSET(3)] AS percentage_of_viewport__p75__total__value,
    percentage_of_viewport__quantiles[OFFSET(3)] - FIRST_VALUE(percentage_of_viewport__quantiles[OFFSET(3)]) OVER (change) AS percentage_of_viewport__p75__total__change,


    percentage_of_viewport__quantiles[OFFSET(4)] AS percentage_of_viewport__max__total__value,
    percentage_of_viewport__quantiles[OFFSET(4)] - FIRST_VALUE(percentage_of_viewport__quantiles[OFFSET(4)]) OVER (change) AS percentage_of_viewport__max__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,


    percentage_of_dom__quantiles[OFFSET(0)] AS percentage_of_dom__min__total__value,
    percentage_of_dom__quantiles[OFFSET(0)] - FIRST_VALUE(percentage_of_dom__quantiles[OFFSET(0)]) OVER (change) AS percentage_of_dom__min__total__change,


    percentage_of_dom__quantiles[OFFSET(1)] AS percentage_of_dom__p25__total__value,
    percentage_of_dom__quantiles[OFFSET(1)] - FIRST_VALUE(percentage_of_dom__quantiles[OFFSET(1)]) OVER (change) AS percentage_of_dom__p25__total__change,


    percentage_of_dom__quantiles[OFFSET(2)] AS percentage_of_dom__p50__total__value,
    percentage_of_dom__quantiles[OFFSET(2)] - FIRST_VALUE(percentage_of_dom__quantiles[OFFSET(2)]) OVER (change) AS percentage_of_dom__p50__total__change,


    percentage_of_dom__quantiles[OFFSET(3)] AS percentage_of_dom__p75__total__value,
    percentage_of_dom__quantiles[OFFSET(3)] - FIRST_VALUE(percentage_of_dom__quantiles[OFFSET(3)]) OVER (change) AS percentage_of_dom__p75__total__change,


    percentage_of_dom__quantiles[OFFSET(4)] AS percentage_of_dom__max__total__value,
    percentage_of_dom__quantiles[OFFSET(4)] - FIRST_VALUE(percentage_of_dom__quantiles[OFFSET(4)]) OVER (change) AS percentage_of_dom__max__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_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,


    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,



    has_phone_button__count__total AS has_phone_button__count__total__value,
    has_phone_button__count__total - FIRST_VALUE(has_phone_button__count__total) OVER (change) AS has_phone_button__count__total__change,

    has_directions_button__count__total AS has_directions_button__count__total__value,
    has_directions_button__count__total - FIRST_VALUE(has_directions_button__count__total) OVER (change) AS has_directions_button__count__total__change,

    has_save_button__count__total AS has_save_button__count__total__value,
    has_save_button__count__total - FIRST_VALUE(has_save_button__count__total) OVER (change) AS has_save_button__count__total__change,

    has_website_button__count__total AS has_website_button__count__total__value,
    has_website_button__count__total - FIRST_VALUE(has_website_button__count__total) OVER (change) AS has_website_button__count__total__change,



  FROM per_serp_metrics_aggregation_main
  WINDOW change AS (PARTITION BY group_by, location, device_code ORDER BY requested)
 )

 SELECT * from group_by_totals_main