URL Report
Generates a report for URL performance in Google Ads. It retrieves data from various tables, including ad stats, accounts, campaigns, ad groups, and ads. The template calculates metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions. Additional columns can be specified manually if needed.
- Grain
- day x ad
- Built from
- 3Raw tables
- 1Core
- 1Analytics
- Reads from Google Ads
- accountadad_stats
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.google_ads.url_report
2-- Daily Google Ads delivery per ad, with the landing page it sends traffic to
3-- and the UTM tags that page receives. Use it to tie ad spend to sessions in
4-- your web analytics.
5--
6-- Grain: day x ad. Ads without final URLs (app and some video ads) are left
7-- out. Spend is in the account currency (account_currency), converted from
8-- micros. Rates are worked out from the day's totals: ctr is clicks /
9-- impressions as a fraction, cpc is spend per click, cpm is spend per thousand
10-- impressions.
11--
12-- UTM tags are read from the ad's final URL suffix. Where a tag is missing it
13-- falls back to google / cpc / campaign name / ad group name, which is what
14-- most auto-tagged setups report.
15--
16-- Depends on: raw.google_ads.ad_stats, ad, account
17--
18-- From the weld.app template library, rewritten and corrected. Not reconciled
19-- against Google Ads; check the output against the source before relying on it.
20
21WITH ad_days AS (
22 -- ad_stats carries the campaign and ad group names as of the report, so no
23 -- campaign or ad group lookup is needed.
24 SELECT
25 CAST(date AS DATE) AS date_day,
26 account_id,
27 campaign_id,
28 ANY_VALUE(campaign_name) AS campaign_name,
29 ad_group_id,
30 ANY_VALUE(ad_group_name) AS ad_group_name,
31 ad_id,
32 SUM(impressions) AS impressions,
33 SUM(clicks) AS clicks,
34 SUM(cost_micros) / 1e6 AS spend,
35 SUM(conversions) AS conversions
36 FROM {{raw.google_ads.ad_stats}}
37 GROUP BY date_day, account_id, campaign_id, ad_group_id, ad_id
38),
39
40landing_pages AS (This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
Retrieve data from the Google Ads integration. It combines multiple tables, such as ad_stats, account, campaign, ad_group, and ad, to generate a comprehensive report on URL performance. The SQL code starts by creating temporary tables using the "with" clause. These tables include "stats" for ad statistics, "accounts" for account information, "campaigns" for campaign details, "ad_groups" for ad group data, and "ads" for ad-specific information. The final select statement retrieves the desired columns from the joined tables. It includes the date truncated to the day, account ID, campaign name and ID, ad group name and ID, ad ID, display URL, final URLs, final URL suffix, and various UTM parameters extracted from the final URL suffix. Additionally, the SQL calculates various metrics such as spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions. These metrics provide insights into the performance of the URLs in terms of advertising costs, impressions, clicks, and conversions. The SQL template can be useful for analyzing the effectiveness of different URLs in driving traffic and conversions. It allows marketers to understand the performance of their Google Ads campaigns, identify successful campaigns, and optimize their advertising strategies based on the extracted UTM parameters and performance metrics.
Raw tables it reads
- raw.google_ads.account
- raw.google_ads.ad
- 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.