URL Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.google_ads.ad_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 )
17 , campaigns as (
18 select
19 *
20 from
21 {{raw.google_ads.campaign}}
22 )
23 , ad_groups as (
24 select
25 *
26 from
27 {{raw.google_ads.ad_group}}
28 )
29 , ads as (
30 select
31 *
32 from
33 {{raw.google_ads.ad}}
34 )
35select
36 date_trunc(cast(date as date), day) date_day
37 , accounts.id account_id
38 , campaigns.name campaign_name
39 , campaigns.id campaign_id
40 , ad_groups.name ad_group_name
41 , ad_groups.id ad_group_id
42 , ads.id ad_id
43 , ads.display_url
44 , ads.final_urls
45 , ads.final_url_suffix
46 , coalesce(
47 regexp_extract(ads.final_url_suffix, r'[?&]utm_source=([^&]*)')
48 , 'google'
49 ) as utm_source
50 , coalesce(
51 regexp_extract(ads.final_url_suffix, r'[?&]utm_medium=([^&]*)')
52 , 'cpc'
53 ) as utm_medium
54 , coalesce(
55 regexp_extract(ads.final_url_suffix, r'[?&]utm_campaign=([^&]*)')
56 , campaigns.name
57 ) as utm_campaign
58 , coalesce(
59 regexp_extract(ads.final_url_suffix, r'[?&]utm_content=([^&]*)')
60 , ad_groups.name
61 ) as utm_content
62 , sum(cost_micros * 0.000001) as spend
63 , sum(impressions) as impressions
64 , sum(clicks) as clicks
65 , sum(safe_divide((cost_micros * 0.000001), clicks)) as cpc
66 , sum(safe_divide(clicks, impressions)) as ctr
67 , sum(
68 safe_divide((cost_micros * 0.000001), impressions)
69 ) * 1000 as cpm
70 , sum(conversions) as conversions
71 -- Additional columns should be manually specified if needed
72from
73 stats
74 left join ads on stats.ad_id = ads.id
75 and stats.ad_group_id = ads.ad_group_id
76 left join ad_groups on ads.ad_group_id = ad_groups.id
77 left join campaigns on ad_groups.campaign_id = campaigns.id
78 left join accounts on campaigns.account_id = accounts.id
79where
80 ads.final_urls is not null
81group by
82 1
83 , 2
84 , 3
85 , 4
86 , 5
87 , 6
88 , 7
89 , 8
90 , 9
91 , 10
92 , 11
93 , 12
94 , 13
95 , 14
96 -- Adjust these numbers according to the selected columns+------------+------------+----------------+-------------+-----------------+-------------+-------+----------------+-----------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+-----------------+
| date_day | account_id | campaign_name | campaign_id | ad_group_name | ad_group_id | ad_id | display_url | final_urls | final_url_suffix | utm_source | utm_medium | utm_campaign | utm_content | spend | impressions | clicks |
+------------+------------+----------------+-------------+-----------------+-------------+-------+----------------+-----------------+-----------------+-------------+-------------+-------------+-------------+-------------+--------------+-----------------+
| 2022-01-01 | 123456 | Campaign A | 789 | Ad Group X | 456 | 789 | example.com | example.com | ?utm_source=google&utm_medium=cpc&utm_campaign=Campaign%20A&utm_content=Ad%20Group%20X | google | cpc | Campaign A | Ad Group X | 100.00 | 1000 | 50 |
| 2022-01-01 | 123456 | Campaign A | 789 | Ad Group X | 456 | 789 | example.com | example.com | ?utm_source=google&utm_medium=cpc&utm_campaign=Campaign%20A&utm_content=Ad%20Group%20X | google | cpc | Campaign A | Ad Group X | 100.00 | 1000 | 50 |
| 2022-01-01 | 123456 | Campaign A | 789 | Ad Group X | 456 | 789 | example.com | example.com | ?utm_source=google&utm_medium=cpc&utm_campaign=Campaign%20A&utm_content=Ad%20Group%20X | google | cpc | Campaign A | Ad Group X | 100.00 | 1000 | 50 |
+------------+------------+----------------+-------------+-----------------+-------------+-------+----------------+-----------------+-----------------+-------------+-------------+-------------+-------------+-------------+--------------+-----------------+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.
