Querying public google_ads_transparency_center table doesn't show all the needed data.

[Agniwald]

I'm having an issue querying table `bigquery-public-data.google_ads_transparency_center.creative_stats` for a specific advertiser.

For example:
SELECT distinct creative_id FROM `bigquery-public-data.google_ads_transparency_center.creative_stats`
where advertiser_id = 'AR01847332847816278017'

The query above outputs only three creatives, but if you look it up in the transparency center UI - https://adstransparency.google.com/advertiser/AR01847332847816278017 it will be ~200

I thought it was a time-related problem, but one of the three creatives was 1 day old, and another was 6 months old. Meaning it doesn't output only the last N days of creatives.

I also tried using the time-travel window as follows:
SELECT *
FROM `bigquery-public-data.google_ads_transparency_center.creative_stats`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE);

But I got the next error:
Access Denied: Table bigquery-public-data:google_ads_transparency_center.creative_stats@1735919371368: User does not have permission to query table bigquery-public-data:google_ads_transparency_center.creative_stats@1735919371368, or perhaps it does not exist.

[gojoe · Accepted solution]

In general, BigQuery public datasets may not have all of the data that exists for that domain. As per Google Ads Transparency Center overview (pasted below) in the BigQuery public data marketplace, it contains data for "the European Economic Area or Turkey" only while https://adstransparency.google.com lets you query globally. If you filter to specific countries in that site (instead of shown "Anywhere"), you'll see different number of ads. 

--

Overview

This dataset contains two tables: creative_stats and removed_creative_stats.

The creative_stats table contains information about advertisers that served ads in the European Economic Area or Turkey: their legal name, verification status, disclosed name, and location. It also includes ad specific information: impression ranges per region (including aggregate impressions for the European Economic Area), first shown and last shown dates, which criteria were used in audience selection, the format of the ad, the ad topic and whether the ad is funded by Google Ad Grants program. A link to the ad in the Google Ads Transparency Center is also provided.

The removed_creative_stats table contains information about ads that served in the European Economic Area that Google removed: where and why they were removed and per-region information on when they served. The removed_creative_stats table also contains a link to the Google Ads Transparency Center for the removed ad.

Data for both tables updates periodically and may be delayed from what appears on the Google Ads Transparency Center website.