Weld logo
snapchat-ads logo
Snapchat ads

Ad Group Report

Generates an ad group report for Snapchat Ads. It retrieves data from various tables such as ad_squad_daily_report, ad_account, ad_squad, and campaign. The resulting report includes metrics such as date, account ID and name, campaign ID and name, ad group ID and name, currency, spend, impressions, clicks, CPC, CTR, CPM, and conversions.
1with
2    report as (
3        select
4            *
5        from
6            {{raw.snapchat_ads.ad_squad_daily_report}}
7    )
8  , account as (
9        select
10            *
11        from
12            {{raw.snapchat_ads.ad_account}}
13    )
14  , ad_squads as (
15        select
16            *
17        from
18            {{raw.snapchat_ads.ad_squad}}
19    )
20  , campaigns as (
21        select
22            *
23        from
24            {{raw.snapchat_ads.campaign}}
25    )
26select
27    cast(report.date as date) as date_day
28  , account.ad_account_id as account_id
29  , account.name account_name
30  , campaigns.id campaign_id
31  , campaigns.name campaign_name
32  , report.ad_squad_id ad_group_id
33  , ad_squads.name ad_group_name
34  , account.currency
35  , round(sum(spend / 1000000), 2) spend
36  , sum(report.impressions) as impressions
37  , sum(report.swipes) as clicks
38  , sum(safe_divide((spend / 1000000), swipes)) as cpc
39  , sum(safe_divide(swipes, impressions)) as ctr
40  , sum(safe_divide((spend / 1000000), impressions)) * 1000 as cpm
41  , sum(conversion_purchases) as conversions
42from
43    report
44    left join ad_squads on report.ad_squad_id = ad_squads.id
45    left join campaigns on ad_squads.campaign_id = campaigns.id
46    left join account on campaigns.ad_account_id = account.ad_account_id
47group by
48    1
49  , 2
50  , 3
51  , 4
52  , 5
53  , 6
54  , 7
55  , 8
Example of output from model:
+------------+------------+-----------------+-------------+-----------------+-------------+-----------------+----------+-------+-------------+-------+-------+-------+-------------+
| date_day   | account_id | account_name    | campaign_id | campaign_name   | ad_group_id | ad_group_name   | currency | spend | impressions | clicks| cpc   | ctr   | cpm         |
+------------+------------+-----------------+-------------+-----------------+-------------+-----------------+----------+-------+-------------+-------+-------+-------+-------------+
| 2022-01-01 | 1234567890 | Example Account | 987654321   | Example Campaign| 56789       | Example Ad Group| USD      | 100.5 | 10000       | 500   | 0.201 | 0.05  | 10.05       |
+------------+------------+-----------------+-------------+-----------------+-------------+-----------------+----------+-------+-------------+-------+-------+-------+-------------+

Allows you to generate a comprehensive report on ad performance, providing valuable insights into your advertising campaigns. By combining data from various tables such as ad_squad_daily_report, ad_account, ad_squad, and campaign, this SQL template retrieves key metrics for each ad group. The resulting output includes information such as the date, account ID, account name, campaign ID, campaign name, ad group ID, ad group name, currency, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and conversions. By analyzing this data, you can gain insights into the effectiveness of your ad campaigns on Snapchat. You can track metrics such as impressions, clicks, and conversions, allowing you to optimize your advertising strategy and make data-driven decisions. The SQL template provides a structured and organized way to extract and analyze the necessary data for generating ad group reports.

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.