Looker Studio Competitor Ads Dashboard: API to Charts

Your boss doesn't want another login. That single fact decides more reporting decisions than any feature matrix, and it's the reason a Looker Studio competitor ads dashboard beats pitching yet another intel subscription. Looker Studio is free, it already hosts your performance reports, and stakeholders trust the format on sight. Feed it competitor ad data through an API and the monthly intel deck starts assembling itself.

TL;DR: Pull competitor ads from the AdLibrary API on a nightly schedule, land the rows in Google Sheets (small watchlist) or BigQuery (agency scale), and point Looker Studio at that source. Build six charts: creative velocity, format mix, platform split, top advertisers, longevity distribution, and a new-ads timeline. Set a nightly refresh, share one link, and retire the screenshot folder.

Why Looker Studio for Competitor Ad Reporting

Dedicated competitive intelligence platforms have a habit of dying in procurement. The demo lands, everyone nods, and then someone asks who else needs seats, what the annual commitment looks like, and whether the data exports into the deck format leadership actually reads. Three weeks later the team is back to screenshots.

Looker Studio skips that fight entirely. It costs nothing, it lives inside the Google account your company already runs on, and sharing is a URL with viewer permissions. Your CMO opens it the same way she opens the GA4 report. Scheduled email delivery drops the dashboard into her inbox on the first Monday of the month without anyone exporting a thing.

The catch: Looker Studio has no native source for competitor ads. It connects beautifully to your own performance data, which is why the own-performance Meta ads build in Looker Studio is such a common first project. Competitor data is a different problem. Meta's Ad Library shows ads one manual search at a time, and its free API carries hard limitations for commercial research. To chart what rivals are running, you need a programmatic source, meaning an ad library API that returns structured JSON you can store and visualize.

That's the build this guide covers end to end: a competitive intelligence layer inside the reporting tool your stakeholders already trust. Think of the Looker Studio competitor ads dashboard as the intel counterpart to the performance dashboard you've already shipped, same tool, same sharing model, different question.

The Architecture: API to Sheets or BigQuery to Looker Studio

Three layers, each replaceable without touching the others.

1. Source. The AdLibrary API returns competitor ads as JSON, with creative URLs, copy, platform, format, engagement, impressions, runtime, and a 0–1000 heat score. One key covers Facebook, Instagram, TikTok, YouTube, Google, LinkedIn, Twitter, Pinterest, and more through multi-platform coverage.
2. Storage. A scheduled job calls the API and appends rows to Google Sheets or BigQuery. This layer is what makes the dashboard fast and free to view.
3. Presentation. Looker Studio connects to storage through a native connector and renders the charts.

The tempting shortcut is to skip storage and wire Looker Studio straight to the API through a community connector. Don't. Viewer interactions in Looker Studio can trigger fresh data fetches, and an API that bills one credit per search is the wrong thing to put behind a filter control an intern will click forty times before lunch. Land the data once per night, then let Looker Studio query the stored copy at zero marginal cost.

Storage also buys you history, and history is the whole product here. Ad libraries show you the present. A cross-platform tracking pipeline that appends nightly gives you the past as well, and half the charts below only exist because of it. A dashboard for Looker Studio competitor ads analysis without history is just a prettier search results page.

Pulling Competitor Ad Data From the AdLibrary API

Authentication is a single header. Keys are created in your dashboard on the Business plan, start with adl_, and travel as a bearer token. No app review, no OAuth dance, no 60-day token expiry to babysit.

The endpoint you'll live in is POST /api/search. The appType parameter is required ("3" for e-commerce), and everything else is optional:

curl "https://adlibrary.com/api/search" \
  -H "Authorization: Bearer adl_your_api_key" \
  -H "Content-Type: application/json" \
  -d '{
    "keyword": "glossier",
    "appType": "3",
    "daysBack": 30,
    "sortField": "-first_seen",
    "pageSize": 100
  }'

Each result carries the fields the dashboard needs: ad_key, advertiser_name, platform, title, body, ads_type, impression, heat, like_count, share_count, first_seen, days_count, geo, and landing_page_url. One credit per search, and a failed search refunds its credit automatically.

For a Sheets-based stack, Apps Script handles ingestion with no server at all:

const COMPETITORS = ["glossier", "the ordinary", "cerave"];

