Weld logo
google-ads logo
Google ads

Account Report

Generates an account report for the Google Ads integration. It retrieves data from the `google_ads.account_stats` and `google_ads.account` tables, and calculates various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. The results are grouped by date, account ID, account name, currency code, auto tagging status, and time zone.
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
Example of output from model:
+------------+------------+-----------------+----------------+------------------------+-------------+-------+------------+-------+-------+--------+-------------+-------------+
| 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.

Ready to start modeling your own google-ads data?

Get started building your data warehouse with google-ads and 100+ more apps and databases available.

google-ads logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.