Created
February 4, 2024 11:44
-
-
Save thomashandorf/c268f94f549e5180f5a6307ab2a08239 to your computer and use it in GitHub Desktop.
Get Google Ads hierarchy by gclid
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with customer as ( | |
| select * except(rn) from ( | |
| select *,row_number() over (partition by customer_id order by _DATA_DATE desc) rn from `project`.`adwords`.`ads_Customer_1234567890` | |
| ) | |
| where rn=1 | |
| ), | |
| campaigns as ( | |
| select * except(rn) from ( | |
| select *,row_number() over (partition by campaign_id order by _DATA_DATE desc) rn from `project`.`adwords`.`ads_Campaign_1234567890` | |
| ) | |
| where rn=1 | |
| ), | |
| adgroups as ( | |
| select * except(rn) from ( | |
| select *,row_number() over (partition by ad_group_id order by _DATA_DATE desc) rn from `project`.`adwords`.`ads_AdGroup_1234567890` | |
| ) | |
| where rn=1 | |
| ), | |
| ads as ( | |
| select * except(rn) from ( | |
| select *, | |
| COALESCE (ad_group_ad_ad_name, ad_group_ad_ad_image_ad_name, ad_group_ad_ad_text_ad_headline, ad_group_ad_ad_legacy_responsive_display_ad_short_headline ,ad_group_ad_ad_responsive_display_ad_headlines, CAST(ad_group_ad_ad_id as STRING)) ad_name, | |
| row_number() over (partition by ad_group_ad_ad_id order by _DATA_DATE desc) rn | |
| from `project`.`adwords`.`ads_Ad_1234567890` | |
| ) | |
| where rn=1 | |
| ), | |
| ads_hierarchy as ( | |
| select | |
| customer_descriptive_name account_name, | |
| ads.customer_id account_id, | |
| ads.campaign_id campaign_id, | |
| campaign_name campaign_name, | |
| ads.ad_group_id adgroup_id, | |
| ad_group_name adgroup_name, | |
| ad_group_ad_ad_id ad_id, | |
| ad_name | |
| from ads | |
| LEFT JOIN campaigns c on ads.campaign_id = c.campaign_id | |
| LEFT JOIN adgroups adset ON ads.ad_group_id = adset.ad_group_id | |
| LEFT JOIN customer cust ON ads.customer_id = cust.customer_id | |
| ) | |
| select | |
| click_view_gclid gclid, | |
| g.campaign_id, | |
| campaign_name, | |
| g.ad_group_id adgroup_id, | |
| adgroup_name, | |
| g.customer_id account_id, | |
| account_name, | |
| regexp_extract(click_view_ad_group_ad,r'~(.*)') ad_id, | |
| ad_name, | |
| click_view_keyword_info_text keyword, | |
| segments_device device | |
| from `project`.`adwords`.`ads_ClickStats_1234567890` g | |
| left join ads_hierarchy a on regexp_extract(click_view_ad_group_ad,r'~(.*)') = CAST(a.ad_id as string) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment