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        select
12            date_day
13          , 'google' as platform
14          , account_id
15          , account_name
16          , campaign_id
17          , campaign_name
18          , ad_id
19          , ad_name
20          , clicks
21          , impressions
22          , spend
23          , conversions
24          , conversions_value as conversion_value
25        from
26            {{staging.google_ads.ad_report}}
27            /*  union all
28            
29            date_day
30          , 'facebook' 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          , purchase as conversions
41          , conversion_value
42            from
43            {{staging.facebook_ads.ad_report}}
44            
45            
46            union all
47            
48            select
49            date_day
50          , 'linkedin' as platform
51          , account_id
52          , account_name
53          , campaign_group_id as campaign_id
54          , campaign_group_name as campaign_name
55          , creative_id as ad_id
56          , creative_name as ad_name
57          , clicks
58          , impressions
59          , spend
60          , conversions
61          , conversion_value
62            from
63            {{staging.linkedin_ads.creative_report}}
64            
65            union all
66            
67            select
68            date_day
69          , 'tiktok' as platform
70          , account_id
71          , account_name
72          , campaign_id
73          , campaign_name
74          , ad_id
75          , ad_name
76          , clicks
77          , impressions
78          , spend
79          , conversion as conversions
80          , conversion_value
81            from
82            {{staging.tiktok_ads.ad_report}}
83            
84            union all
85            
86            select
87            date_day
88          , 'snapchat' as platform
89          , account_id
90          , account_name
91          , creative_id as campaign_id
92          , campaign_id as campaign_name
93          , ad_id
94          , ad_name
95          , clicks
96          , impressions
97          , spend
98          , conversions
99          , conversion_value
100            from
101            {{staging.snapchat.ad_report}}
102            
103            union all
104            
105            select
106            date_day
107          , 'pinterest' as platform
108          , account_id
109          , account_name
110          , campaign_id
111          , campaign_name
112          , ad_id
113          , ad_name
114          , clicks
115          , impressions
116          , spend
117          , conversions
118          , null as conversion_value
119            from
120            {{staging.pinterest.ad_report}}
121            
122            */
123    )
124select
125    date_day
126  , platform
127  , account_id
128  , account_name
129  , campaign_id
130  , campaign_name
131  , ad_id
132  , ad_name
133  , sum(clicks) as clicks
134  , sum(impressions) as impressions
135  , sum(spend) as spend
136  , sum(conversions) as conversions
137  , sum(conversion_value) as conversion_value
138  , safe_divide(sum(clicks), sum(impressions)) * 100 as ctr
139  , safe_divide(sum(spend), sum(clicks)) as cpc
140  , safe_divide(sum(spend), sum(impressions)) * 1000 as cpm
141  , safe_divide(sum(conversions), sum(clicks)) * 100 as conversion_rate
142from
143    all_data
144group by
145    date_day
146  , platform
147  , account_id
148  , account_name
149  , campaign_id
150  , campaign_name
151  , ad_id
152  , ad_name;