Campaign performance daily

1-- core.reddit_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.reddit_ads.ad_performance_daily
7--
8-- Rates are ratios of sums at this grain. To report a week or a month, re-sum the
9-- base columns and divide again; averaging the daily rates weights a quiet day the
10-- same as a busy one.
11
12SELECT
13    date_day,
14    account_id,
15    account_name,
16    currency,
17    campaign_id,
18    campaign_name,
19    campaign_objective,
20
21    SUM(spend)            AS spend,
22    SUM(impressions)      AS impressions,
23    SUM(clicks)           AS clicks,
24    SUM(conversions)      AS conversions,
25    SUM(conversion_value) AS conversion_value,
26    COUNT(DISTINCT ad_id) AS active_ads,
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
33FROM {{core.reddit_ads.ad_performance_daily}}
34GROUP BY 1, 2, 3, 4, 5, 6, 7