Ad squad performance daily

1-- core.snapchat_ads.ad_squad_performance_daily
2-- Daily performance per ad squad, Snapchat's ad set level, where targeting,
3-- bidding, budget and the optimisation goal are set. Use it to compare squads
4-- with different goals or bid strategies inside one campaign.
5--
6-- Grain: date_day x account_id x ad_squad_id. Currency: the ad account's.
7-- Depends on: core.snapchat_ads.ad_performance_daily, staging.snapchat_ads.ad_squad
8--
9-- conversions are purchases, whatever the squad optimises for. A squad that
10-- optimises for swipes or installs is judged here on purchases; compare it on
11-- app_installs or clicks instead.
12--
13-- daily_budget is the current setting, not the budget on that day.
14
15WITH squad_daily AS (
16    SELECT
17        date_day,
18        account_id,
19        account_name,
20        currency,
21        campaign_id,
22        campaign_name,
23        ad_squad_id,
24        SUM(spend)            AS spend,
25        SUM(impressions)      AS impressions,
26        SUM(clicks)           AS clicks,
27        SUM(conversions)      AS conversions,
28        SUM(conversion_value) AS conversion_value,
29        SUM(app_installs)     AS app_installs,
30        SUM(video_views)      AS video_views,
31        COUNT(DISTINCT ad_id) AS active_ads
32    FROM {{core.snapchat_ads.ad_performance_daily}}
33    GROUP BY 1, 2, 3, 4, 5, 6, 7
34)
35
36SELECT
37    d.date_day,
38    d.account_id,
39    d.account_name,
40    d.currency,