Ad Report

1with
2    stats as (
3        select
4            *
5        from
6            {{raw.facebook_ads.ad_insight}}
7    )
8  , ad as (
9        select
10            *
11        from
12            {{raw.facebook_ads.ad}}
13    )
14  , creative as (
15        select
16            *
17        from
18            {{raw.facebook_ads.creative}}
19    )
20  , action_data as (
21        select
22            * except (
23                account_id
24              , account_name
25              , account_currency
26              , campaign_id
27              , adset_id
28            )
29        from
30            {{raw.facebook_ads.ad_roas_insight_conversion_insights}}
31        order by
32            date desc
33    )
34  , action_value as (
35        select
36            date
37          , ad_id
38          , case
39                when action_type = 'purchase' then value
40            end as conversion_value
41        from
42            {{raw.facebook_ads.ad_roas_insight_action_values}}
43        order by
44            date desc
45    )
46select
47    cast(stats.date as date) date_day
48  , stats.account_id account_id
49  , stats.account_name account_name
50  , stats.campaign_id campaign_id
51  , stats.campaign_name campaign_name
52  , stats.adset_id ad_set_id
53  , stats.adset_name ad_set_name
54  , stats.ad_id ad_id
55  , stats.ad_name ad_name
56  , ad.status
57  , creative.title as creative_title
58  , spend
59  , reach
60  , impressions
61  , clicks
62  , inline_link_clicks
63  , ctr
64  , cpm
65  , cpc
66  , action_data.* except (ad_id, date)
67  , action_value.conversion_value
68from
69    stats
70    left join action_data on action_data.ad_id = stats.ad_id
71    and cast(action_data.date as date) = cast(stats.date as date)
72    left join action_value on action_value.ad_id = stats.ad_id
73    and cast(action_value.date as date) = cast(stats.date as date)
74    left join ad on stats.ad_id = ad.id
75    left join creative on creative.id = ad.creative_id
76order by
77    1 desc