Ad Report

1with
2    stats as (
3        select
4            *
5        from
6            {{raw.facebook_ads.ad_insight}}
7    )
8  , accounts as (
9        select
10            *
11        from
12            {{raw.facebook_ads.account}}
13    )
14  , campaigns as (
15        select
16            *
17        from
18            {{raw.facebook_ads.campaign}}
19    )
20  , ad_sets as (
21        select
22            *
23        from
24            {{raw.facebook_ads.ad_set}}
25    )
26  , ads as (
27        select
28            *
29        from
30            {{raw.facebook_ads.ad}}
31    )
32select
33    cast(stats.date as date) date_day
34  , accounts.id account_id
35  , accounts.name account_name
36  , campaigns.id campaign_id
37  , campaigns.name campaign_name
38  , ad_sets.id ad_set_id
39  , ad_sets.name ad_set_name
40  , ads.id ad_id
41  , ads.name ad_name
42  , sum(spend) spend
43  , sum(reach) reach
44  , sum(impressions) impressions
45  , sum(clicks) clicks
46  , sum(safe_divide(clicks, spend)) as cpc
47  , sum(safe_divide(clicks, impressions)) as ctr
48  , sum(safe_divide(spend, impressions)) * 1000 as cpm
49    -- Additional columns should be manually specified if needed
50from
51    stats
52    left join accounts on stats.account_id = accounts.id
53    left join ads on stats.ad_id = ads.id
54    left join ad_sets on ads.adset_id = ad_sets.id
55    left join campaigns on ad_sets.campaign_id = campaigns.id
56group by
57    1
58  , 2
59  , 3
60  , 4
61  , 5
62  , 6
63  , 7
64  , 8
65  , 9
66    -- Adjust these numbers according to the selected columns