Campaign performance daily

1-- core.snapchat_ads.campaign_performance_daily
2-- Daily campaign performance with the rates Ads Manager shows: CTR (swipe up
3-- rate), CPC (cost per swipe up), CPM, 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.snapchat_ads.ad_performance_daily
7--
8-- Built by summing ads. Every ad belongs to one ad squad and one campaign, so the
9-- sum matches the campaign row in Ads Manager, apart from ads whose campaign is
10-- unknown: they land under a NULL campaign_id rather than being dropped.
11--
12-- Rates are ratios of sums at this grain. To report a week or a month, re-sum the
13-- base columns and divide again; averaging the daily rates weights a quiet day the
14-- same as a busy one.
15
16SELECT
17    date_day,
18    account_id,
19    account_name,
20    currency,
21    campaign_id,
22    campaign_name,
23    campaign_objective,
24
25    SUM(spend)            AS spend,
26    SUM(impressions)      AS impressions,
27    SUM(clicks)           AS clicks,
28    SUM(conversions)      AS conversions,
29    SUM(conversion_value) AS conversion_value,
30    SUM(app_installs)     AS app_installs,
31    SUM(video_views)      AS video_views,
32    COUNT(DISTINCT ad_id) AS active_ads,
33
34    SAFE_DIVIDE(SUM(clicks), SUM(impressions))         AS ctr,
35    SAFE_DIVIDE(SUM(spend), SUM(clicks))               AS cpc,
36    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000   AS cpm,
37    SAFE_DIVIDE(SUM(spend), SUM(conversions))          AS cost_per_conversion,
38    SAFE_DIVIDE(SUM(conversion_value), SUM(spend))     AS roas
39FROM {{core.snapchat_ads.ad_performance_daily}}
40GROUP BY 1, 2, 3, 4, 5, 6, 7