I recently came across an interesting request on Upwork. The Upwork user wanted to generate CSV files showing ads in a specific region within a date range for Google Ads and Meta Ads. These CSV files could be very large, but I created a draft solution for how to approach the task.
The Google Ads Transparency Center provides a public dataset, so you can query it directly as if it were an SQL database. However, there is one challenge you need to resolve first: it’s a 150 GB file with nested values. You’ll need to UNNEST it and save the data into separate tables (this process should be repeated at least once a month). Since the dataset includes region filters, it makes sense to create these tables by creative_stats.region_code (a separate table for each country). This will help avoid the extra cost of UNNESTing the dataset every time you query it.
Stored Procedure:
BEGIN
-- Variable declarations at start
DECLARE countries ARRAY<STRING>;
DECLARE i INT64;
DECLARE country STRING;
DECLARE table_name STRING;
-- Initialize
-- The list of countries is better to be provided as a fixed list for better performance. I listed only 3 countries
SET countries = ['FR', 'DE', 'IT'];
SET i = 0;
WHILE i < ARRAY_LENGTH(countries) DO
SET country = countries[OFFSET(i)];
SET table_name = CONCAT('`future-abacus-432804-b6.test.ads_', LOWER(country), '`');
-- Create or replace table for each country
EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE %s AS
-- This list of metrics should be expanded
SELECT ad_id, ad_creation_time, r.region_code
FROM `bigquery-public-data.google_ads_transparency_center.creative_stats`,
UNNEST(region_stats) AS r
WHERE r.region_code = '%s'
""", table_name, country);
SET i = i + 1;
END WHILE;
ENDImport from BigQuery to local database:
table_ids = <<A list of Created Country table>>
def download_semantics():
# Path to your service account key JSON file
service_account_path = "secret.json"
# Initialize client
client = bigquery.Client.from_service_account_json(service_account_path)
# Define project, dataset, and table
project_id = <<PROJECT>>
dataset_id = <<DATASET>>
# Construct a full table reference
for table_id in table_ids:
table_ref = f"{project_id}.{dataset_id}.{table_id}"
# Query the table (example: select all rows)
query = f"SELECT * FROM `{table_ref}`"
# Run the query
query_job = client.query(query)
df = query_job.to_dataframe()
conn = sqlite3.connect("transparency.db")
df.to_sql(f"{table_id}",conn,if_exists="replace",index=False)
print(f"Data saved to 'word_storage.bd' database table {table_id}")
conn.commit()
conn.close()
The Meta Ad Library API offers a Python library, which makes it easy to process programmatically. However, there is one important limitation: the Meta API doesn’t return data unless you provide a search term. This means you cannot generate a complete list of all ads shown in a region within a given date range—you are required to specify a search term.
Import from Meta Ad Library:
import requests
import pandas as pd
ACCESS_TOKEN = "YOUR_ACCESS_TOKEN"
BASE_URL = "https://graph.facebook.com/v17.0/ads_archive"
params = {
"access_token": ACCESS_TOKEN,
"ad_reached_countries": "FR", # France
"fields": "id,page_id,ad_snapshot_url,ad_creation_time",
"ad_type": "POLITICAL_AND_ISSUE_ADS",
"search_terms": "election",
"limit": 10
}
response = requests.get(BASE_URL, params=params)
data = response.json()
# Convert ads into DataFrame
ads = data.get("data", [])
df = pd.DataFrame(ads)
print(df.head())
Data from both the Google Ads Transparency Center and the Meta Ad Library API can be pushed to BigQuery for further analysis. At least, that’s how the Upwork user planned to use it. In my opinion, without applying machine learning, this data won’t be very insightful because it’s too large to process effectively. Still, it could be useful if you build a model that classifies whether a business belongs to your client or not, and then use it to analyze the competitive ad landscape.
If you want to review your competitors’ current ads, you can also use SerpAPI—a paid tool that scrapes Search Results and the Meta Ad Library.