Build a Real-Time Marketing Dashboard: Pull 5 Ad Platforms Into One Looker Studio Report

The Reporting Problem That Costs You 4 Hours Every Week

Every Monday morning, same ritual. Open Meta Ads Manager. Export. Open Google Ads. Export. LinkedIn, TikTok, Microsoft Ads—export, export, export. Then spend the next three hours copy-pasting into a spreadsheet, fighting date format mismatches, and trying to make a pivot table that doesn't lie to you.

I did this for years across accounts spending $50M+. Not because I wanted to—because there was no dashboard that pulled everything together without paying $300/month for a connector that still needed babysitting.

So I built one. A Python pipeline that pulls data from Meta Ads, LinkedIn Ads, TikTok Ads, and Microsoft Ads via their APIs, normalizes everything into a single schema, writes to BigQuery, and feeds a Looker Studio dashboard. Google Ads connects natively. The result: reporting time dropped from 4 hours a week to near-zero. Not "reduced." Near-zero. I open the dashboard, it's already updated.

This article shows you exactly how to build the same thing.

The Architecture: Hybrid by Design

Most guides push you toward one of two extremes: all-in on a paid connector like Supermetrics, or build everything from scratch. I went hybrid, and it's the right call for anyone managing real budgets across multiple platforms.

Why Not One Approach for Everything?

Google Ads has a native Looker Studio connector. It's free, it's fast, and it works. There's zero reason to route Google Ads data through Python and BigQuery when Looker Studio can pull it directly. That would be over-engineering—the exact thing I hate.

But Meta, LinkedIn, TikTok, and Microsoft Ads? No free native connectors. You either pay for Supermetrics (roughly $39–299/month depending on the plan—pricing changes frequently, verify current rates), build a custom pipeline, or keep exporting CSVs like it's 2018.

Here's the architecture:

Meta Ads API  ──────────────│
LinkedIn Ads API ────────────│
TikTok Ads API ──────────────│
Microsoft Ads API ────────────│
                       ▼
              Python Script
           (API Pull + Normalize)
                       ▼
                  BigQuery
               (unified_ads)
                       ▼
              Looker Studio  ◄── Google Ads (native connector)
                Dashboard

The flow: Python scripts pull from the four non-Google APIs, normalize the data into a single schema using pandas, and write to a BigQuery table called unified_ads. Looker Studio connects to BigQuery for those four platforms and uses its native Google Ads connector for the fifth. A blend inside Looker Studio merges both sources on date.

This isn't theoretical. This is the exact pipeline I run.

The Normalization Schema: One Table to Rule Them All

Every ad platform calls the same things by different names. "Spend" is spend in Meta, cost in Google, revenue in LinkedIn. Conversions might be actions here and conversions there. If you don't normalize, you're building five dashboards and pretending it's one.

Here's the platform-agnostic schema I use:

Column          | Type     | Description
-----------------------------
date            | DATE     | Performance date (YYYY-MM-DD)
platform        | STRING   | meta, google, linkedin, tiktok, microsoft
account_name    | STRING   | Human-readable account name
campaign_name   | STRING   | Campaign name from the platform
spend           | FLOAT    | Total spend in account currency
impressions     | INTEGER  | Total impressions
clicks          | INTEGER  | Total clicks
conversions     | INTEGER  | Primary conversion count
conv_value      | FLOAT    | Conversion revenue/value
currency        | STRING   | 3-letter currency code (USD, EUR, etc.)

That's it. Ten columns. Every platform maps into this. You can add more—CTR, CPC, CPM, ROAS—but those are computed metrics. Store the raw numbers and calculate derived metrics in Looker Studio or SQL.

Pulling Data: Python Snippets That Actually Run

Let's walk through pulling Meta Ads data and normalizing it. This is the pattern you'll replicate for LinkedIn, TikTok, and Microsoft.

Meta Ads API Pull

You'll need the facebook_business package. Install it with pip install facebook-business. The API version changes—check Meta's developer docs for the current version.

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
import pandas as pd
from datetime import datetime, timedelta

# Initialize the API
ACCESS_TOKEN = "your_long_lived_token"
ACCOUNT_ID = "act_123456789"
FacebookAdsApi.init(access_token=ACCESS_TOKEN)

# Define date range (yesterday)
yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")

