Facebook ads
Ad stats by ad monthly
Generates monthly ad statistics for Facebook Ads, including spend, impressions, clicks, click-through rate (CTR), cost per click (CPC), cost per thousand impressions (CPM), reach, and frequency. It groups the data by month and ad name.
1select
2 date_trunc(date, month) month
3 , ad_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 | ad_name | spend | impressions | clicks | ctr | cpc | cpm | reach |
+---------------------+------------------------+---------+-------------+--------+--------+--------+---------+------------+
| 2021-01-01 00:00:00 | Facebook Ad #1 | 100.00 | 100,000 | 1,000 | 1.00 % | 0.10 | 1,000 | 50,000 |
| 2021-01-01 00:00:00 | Facebook Ad #2 | 200.00 | 200,000 | 2,000 | 1.00 % | 0.10 | 1,000 | 100,000 |
| 2021-01-01 00:00:00 | Facebook Ad #3 | 300.00 | 300,000 | 3,000 | 1.00 % | 0.10 | 1,000 | 150,000 |
+---------------------+------------------------+---------+-------------+--------+--------+--------+---------+------------+By integrating with Facebook Ads, this SQL model allows you to select and group data by month, ad name, and various performance metrics such as spend, impressions, clicks, and reach. Additionally, it calculates important metrics such as click-through rate (CTR), cost per click (CPC), and cost per thousand impressions (CPM). With this information, you can gain valuable insights into the effectiveness of your Facebook ad campaigns, identify areas for improvement, and optimize your ad spend for maximum ROI. The output from this SQL model is a comprehensive report that provides a clear picture of your ad performance over time, making it an essential tool for any marketer looking to improve their Facebook ad strategy.
