BigQuery Competitor Ads: Warehouse Schema and Load Pipeline

Your Meta spend, your Google conversions, your GA4 sessions: all of it already lands in BigQuery. Your competitors' ads don't. A BigQuery competitor ads warehouse fixes that gap, and it changes the kind of question you can ask. "What is Brand X doing right now?" stops being a screenshot hunt and becomes a JOIN against tables you already trust.

TL;DR: Build four tables (advertisers, ads, ad_snapshots, ad_enrichments), load them nightly with a Cloud Function that calls an ad library API, partition snapshots by date, and dedup on ad_key with a MERGE. You get share-of-voice, creative velocity, and survival queries sitting next to your own performance data. Storage costs pennies. The API credits are the real line item, and they're predictable.

This guide walks through the full build: warehouse schema, load pipeline, partitioning and dedup strategy, six SQL analyses worth shipping in week one, and the join that puts competitor activity next to your own CPMs. It's written for a data engineer or an analytics-heavy marketer who is comfortable with SQL and has deployed a Cloud Function before.

Why Competitor Ads Belong in Your Warehouse

Ad libraries are browse tools. Meta's Ad Library shows you what's running today, and that's the problem: today. Close the tab and the state of the market evaporates. Nobody at Meta is keeping a history of which competitor creatives appeared in March, scaled in April, and quietly died in May. If you want longitudinal competitive intelligence, you have to record it yourself.

That recording instinct is what separates ad intelligence from ad browsing. A warehouse gives you three things a library tab never will:

1. History. Every snapshot you load is a fact that can't be un-published. Competitors delete losing ads. Your table remembers them.
2. Joins. Competitor launch dates next to your CPM curve. Their creative refresh next to your ad fatigue metrics. One query.
3. Automation. Scheduled queries, alerting, dashboards, agents. Screenshots feed none of these. Rows do.

The pattern here extends the general approach in our guide to building a competitor ad database, but goes BigQuery-native: partitioned tables, MERGE-based dedup, and scheduled queries instead of a generic Postgres setup. A BigQuery competitor ads dataset also inherits everything your warehouse already has, from IAM and Looker Studio connectivity to dbt models and scheduled exports, so nothing about the tooling is new to your team. If you're still deciding whether you need programmatic access at all, start with what an ad library API actually is and come back.

The Source: Where Competitor Ad Data Comes From

You can't warehouse what you can't pull, so be honest about sources first.

Meta's Ad Library API is free and official, and it's the right call if it covers your case. It returns political and social-issue ads worldwide, and all ad types only for the EU and UK, limited to the last 12 months. It requires app review, identity verification, and an access token that expires every 60 days. The full list of constraints is longer, and we cataloged them in Meta Ad Library API limitations. Google publishes its own Ads Transparency Center, and LinkedIn has an ad library too. Stitching all three yourself means three integrations, each with its own auth scheme and response shape.

The AdLibrary API is the paid alternative built for exactly this pipeline. It's not free and it's not a replacement for Meta's transparency tooling. What you pay for: commercial ads across eleven platforms (Facebook, Instagram, TikTok, YouTube, Google, LinkedIn, Twitter, Pinterest, Yahoo, Unity Ads, AdMob) from one endpoint, with performance signals Meta never exposes: estimated spend, a 0–1000 heat score, impressions, and runtime per ad. Auth is a single adl_ bearer key. No app review, no token refresh cron. The trade-off math between the free and paid routes is covered in free vs paid ad library APIs. This guide uses the AdLibrary API in the examples because its response shape maps cleanly onto a warehouse schema.

One search costs one credit and a failed search refunds automatically, which matters for pipeline retry logic: a 500 never burns budget. For a BigQuery competitor ads pipeline specifically, the response shape is the selling point. Every result arrives with ad_key, advertiser name, platform, copy fields, format, engagement counts, impressions, heat, runtime, and geo in one flat JSON object, which loads into a staging table without a transformation layer in between.

The BigQuery Competitor Ads Schema: Four Tables

Resist the one-big-table urge. Competitor ad data has three different change rates: advertisers barely change, ad metadata changes once (at first sight), and performance signals change daily. Model each rate separately, plus one table for AI enrichments. Here's the DDL for a dataset called competitor_ads:

