Account performance daily

1-- core.microsoft_ads.account_performance_daily
2-- Daily totals per account, the Microsoft Advertising "Accounts" view by day,
3-- with the account's time zone.
4--
5-- Grain: date_day x account_id. Currency: the account's.
6-- Depends on: core.microsoft_ads.campaign_performance_daily, staging.microsoft_ads.account
7--
8-- Summed from the campaign model, which covers every campaign type. Dates are
9-- the account's own time zone (time_zone), as Microsoft reports them; accounts
10-- in different time zones do not share a day boundary.
11
12SELECT
13    p.date_day,
14    p.account_id,
15    p.account_name,
16    acc.time_zone,
17    p.currency,
18    SUM(p.spend)                    AS spend,
19    SUM(p.impressions)              AS impressions,
20    SUM(p.clicks)                   AS clicks,
21    SUM(p.conversions)              AS conversions,
22    SUM(p.conversion_value)         AS conversion_value,
23    SUM(p.all_conversions)          AS all_conversions,
24    SUM(p.all_revenue)              AS all_revenue,
25    SUM(p.view_through_conversions) AS view_through_conversions,
26    COUNT(DISTINCT p.campaign_id)   AS active_campaigns,
27    SAFE_DIVIDE(SUM(p.clicks), SUM(p.impressions))           AS ctr,
28    SAFE_DIVIDE(SUM(p.spend), SUM(p.clicks))                 AS cpc,
29    SAFE_DIVIDE(SUM(p.spend), SUM(p.impressions)) * 1000     AS cpm,
30    SAFE_DIVIDE(SUM(p.spend), SUM(p.conversions))            AS cost_per_conversion,
31    SAFE_DIVIDE(SUM(p.conversion_value), SUM(p.spend))       AS roas
32FROM {{core.microsoft_ads.campaign_performance_daily}} p
33-- One row per account after staging, so no fan-out.
34LEFT JOIN {{staging.microsoft_ads.account}} acc ON acc.account_id = p.account_id
35GROUP BY 1, 2, 3, 4, 5