Ad stats by ad group monthly
1select
2 date_trunc(cast(date as date), month) month
3 , ad_group_name
4 , sum(cost_micros * 0.000001) as spend
5 , sum(clicks) as clicks
6 , avg(safe_divide((cost_micros * 0.000001), clicks)) as cpc
7 , sum(
8 safe_divide((cost_micros * 0.000001), conversions)
9 ) as cost_per_conversion
10 , sum(safe_divide(clicks, impressions)) as ctr
11 , sum(conversions) conversions
12 , sum(conversions_value) conversions_value
13 , sum(impressions) as impressions
14 , sum(interactions) as interactions
15from
16 {{raw.google_ads.ad_stats}}
17group by+---------------------+------------------------+-----------+--------+--------+------------------------+--------+-------------+-----------------+---------------+
| month | ad_group_name | spend | clicks | cpc | cost_per_conversion | ctr | conversions | conversions_value| impressions |
+---------------------+------------------------+-----------+--------+--------+------------------------+--------+-------------+-----------------+---------------+
| 2021-01-01 00:00:00 | Market Mover | 1000.00 | 500 | 2.00 | 50.00 | 0.05 | 20 | 500.00 | 100000 |
| 2021-01-01 00:00:00 | Advertise Now | 1500.00 | 750 | 2.00 | 75.00 | 0.07 | 30 | 750.00 | 150000 |
| 2021-02-01 00:00:00 | Market Mover | 2000.00 | 1000 | 2.00 | 100.00 | 0.10 | 40 | 1000.00 | 200000 |
| 2021-02-01 00:00:00 | Advertise Now | 2500.00 | 1250 | 2.00 | 125.00 | 0.12 | 50 | 1250.00 | 250000 |
+---------------------+------------------------+-----------+--------+--------+------------------------+--------+-------------+-----------------+---------------+This SQL model allows you to view your ad group statistics on a monthly basis, providing valuable insights into your ad spend, clicks, cost per click (CPC), cost per conversion, click-through rate (CTR), conversions, conversions value, impressions, and interactions. By using this SQL template, you can easily identify which ad groups are performing well and which ones need improvement, allowing you to optimize your campaigns for maximum ROI. This SQL model is integrated with Google Ads, making it easy to access and use. Simply input your data into the {{raw.google_ads.ad_stats}} field and let the SQL do the rest. Whether you're a small business owner or a marketing professional, the Ad stats by ad group monthly SQL template is an essential tool for optimizing your Google Ads campaigns.
