Google ads
Ad stats by ad monthly
Generates monthly ad statistics for Google Ads, including spend, clicks, cost per click, cost per conversion, click-through rate, conversions, conversion value, impressions, and interactions. It groups the data by month and ad name.
1select
2 date_trunc(cast(date as date), month) month
3 , ad_name
4 , sum(cost_micros * 0.000001) as spend
5 , sum(clicks) as clicks
6 , sum(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
18 1
19 , 2Example of output from model:
+------------+-------------+-----------+--------+-------+-----------------------+--------+--------------+-------------+--------------+
| month | ad_name | spend | clicks | cpc | cost_per_conversion | ctr | conversions | conversions_value | impressions |
+------------+-------------+-----------+--------+-------+-----------------------+--------+--------------+-------------+--------------+
| 2021-01-01 | ad_name_1 | 100.00 | 1000 | 0.10 | 10.00 | 0.10 | 50 | 500.00 | 100000 |
| 2021-01-01 | ad_name_2 | 200.00 | 2000 | 0.10 | 20.00 | 0.20 | 100 | 1000.00 | 200000 |
| 2021-01-01 | ad_name_3 | 300.00 | 3000 | 0.10 | 30.00 | 0.30 | 150 | 1500.00 | 300000 |
| 2021-01-01 | ad_name_4 | 400.00 | 4000 | 0.10 | 40.00 | 0.40 | 200 | 2000.00 | 400000 |
+------------+-------------+-----------+--------+-------+-----------------------+--------+--------------+-------------+--------------+By integrating with Google Ads, this SQL model allows you to extract valuable insights on your ad spend, clicks, conversions, and more. The SQL code selects data from the ad_stats table and groups it by month and ad name, providing a clear overview of your ad performance over time. With this information, you can identify which ads are performing well and which ones need improvement, adjust your ad spend accordingly, and optimize your campaigns for maximum ROI. Whether you're a small business owner or a marketing professional, the Ad stats by ad monthly SQL template is an essential tool for making data-driven decisions and achieving your advertising goals.
