Campaign performance daily

1-- core.applovin.campaign_performance_daily
2-- Daily campaign performance with the rates AppLovin shows: CTR, CPC, CPM, cost
3-- per install (CPI), install rate and day-0 and day-7 ROAS.
4--
5-- Grain: date_day x campaign_id. Days are UTC. Currency: USD (see the README).
6-- Depends on: staging.applovin.advertiser_campaign_report
7--
8-- Built from the campaign-level report, which carries all of a campaign's
9-- traffic. ROAS is cohort revenue over spend for the same day: day-7 ROAS is NULL
10-- until seven days have passed. Rates are ratios of sums at this grain. To report
11-- a week or a month, re-sum the base columns and divide again.
12
13SELECT
14    date_day,
15    campaign_id,
16    MAX(campaign_name)          AS campaign_name,
17    MAX(campaign_type)          AS campaign_type,
18    MAX(campaign_bid_goal)      AS campaign_bid_goal,
19    MAX(app_package_name)       AS app_package_name,
20    MAX(app_store_id)           AS app_store_id,
21
22    SUM(spend)                  AS spend,
23    SUM(impressions)            AS impressions,
24    SUM(clicks)                 AS clicks,
25    SUM(installs)               AS installs,
26    SUM(sales)                  AS sales,
27    SUM(total_revenue_d0)       AS total_revenue_d0,
28    SUM(total_revenue_d7)       AS total_revenue_d7,
29    SUM(iap_revenue_d7)         AS iap_revenue_d7,
30    SUM(ad_revenue_d7)          AS ad_revenue_d7,
31    COUNT(DISTINCT creative_set_name) AS active_creative_sets,
32
33    SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS ctr,
34    SAFE_DIVIDE(SUM(spend), SUM(clicks))             AS cpc,
35    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS cpm,
36    SAFE_DIVIDE(SUM(spend), SUM(installs))           AS cpi,
37    SAFE_DIVIDE(SUM(installs), SUM(clicks))          AS install_rate,
38    SAFE_DIVIDE(SUM(total_revenue_d0), SUM(spend))   AS roas_d0,
39    SAFE_DIVIDE(SUM(total_revenue_d7), SUM(spend))   AS roas_d7
40FROM {{staging.applovin.advertiser_campaign_report}}