function pullCompetitorAds() {
  const sheet = SpreadsheetApp.openById("YOUR_SHEET_ID")
    .getSheetByName("ads_raw");
  const seen = new Set(
    sheet.getRange("A2:A").getValues().flat().filter(String)
  );
  const key = PropertiesService.getScriptProperties()
    .getProperty("ADL_KEY");

  COMPETITORS.forEach(function (kw) {
    const resp = UrlFetchApp.fetch("https://adlibrary.com/api/search", {
      method: "post",
      contentType: "application/json",
      headers: { Authorization: "Bearer " + key },
      payload: JSON.stringify({
        keyword: kw,
        appType: "3",
        daysBack: 30,
        sortField: "-first_seen",
        pageSize: 100
      })
    });
    const data = JSON.parse(resp.getContentText());

    (data.results || []).forEach(function (ad) {
      if (seen.has(ad.ad_key)) return;
      sheet.appendRow([
        ad.ad_key,
        ad.advertiser_name,
        ad.platform,
        ad.ads_type,
        new Date(ad.first_seen * 1000),
        ad.days_count,
        ad.impression,
        ad.heat,
        ad.like_count,
        ad.share_count,
        (ad.geo || []).join(","),
        ad.landing_page_url
      ]);
      seen.add(ad.ad_key);
    });

    Utilities.sleep(7000); // stay under the 10 requests/minute limit
  });
}

The seen set dedupes on ad_key so re-runs never double-count an ad, and the seven-second sleep keeps a long competitor list inside the rate limit. Prefer Python with retries, pagination, and proper logging? The Python ad library API cookbook has working scripts to lift, and the API access feature page covers every parameter the endpoint accepts.

Field Mapping: From Ad JSON to a Flat Reporting Schema

Looker Studio wants flat, typed columns. The API gives you nested-ish JSON with unix timestamps and numeric format codes. Do the conversion at ingest, in your script, rather than stacking calculated fields inside the report. Calculated fields work, but every transformation in the report slows rendering and silently duplicates when someone copies the template.

Two judgment calls worth making explicitly. First, the API also returns an estimated spend signal on supported platforms. Keep the word estimated in the column header, and sanity-check totals against the ad spend estimator before they reach a slide. If a stakeholder quotes that number as audited spend, the dashboard misled them. Second, store the ad creative preview URL even if you don't chart it. Looker Studio tables can render image columns, and a thumbnail next to a runtime number turns an abstract row into an ad someone recognizes.

If a format code ever arrives unmapped, one defensive calculated field in Looker Studio earns its place:

CASE
  WHEN format_code = 1 THEN "Image"
  WHEN format_code = 2 THEN "Video"
  WHEN format_code = 3 THEN "Carousel"
  WHEN format_code = 4 THEN "Collection"
  ELSE "Other"
END

Sheets or BigQuery: Picking the Right Connector

Start with Sheets unless you already know you'll outgrow it. The native Sheets connector is the most forgiving on-ramp in Looker Studio, and Apps Script time-driven triggers run the pull function nightly with zero infrastructure. A watchlist of five competitors at 12 columns produces a few thousand rows per quarter, which leaves years of headroom under the 10-million-cell ceiling. The full spreadsheet-side build, including Apps Script setup, lives in the Google Sheets competitor ad dashboard guide.

Graduate to BigQuery when any of these become true: you're an agency tracking thirty brands for a client roster, your history crosses a few hundred thousand rows, or you want SQL for dedupe and rollups instead of script logic. BigQuery scheduled queries handle nightly aggregation natively, a date partition on first_seen_date keeps scans tiny, and at this data volume the monthly bill rounds to pocket change. The schema design and the queries worth stealing are in the competitor ad database build.

The decision rule in one line: fewer than ten competitors in one market means Sheets, a client roster or multi-year history means BigQuery. Migrating later is painless because Looker Studio lets you swap the data source on an existing report, and every chart survives the swap as long as your column names stay consistent.

The 6 Charts That Make a Looker Studio Competitor Ads Dashboard Worth Opening

Plenty of intel dashboards die from chart sprawl. Six views answer the questions a Looker Studio competitor ads report actually gets asked in meetings, and each one maps to a specific chart type with a specific configuration.

1. Creative velocity

A time series chart. Dimension: first_seen_date at week granularity. Metric: count distinct ad_id. Breakdown dimension: advertiser.

This is the testing tempo of your market. A competitor launching forty new creatives a week is running an aggressive creative testing program, and a sudden spike usually precedes a product launch or a budget bump you'll feel in the auction two weeks later. A line that goes flat is just as informative. It means a rival is coasting on proven creative or quietly cutting spend.

2. Format mix

A 100% stacked column chart. Dimension: advertiser. Breakdown: format. Metric: count distinct ad_id.

