Weld logo
google-ads logo
Google ads

Ad-group Report

Generates an ad-group report by retrieving data from the Google Ads integration. It combines information from various tables such as ad_group_stats, account, campaign, and ad_group to provide metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. The report is grouped by date, account, campaign, and ad group.
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
Example of output from model:
+------------+-----------------+------------+----------------+--------------+-----------------+--------------+-----------------+-----------------+---------+-------------+----------+----------+-----------+--------------+
| 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.

Ready to start modeling your own google-ads data?

Get started building your data warehouse with google-ads and connect all your apps and databases.

google-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.