Geo Ranking Report Template:
https://lookerstudio.google.com/u/0/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)