Format share is strategy made visible. When a competitor's mix shifts from 70% static image to 60% video over a quarter, their team made a deliberate production bet, and your creative roadmap should at least acknowledge it. Watch carousel share too. A climbing carousel share among e-commerce rivals often signals a catalog or bundle push.

3. Platform split

A stacked bar chart, one bar per advertiser, segmented by platform. Donut works for a single competitor, but the bar version lets stakeholders compare channel bets side by side.

This chart only exists because the source covers eleven platforms through one endpoint. A rival who looks dormant on Meta while lighting up TikTok and YouTube is repositioning, and a Meta-only view would have told you the comforting lie that they'd gone quiet.

4. Top advertisers

A horizontal bar chart. Dimension: advertiser. Metric: count distinct ad_id filtered to active ads, with summed estimated spend as an optional second metric. Add scorecard tiles above it for total tracked ads, new ads this week, and median runtime.

Sort descending and you have the noisiest players in the category at a glance. It's the chart executives screenshot into their own decks, so label the spend column "estimated" right in the header.

5. Longevity distribution

A column chart over runtime buckets. Build a bucket column at ingest (1–7, 8–30, 31–90, 90+ days) and count distinct ad_id per bucket, broken down by advertiser.

Runtime is the most honest performance signal in competitor data because losers get killed in week one while winners keep earning budget. A fat 90+ bucket means a competitor holds a stable of proven creatives, and those specific long-runners deserve a programmatic winner-detection pass. The same distribution doubles as an early-warning view for creative fatigue, which is exactly how practitioners use competitor longevity signals to diagnose fatigue in their own accounts.

6. New-ads timeline

A table, sorted by first_seen_date descending, with the thumbnail column, advertiser, format, platform, and runtime. Add a date-range control pinned to "last 14 days."

This is the Monday-morning view, the answer to "what changed since the last meeting." Pair it with Slack alerts for new competitor ads and the dashboard stops being something people remember to check and becomes something that pings them.

Refresh Cadence: How Fresh Does Competitor Intel Need to Be?

Nightly. Competitor ad portfolios change daily, not hourly, and a 2 a.m. pull means the Looker Studio competitor ads view is current before anyone in the room has had coffee. Chasing real-time here burns credits to answer questions nobody asked.

Three numbers shape the schedule. The API allows 10 requests per minute and 10,000 per day per key, and each search costs one credit. A nightly pull across eight competitors is eight credits a day, roughly 240 a month, which sits comfortably inside a Business plan's 1000+ monthly credits with room left for AI enrichment on the winners you want briefs for.

The second half of freshness is the connector. Looker Studio caches data per source, and the data freshness setting controls how often that cache refreshes: Sheets sources can refresh as often as every 15 minutes, BigQuery defaults to 12 hours. Set the freshness window to match your pull schedule and stop there. A dashboard that refreshes every 15 minutes against data that lands nightly just spends quota recomputing the same answer.

For the scheduler itself, pick whatever your team already operates. Apps Script triggers cover the Sheets path. A GitHub Actions workflow runs the same pull serverless on cron. Visual-automation teams can build the whole loop in n8n instead. The pattern is identical across all three, and it's the same backbone described in the automated competitor ad monitoring use case: scheduled pull, dedupe on ad_key, append, sleep.

Building the Template: Layout, Filters, and Stakeholder Views

A three-page layout has survived every stakeholder review I've put it through.

Page 1, Market overview. Four scorecards across the top (tracked competitors, active ads, new ads this week, median runtime), creative velocity underneath, top advertisers beside it. This page answers "what's happening" in ten seconds, which is the entire job of page one.

Page 2, Creative detail. Format mix, platform split, and longevity distribution side by side, with the thumbnail table below. This is where your creative strategist lives, and where the agency pitch deck gets its evidence slides.

Page 3, Movers. The new-ads timeline, filtered to the last 14 days, plus a velocity chart restricted to the two fastest-moving competitors. When someone asks for "just the changes," this page is the answer.

Add three controls pinned to every page: a date-range picker, an advertiser drop-down, and a platform drop-down. With those in place one report serves the CMO who wants the quarter and the media buyer who wants last week on TikTok, and nobody files a dashboard request to engineering. If your competitors run distinct campaigns per market, a fourth control on the countries column lets regional leads self-serve their slice without a separate report.

