Landing page performance daily

1-- core.google_ads.landing_page_performance_daily
2-- Daily spend, clicks and conversions per landing page, so ad traffic can be set
3-- against sessions and orders in web analytics.
4--
5-- Grain: date_day x account_id x campaign_id x landing_page.
6-- Currency: the account's. Depends on: core.google_ads.ad_performance_daily
7--
8-- landing_page is the ad's first final URL with any query string or fragment
9-- removed, so URL variants of one page land together. The full final URL and the
10-- effective final URL suffix (where UTM tags usually live) are on
11-- ad_performance_daily. Ads without a final URL (some app and video ads) show
12-- as a NULL landing page. Ad level only: Performance Max is not in it.
13
14SELECT
15    date_day,
16    account_id,
17    account_name,
18    currency,
19    campaign_id,
20    campaign_name,
21    REGEXP_REPLACE(final_url, r'[?#].*$', '')      AS landing_page,
22    COUNT(DISTINCT ad_id)                          AS ads,
23    SUM(spend)                                     AS spend,
24    SUM(impressions)                               AS impressions,
25    SUM(clicks)                                    AS clicks,
26    SUM(conversions)                               AS conversions,
27    SUM(conversion_value)                          AS conversion_value,
28    SAFE_DIVIDE(SUM(clicks), SUM(impressions))     AS ctr,
29    SAFE_DIVIDE(SUM(spend), SUM(clicks))           AS cpc,
30    SAFE_DIVIDE(SUM(spend), SUM(conversions))      AS cost_per_conversion,
31    SAFE_DIVIDE(SUM(conversion_value), SUM(spend)) AS roas
32FROM {{core.google_ads.ad_performance_daily}}
33GROUP BY 1, 2, 3, 4, 5, 6, 7