Query page monthly

1-- core.google_search_console.query_page_monthly
2-- Which pages rank for which query, per month, and how a query's clicks split
3-- across your pages. The usual way to spot two pages competing for one query.
4--
5-- Grain: month x site x query x page. Month is the first day, Pacific Time.
6-- Depends on: staging.google_search_console.query_page_analytics
7--
8-- Shares are of the query's clicks and impressions across your pages in the
9-- month. A query with several pages each holding a real share of impressions is a
10-- cannibalisation candidate. Anonymized queries are absent.
11
12WITH by_page AS (
13    SELECT
14        DATE_TRUNC(date_day, MONTH)         AS month,
15        site,
16        query,
17        page,
18        SUM(clicks)                         AS clicks,
19        SUM(impressions)                    AS impressions,
20        SUM(position * impressions)         AS weighted_position_sum
21    FROM {{staging.google_search_console.query_page_analytics}}
22    GROUP BY 1, 2, 3, 4
23)
24
25SELECT
26    month,
27    site,
28    query,
29    page,
30    clicks,
31    impressions,
32    SAFE_DIVIDE(clicks, impressions)                         AS ctr,
33    SAFE_DIVIDE(weighted_position_sum, impressions)          AS average_position,
34    weighted_position_sum,
35    SAFE_DIVIDE(clicks, SUM(clicks) OVER (PARTITION BY month, site, query))           AS query_click_share,
36    SAFE_DIVIDE(impressions, SUM(impressions) OVER (PARTITION BY month, site, query)) AS query_impression_share,
37    COUNT(*) OVER (PARTITION BY month, site, query)                                   AS ranking_pages
38FROM by_page