Geo Ranking Report Query And Template

Geo Ranking Report Template:

https://datastudio.google.com/reporting/00b01a58-89a0-41fc-b742-85bb4a2ee3b4/page/tEnnC

CREATE OR REPLACE TABLE nozzle-timberlinemark.looker.summitmountain_locations
CLUSTER BY requested, domain, location, segment AS
WITH
  -- get the average of all the minimum values
  per_serp_metrics_aggregation AS (
  SELECT
    *,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__total) AS unique_keywords__count__total,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__total) AS unique_urls__count__total,
    results__count__total AS unique_results__count__total,
  FROM
    nozzledata.timberlinemark_summitmountain_raw.rollup__by__domain__fresh
  WHERE
    requested >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -700 DAY)
    AND segment_id >= 1 ),
  keyword_groups AS (
  SELECT
    SUBSTR(SUBSTR(kg, 13), 0, LENGTH(kg) - 40) AS keyword_group,
    FARM_FINGERPRINT(kg) AS keyword_group_hash
  FROM
    nozzledata.timberlinemark_summitmountain.latest_keywords_by_keyword_id
  JOIN
    UNNEST(keyword_groups) AS kg
  WHERE
    STARTS_WITH(kg, '- Location:')
    AND country_code = 'us'
    AND location_type IN ('City',
      'Postal Code')
  GROUP BY
    kg ),
  segments AS (
  SELECT
    segment_id,
    MAX_BY(name, segment_version_id) AS name,
  FROM
    nozzledata.timberlinemark_summitmountain.segments
  GROUP BY
    segment_id ),
  group_by_totals AS (
  SELECT
    requested,
    domain_id,
    keyword_group_hash,
    segment_id,
    keyword_count,
    -- adwords_search_volume__sum__total,
    -- summary_metrics__percentage_of_viewport__sum__total,
    -- summary_metrics__percentage_of_dom__sum__total,
    -- summary_metrics__click_through_rate__sum__total,
    -- summary_metrics__estimated_traffic__sum__total,
    -- summary_metrics__ppc_value__sum__total,
    -- summary_metrics__results__count__total,
    ROUND(top_rank__avg__total, 1) AS rank,
    ROUND(top_stat_base_rank__avg__total, 1) AS base_rank,
    CAST(top_pixels_from_top__avg__total AS INT64) AS pixels_from_top,
    ROUND(percentage_of_viewport__avg__total, 4) AS above_the_fold_percentage,
    ROUND(percentage_of_dom__avg__total, 4) AS serp_percentage,
    ROUND(click_through_rate__avg__total, 4) AS ctr,
    estimated_traffic__sum__total AS estimated_traffic,
    ppc_value__sum__total AS ppc_value,
    unique_keywords__count__total AS unique_keywords,
    unique_urls__count__total AS unique_urls,
    unique_results__count__total AS unique_results,
    ROUND(estimated_traffic__sum__total / summary_metrics__estimated_traffic__sum__total, 3) AS estimated_traffic_sov,
    ROUND(ppc_value__sum__total / summary_metrics__ppc_value__sum__total, 3) AS ppc_value_sov,
  FROM
    per_serp_metrics_aggregation
  WINDOW
    change AS (
    PARTITION BY
      domain_id
    ORDER BY
      requested) )
SELECT
  kg.keyword_group AS location,
  s.name AS segment,
  d.domain,
  t.* EXCEPT (domain_id,
    keyword_group_hash,
    segment_id)
FROM
  group_by_totals t
JOIN
  keyword_groups kg
USING
  (keyword_group_hash)
JOIN
  segments s
USING
  (segment_id)
JOIN
  nozzledata.timberlinemark_summitmountain.top_domains d
USING
  (domain_id)