Snapchat ads
Aggregated Daily Campaign Report
Extracts a day-by-day breakdown of advertising performance on Snapchat, grouped by campaigns and accounts. It integrates data from the daily campaign report with the campaign and account tables to deliver metrics like swipes, impressions, and spend for each campaign.
1with
2 campaign_daily as (
3 select
4 *
5 from
6 {{raw.snapchat_ads.campaign_daily_report}}
7 )
8 , account as (
9 select
10 *
11 from
12 {{raw.snapchat_ads.ad_account}}
13 )
14 , campaigns as (
15 select
16 *
17 from
18 {{raw.snapchat_ads.campaign}}
19 )
20 , aggregated as (
21 select
22 cast(campaign_daily.date as date) as date_day
23 , account.id
24 , account.name as account_name
25 , campaign_daily.campaign_id
26 , campaigns.name as campaign_name
27 , account.currency
28 , sum(campaign_daily.swipes) as swipes
29 , sum(campaign_daily.impressions) as impressions
30 , round(sum(campaign_daily.spend), 2) as spend
31 from
32 campaign_daily
33 left join campaigns on campaign_daily.campaign_id = campaigns.id
34 left join account on campaigns.ad_account_id = account.id
35 group by
36 1
37 , 2
38 , 3
39 , 4
40 , 5
41 , 6
42 )
43select
44 *
45from
46 aggregatedExample of output from model:
+------------+--------+--------------+--------------+----------------+----------+--------+-------------+-------+
| date_day | id | account_name | campaign_id | campaign_name | currency | swipes | impressions | spend |
+------------+--------+--------------+--------------+----------------+----------+--------+-------------+-------+
| 2023-04-20 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1000 | 5000 | 150.0 |
| 2023-04-19 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1100 | 5100 | 160.0 |
| 2023-04-18 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1150 | 5200 | 165.0 |
| 2023-04-17 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1200 | 5300 | 170.0 |
| 2023-04-16 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1250 | 5400 | 175.0 |
| 2023-04-15 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1300 | 5500 | 180.0 |
| 2023-04-14 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1350 | 5600 | 185.0 |
| 2023-04-13 | acc_01 | Account_One | camp_001 | Spring Sale | USD | 1400 | 5700 | 190.0 |
+------------+--------+--------------+--------------+----------------+----------+--------+-------------+-------+This SQL model delivers a granular, daily report of advertising activity on Snapchat at the campaign level. By amalgamating insights from the daily campaign report with information on specific campaigns and accounts, marketers receive an all-encompassing view of metrics such as swipes, impressions, and total expenditure. This deep dive facilitates the optimization of advertising strategies by shedding light on the performance of individual campaigns within specified accounts.
