Location performance daily

1-- core.google_my_business_profile.location_performance_daily
2-- The Business Profile Performance view per location per day: how often the
3-- profile was shown, on which surface, and what people did with it.
4--
5-- Grain: date_day x location_id.
6-- Depends on: staging.google_my_business_profile.location_performance, .location
7--
8-- Impressions are unique users per day per surface, as Google defines them, so
9-- the four surface columns can be added for a day but a "unique viewers this
10-- month" figure cannot be built by summing days.
11--
12-- interactions follows Google's "Profile interactions": calls, messages,
13-- bookings, directions, website clicks, food orders and menu clicks.
14
15WITH pivoted AS (
16    SELECT
17        date_day,
18        location_id,
19        SUM(IF(metric = 'BUSINESS_IMPRESSIONS_DESKTOP_MAPS',   value, 0)) AS impressions_desktop_maps,
20        SUM(IF(metric = 'BUSINESS_IMPRESSIONS_DESKTOP_SEARCH', value, 0)) AS impressions_desktop_search,
21        SUM(IF(metric = 'BUSINESS_IMPRESSIONS_MOBILE_MAPS',    value, 0)) AS impressions_mobile_maps,
22        SUM(IF(metric = 'BUSINESS_IMPRESSIONS_MOBILE_SEARCH',  value, 0)) AS impressions_mobile_search,
23        SUM(IF(metric = 'CALL_CLICKS',                         value, 0)) AS calls,
24        SUM(IF(metric = 'BUSINESS_DIRECTION_REQUESTS',         value, 0)) AS direction_requests,
25        SUM(IF(metric = 'WEBSITE_CLICKS',                      value, 0)) AS website_clicks,
26        SUM(IF(metric = 'BUSINESS_CONVERSATIONS',              value, 0)) AS conversations,
27        SUM(IF(metric = 'BUSINESS_BOOKINGS',                   value, 0)) AS bookings,
28        SUM(IF(metric = 'BUSINESS_FOOD_ORDERS',                value, 0)) AS food_orders,
29        SUM(IF(metric = 'BUSINESS_FOOD_MENU_CLICKS',           value, 0)) AS food_menu_clicks
30    FROM {{staging.google_my_business_profile.location_performance}}
31    GROUP BY 1, 2
32)
33
34SELECT
35    p.date_day,
36    p.location_id,
37    l.location_name,
38    l.store_code,
39    l.locality,
40    l.primary_category,