If you publish the file internally as a template, write the description like a product listing, because that text is what makes the next team adopt it instead of rebuilding from scratch. A working pattern: "Competitor ad intelligence across 11 platforms. Page 1: market overview and creative velocity. Page 2: format, platform, and longevity breakdowns with creative thumbnails. Page 3: new ads in the last 14 days. Data refreshes nightly from the AdLibrary API via Google Sheets. To reuse, copy the report and the source Sheet, then add your own competitor keywords to the Apps Script." Forty seconds of writing, and the template stops depending on you for onboarding.

Two delivery details turn the template into a habit. Schedule email delivery for the first Monday of each month so the intel deck arrives without anyone opening Looker Studio. And for agencies, the template duplicates cleanly: copy the report, swap the data source to the next client's sheet or table, and the whole layout carries over. That duplication trick is the backbone of client competitor ad reports that build themselves, and it slots straight into a broader agency reporting system. Budget-context pages pair well with the media mix modeler when clients ask what the intel implies for their own allocation.

What the Build Costs: The Honest Math

The visualization layer is genuinely free. Looker Studio costs nothing at any scale that matters here, Sheets is free, and BigQuery at this volume is negligible. The only real line item is the data source, so it deserves an honest comparison.

Meta's Ad Library API is free, and Meta deserves credit for building it. For its purpose, transparency research on political and social-issue ads, it works. For this dashboard it falls short in three specific ways: commercial ad coverage is limited to the EU and UK, the data is Meta-only, and access requires identity verification plus an access token that expires every 60 days. Google's Ads Transparency Center is browsable for Google ads but isn't built for programmatic pipelines. Neither gets you a cross-platform table that refreshes itself nightly.

The AdLibrary API is the paid power-user upgrade on top of what those free tools started: more data per ad (heat score, runtime, engagement, estimated spend), eleven platforms behind one key, and an integration that's a curl command instead of an app review. API access ships with the Business plan at €329/mo with 1000+ monthly credits, which covers a serious nightly watchlist plus enrichment, and Business includes free integration help, meaning you can hand this article to their team and ask them to wire the pipeline with you. If you want to see the data shape before committing, run a few searches on a lower tier first, then upgrade when you're ready to automate.

Frequently Asked Questions

Can I build a Looker Studio competitor ads dashboard for free?

The visualization layer is free: Looker Studio, the Sheets connector, and Apps Script cost nothing. The constraint is data. Meta's free Ad Library API only returns commercial ads for the EU and UK and covers Meta platforms only, so a free build caps out at a narrow, single-network view. A paid ad library API is what makes the cross-platform, history-keeping version possible.

Should I use Google Sheets or BigQuery as the data source?

Sheets for a watchlist under ten competitors in one market. It's free, the connector is native, and Apps Script handles nightly ingestion. Move to BigQuery when you track a client roster, cross a few hundred thousand rows, or want SQL-based dedupe and rollups. Charts survive the migration if column names stay consistent.

How often should the dashboard refresh?

Nightly. Competitor portfolios shift daily, so a 2 a.m. pull keeps the dashboard current for every morning meeting. Match Looker Studio's data freshness setting to that schedule. An eight-competitor nightly pull costs roughly 240 credits a month, well within a Business plan's 1000+ monthly allowance.

Can I use Meta's free Ad Library API instead of a paid one?

You can, within its limits. It's free and legitimate, but it returns commercial ads only for the EU and UK, covers Facebook and Instagram only, and requires identity verification plus tokens that expire every 60 days. If you need TikTok, YouTube, Google, or LinkedIn in the same dashboard, or performance signals like runtime and heat score, you need a paid multi-platform source.

Which chart convinces stakeholders fastest?

The longevity distribution. Executives understand instantly that ads running 90+ days are earning their budget, and seeing a competitor's stable of proven long-runners next to their own short-lived creatives makes the case for creative investment better than any commentary slide.

Ship Your Looker Studio Competitor Ads Dashboard This Week

The architecture is three layers and none of them are exotic: a nightly API pull, a Sheet or a BigQuery table, and six charts in a free reporting tool your stakeholders already trust. The Apps Script above is a working starting point, the field mapping table removes the schema guesswork, and the creative-strategist research loop picks up where the dashboard leaves off, turning the long-runners it surfaces into briefs.

Start small. One Sheet, three competitors, the velocity chart and the new-ads table. That version takes an afternoon, and it will earn the requests that justify the full build. When the watchlist grows and the manual searches stop scaling, API access on the Business plan at €329/mo turns the whole thing into infrastructure that runs while you sleep. The monthly intel deck stops being a chore you assemble and becomes a link you forward.