CREATE TABLE competitor_ads.advertisers (
  advertiser_key   STRING NOT NULL,   -- your stable slug, e.g. 'acme-running'
  display_name     STRING,
  domain           STRING,
  platforms        ARRAY<STRING>,     -- where you've seen them run
  tracked_since    DATE,
  priority         STRING             -- 'primary' | 'secondary' | 'watch'
);

CREATE TABLE competitor_ads.ads (
  ad_key            STRING NOT NULL,  -- API-stable id, e.g. 'fb_123456789'
  advertiser_key    STRING,
  advertiser_name   STRING,
  platform          STRING,
  ads_type          INT64,            -- 1 image, 2 video, 3 carousel, 4 collection
  title             STRING,
  body              STRING,
  button_text       STRING,
  landing_page_url  STRING,
  preview_img_url   STRING,
  video_url         STRING,
  geo               ARRAY<STRING>,
  first_seen        TIMESTAMP,
  first_loaded_at   TIMESTAMP
);

CREATE TABLE competitor_ads.ad_snapshots (
  snapshot_date   DATE NOT NULL,
  ad_key          STRING NOT NULL,
  advertiser_key  STRING,
  platform        STRING,
  impression      INT64,            -- treat as a band, not a precise count
  heat            INT64,            -- 0-1000 momentum score
  like_count      INT64,
  share_count     INT64,
  days_count      INT64,            -- runtime in days, the survival signal
  is_active       BOOL
)
PARTITION BY snapshot_date
CLUSTER BY advertiser_key, platform;

CREATE TABLE competitor_ads.ad_enrichments (
  ad_key        STRING NOT NULL,
  analysis      JSON,              -- full creative teardown
  summary       STRING,
  transcript    STRING,
  enriched_at   TIMESTAMP
);

Design notes worth internalizing:

• ad_key is your primary identity everywhere. The API documents it as the stable identifier across calls. Dedup on it, join on it, never on advertiser name plus title.
• ads is write-once per creative. Copy and landing pages rarely change after launch. When they do, the platform issues a new ad, which means a new ad_key, which means a new row. You get slowly-changing-dimension behavior for free.
• ad_snapshots is append-only and dated. One row per ad per load day. This is the table every interesting query hits, which is why it gets the partition and the clustering.
• ad_enrichments is sparse on purpose. AI teardowns cost a credit each, so you only enrich winners. Keeping them separate stops expensive JSON from bloating your hot table. The enrichment workflow itself is covered in AI ad analysis at scale.

The impression comment is not decoration. Impression figures from ad transparency sources are bands, not audited counts, and estimated spend is exactly that: estimated. Build dashboards that say "roughly," because the data does.

The Load Pipeline: Cloud Function Plus Scheduled Query

The pipeline has three stages: pull (Cloud Function hits the API), stage (raw JSON lands in a staging table), merge (a scheduled query promotes staging rows into the model). Keep the stages separate. When something breaks at 3am, you want to know which stage lied.

Here's the core of the Cloud Function. It reads your tracked competitors from the advertisers table, runs one search per advertiser, and streams rows into staging:

import os, time, requests
from datetime import date
from google.cloud import bigquery

API = "https://adlibrary.com/api/search"
KEY = os.environ["ADLIBRARY_API_KEY"]   # adl_... from your dashboard
bq = bigquery.Client()

def pull_competitor_ads(request):
    advertisers = [
        dict(r) for r in
        bq.query("SELECT advertiser_key, display_name "
                 "FROM competitor_ads.advertisers "
                 "WHERE priority IN ('primary','secondary')").result()
    ]
    rows, today = [], date.today().isoformat()

    for adv in advertisers:
        resp = requests.post(
            API,
            headers={"Authorization": f"Bearer {KEY}"},
            json={
                "keyword": adv["display_name"],
                "appType": "3",            # e-commerce vertical
                "daysBack": 30,
                "sortField": "-last_seen",
                "pageSize": 100,
            },
            timeout=60,
        )
        if resp.status_code == 429:        # respect Retry-After, then re-queue
            time.sleep(int(resp.headers.get("Retry-After", "30")))
            continue
        resp.raise_for_status()
        for ad in resp.json()["results"]:
            ad["advertiser_key"] = adv["advertiser_key"]
            ad["snapshot_date"] = today
            rows.append(ad)
        time.sleep(7)   # stay under the 10 requests/minute limit

    errors = bq.insert_rows_json("competitor_ads.staging_raw", rows)
    assert not errors, errors
    return f"loaded {len(rows)} rows"