# Pull campaign-level stats
account = AdAccount(ACCOUNT_ID)
fields = [
    'spend',
    'impressions',
    'clicks',
    'actions',
    'action_values',
    'date_start',
    'date_stop',
]
params = {
    'time_range': {'since': yesterday, 'until': yesterday},
    'level': 'campaign',
    'time_increment': 1,
}

insights = account.get_insights(fields=fields, params=params)

# Extract conversion counts from nested action arrays
def extract_metric(actions_list, action_type):
    """Pull a specific action type from Meta nested action arrays."""
    if not actions_list:
        return 0
    for action in actions_list:
        if action.get('action_type') == action_type:
            return int(action.get('value', 0))
    return 0

rows = []
for insight in insights:
    spend = float(insight.get('spend', 0))
    impressions = int(insight.get('impressions', 0))
    clicks = int(insight.get('clicks', 0))
    conversions = extract_metric(insight.get('actions', []), 'offsite_conversion')
    conv_value = extract_metric(insight.get('action_values', []), 'offsite_conversion')

    rows.append({
        'date': insight.get('date_start'),
        'platform': 'meta',
        'account_name': 'Your Account Name',
        'campaign_name': insight.get('campaign_name', 'Unknown'),
        'spend': spend,
        'impressions': impressions,
        'clicks': clicks,
        'conversions': conversions,
        'conv_value': float(conv_value),
        'currency': 'USD',
    })

df = pd.DataFrame(rows)
print(df.head())

A few things worth noting:

• Actions are nested. Meta returns conversions as an array of {action_type, value} objects. You have to filter by type. Replace 'offsite_conversion' with whatever action type matches your primary conversion.
• Token management. Long-lived tokens expire. You'll need a refresh flow or a system user token for production. This is the part most tutorials skip. Meta's system user tokens don't expire if you use them with a Business Manager—set that up from day one.
• Rate limits. Meta's Marketing API has per-account rate limits. If you're pulling data for 20 accounts, stagger your requests. I add a 2-second sleep between accounts and haven't hit limits.

Normalizing with Pandas

Once you've pulled data from all four platforms into separate DataFrames, the normalization step is straightforward. Here's how I combine them:

# Assume you have: df_meta, df_linkedin, df_tiktok, df_microsoft
# Each already has the 10-column schema from above

# Stack them into one DataFrame
unified = pd.concat([
    df_meta,
    df_linkedin,
    df_tiktok,
    df_microsoft,
], ignore_index=True)

# Enforce types (API responses can be inconsistent)
unified['date'] = pd.to_datetime(unified['date'])
unified['spend'] = unified['spend'].astype(float)
unified['impressions'] = unified['impressions'].astype(int)
unified['clicks'] = unified['clicks'].astype(int)
unified['conversions'] = unified['conversions'].astype(int)
unified['conv_value'] = unified['conv_value'].astype(float)

# Drop any rows with missing dates (APIs sometimes return garbage)
unified = unified.dropna(subset=['date'])

# Sort for clean inserts
unified = unified.sort_values(['date', 'platform']).reset_index(drop=True)

print(f"Total rows: {len(unified)}")
print(unified.groupby('platform')['spend'].sum())

That's the entire normalization layer. No complex ETL tool. No Airbyte. No Fivetran. Just pandas.

If you're dealing with multiple currencies across accounts, add a currency conversion step before stacking. I use a static monthly rate table in BigQuery rather than hitting a forex API—close enough for marketing dashboards.

Writing to BigQuery

With the unified DataFrame ready, writing to BigQuery takes three lines:

from google.cloud import bigquery

client = bigquery.Client(project="your-gcp-project")
table_id = "your-gcp-project.marketing_data.unified_ads"

# Append new data (don't overwrite -- your table has history)
job = client.load_table_from_dataframe(unified, table_id, job_config=bigquery.LoadJobConfig(
    write_disposition="WRITE_APPEND",
))
job.result()  # Wait for completion
print(f"Loaded {len(unified)} rows to {table_id}")

Important: Use WRITE_APPEND, not WRITE_TRUNCATE. You're adding yesterday's data each day, not replacing the whole table. To prevent duplicates, add a deduplication query that runs after the insert:

DEDUP_QUERY = """
CREATE OR REPLACE TABLE `your-gcp-project.marketing_data.unified_ads` AS
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY date, platform, account_name, campaign_name
           ORDER BY spend DESC
         ) AS row_num
  FROM `your-gcp-project.marketing_data.unified_ads`
)
WHERE row_num = 1
"""

