Campaign performance daily

1-- core.criteo.campaign_performance_daily
2-- Daily campaign performance with the rates the Criteo UI shows: CTR, CPC, CPM,
3-- cost per order (CPO), conversion rate and ROAS.
4--
5-- Grain: date_day x account_id x campaign_id. Currency: the connector setting.
6-- Depends on: core.criteo.ad_performance_daily
7--
8-- Built from the ad report, including the "-1" placeholder ad, so spend ties to
9-- the campaign totals in Criteo. Rates are ratios of sums at this grain. To
10-- report a week or a month, re-sum the base columns and divide again; averaging
11-- the daily rates weights a quiet day the same as a busy one.
12
13SELECT
14    date_day,
15    account_id,
16    account_name,
17    currency,
18    campaign_id,
19    campaign_name,
20    campaign_goal,
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    COUNT(DISTINCT IF(ad_id != '-1', ad_id, NULL)) AS active_ads,
28
29    SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS ctr,
30    SAFE_DIVIDE(SUM(spend), SUM(clicks))             AS cpc,
31    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS cpm,
32    SAFE_DIVIDE(SUM(spend), SUM(conversions))        AS cost_per_order,
33    SAFE_DIVIDE(SUM(conversions), SUM(clicks))       AS conversion_rate,
34    SAFE_DIVIDE(SUM(conversion_value), SUM(spend))   AS roas
35FROM {{core.criteo.ad_performance_daily}}
36GROUP BY 1, 2, 3, 4, 5, 6, 7