Operational details that save you a rewrite later:

• Rate limits are 10 requests per minute and 10,000 per day per key. The sleep(7) keeps a sequential loop legal. A 429 response carries a Retry-After header, so honor it instead of hammering.
• Each search costs one credit. Ten tracked competitors, one search each, nightly: 300 credits a month. Budget before you scale the competitor list, and check your balance programmatically via the free /api/credits endpoint.
• Trigger with Cloud Scheduler, nightly at 06:00, so new ads from last night are queryable before standup. If you'd rather not run any infrastructure, the same loop works as a cron job in CI. We published a serverless variant using GitHub Actions for scheduled competitor scans, and a no-code version exists as n8n workflows.
• For brands you track permanently, the API's saved-advertiser route is cheaper than keyword search. Resolve a brand once with the free GET /api/advertisers/search, save it, then POST /api/advertisers/{id}/curate pulls every recent ad across platforms for one credit per session. The cross-platform mechanics are detailed in cross-platform competitor ad tracking.

More Python patterns for this API, including pagination and backoff helpers, live in our Python ad library API cookbook.

Partitioning, Clustering, and Dedup

Three decisions keep a BigQuery competitor ads warehouse fast and cheap for years.

Partition ad_snapshots by snapshot_date. Almost every analysis you'll run scans a window: last 30 days, last quarter, this week versus last. Date partitioning means BigQuery prunes everything outside the window before it reads a byte, so a 30-day share-of-voice query over two years of history scans only 30 partitions. Add a WHERE snapshot_date >= ... filter to every query, and consider require_partition_filter = TRUE on the table so nobody can forget.

Cluster by advertiser_key, platform. Your second-most-common filter after date is "just show me Brand X" or "just Meta." Clustering co-locates those rows inside each partition. It costs nothing to add and quietly cuts scanned bytes on every filtered query.

Dedup with MERGE, keyed on ad_key. The same ad will arrive repeatedly: it matches two tracked keywords, or it appears in both tonight's pull and tomorrow's. The staging-to-model promotion handles this in one statement per table. New creatives insert into ads, and existing ones are left alone:

MERGE competitor_ads.ads AS t
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY ad_key ORDER BY first_loaded_at DESC
    ) AS rn
    FROM competitor_ads.staging_raw
    WHERE DATE(first_loaded_at) = CURRENT_DATE()
  ) WHERE rn = 1
) AS s
ON t.ad_key = s.ad_key
WHEN NOT MATCHED THEN
  INSERT (ad_key, advertiser_key, advertiser_name, platform, ads_type,
          title, body, button_text, landing_page_url, preview_img_url,
          video_url, geo, first_seen, first_loaded_at)
  VALUES (s.ad_key, s.advertiser_key, s.advertiser_name, s.platform, s.ads_type,
          s.title, s.body, s.button_text, s.landing_page_url, s.preview_img_url,
          s.video_url, s.geo, s.first_seen, s.first_loaded_at);

Snapshots get a similar treatment with ROW_NUMBER() over (ad_key, snapshot_date) so a re-run of the Cloud Function never double-counts a day. Wire both statements into a scheduled query that fires 30 minutes after the load, and the model maintains itself.

Six SQL Analyses for Your BigQuery Competitor Ads

The warehouse earns its keep the first time one of these queries changes a decision. All six run on ad_snapshots plus ads, and all six are scheduled-query friendly.

1. Share of voice by active creatives. Who owns the category this week? Count distinct active ads per advertiser and compute each one's share:

SELECT
  advertiser_key,
  COUNT(DISTINCT ad_key) AS active_ads,
  ROUND(100 * COUNT(DISTINCT ad_key)
    / SUM(COUNT(DISTINCT ad_key)) OVER (), 1) AS sov_pct
FROM competitor_ads.ad_snapshots
WHERE snapshot_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND is_active
GROUP BY advertiser_key
ORDER BY sov_pct DESC;

