Account Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.google_ads.account_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 )
17select
18 date_trunc(cast(date as date), day) date_day
19 , cast(accounts.id as string) account_id
20 , accounts.descriptive_name account_name
21 , accounts.currency_code
22 , accounts.auto_tagging_enabled
23 , accounts.time_zone
24 , sum(cost_micros * 0.000001) as spend
25 , sum(impressions) as impressions
26 , sum(clicks) as clicks
27 , sum(safe_divide((cost_micros * 0.000001), clicks)) as cpc
28 , sum(safe_divide(clicks, impressions)) as ctr
29 , sum(
30 safe_divide((cost_micros * 0.000001), impressions)
31 ) * 1000 as cpm
32 , sum(conversions) as conversions -- Additional pass-through columns should be manually specified if needed
33from
34 stats
35 left join accounts on stats.account_id = accounts.id
36group by
37 1
38 , 2
39 , 3
40 , 4
41 , 5
42 , 6 -- Adjust these numbers according to the selected columns+------------+------------+-----------------+----------------+------------------------+-------------+-------+------------+-------+-------+--------+-------------+-------------+
| date_day | account_id | account_name | currency_code | auto_tagging_enabled | time_zone | spend | impressions | clicks| cpc | ctr | cpm | conversions |
+------------+------------+-----------------+----------------+------------------------+-------------+-------+------------+-------+-------+--------+-------------+-------------+
| 2022-01-01 | 123456789 | Example Account | USD | true | America/Los_Angeles | 100.0 | 1000 | 50 | 2.0 | 0.05 | 100.0 | 10 |
+------------+------------+-----------------+----------------+------------------------+-------------+-------+------------+-------+-------+--------+-------------+-------------+Retrieve data from the Google Ads integration. It combines information from two tables, "account_stats" and "account", to generate a comprehensive report on account performance. The SQL code begins by creating a temporary table called "stats" that selects all columns from the "account_stats" table. This table can be extended to include data from other Google Ads accounts by uncommenting the appropriate section. Next, a second temporary table called "accounts" is created, which selects all columns from the "account" table. The final SELECT statement combines data from both temporary tables and performs various calculations to derive insights. It includes columns such as the truncated date, account ID, account name, currency code, auto-tagging status, time zone, 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 needed. The SQL code then joins the "stats" and "accounts" tables based on the account ID and groups the results by the selected columns. This SQL template is useful for generating account-level reports in Google Ads, providing valuable insights into performance metrics such as spend, impressions, clicks, and conversions. It allows users to analyze data across different accounts and evaluate key performance indicators to optimize advertising strategies.