client.query(DEDUP_QUERY).result()

This keeps only one row per date/platform/account/campaign combination. Run it after every insert and you'll never have duplicates, even if the script accidentally runs twice.

BigQuery Cost Reality Check

BigQuery pricing depends on storage and query volume. For a marketing dashboard pulling daily data across 5–20 accounts, you're looking at a table with maybe 50,000–200,000 rows after a year. Storage cost: under $1/month. Query cost: negligible if you're not running massive scans. The free tier (1 TB of querying per month) likely covers you. Don't let BigQuery pricing scare you—it's cheaper than a Supermetrics subscription by an order of magnitude at any real volume. But verify current pricing on Google's pricing page since it changes.

The Other Three Platforms: What to Watch For

I showed Meta in detail. Here's what's different about the remaining three.

LinkedIn Ads API

LinkedIn uses OAuth 2.0 three-legged auth. It's the most annoying setup of the five. You need a LinkedIn Developer Application, and the access tokens expire every 60 days. For production, implement a token refresh flow that runs on a schedule. The API endpoint for ad analytics is /v2/adAnalyticsV2—you'll query it with your campaign URNs and specify fields like impressions, clicks, costInLocalCurrency, and conversions. The response format is different from Meta's—LinkedIn returns rows with pivot values you specify (e.g., pivot by CAMPAIGN). Map costInLocalCurrency to your spend column. LinkedIn's API documentation is decent but the auth flow is where people get stuck. Budget a full afternoon for it.

TikTok Ads API

TikTok's Reporting API is simpler than you'd expect. Authenticate with an access token, then hit the /report/integrated/get/ endpoint. You specify dimensions (like "campaign_name"), metrics (like "stat_cost", "impression", "click", "conversion"), and a date range. The stat_cost field maps to spend. TikTok's rate limits are generous compared to Meta—I haven't hit them pulling daily data for a handful of accounts. The main gotcha: TikTok's API returns data in the advertiser's timezone, not UTC. If you're blending with other platforms, make sure all dates align on the same timezone or you'll get mismatched rows.

Microsoft (Bing) Ads API

Microsoft Advertising uses the Bing Ads API (now called Microsoft Advertising API). You authenticate via OAuth and use the Reporting service to request a report, then poll for completion and download the result. It's a two-step process—submit a report request, then download it when ready. The Python library bingads handles this, but it's less polished than Meta's or Google's SDKs. The key fields: Spend, Impressions, Clicks, Conversions, and Revenue (which maps to conv_value). Microsoft's API is the slowest of the five—report generation can take a few minutes. Schedule your pipeline to request the report, wait, then process. Don't try to make it synchronous.

Scheduling the Pipeline

The Python scripts need to run daily. I use a cron job on a small VM, but you could also use Cloud Functions or Cloud Run on GCP since you're already in the Google ecosystem for BigQuery.

A simple cron schedule:

# Run at 7 AM UTC daily (gives platforms time to finalize yesterday's data)
0 7 * * * cd /home/user/ad-pipeline && /usr/bin/python3 run_all_platforms.py >> /var/log/ad-pipeline.log 2>&1

The run_all_platforms.py script imports each platform's pull function, runs them sequentially, normalizes, writes to BigQuery, and deduplicates. If one platform fails (API timeout, token expired), the others still succeed because they run independently. Log everything. When a token expires—and it will—you'll see it in the logs immediately.

Connecting Looker Studio

Now the fun part: making the data visible.

Step 1: Connect BigQuery

In Looker Studio, add a data source and select BigQuery. Point it at your unified_ads table. Looker Studio will auto-detect the columns and types. Verify that date is recognized as a Date type and numeric fields are set to Number.

Step 2: Connect Google Ads Natively

Add a second data source using the Google Ads connector. Select your account and the same date range. Pull the same core metrics: Date, Campaign, Cost, Impressions, Clicks, Conversions, Conversion Value.

Step 3: Blend the Data

This is where it comes together. Looker Studio's blend feature lets you merge two data sources on a shared key. Your blend key is date.

1. Create a blend with both data sources.
2. Set the join operator to Outer—you want all rows from both sources, even if one platform has no data on a given date.
3. Map the date fields together.
4. Rename columns so they're consistent: Cost from Google Ads becomes spend, matching your BigQuery schema.