Weight by impression instead of ad count for a reach-flavored share of voice, with the caveat that impression figures are bands rather than audited counts.

2. Creative velocity. How many genuinely new creatives does each competitor ship per week? Velocity spikes precede spend pushes, and a sustained rise usually means a new agency or a new internal mandate:

SELECT
  advertiser_key,
  DATE_TRUNC(DATE(first_seen), WEEK) AS launch_week,
  COUNT(*) AS new_creatives
FROM competitor_ads.ads
WHERE first_seen >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY advertiser_key, launch_week
ORDER BY launch_week DESC, new_creatives DESC;

3. Survival curves. Runtime is the most honest performance signal in public ad data, because losers get killed in week one. Bucket days_count to see what share of each competitor's creatives survive past 30 days. A brand whose ads routinely outlive 60 days has found a durable creative angle worth tearing down.

SELECT
  advertiser_key,
  COUNTIF(max_days >= 30) / COUNT(*) AS pct_past_30d,
  COUNTIF(max_days >= 60) / COUNT(*) AS pct_past_60d
FROM (
  SELECT advertiser_key, ad_key, MAX(days_count) AS max_days
  FROM competitor_ads.ad_snapshots
  GROUP BY advertiser_key, ad_key
)
GROUP BY advertiser_key;

4. Format mix shift. Group ads_type by launch month and advertiser, then compute each format's share with a window function, the same pattern as the share-of-voice query. When a competitor's mix swings from image-heavy to video-heavy quarter over quarter, they're telling you where their conversion data points, without saying a word. The reverse shift is rarer and louder: a brand retreating from video usually has a production-cost problem you can exploit.

5. Heat movers. The heat score is a 0–1000 momentum signal. A week-over-week heat delta query (LAG(heat) OVER (PARTITION BY ad_key ORDER BY snapshot_date)) flags the specific creatives a competitor is scaling right now, which is your shortlist for AI teardown and for the swipe file.

6. Geo expansion. Diff each advertiser's geo arrays month over month with UNNEST. A competitor that suddenly adds DE and FR to a US-only footprint is announcing a market entry months before any press release. Pipe that diff into the launch-detection patterns from detecting competitor campaign launches.

Joining Competitor Ads With Your Own Performance Data

This is the payoff section, and it assumes your own ad spend already lands in the warehouse. If it doesn't yet, fix that first: our guide to pulling your Meta Ads data into BigQuery covers the own-performance side of this exact architecture, and the Fivetran pipeline setup is the managed-connector route. That data is your ads. This warehouse is theirs. The value is in the seam.

The simplest high-value join lines up competitor creative velocity against your auction costs:

WITH their_velocity AS (
  SELECT DATE_TRUNC(DATE(first_seen), WEEK) AS wk,
         COUNT(*) AS competitor_launches
  FROM competitor_ads.ads
  GROUP BY wk
),
our_costs AS (
  SELECT DATE_TRUNC(date_day, WEEK) AS wk,
         SAFE_DIVIDE(SUM(spend), SUM(impressions)) * 1000 AS our_cpm
  FROM marts.meta_ads_daily
  GROUP BY wk
)
SELECT o.wk, t.competitor_launches, ROUND(o.our_cpm, 2) AS our_cpm
FROM our_costs o
LEFT JOIN their_velocity t USING (wk)
ORDER BY o.wk DESC;

When CPMs jump 20% in a week where tracked competitors tripled their launch count, you have an explanation that isn't "the algorithm." Sanity-check the magnitude against a CPM calculator baseline for your vertical, and when a rival's footprint balloons, run their creative count through an ad spend estimator to size what that push is costing them.

Two more joins worth shipping: competitor survival rates against your own creative refresh cadence (are you killing ads faster than the market does?), and category share of voice against your weekly CPA to quantify how much crowding actually costs you.

What the Pipeline Costs to Run

The BigQuery side is nearly free at this scale. A serious setup tracking 20 competitors accumulates a few hundred thousand snapshot rows a year, which is megabytes, not terabytes. BigQuery's free tier covers 10 GiB of storage and 1 TiB of queries per month, and partition pruning keeps scheduled queries scanning kilobytes. The Cloud Function runs for a couple of minutes a day. Expect single-digit euros a month on the Google side, often zero.

