1select
2 date_trunc(cast(date as date), day) date_day
3 , cast(account_id as string) account_id
4 , account_descriptive_name as account_name
5 , campaign_name campaign_name
6 , cast(campaign_id as string) campaign_id
7 , ad_group_name ad_group_name
8 , cast(ad_group_id as string) ad_group_id
9 , cast(ad_id as string) ad_id
10 , ad_name ad_name
11 , ad_network_type ad_type
12 , cost_micros * 0.000001 as spend
13 , clicks
14 , impressions
15 , interactions
16 , conversions
17 , conversions_value
18 , safe_divide(
19 (cost_micros * 0.000001)
20 , ifnull(conversions_value, 0)
21 ) as roas
22from
23 {{raw.google_ads.ad_stats}} statsExample output
+ ------------+-----------+----------------+---------------+-------------+-----------------+--------------+---------+-----------------+-----------+-----------------+-----------------+---------+-------------+--------+-------------------+--------------+
| 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.