Ad Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.google_ads.ad_stats}}
7 -- to join another google ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , accounts as (
12 select
13 *
14 from
15 {{raw.google_ads.account}}
16 )
17 , campaigns as (
18 select
19 *
20 from
21 {{raw.google_ads.campaign}}
22 )
23 , ad_groups as (
24 select
25 *
26 from
27 {{raw.google_ads.ad_group}}
28 )
29 , ads as (
30 select
31 *
32 from
33 {{raw.google_ads.ad}}
34 )
35select
36 date_trunc(cast(date as date), day) date_day
37 , cast(accounts.id as string) account_id
38 , descriptive_name as account_name
39 , campaigns.name campaign_name
40 , cast(campaigns.id as string) campaign_id
41 , ad_groups.name ad_group_name
42 , cast(ad_groups.id as string) ad_group_id
43 , cast(ads.id as string) ad_id
44 , ads.name ad_name
45 , ads.status ad_status
46 , ads.type ad_type
47 , ads.display_url
48 , ads.final_urls
49 , sum(cost_micros * 0.000001) as spend
50 , sum(impressions) as impressions
51 , sum(clicks) as clicks
52 , sum(safe_divide((cost_micros * 0.000001), clicks)) as cpc
53 , sum(safe_divide(clicks, impressions)) as ctr
54 , sum(
55 safe_divide((cost_micros * 0.000001), impressions)
56 ) * 1000 as cpm
57 , sum(conversions) as conversions
58 -- Additional columns should be manually specified if needed
59from
60 stats
61 left join ads on stats.ad_id = ads.id
62 and stats.ad_group_id = ads.ad_group_id
63 left join ad_groups on ads.ad_group_id = ad_groups.id
64 left join campaigns on ad_groups.campaign_id = campaigns.id
65 left join accounts on campaigns.account_id = accounts.id
66group by
67 1
68 , 2
69 , 3
70 , 4
71 , 5
72 , 6
73 , 7
74 , 8
75 , 9
76 , 10
77 , 11
78 , 12
79 , 13
80 -- Adjust these numbers according to the selected columns+------------+-----------+----------------+---------------+-------------+-----------------+--------------+---------+-----------------+-----------+-----------------+-----------------+---------+-------------+--------+-------------------+--------------+
| date_day | account_id| account_name | campaign_name | campaign_id | ad_group_name | ad_group_id | ad_id | ad_name | ad_status | ad_type | display_url | final_urls | spend | impressions| clicks | cpc | ctr |
+------------+-----------+----------------+---------------+-------------+-----------------+--------------+---------+-----------------+-----------+-----------------+-----------------+---------+-------------+--------+-------------------+--------------+
| 2022-01-01 | 123456789 | Example Account| Campaign 1 | 987654321 | Ad Group 1 | 456789 | 123456 | Example Ad 1 | Active | Text Ad | www.example.com | www.example.com| 100.00 | 1000 | 50 | 2.00 | 0.05 |
| 2022-01-01 | 123456789 | Example Account| Campaign 1 | 987654321 | Ad Group 1 | 456789 | 789012 | Example Ad 2 | Active | Text Ad | www.example.com | www.example.com| 100.00 | 1000 | 50 | 2.00 | 0.05 |
| 2022-01-01 | 123456789 | Example Account| Campaign 2 | 987654322 | Ad Group 2 | 456790 | 123457 | Example Ad 3 | Active | Image Ad | www.example.com | www.example.com| 200.00 | 2000 | 100 | 2.00 | 0.05 |
+------------+-----------+----------------+---------------+-------------+-----------------+--------------+---------+-----------------+-----------+-----------------+-----------------+---------+-------------+--------+-------------------+--------------+Retrieve data from the Google Ads integration. It consists of several common table expressions (CTEs) that extract data from different tables within the Google Ads database. These CTEs include "stats," "accounts," "campaigns," "ad_groups," and "ads." The main query then joins these CTEs to retrieve specific information such as the date, account ID and name, campaign name and ID, ad group name and ID, ad ID and name, ad status and type, display URL, final URLs, spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions. This SQL template is useful for generating ad reports that provide insights into the performance of Google Ads campaigns. By aggregating data from different tables, it allows users to analyze metrics such as spend, impressions, clicks, and conversions at various levels, including the account, campaign, ad group, and ad levels. The template also calculates additional metrics like CPC, CTR, and CPM, which can help advertisers optimize their campaigns and make data-driven decisions. To customize the report, additional columns can be manually specified in the SELECT statement, and the GROUP BY clause can be adjusted accordingly. By leveraging this SQL template, users can gain valuable insights into their Google Ads performance and make informed decisions to improve their advertising strategies.
