Ad performance daily

1-- core.applovin.ad_performance_daily
2-- Daily delivery, installs and day-7 cohort revenue per creative set, the
3-- AppLovin advertiser report by campaign and creative set.
4--
5-- Grain: date_day x campaign_id x creative set (ad_id). Days are UTC.
6-- Currency: USD (see the README).
7-- Depends on: staging.applovin.advertiser_report
8--
9-- The first twelve columns are the cross-channel ad contract (date_day,
10-- account_id, account_name, campaign_id, campaign_name, ad_id, ad_name, spend,
11-- impressions, clicks, conversions, conversion_value) shared with the other paid
12-- ads templates in this repo, so they can be stacked with UNION ALL.
13--
14-- AppLovin's report has no stable ad id, so the creative set is the lowest level:
15-- ad_id = creative_set_id, ad_name = the creative set name. The report has no
16-- account column either: account_id and account_name are NULL, one Weld
17-- connection being one AppLovin account.
18-- conversions = installs. conversion_value = total revenue (in-app purchases plus
19-- ad revenue) from users who installed that day, within 7 days. It is NULL for
20-- the last seven days, until the window has elapsed.
21
22WITH daily AS (
23    -- The report splits each creative set by country, platform, device, placement,
24    -- ad type and more; sum them back up to one row per creative set.
25    SELECT
26        date_day,
27        campaign_id,
28        creative_set_id,
29        MAX(campaign_name)        AS campaign_name,
30        MAX(campaign_type)        AS campaign_type,
31        MAX(app_package_name)     AS app_package_name,
32        MAX(creative_set_name)    AS creative_set_name,
33        SUM(spend)                AS spend,
34        SUM(impressions)          AS impressions,
35        SUM(clicks)               AS clicks,
36        SUM(installs)             AS installs,
37        SUM(sales)                AS sales,
38        SUM(total_revenue_d0)     AS total_revenue_d0,
39        SUM(total_revenue_d7)     AS total_revenue_d7
40    FROM {{staging.applovin.advertiser_report}}