Watch out: Looker Studio has a 5-source blend limit. With BigQuery as one source (containing 4 platforms) and Google Ads as the second, you're at 2 sources. Plenty of headroom. If you tried connecting each platform individually, you'd hit the limit immediately. That's another reason the BigQuery normalization layer matters—it collapses 4 sources into 1.

Step 4: Build the Dashboard

With the blended data source, build these core widgets:

• Scorecards: Total Spend, Total Conversions, Blended ROAS (conv_value / spend), Blended CPL (spend / conversions)
• Time series: Spend by platform over time (stacked or line), Conversions by platform over time
• Pivot table: Platform x Date with spend, clicks, conversions, ROAS
• Bar chart: Spend by platform for the selected date range

Add a date range control at the top so you (or your clients) can filter by any period. Add a platform dropdown control for drilling into one platform at a time.

For distribution: Looker Studio supports scheduled email reports. Set up a weekly email to stakeholders every Monday at 8 AM. That's the "near-zero" part—you don't even open the dashboard unless something looks off.

Why This Beats Supermetrics and Funnel

I'm not anti-paid tools. If you have zero Python experience and no developer on the team, Supermetrics or Funnel are reasonable starting points. But here's the reality at scale:

Cost. Supermetrics ranges from roughly $39 to $299/month depending on the plan and number of sources (verify current pricing—they change it often). Funnel starts at significantly more. My BigQuery costs for the same data? Under $5/month. At $50M in ad spend across dozens of accounts, that gap compounds fast. The Python+BigQuery pipeline pays for its development time within a month or two of saved subscription fees.

Control. When Supermetrics changes a field name or deprecates a connector, your dashboard breaks and you wait for their fix. When I control the pipeline, I fix it in 20 minutes. I've had this happen with Meta API changes—my custom script was updated the same day. The Supermetrics users in my network waited a week.

Flexibility. Need to add a computed column? Want to join ad data with CRM data? Need to filter by a custom logic that Supermetrics doesn't support? With your own pipeline, you just write the code. With a paid connector, you're filing a feature request.

The one honest tradeoff: setup time. Building this pipeline takes 8–20 hours depending on your Python comfort level. Supermetrics takes 30 minutes. If you're managing $5k/month in ad spend, the subscription is fine. If you're managing $50k+/month or multiple clients, the custom build wins within weeks.

For more on this decision, see our Build vs Buy vs Automate framework.

The "Real-Time" Caveat

Let me be honest about what "real-time" means here. Your dashboard updates as fast as your pipeline runs. If you schedule the Python script once daily at 7 AM, the data is current as of yesterday. That's not real-time. It's next-day, and for 95% of marketing reporting, that's more than enough.

If you need intraday updates, you can run the pipeline every 4–6 hours. But be aware of API rate limits—Meta and LinkedIn in particular will push back if you're hitting their endpoints too frequently. And some platforms don't finalize their conversion data until the next day anyway. Meta's attribution window means yesterday's numbers can shift slightly for up to 28 days. Don't chase real-time at the cost of accuracy.

For campaigns where you need true intraday monitoring—like a launch or a promotion—use the native platform dashboards. They're built for that. Your Looker Studio dashboard is for the weekly and monthly reporting that eats 4+ hours if you do it manually.

What I'd Do Differently

Two things I learned the hard way:

1. Handle OAuth token refresh from the start. I initially set up manual token refreshes. Every 60 days, LinkedIn would expire and the pipeline would silently fail. I'd notice a week later when the dashboard showed a gap. Automate the refresh flow on day one, not after the first failure.

2. Add error alerts immediately. My pipeline ran silently for months. When it worked, great. When it didn't, I found out from a client asking why their numbers looked wrong. Now I have a simple Slack webhook that fires when any platform pull fails. Five lines of code, saves hours of embarrassment.

import requests

def alert_slack(message):
    webhook_url = "https://hooks.slack.com/services/YOUR/WEBHOOK/URL"
    payload = {"text": f"Ad Pipeline Alert: {message}"}
    requests.post(webhook_url, json=payload)

# Use it in your pull functions
try:
    df_meta = pull_meta_ads()
except Exception as e:
    alert_slack(f"Meta Ads pull failed: {str(e)}")
    df_meta = pd.DataFrame()  # Empty DF so other platforms still load

Getting Started: The 80/20 Version

If the full 5-platform pipeline feels overwhelming, start with two. Meta and Google Ads cover the majority of ad spend for most businesses. Build the pipeline for Meta (Python to BigQuery) and connect Google Ads natively. Get that working, then add the other three one at a time.

