Account Report
1with
2 ad_daily as (
3 select
4 *
5 from
6 {{raw.snapchat_ads.ad_daily_report}} -- to join another snapchat ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , account as (
11 select
12 *
13 from
14 {{raw.snapchat_ads.ad_account}}
15 )
16 , ads as (
17 select
18 *
19 from
20 {{raw.snapchat_ads.ad}}
21 )
22 , ad_squads as (
23 select
24 *
25 from
26 {{raw.snapchat_ads.ad_squad}}
27 )
28 , campaigns as (
29 select
30 *
31 from
32 {{raw.snapchat_ads.campaign}}
33 )
34select
35 cast(ad_daily.date as date) as date_day
36 , account.ad_account_id as account_id
37 , account.name account_name
38 , account.currency
39 , round(sum(spend / 1000000), 2) spend
40 , sum(ad_daily.impressions) as impressions
41 , sum(ad_daily.swipes) as clicks
42 , sum(safe_divide((spend / 1000000), swipes)) as cpc
43 , sum(safe_divide(swipes, impressions)) as ctr
44 , sum(safe_divide((spend / 1000000), impressions)) * 1000 as cpm
45 , sum(conversion_purchases) as conversions
46from
47 ad_daily
48 left join ads on ad_daily.ad_id = ads.id
49 left join ad_squads on ads.ad_squad_id = ad_squads.id
50 left join campaigns on ad_squads.campaign_id = campaigns.id
51 left join account on campaigns.ad_account_id = account.id
52group by
53 1
54 , 2
55 , 3
56 , 4 -- Adjust these numbers according to the selected columns+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+
| date_day | account_id | account_name | currency | spend | impressions | clicks | cpc | ctr | cpm | conversions |
+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+
| 2022-01-01 | 1234567890 | Example Account| USD | 10.25 | 10000 | 500 | 0.02 | 0.05 | 1.03 | 25 |
| 2022-01-02 | 1234567890 | Example Account| USD | 15.75 | 15000 | 750 | 0.03 | 0.05 | 1.05 | 30 |
| 2022-01-03 | 1234567890 | Example Account| USD | 12.50 | 12000 | 600 | 0.02 | 0.05 | 1.04 | 28 |
| 2022-01-04 | 1234567890 | Example Account| USD | 9.75 | 9000 | 450 | 0.02 | 0.05 | 1.08 | 22 |
| 2022-01-05 | 1234567890 | Example Account| USD | 11.00 | 11000 | 550 | 0.02 | 0.05 | 1.00 | 24 |
+------------+------------+----------------+----------+-------+-------------+--------+-------+-------+-------+--------------+Generate a report on Snapchat Ads data. It integrates with the Snapchat Ads platform and retrieves data from various tables such as ad_daily, ad_account, ad, ad_squad, and campaign. The SQL code joins these tables together to calculate various metrics related to ad performance. The generated report includes insights such as the date of the ad, the account ID and name, the currency used, the total spend (in millions), the number of impressions, clicks (swipes), the cost per click (CPC), the click-through rate (CTR), the cost per thousand impressions (CPM), and the number of conversions. This SQL template is useful for analyzing and monitoring the performance of Snapchat Ads campaigns. It provides valuable insights into key metrics such as spend, impressions, clicks, and conversions. By aggregating data at the account level, advertisers can gain a comprehensive understanding of their ad performance and make informed decisions to optimize their campaigns.
