Ad-group Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.google_ads.ad_group_stats}} -- to join another google ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , accounts as (
11 select
12 *
13 from
14 {{raw.google_ads.account}}
15 )
16 , campaigns as (
17 select
18 *
19 from
20 {{raw.google_ads.campaign}}
21 )
22 , ad_groups as (
23 select
24 *
25 from
26 {{raw.google_ads.ad_group}}
27 )
28select
29 date_trunc(cast(date as date), day) date_day
30 , accounts.descriptive_name account_name
31 , cast(accounts.id as string) account_id
32 , campaigns.name campaign_name
33 , cast(campaigns.id as string) campaign_id
34 , ad_groups.name ad_group_name
35 , cast(ad_groups.id as string) ad_group_id
36 , ad_groups.status ad_group_status
37 , ad_groups.type ad_group_type
38 , sum(cost_micros * 0.000001) as spend
39 , sum(impressions) as impressions
40 , sum(clicks) as clicks
41 , sum(safe_divide((cost_micros * 0.000001), clicks)) as cpc
42 , sum(safe_divide(clicks, impressions)) as ctr
43 , sum(
44 safe_divide((cost_micros * 0.000001), impressions)
45 ) * 1000 as cpm
46 , sum(conversions) as conversions -- Additional columns should be manually specified if needed
47from
48 stats
49 left join ad_groups on stats.ad_group_id = ad_groups.id
50 left join campaigns on ad_groups.campaign_id = campaigns.id
51 left join accounts on campaigns.account_id = accounts.id
52group by
53 1
54 , 2
55 , 3
56 , 4
57 , 5
58 , 6
59 , 7
60 , 8
61 , 9 -- Adjust these numbers according to the selected columns+------------+-----------------+------------+----------------+--------------+-----------------+--------------+-----------------+-----------------+---------+-------------+----------+----------+-----------+--------------+
| date_day | account_name | account_id | campaign_name | campaign_id | ad_group_name | ad_group_id | ad_group_status | ad_group_type | spend | impressions | clicks | cpc | ctr | cpm |
+------------+-----------------+------------+----------------+--------------+-----------------+--------------+-----------------+-----------------+---------+-------------+----------+----------+-----------+--------------+
| 2022-01-01 | Example Account | 1234567890 | Example Campaign | 9876543210 | Example Ad Group | 5678901234 | ENABLED | SEARCH_STANDARD | 100.00 | 1000 | 50 | 2.00 | 0.05 | 100.00 |
+------------+-----------------+------------+----------------+--------------+-----------------+--------------+-----------------+-----------------+---------+-------------+----------+----------+-----------+--------------+Retrieve various metrics and insights related to ad groups within your Google Ads account. By joining multiple tables, including ad group statistics, account, campaign, and ad group tables, you can extract valuable information for analysis. The SQL code begins by creating a temporary table called "stats" that contains all the columns from the "ad_group_stats" table in the Google Ads data. This table can be joined with other Google Ads accounts if needed. Similarly, temporary tables are created for accounts, campaigns, and ad groups, containing all the columns from their respective tables. The final SELECT statement combines the data from these temporary tables and performs various calculations to derive meaningful metrics. These metrics include the date truncated to the day, account name and ID, campaign name and ID, ad group name and ID, ad group status and type, spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions. Additional columns can be manually specified if required. By grouping the data based on specific columns, such as date, account, campaign, and ad group, you can obtain aggregated insights and performance metrics for each combination. This SQL template provides a comprehensive overview of ad group performance, allowing you to analyze and optimize your advertising campaigns effectively.
