Site performance daily

1-- core.google_search_console.site_performance_daily
2-- The Search Console Performance report headline: clicks, impressions, CTR and
3-- average position per site per day, plus how much of it the query tables cannot
4-- see.
5--
6-- Grain: date_day x site. Dates are Pacific Time, as Search Console reports them.
7-- Depends on: staging.google_search_console.site_analytics, .query_analytics
8--
9-- POSITION IS IMPRESSION-WEIGHTED. Search Console's average position is the mean
10-- over impressions, so rows are combined as SUM(position x impressions) /
11-- SUM(impressions). `weighted_position_sum` is kept so a BI tool can re-aggregate
12-- to a week or month the same way; averaging `average_position` is wrong.
13--
14-- ANONYMIZED QUERIES. Google withholds rare queries from every query-level
15-- table but keeps them in the property totals. `anonymized_clicks` and
16-- `anonymized_impressions` are the gap between the two, which is usually a
17-- large share of impressions on long-tail sites. Expect query reports to sum to
18-- less than this model.
19
20WITH site AS (
21    SELECT
22        date_day,
23        site,
24        SUM(clicks)                    AS clicks,
25        SUM(impressions)               AS impressions,
26        SUM(position * impressions)    AS weighted_position_sum
27    FROM {{staging.google_search_console.site_analytics}}
28    GROUP BY 1, 2
29),
30
31queries AS (
32    SELECT
33        date_day,
34        site,
35        SUM(clicks)      AS clicks,
36        SUM(impressions) AS impressions
37    FROM {{staging.google_search_console.query_analytics}}
38    GROUP BY 1, 2
39)
40