Snapchat ads
Aggregated Daily Ad Report
Extracts a daily breakdown of advertising performance on Snapchat, specifically focusing on individual ads. This integrates data from the daily ad report with the ad, creative, and account tables to deliver metrics like swipes, impressions, and spend for each ad.
1with
2 ads_daily as (
3 select
4 *
5 from
6 {{raw.snapchat_ads.ad_daily_report}}
7 )
8 , account as (
9 select
10 *
11 from
12 {{raw.snapchat_ads.ad_account}}
13 )
14 , ads as (
15 select
16 *
17 from
18 {{raw.snapchat_ads.ad}}
19 )
20 , creatives as (
21 select
22 *
23 from
24 {{raw.snapchat_ads.creative}}
25 )
26 , aggregated as (
27 select
28 cast(ads_daily.date as date) as date_day
29 , account.id
30 , account.name as account_name
31 , ads_daily.ad_id
32 , ads.name as ad_name
33 , account.currency
34 , sum(ads_daily.swipes) as swipes
35 , sum(ads_daily.impressions) as impressions
36 , round(sum(ads_daily.spend), 2) as spend
37 from
38 ads_daily
39 left join ads on ads_daily.ad_id = ads.id
40 left join creatives on ads.creative_id = creatives.id
41 left join account on creatives.ad_account_id = account.id
42 group by
43 1
44 , 2
45 , 3
46 , 4
47 , 5
48 , 6
49 )
50select
51 *
52from
53 aggregatedExample of output from model:
+------------+--------+--------------+--------+------------+----------+--------+-------------+-------+
| date_day | id | account_name | ad_id | ad_name | currency | swipes | impressions | spend |
+------------+--------+--------------+--------+------------+----------+--------+-------------+-------+
| 2023-04-20 | acc_01 | Account_One | ad_01 | Ad_One | USD | 500 | 3000 | 75.0 |
| 2023-04-19 | acc_01 | Account_One | ad_01 | Ad_One | USD | 550 | 3100 | 77.5 |
| 2023-04-18 | acc_01 | Account_One | ad_01 | Ad_One | USD | 575 | 3150 | 78.8 |
| 2023-04-17 | acc_01 | Account_One | ad_01 | Ad_One | USD | 600 | 3200 | 80.0 |
| 2023-04-16 | acc_01 | Account_One | ad_01 | Ad_One | USD | 625 | 3250 | 81.3 |
| 2023-04-15 | acc_01 | Account_One | ad_01 | Ad_One | USD | 650 | 3300 | 82.5 |
| 2023-04-14 | acc_01 | Account_One | ad_01 | Ad_One | USD | 675 | 3350 | 83.8 |
| 2023-04-13 | acc_01 | Account_One | ad_01 | Ad_One | USD | 700 | 3400 | 85.0 |
+------------+--------+--------------+--------+------------+----------+--------+-------------+-------+This SQL model offers a granular, daily perspective of advertising activity on Snapchat at the individual ad level. By merging insights from the daily ad report with details about specific ads, creatives, and accounts, marketers can obtain an in-depth view of metrics such as swipes, impressions, and overall expenditure for each advertisement. Such comprehensive insights allow for a more detailed analysis and optimization of advertising strategies on the platform.
