Weld logo
snapchat-ads logo
Snapchat ads

Campaign Report

Generates a campaign report for Snapchat Ads. It retrieves data from three tables - campaign_daily_report, ad_account, and campaign. The report includes information such as date, account ID and name, campaign ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions. The data is grouped by various columns for analysis.
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
Example of output from model:
+------------+------------+----------------+-------------+----------------+----------+-------+-------------+--------+-------+---------+-------------+
| 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.

Ready to start modeling your own snapchat-ads data?

Get started building your data warehouse with snapchat-ads and connect all your apps and databases.

snapchat-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.