App performance daily

1-- core.apple_ads.app_performance_daily
2-- Daily spend and results per promoted app, across all its campaigns: what an
3-- app's Apple Ads budget bought each day.
4--
5-- Grain: date_day x account_id x app_id. Currency: the organization's.
6-- Depends on: core.apple_ads.campaign_performance_daily
7--
8-- app_id is the App Store id (adamId) the campaign promotes. Campaigns missing
9-- from the campaigns stream roll up under a NULL app. Rates are ratios of sums.
10
11SELECT
12    date_day,
13    account_id,
14    currency,
15    app_id,
16
17    COUNT(DISTINCT campaign_id) AS active_campaigns,
18    SUM(spend)                  AS spend,
19    SUM(impressions)            AS impressions,
20    SUM(clicks)                 AS taps,
21    SUM(conversions)            AS installs,
22    SUM(new_downloads)          AS new_downloads,
23    SUM(redownloads)            AS redownloads,
24
25    SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS ttr,
26    SAFE_DIVIDE(SUM(spend), SUM(clicks))             AS avg_cpt,
27    SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS avg_cpm,
28    SAFE_DIVIDE(SUM(spend), SUM(conversions))        AS cost_per_install,
29    SAFE_DIVIDE(SUM(conversions), SUM(clicks))       AS install_rate
30FROM {{core.apple_ads.campaign_performance_daily}}
31GROUP BY 1, 2, 3, 4