Campaign group performance daily

1-- core.linkedin_ads.campaign_group_performance_daily
2-- Daily performance per campaign group. Campaign groups hold the shared budget and
3-- schedule on LinkedIn, so this is the level many teams plan and pace spend at.
4--
5-- Grain: date_day x account_id x campaign_group_id. Currency: the ad account's.
6-- Depends on: core.linkedin_ads.campaign_performance_daily
7--
8-- Same conversion definition as the campaign model: external website conversions,
9-- with Lead Gen Form leads in `leads`. Rates are ratios of sums at this grain.
10
11SELECT
12    date_day,
13    account_id,
14    account_name,
15    currency,
16    campaign_group_id,
17    campaign_group_name,
18
19    SUM(spend)            AS spend,
20    SUM(impressions)      AS impressions,
21    SUM(clicks)           AS clicks,
22    SUM(conversions)      AS conversions,
23    SUM(conversion_value) AS conversion_value,
24    SUM(leads)            AS leads,
25    COUNT(DISTINCT IF(impressions > 0, campaign_id, NULL)) AS campaigns_with_impressions,
26
27    SAFE_DIVIDE(SUM(clicks), SUM(impressions))             AS ctr,
28    SAFE_DIVIDE(SUM(spend), SUM(clicks))                   AS cpc,
29    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000       AS cpm,
30    SAFE_DIVIDE(SUM(spend), SUM(conversions))              AS cost_per_conversion,
31    SAFE_DIVIDE(SUM(conversion_value), SUM(spend))         AS roas,
32    SAFE_DIVIDE(SUM(spend), SUM(leads))                    AS cost_per_lead
33FROM {{core.linkedin_ads.campaign_performance_daily}}
34GROUP BY 1, 2, 3, 4, 5, 6