Facebook ads
Ad stats by campaign monthly
Retrieves monthly ad statistics by campaign from Facebook Ads, including spend, impressions, clicks, CTR, CPC, CPM, reach, and frequency. It groups the data by month and campaign name.
1select
2 date_trunc(date, month) month
3 , campaign_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_insight}}
14group by
15 1
16 , 2Example of output from model:
+------------+----------------------+-----------+-------------+--------+--------+---------+---------+-------+-----------+
| month | campaign_name | spend | impressions | clicks | ctr | cpc | cpm | reach | frequency |
+------------+----------------------+-----------+-------------+--------+--------+---------+---------+-------+-----------+
| 2021-01-01 | Advertise Now | 10,000.0 | 500,000 | 5,000 | 1.0% | 2.00 | 20.00 | 50,000| 2.5 |
| 2021-01-01 | Brand Boost | 12,000.0 | 600,000 | 6,000 | 1.0% | 2.00 | 20.00 | 60,000| 2.5 |
| 2021-01-01 | Market Mover | 15,000.0 | 750,000 | 7,500 | 1.0% | 2.00 | 20.00 | 75,000| 2.5 |
| 2021-01-01 | Campaign Connect | 13,000.0 | 650,000 | 6,500 | 1.0% | 2.00 | 20.00 | 65,000| 2.5 |
+------------+----------------------+-----------+-------------+--------+--------+---------+---------+-------+-----------+By integrating with Facebook Ads, this SQL model allows you to select and group data by month and campaign name, 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 trends, and make data-driven decisions to improve overall ad performance. The SQL code is simple to use and can be easily customized to fit your specific needs. With the Ad stats by campaign monthly SQL template, you can gain a deeper understanding of your Facebook ad campaigns and make informed decisions to drive better results.
