Weld logo
google-ads
Google ads

Ad stats by country monthly

Generates monthly ad statistics by country, including spend, clicks, CPC, cost per conversion, CTR, conversions, conversion value, impressions, and interactions, using data from Google Ads. It groups the data by month and country criterion ID.
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
Example of output from model:
+---------------------+---------------------+-----------+--------+---------+------------------------+--------+-------------+--------------+--------------+
|        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.

Ready to start modeling your own google-ads data?

Get started building your data warehouse with google-ads and 100+ more apps and databases available.

google-ads
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.