Campaign performance daily

1-- core.facebook_ads.campaign_performance_daily
2-- Daily campaign performance with the rates Ads Manager shows: CTR, CPC, CPM,
3-- cost per purchase and ROAS.
4--
5-- Grain: date_day x account_id x campaign_id. Currency: the ad account's.
6-- Depends on: core.facebook_ads.ad_performance_daily
7--
8-- Summed from ads: every Meta delivery is attributed to an ad, so ad totals add
9-- up to campaign totals. Reach does not add up (one person can see several ads)
10-- and is left out. conversions are purchases, as defined in ad_performance_daily.
11-- Rates are ratios of sums; to report a week or a month, re-sum the base columns
12-- and divide again.
13
14SELECT
15    date_day,
16    account_id,
17    account_name,
18    currency,
19    campaign_id,
20    campaign_name,
21
22    SUM(spend)            AS spend,
23    SUM(impressions)      AS impressions,
24    SUM(clicks)           AS clicks,
25    SUM(conversions)      AS conversions,
26    SUM(conversion_value) AS conversion_value,
27
28    SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS ctr,
29    SAFE_DIVIDE(SUM(spend), SUM(clicks))             AS cpc,
30    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS cpm,
31    SAFE_DIVIDE(SUM(spend), SUM(conversions))        AS cost_per_conversion,
32    SAFE_DIVIDE(SUM(conversion_value), SUM(spend))   AS roas,
33
34    'facebook_ads'                  AS source,
35    MAX(campaign_objective)         AS campaign_objective,
36    SUM(clicks_all)                 AS clicks_all,
37    COUNT(DISTINCT ad_id)           AS active_ads
38FROM {{core.facebook_ads.ad_performance_daily}}
39GROUP BY 1, 2, 3, 4, 5, 6