Google ads
Ad stats by campaign monthly
Generates monthly ad statistics by campaign, including spend, clicks, cost per click, cost per conversion, click-through rate, conversions, conversion value, impressions, and interactions, using data from Google Ads.
1select
2 date_trunc(cast(date as date), month) month
3 , campaign_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.campaign_stats}}
17group by
18 1
19 , 2Example of output from model:
+------------+----------------------+----------+--------+--------+-----------------------+-------+-------------+-------------+-------------+
| month | campaign_name | spend | clicks | cpc | cost_per_conversion | ctr | conversions | conversions | impressions |
| | | | | | | | | _value | |
+------------+----------------------+----------+--------+--------+-----------------------+-------+-------------+-------------+-------------+
| 2021-01-01 | Summer Collection | 500.00 | 100 | 5.00 | 10.00 | 0.10 | 10 | 1000.00 | 10000 |
| 2021-01-01 | Winter Collection | 1000.00 | 200 | 5.00 | 20.00 | 0.20 | 20 | 2000.00 | 20000 |
| 2021-01-01 | Spring Collection | 750.00 | 150 | 5.00 | 15.00 | 0.15 | 15 | 1500.00 | 15000 |
+------------+----------------------+----------+--------+--------+-----------------------+-------+-------------+-------------+-------------+This SQL model allows you to view your campaign data on a monthly basis, providing insights into your spend, clicks, cost per click (CPC), cost per conversion, click-through rate (CTR), conversions, conversions value, impressions, and interactions. By grouping your data by month and campaign name, you can easily identify trends and patterns in your advertising performance over time. This SQL template is particularly useful for businesses looking to optimize their advertising spend and improve their return on investment (ROI) from Google Ads campaigns. With the Ad stats by campaign monthly SQL template, you can gain a deeper understanding of your advertising performance and make data-driven decisions to improve your results.
