Ad set performance daily

1-- core.facebook_ads.ad_set_performance_daily
2-- Daily ad set performance with its optimisation goal and attribution setting,
3-- the Ads Manager "Ad sets" tab by day.
4--
5-- Grain: date_day x account_id x ad_set_id. Currency: the ad account's.
6-- Depends on: core.facebook_ads.ad_performance_daily
7--
8-- attribution_setting is what the ad set optimises for in Ads Manager. The
9-- conversion columns are reported under the windows the connector requests
10-- (7-day click and 1-day view by default), which can differ from it; compare
11-- the two before reading an ad set's ROAS against its own setting.
12
13SELECT
14    date_day,
15    account_id,
16    account_name,
17    currency,
18    campaign_id,
19    campaign_name,
20    ad_set_id,
21    ad_set_name,
22    ad_set_optimization_goal,
23    ad_set_attribution_setting,
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
31    SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS ctr,
32    SAFE_DIVIDE(SUM(spend), SUM(clicks))             AS cpc,
33    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS cpm,
34    SAFE_DIVIDE(SUM(spend), SUM(conversions))        AS cost_per_conversion,
35    SAFE_DIVIDE(SUM(conversion_value), SUM(spend))   AS roas
36FROM {{core.facebook_ads.ad_performance_daily}}
37GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10