Campaign performance daily

1-- core.apple_ads.campaign_performance_daily
2-- Daily campaign performance, the Apple Ads Campaigns view by day, with the rates
3-- Apple shows: TTR, average CPT, average CPM, cost per install and install rate.
4--
5-- Grain: date_day x account_id x campaign_id. Currency: the organization's.
6-- Depends on: staging.apple_ads.campaign_report, .campaign
7--
8-- The first twelve columns are the cross-channel ad contract (date_day,
9-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
10-- impressions, clicks, conversions, conversion_value) shared with the other paid
11-- ads templates in this repo, so they can be stacked with UNION ALL.
12--
13-- The connector syncs performance at campaign level only, so this is the lowest
14-- level available: ad_id and ad_name are NULL. account_id is the Apple Ads
15-- organization; its name is not synced, so account_name is NULL.
16-- clicks = taps. conversions = tap-through installs (new downloads plus
17-- redownloads, 30-day attribution window). Apple Ads reports no revenue, so
18-- conversion_value is NULL.
19--
20-- Rates are ratios of sums at this grain. To report a week or a month, re-sum the
21-- base columns and divide again.
22
23SELECT
24    r.date_day,
25    c.account_id,
26    CAST(NULL AS STRING)                              AS account_name,
27    r.campaign_id,
28    COALESCE(c.campaign_name, r.campaign_name)        AS campaign_name,
29    CAST(NULL AS STRING)                              AS ad_id,
30    CAST(NULL AS STRING)                              AS ad_name,
31    r.spend,
32    r.impressions,
33    r.taps                                            AS clicks,
34    r.installs                                        AS conversions,
35    CAST(NULL AS NUMERIC)                             AS conversion_value,
36
37    'apple_ads'                                       AS source,
38    r.currency,
39    c.app_id,
40    c.ad_channel_type,