Daily Ad Report

1with
2    ad_date as (
3        select
4            *
5        from
6            {{raw.snapchat_ads.ad_daily_report}}
7    )
8  , account as (
9        select
10            *
11        from
12            {{raw.snapchat_ads.ad_account}}
13    )
14  , ads as (
15        select
16            *
17        from
18            {{raw.snapchat_ads.ad}}
19    )
20  , ad_squads as (
21        select
22            *
23        from
24            {{raw.snapchat_ads.ad_squad}}
25    )
26  , campaigns as (
27        select
28            *
29        from
30            {{raw.snapchat_ads.campaign}}
31    )
32  , aggregated as (
33        select
34            cast(ad_date.date as date) as date_day
35          , account.id
36          , account.name
37          , account.currency
38          , sum(ad_date.swipes) as swipes
39          , sum(ad_date.impressions) as impressions
40          , round(sum(ad_date.spend), 2) as spend
41        from
42            ad_date
43            left join ads on ad_date.ad_id = ads.id
44            left join ad_squads on ads.ad_squad_id = ad_squads.id
45            left join campaigns on ad_squads.campaign_id = campaigns.id
46            left join account on campaigns.ad_account_id = account.id
47        group by
48            1
49          , 2
50          , 3
51          , 4
52    )
53select
54    *
55from
56    aggregated
57order by
58    date_day desc