The first platform is the hardest—figuring out OAuth, testing the API response, building the normalization. Each additional platform after that is just mapping different field names into the same schema. Maybe 2 hours per platform once the pattern is established.

And if you're running Meta Advantage+ campaigns or scaling AI-generated creative, having this dashboard is even more critical—you need cross-platform visibility to know if your automated campaigns are actually performing or just spending.

The Bottom Line

Manual reporting is a tax on your time that compounds every week. Four hours doesn't sound like much until you realize that's 200+ hours a year spent copy-pasting numbers into spreadsheets. That's five full work weeks.

The Python+BigQuery+Looker Studio pipeline isn't the easiest option. It requires writing code and dealing with OAuth headaches. But it's the option that gives you full control, minimal ongoing cost, and the ability to customize without waiting on a vendor's roadmap.

I cut my reporting from 4 hours a week to near-zero. Not by working faster, but by making the computer do it. That's the whole point.

Frequently Asked Questions

• How do I connect multiple ad platforms to one Looker Studio dashboard?

  Use a hybrid approach: connect Google Ads via Looker Studio's native connector, then pull Meta Ads, LinkedIn Ads, TikTok Ads, and Microsoft Ads via their APIs using Python, normalize the data into a single schema with pandas, write to BigQuery, and connect Looker Studio to that BigQuery table. Blend both sources inside Looker Studio on the date field. This avoids paying for third-party connectors and gives you full control over the data pipeline.

• Can I pull Meta Ads data into Looker Studio without a paid connector?

  Yes. Use the Meta Marketing API (facebook_business Python SDK) to pull campaign-level insights, normalize the data with pandas, write it to BigQuery, and connect BigQuery as a Looker Studio data source. No Supermetrics or paid connector required. The tradeoff is you'll need to write Python and manage OAuth tokens, but the cost savings are significant at scale.

• What's the cheapest way to build a cross-platform ad dashboard?

  A Python + BigQuery + Looker Studio pipeline. BigQuery storage and query costs for typical marketing data volumes run under $5/month. Python scripts are free to write and run. Looker Studio is free. The only costs are your time (8-20 hours to build) and a small VM or Cloud Function to schedule the daily runs. Compare that to Supermetrics at roughly $39-299/month or Funnel at significantly more.

• How do I normalize ad data from different platforms into one schema?

  Create a platform-agnostic table with columns like date, platform, account_name, campaign_name, spend, impressions, clicks, conversions, conv_value, and currency. Each platform maps its own field names to these columns: Meta's 'spend' goes to spend, LinkedIn's 'costInLocalCurrency' goes to spend, TikTok's 'stat_cost' goes to spend, Microsoft's 'Spend' goes to spend. Use pandas to concat all platform DataFrames into one, enforce consistent data types, and write to BigQuery.

• Is Supermetrics worth it vs. building a custom BigQuery pipeline?

  It depends on your volume and technical capacity. If you're managing under $50k/month in ad spend and have no Python experience, Supermetrics saves time and is worth the subscription. If you're managing $50k+/month across multiple clients, the custom pipeline wins on cost (BigQuery is under $5/month vs. roughly $39-299/month for Supermetrics), control (you fix API changes same-day), and flexibility (custom columns, CRM joins, no vendor lock-in). The break-even point on development time is roughly 1-2 months of saved subscription fees.

• How often should I refresh my marketing dashboard data?

  Once daily is sufficient for 95% of reporting needs. Schedule your Python pipeline to run at 7 AM UTC, giving platforms time to finalize the previous day's data. Intraday refreshes (every 4-6 hours) are possible but watch for API rate limits and remember that some platforms don't finalize conversion data until the next day. For launch-day or promotion monitoring, use the native platform dashboards instead.

Related reading: AI Email Personalization Beyond Hi First Name: Real 1:1 Personalization at Scale

Related reading: Local-First Software Is the Future — Here's How I Built One With Tauri, SQLite, and CRDTs

References

• How to Connect Multiple Data Sources in a Marketing Dashboard
• How to Build Cross-Channel Dashboards in Looker Studio
• How to Build a Marketing Dashboard in Google Looker Studio: Step-by-Step Tutorial
• Automated Reporting Dashboards for Ads: Looker Studio, Custom Dashboards & Scheduled Reports
• Template Results Paid Ads for Google Data Studio
• Agency Client Reporting: Automate Dashboards in Sheets & Looker Studio