Ad stats by ad_set monthly
1select
2 date_trunc(date, month) month
3 , adset_name
4 , sum(spend) spend
5 , sum(impressions) impressions
6 , sum(clicks) clicks
7 , sum(safe_divide(clicks, impressions)) as ctr
8 , sum(safe_divide(clicks, spend)) as cpc
9 , sum(safe_divide(spend, impressions)) * 1000 as cpm
10 , sum(reach) reach
11 , avg(frequency) frequency
12from
13 {{raw.facebook_ads.ad_set_insight}}
14group by
15 1
16 , 2+------------+---------------------+---------+-------------+--------+--------+--------+--------+-------+----------+
| month | adset_name | spend | impressions | clicks | ctr | cpc | cpm | reach | frequency|
+------------+---------------------+---------+-------------+--------+--------+--------+--------+-------+----------+
| 2021-01-01 | Ad Set #1 | 100.00 | 1,000,000 | 10,000| 1.00% | 0.01 | 100.00| 500,000| 2.00 |
| 2021-01-01 | Ad Set #2 | 200.00 | 2,000,000 | 20,000| 1.00% | 0.01 | 100.00| 800,000| 2.50 |
| 2021-01-01 | Ad Set #3 | 300.00 | 3,000,000 | 30,000| 1.00% | 0.01 | 100.00| 1,200,000| 3.00 |
+------------+---------------------+---------+-------------+--------+--------+--------+--------+-------+----------+By integrating with Facebook Ads, this SQL model allows you to select and aggregate data on a monthly basis, providing insights into ad spend, impressions, clicks, click-through rate (CTR), cost per click (CPC), cost per thousand impressions (CPM), reach, and frequency. This information can be used to optimize ad campaigns, identify top-performing ad sets, and make data-driven decisions to improve overall ad performance. The SQL code is simple to use, requiring only the selection of the desired ad set insight data, and the resulting output is easy to read and interpret. Whether you're a marketer looking to improve your Facebook ad strategy or a business owner seeking to maximize your ad spend, the Ad stats by ad_set monthly SQL template is an essential tool for achieving your goals.
