Weld logo
google-ads logo
Google ads

Ad Report

Generates an ad report by joining data from various tables in the Google Ads integration. It retrieves information such as 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, CPC, CTR, CPM, and conversions. Additional columns can be manually specified if needed.
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}} stats
Example of output from model:
+------------+-----------+----------------+---------------+-------------+-----------------+--------------+---------+-----------------+-----------+-----------------+-----------------+---------+-------------+--------+-------------------+--------------+
| 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.

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
Weld logo

Tired of scattered data? Sync your data in minutes with our AI-powered ETL platform. Seamlessly connect all your apps, files, and databases.

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