Axel Sosa
Reputation: 1
Discrepancy between political ads information in BigQuery and the Transparency UI
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!
Upvotes: 0
Views: 31
Answers (0)
Related Questions
- JSON with different schemas for a single external table in BigQuery
- Replace/modify json keys and generate new json to a different column to existing record
- schema of an existing table in JSON format using bigquery select statement in bigquery
- Not able to use Struct or Unnest in parsing JSON string data
- Big query remove column nested in STRUCT object
- In bigquery, is there a way to set data fields to nullable through browser interface
- bq CLI says my JSON schema is invalid while the browser GUI says it's fine. Where am I going wrong?