Campaign performance daily

1-- core.pinterest_ads.campaign_performance_daily
2-- Daily campaign performance with the rates Ads Manager shows: CTR, CPC, CPM,
3-- cost per conversion and checkout ROAS.
4--
5-- Grain: date_day x account_id x campaign_id. Currency: the ad account's.
6-- Depends on: core.pinterest_ads.ad_performance_daily
7--
8-- Summed from ads, so it uses the same definitions: conversions are all conversion
9-- events, conversion_value is checkout value, Pinterest's default attribution
10-- (30 day click, 30 day engagement, 1 day view). Rates are ratios of sums at this
11-- grain; never average the daily rates.
12
13SELECT
14    date_day,
15    account_id,
16    account_name,
17    currency,
18    campaign_id,
19    campaign_name,
20    campaign_objective,
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    'pinterest_ads'                 AS source,
35    SUM(earned_impressions)         AS earned_impressions,
36    SUM(paid_outbound_clicks)       AS paid_outbound_clicks,
37    SUM(signups)                    AS signups,
38    SUM(checkout_value_click)       AS checkout_value_click,
39    SUM(checkout_value_view)        AS checkout_value_view,
40    COUNT(DISTINCT IF(impressions > 0, ad_id, NULL)) AS ads_with_impressions