Ad stats by country monthly
1select
2 date_trunc(cast(date as date), month) month
3 , country_criterion_id
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_group_country_stats}}
17group by
18 1
19 , 2
20 , 3+---------------------+---------------------+-----------+--------+---------+------------------------+--------+-------------+--------------+--------------+
| month | country_criterion_id | spend | clicks | cpc | cost_per_conversion | ctr | conversions | conversions_value| impressions |
+---------------------+---------------------+-----------+--------+---------+------------------------+--------+-------------+--------------+--------------+
| 2021-01-01 00:00:00 | 1 | 1000.00 | 100 | 10.00 | 10.00 | 0.10 | 10 | 1000 | 100000 |
| 2021-01-01 00:00:00 | 2 | 2000.00 | 200 | 10.00 | 10.00 | 0.20 | 20 | 2000 | 200000 |
| 2021-01-01 00:00:00 | 3 | 3000.00 | 300 | 10.00 | 10.00 | 0.30 | 30 | 3000 | 300000 |
+---------------------+---------------------+-----------+--------+---------+------------------------+--------+-------------+--------------+--------------+This SQL model allows you to view ad statistics by country on a monthly basis. The SQL code selects data from the Google Ads ad group country stats table and groups it by month, country criterion ID, and spend. The resulting output provides valuable insights into the performance of your ads, including the number of clicks, cost per click (CPC), cost per conversion, click-through rate (CTR), conversions, conversion value, impressions, and interactions. This information can be used to optimize your ad campaigns and improve your return on investment (ROI). Whether you're a small business owner or a marketing professional, the Ad stats by country monthly SQL template is an essential tool for maximizing the effectiveness of your Google Ads campaigns.
