Weld
Start free
Google Ads model

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.

See the SQLAll Google Ads models
Grain
See the notes
Built from
  1. 1
    Raw tables
  2. 1
    Staging
Reads from Google Ads
ad_stats
Ships as
Weld and dbt SQL, open source on GitHub
staging.google_ads.ad_report
1-- staging.google_ads.ad_report
2-- Ad Report. Generates an ad report by joining data from various tables in the
3-- Google Ads integration. It retrieves information such as date, account ID and
4-- name, campaign name and ID, ad group name and ID, ad ID and name, ad status and
5-- type, display URL, final URLs, spend, impressions, clicks, CPC, CTR, CPM, and
6-- conversions. Additional columns can be manually specified if needed.
7--
8-- From the weld.app template library. Not reconciled against Google Ads;
9-- check the output against the source before relying on it.
10
11select
12    date_trunc(cast(date as date), day) date_day
13  , cast(account_id as string) account_id
14  , account_descriptive_name as account_name
15  , campaign_name campaign_name
16  , cast(campaign_id as string) campaign_id
17  , ad_group_name ad_group_name
18  , cast(ad_group_id as string) ad_group_id
19  , cast(ad_id as string) ad_id
20  , ad_name ad_name
21  , ad_network_type ad_type
22  , cost_micros * 0.000001 as spend
23  , clicks
24  , impressions
25  , interactions
26  , conversions 
27  , conversions_value
28  , safe_divide(conversions_value, cost_micros * 0.000001) as roas -- conversion value per unit of spend
29from
30  {{raw.google_ads.ad_stats}} stats

Refs like {{raw.…}} resolve to your synced tables in Weld; the dbt view uses source() instead.

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.google_ads.ad_stats

More Google Ads models

Put Google Ads in front
of your agents.

Connect Google Ads, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us