I have loaded google ads data to Big query using data transfer. I am trying to get keyword and search keyword metrics for my analysis

I used below code from p_ads_KeywordBasicStats table

WITH stats AS (   SELECT     campaign_id AS CampaignId,     ad_group_id AS AdGroupId,     ad_group_criterion_criterion_id AS CriterionID,     segments_ad_network_type AS AdNetworkType1,     metrics_clicks AS Clicks,     metrics_cost_micros AS Cost,     segments_date AS Date,     metrics_impressions AS Impressions   FROM `micro-reserve.google_ad.p_ads_KeywordBasicStats_xxxxxx    ), keywords AS (   SELECT     ad_group_criterion_criterion_id AS CriterionId,     MAX(ad_group_criterion_keyword_text) AS Criteria,     ad_group_criterion_negative,     ad_group_criterion_status   FROM `micro-reserve.google_ad.p_ads_Keyword_xxxxxxx      GROUP BY ad_group_criterion_criterion_id, ad_group_criterion_negative, ad_group_criterion_status ), campaigns AS (   SELECT     campaign_id,     MAX(campaign_name) AS campaign_name   FROM `micro-reserve.google_ad.p_ads_Campaign_xxxxx      GROUP BY campaign_id ) SELECT    Clicks,   Cost,   Date,   Impressions,   Criteria AS Keywords,   campaign_name,   ROUND(SAFE_DIVIDE(Clicks, Impressions) * 100, 2) AS CTR FROM stats LEFT JOIN keywords   ON stats.CriterionID = keywords.CriterionId LEFT JOIN campaigns   ON stats.CampaignId = campaigns.campaign_id WHERE keywords.ad_group_criterion_negative IS FALSE    AND keywords.ad_group_criterion_status = 'ENABLED'    AND Date BETWEEN '2024-09-01' AND '2024-09-13' 

========== Using below query for p_ads_SearchQueryStats table

WITH stats AS (   SELECT     campaign_id AS CampaignId,     ad_group_id AS AdGroupId,     ad_group_ad_ad_id AS CriterionID,     segments_ad_network_type AS AdNetworkType1,     SUM(metrics_clicks) AS Clicks,     SUM(metrics_cost_micros) AS Cost,     segments_date AS Date,     SUM(metrics_impressions) AS Impressions,     MAX(search_term_view_search_term) AS Criteria,     search_term_view_status   FROM `micro-reserve.google_ad.p_ads_SearchQueryStats_xxxxxx   WHERE _PARTITIONTIME = TIMESTAMP('2024-09-13')   GROUP BY ad_group_ad_ad_id, search_term_view_status, ad_group_id, campaign_id, segments_ad_network_type, segments_date ), campaigns AS (   SELECT     campaign_id,     MAX(campaign_name) AS campaign_name   FROM `micro-reserve=.google_ad.p_ads_Campaign_xxxxxxxx   WHERE _PARTITIONTIME = TIMESTAMP('2024-09-13')   GROUP BY campaign_id ) SELECT    campaigns.campaign_name,   stats.Date,   stats.Clicks,   stats.Impressions,   stats.Cost,   stats.Criteria AS Search_Keywords FROM stats LEFT JOIN campaigns   ON stats.CampaignId = campaigns.campaign_id WHERE stats.Date = '2024-09-13' 

The above query works, but the numbers are not matching with Google Ads. Is something I am doing wrong? Can anyone advise please?

The problem is that there is a significant discrepancy between the spending or amount displayed on the transparency page UI and the information provided by BigQuery. For example, in the specific case where I send the screenshot (Ad: https://adstransparency.google.com/advertiser/AR09575383261467639809/creative/CR03209405000446902273?region=MX&topic=political), it shows a spending range of 6k - 7k MXN, which approximates to 375 USD (since BigQuery does not yet support MXN). However, BigQuery reports a maximum spend (spend_range_max_usd) of 100 USD. Below is the specific response for this ad.

Row, Advertiser, Advertiser_Page, Advertiser_URL, Number_of_Political_Ads, gender_targeting, geo_targeting_included, spend_range_min_usd, spend_range_max_usd,

2
PARTIDO ACCIÓN NACIONAL https://adstransparency.google.com/advertiser/AR09575383261467639809?region=MX&political= https://adstransparency.google.com/advertiser/AR09575383261467639809/creative/CR03209405000446902273?region=MX&topic=political 1 Male, Female, Unknown gender Mexico 0 100

Furthermore, this issue is not limited to this particular ad. All spend responses from BigQuery show the same amount, 100 USD, or the relative to the currency.

I was expecting at least a reasonable currency exchange, or perhaps I'm missing something which is causing all the values to return 100 USD.

I wonder if anyone else has had a similar issue.

#standardSQL SELECT   CS.advertiser_name AS Advertiser,   CONCAT("https://adstransparency.google.com/advertiser/", CS.advertiser_id, "?region=MX&political=") AS Advertiser_Page,   CONCAT("https://adstransparency.google.com/advertiser/", CS.advertiser_id, "/creative/", CS.ad_id, "?region=MX&topic=political") AS Advertiser_Page,   COUNT(1) AS Number_of_Political_Ads,   CS.gender_targeting,   CS.geo_targeting_included,   spend_range_min_usd,   spend_range_max_usd, FROM   bigquery-public-data.google_political_ads.creative_stats AS CS WHERE   REGEXP_CONTAINS(CS.gender_targeting, r"Female")   AND REGEXP_CONTAINS(CS.regions, r"MX") GROUP BY   1,   2,   3,   5,   6,   7,   8 ORDER BY   spend_range_max_usd DESC; 

I leave here the diferent data you can get.

[   {     "name": "ad_id",     "mode": "NULLABLE",     "type": "STRING",     "description": "Unique id for a specific election ad.",     "fields": []   },   {     "name": "ad_url",     "mode": "NULLABLE",     "type": "STRING",     "description": "URL to view the election ad in the election Advertising on Google report.",     "fields": []   },   {     "name": "ad_type",     "mode": "NULLABLE",     "type": "STRING",     "description": "The type of the ad. Can be TEXT VIDEO or IMAGE.",     "fields": []   },   {     "name": "regions",     "mode": "NULLABLE",     "type": "STRING",     "description": "The regions that this ad is verified for or were served in.",     "fields": []   },   {     "name": "advertiser_id",     "mode": "NULLABLE",     "type": "STRING",     "description": "ID of the advertiser who purchased the ad.",     "fields": []   },   {     "name": "advertiser_name",     "mode": "NULLABLE",     "type": "STRING",     "description": "Name of advertiser.",     "fields": []   },   {     "name": "ad_campaigns_list",     "mode": "NULLABLE",     "type": "STRING",     "description": "IDs of all election ad campaigns that included the ad.",     "fields": []   },   {     "name": "date_range_start",     "mode": "NULLABLE",     "type": "DATE",     "description": "First day a election ad ran and had an impression.",     "fields": []   },   {     "name": "date_range_end",     "mode": "NULLABLE",     "type": "DATE",     "description": "Most recent day a election ad ran and had an impression.",     "fields": []   },   {     "name": "num_of_days",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Total number of days a election ad ran and had an impression.",     "fields": []   },   {     "name": "impressions",     "mode": "NULLABLE",     "type": "STRING",     "description": "Number of impressions for the election ad. Impressions are grouped into several buckets ≤ 10k 10k-100k 100k-1M 1M-10M > 10M.",     "fields": []   },   {     "name": "first_served_timestamp",     "mode": "NULLABLE",     "type": "TIMESTAMP",     "description": "The timestamp of the earliest impression for this ad.",     "fields": []   },   {     "name": "last_served_timestamp",     "mode": "NULLABLE",     "type": "TIMESTAMP",     "description": "The timestamp of the most recent impression for this ad.",     "fields": []   },   {     "name": "age_targeting",     "mode": "NULLABLE",     "type": "STRING",     "description": "Age ranges included in the ad's targeting",     "fields": []   },   {     "name": "gender_targeting",     "mode": "NULLABLE",     "type": "STRING",     "description": "Genders included in the ad's targeting.",     "fields": []   },   {     "name": "geo_targeting_included",     "mode": "NULLABLE",     "type": "STRING",     "description": "Geographic locations included in the ad's targeting.",     "fields": []   },   {     "name": "geo_targeting_excluded",     "mode": "NULLABLE",     "type": "STRING",     "description": "Geographic locations excluded in the ad's targeting.",     "fields": []   },   {     "name": "spend_range_min_usd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in USD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_usd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in USD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_eur",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in EUR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_eur",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in EUR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_inr",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in INR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_inr",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in INR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_bgn",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in BGN spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_bgn",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in BGN spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_czk",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in CZK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_czk",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in CZK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_dkk",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in DKK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_dkk",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in DKK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_huf",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in HUF spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_huf",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in HUF spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_pln",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in PLN spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_pln",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in PLN spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_ron",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in RON spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_ron",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in RON spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_sek",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in SEK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_sek",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in SEK spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_gbp",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in GBP spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_gbp",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in GBP spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_nzd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in NZD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_nzd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in NZD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_ils",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in ILS spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_ils",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in ILS spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_aud",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in AUD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_aud",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in AUD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_twd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in TWD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_twd",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in TWD spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_brl",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in BRL spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_brl",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in BRL spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_ars",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in ARS spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_ars",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in ARS spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_zar",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in ZAR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_zar",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in ZAR spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_min_clp",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Lower bound of the amount in CLP spent by the advertiser on the election ad.",     "fields": []   },   {     "name": "spend_range_max_clp",     "mode": "NULLABLE",     "type": "INTEGER",     "description": "Upper bound of the amount in CLP spent by the advertiser on the election ad.",     "fields": []   } ]``` Thanks in advance! 

Goal: I am trying to calculate the total spend (cost) of an ad from by Google Ads data that is in BigQuery

Problem: The output of my BQ SQL does not match the output I see in Google Ads reports

Example: In BigQuery, I grab the metrics_cost_micros from AdStats table and divide it by 1m for the time period. This tells me for 'Ad 1' the total cost is $3,625.76 and avg CPC $1.21

SELECT    a.ad_group_ad_ad_name,   SUM(metrics_cost_micros)/ 1000000 AS total_spend,   AVG(metrics_cost_micros)/ 1000000 AS avg_daily_spend,   MAX(metrics_cost_micros)/ 1000000 AS max_spend FROM `[project].google_ads.p_ads_AdStats_xxx` ast LEFT JOIN `[project].google_ads.p_ads_Ad_xxx` a    ON ast.ad_group_ad_ad_id = a.ad_group_ad_ad_id WHERE a.ad_group_ad_ad_name = "Ad 1" GROUP BY ALL 

However, in Google Ads reporting tool, the same ad in the same timeframe has a total cost of $107.53 and avg CPC of $0.38

I thought currency conversion might be an issue, but it doesn't account for the discrepancy

I need to create a report on asset performance from google ads transfer data in bigquery. The report should be similar to the one in Google Ads UI (under Assets with assets names, campaigns, clicks etc). I have enabled PMAX within the bigquery data transfer and now have views about Assets (e.g. ads_Assets, ads_CampaignAssetStats etc) When I query ads_CampaignAssetStats I see an asset field campaign_asset_asset of format customers/1111111111/assets/111111111111, I would like to somehow get the name of this particular asset, but I cannot find this in any asset related tables.

So either that data is not available in BQ data transfer and I need to get it from the api or I am not searching or understanding the data. Where can I find this data within BQ data transfer tables (if at all?

I have looked through google ads data transfer documentation expecting some more insights into the asset views but it didn't clarify how I can get the names of the assets.

I have browsed through other tables within BQ google ads data transfer but haven't found a way to connect the data.

I am getting duplicate data in my Big Query tables from Google Ads.

I can look at the tables and see rows with the exact same data. Furthermore when I aggregate the data to see total cost of campaigns, it is double what is shown in the Google Ads platform.

I followed the guide for the data transfer and didn't do anything outside the standard set up. I did do a backfill to get data for the entire month because it originally only imported data for the previous week. I also set the date range on the backfill to not include the week of data already imported. And there are duplicates for everyday of the month.

Has anyone experiences this and know why it is making duplicate entries and if so how do I get rid of the duplicates?