Campaign Report
1with
2 report 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 )
20select
21 cast(report.date as date) as date_day
22 , account.id account_id
23 , account.name account_name
24 , report.campaign_id
25 , campaigns.name campaign_name
26 , account.currency
27 , round(sum(spend / 1000000), 2) spend
28 , sum(report.impressions) as impressions
29 , sum(report.swipes) as clicks
30 , sum(safe_divide((spend / 1000000), swipes)) as cpc
31 , sum(safe_divide(swipes, impressions)) as ctr
32 , sum(safe_divide((spend / 1000000), impressions)) * 1000 as cpm
33 , sum(conversion_purchases) as conversions
34from
35 report
36 left join campaigns on report.campaign_id = campaigns.id
37 left join account on campaigns.ad_account_id = account.id
38group by
39 1
40 , 2
41 , 3
42 , 4
43 , 5
44 , 6+------------+------------+----------------+-------------+----------------+----------+-------+-------------+--------+-------+---------+-------------+
| date_day | account_id | account_name | campaign_id | campaign_name | currency | spend | impressions | clicks | cpc | ctr | cpm |
+------------+------------+----------------+-------------+----------------+----------+-------+-------------+--------+-------+---------+-------------+
| 2022-01-01 | 1234567890 | Example Account| 987654321 | Example Campaign| USD | 10.50 | 10000 | 500 | 0.02 | 0.05 | 1.05 |
| 2022-01-02 | 1234567890 | Example Account| 987654321 | Example Campaign| USD | 15.75 | 15000 | 750 | 0.03 | 0.05 | 1.05 |
| 2022-01-03 | 1234567890 | Example Account| 987654321 | Example Campaign| USD | 20.00 | 20000 | 1000 | 0.02 | 0.05 | 1.00 |
+------------+------------+----------------+-------------+----------------+----------+-------+-------------+--------+-------+---------+-------------+Generate a report based on data from the Snapchat Ads integration. This SQL model combines information from three tables: "campaign_daily_report," "ad_account," and "campaign." The resulting report provides insights into various metrics related to advertising campaigns on Snapchat. It includes data such as the date of the campaign, the account ID and name, the campaign ID and name, the currency used, the total spend (in millions), the number of impressions, the number of clicks (swipes), the cost per click (CPC), the click-through rate (CTR), the cost per thousand impressions (CPM), and the number of conversions. By executing this SQL template, you can gain valuable insights into the performance of Snapchat ad campaigns. It allows you to analyze key metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. These insights can help you evaluate the effectiveness of your campaigns, optimize your advertising strategies, and make data-driven decisions to improve your Snapchat ad performance.
