Marketing Insights Dashboard

1-- Facebook staging report can be created using the template: facebook ad reports
2-- Google staging report can be created using the template: google ad reports
3-- LinkedIn staging report can be created using the template: linkedin ad reports
4-- TikTok staging report can be created using the template: tiktok ad reports
5-- Snapchat staging report can be created using the template: snapchat ad reports
6-- Pinterest staging report can be created using the template: pinterest ad reports
7-- Note: If certain platforms are not used, you can remove their respective sections from the UNION ALL queries below.
8with
9    all_data as (
10        select
11            date_day
12          , 'facebook' as platform
13          , account_id
14          , account_name
15          , campaign_id
16          , campaign_name
17          , ad_id
18          , ad_name
19          , clicks
20          , impressions
21          , spend
22          , purchase as conversions
23          , conversion_value
24        from
25            {{staging.facebook_ads.ad_report}}
26            /*  union all
27            
28            select
29            date_day
30          , 'google' as platform
31          , account_id
32          , account_name
33          , campaign_id
34          , campaign_name
35          , ad_id
36          , ad_name
37          , clicks
38          , impressions
39          , spend
40          , conversions
41          , conversions_value as conversion_value
42            from
43            {{staging.google_ads.ad_report}}
44            
45            union all
46            
47            select
48            date_day
49          , 'linkedin' as platform
50          , account_id
51          , account_name
52          , campaign_group_id as campaign_id
53          , campaign_group_name as campaign_name
54          , creative_id as ad_id
55          , creative_name as ad_name
56          , clicks
57          , impressions
58          , spend
59          , conversions
60          , conversion_value
61            from
62            {{staging.linkedin_ads.creative_report}}
63            
64            union all
65            
66            select
67            date_day
68          , 'tiktok' as platform
69          , account_id
70          , account_name
71          , campaign_id
72          , campaign_name
73          , ad_id
74          , ad_name
75          , clicks
76          , impressions
77          , spend
78          , conversion as conversions
79          , conversion_value
80            from
81            {{staging.tiktok_ads.ad_report}}
82            
83            union all
84            
85            select
86            date_day
87          , 'snapchat' as platform
88          , account_id
89          , account_name
90          , creative_id as campaign_id
91          , campaign_id as campaign_name
92          , ad_id
93          , ad_name
94          , clicks
95          , impressions
96          , spend
97          , conversions
98          , conversion_value
99            from
100            {{staging.snapchat.ad_report}}
101            
102            union all
103            
104            select
105            date_day
106          , 'pinterest' as platform
107          , account_id
108          , account_name
109          , campaign_id
110          , campaign_name
111          , ad_id
112          , ad_name
113          , clicks
114          , impressions
115          , spend
116          , conversions
117          , null as conversion_value
118            from
119            {{staging.pinterest.ad_report}}
120            
121            */
122    )
123select
124    date_day
125  , platform
126  , account_id
127  , account_name
128  , campaign_id
129  , campaign_name
130  , ad_id
131  , ad_name
132  , sum(clicks) as clicks
133  , sum(impressions) as impressions
134  , sum(spend) as spend
135  , sum(conversions) as conversions
136  , sum(conversion_value) as conversion_value
137  , safe_divide(sum(clicks), sum(impressions)) * 100 as ctr
138  , safe_divide(sum(spend), sum(clicks)) as cpc
139  , safe_divide(sum(spend), sum(impressions)) * 1000 as cpm
140  , safe_divide(sum(conversions), sum(clicks)) * 100 as conversion_rate
141from
142    all_data
143group by
144    date_day
145  , platform
146  , account_id
147  , account_name
148  , campaign_id
149  , campaign_name
150  , ad_id
151  , ad_name;