Ad performance daily

1-- core.linkedin_ads.ad_performance_daily
2-- Daily delivery and results per creative, the Campaign Manager "Ads" view by day.
3-- LinkedIn calls an ad a creative, so ad_id is the creative id.
4--
5-- Grain: date_day x account_id x ad_id. Currency: the ad account's.
6-- Depends on: staging.linkedin_ads.creative_analytics, .creative, .campaign,
7--             .campaign_group, .account
8--
9-- Hierarchy: account > campaign group > campaign > creative. LinkedIn has no level
10-- between campaign and ad, so the grouping columns carried here are the campaign
11-- group's.
12--
13-- Conversion definition: conversions = external website conversions, post-click
14-- plus post-view, under the attribution windows set on each conversion rule in
15-- Campaign Manager (LinkedIn's defaults are 30 days after a click and 7 days after
16-- a view). This matches the "Conversions" column in Campaign Manager. Lead Gen
17-- Form submissions are NOT included; they are in `leads` (one-click leads), with
18-- their own cost_per_lead. conversion_value is the value LinkedIn attributes to
19-- website conversions, NULL when no conversion rule carries a value.
20--
21-- Rates are ratios of the row's own sums. Re-sum the base columns and divide again
22-- for any other period or level.
23
24WITH perf AS (
25    SELECT
26        d.date_day,
27        cr.account_id,
28        acc.account_name,
29        acc.currency,
30        cr.campaign_id,
31        c.campaign_name,
32        c.campaign_group_id,
33        g.campaign_group_name,
34        d.creative_id                    AS ad_id,
35        cr.creative_title                AS ad_name,
36        c.objective_type                 AS campaign_objective,
37        c.campaign_type,
38        cr.intended_status               AS ad_status,
39        d.spend,
40        d.impressions,