The real line item is the data source. On the AdLibrary side, API access ships with the Business plan at €329/mo, which includes 1000+ monthly credits. The nightly loop above (10 competitors, 1 credit each) consumes about 300 credits a month, leaving ample room for AI enrichment of the winners your heat-mover query flags, at one credit per teardown. Free calls (advertiser resolution, credit balance) don't touch the budget. If you're weighing that against engineering time spent scraping, the comparison post on what an end-to-end monitoring build involves does the math honestly.

Pitfalls That Quietly Corrupt the Warehouse

Every one of these comes from a real failure mode in a BigQuery competitor ads build.

• Treating impressions as exact. They're bands. Aggregate them for direction, never for precision, and label dashboard axes accordingly.
• Quoting estimated spend as actual spend. The API calls it an estimate because it is one. Write estimated_spend in the column name so nobody downstream forgets.
• Deduping on advertiser name plus headline. Brands run one headline across eight variants. ad_key exists precisely so you don't invent your own identity logic.
• Skipping the staging table. Merging API responses straight into the model means a malformed pull corrupts history. Staging plus MERGE makes every load idempotent and every incident replayable.
• Unbounded keyword searches. A generic keyword like "running shoes" pulls a whole category into a warehouse meant for tracked competitors. Use keyword with the brand name, or better, saved advertisers via curate, and keep discovery searches in an ad-hoc notebook.
• Forgetting is_active decay. An ad absent from three consecutive pulls is dead. Mark it in a scheduled query, or your share-of-voice numbers will drift upward forever.

Where to Take It Next

A working BigQuery competitor ads warehouse is a foundation, and the extensions are mostly plumbing you already know. Pipe the heat-mover query into Slack alerts so creative strategists see scaling ads the morning they spike. Feed the share-of-voice table into the client reporting workflow if you're an agency. For stakeholders who live in spreadsheets, a Google Sheets dashboard reads the same tables. And the whole loop slots into the broader automated competitor ad monitoring use case, where the warehouse becomes the memory behind agents and alerting.

The build itself is a weekend: four tables, one Cloud Function, two scheduled queries. What you get back is competitor ads as a first-class analytical dataset, where "what are they doing" is answered with a JOIN instead of a tab farm. If the pipeline above is the workflow you've been meaning to build, API access is the missing credential: it ships on the Business plan (€329/mo, 1000+ credits), with integration help included, so the first load job can run this week.

Frequently Asked Questions

How much does it cost to keep competitor ads in BigQuery?

A BigQuery competitor ads warehouse is effectively free on the Google side at this scale: 20 tracked brands store megabytes, well inside the free tier's 10 GiB of storage and 1 TiB of monthly query volume. The real cost is the data source. With the AdLibrary API on the Business plan (€329/mo, 1000+ credits), a nightly 10-competitor pull uses roughly 300 credits a month.

How often should the pipeline pull competitor ads?

Nightly is the sweet spot for most teams. Creative changes meaningfully on a daily cadence, not hourly, and a nightly pull keeps credit spend predictable while catching every launch within 24 hours. Track a fast-moving category or a launch window by raising the cadence for priority = 'primary' advertisers only, and keep the daily 10,000-request key limit in mind.

Can I use Meta's free Ad Library API as the source instead?

Yes, if its coverage fits your case: it returns political and social-issue ads worldwide, and all ad types only for the EU and UK, on Meta platforms only, with app review and 60-day token expiry. The schema in this guide still works, minus heat, estimated spend, and the non-Meta platforms. Many teams start there and switch when they need commercial ads across all major networks.

How do I deduplicate competitor ads in BigQuery?

Key your BigQuery competitor ads tables on ad_key, the stable per-creative identifier the API returns. Land raw responses in a staging table, then promote with a MERGE that inserts only unmatched ad_key values into ads and applies ROW_NUMBER() over (ad_key, snapshot_date) for snapshots. Loads become idempotent, so re-running a failed night never double-counts.

Can I join competitor ad data with my own ad performance data?

That's the main reason to build this in BigQuery rather than a standalone tool. Once your own spend lands in the warehouse via a connector or the platform APIs, you join on date or week: competitor creative velocity against your CPM, category share of voice against your CPA, their survival rates against your refresh cadence. Each join turns a market anecdote into a measured input.