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!