How to Create a PPC Report in Google BigQuery

A PPC report in Google BigQuery gives you a single view of all your paid advertising performance across Meta Ads, Google Ads, TikTok, and LinkedIn. Instead of logging into each platform separately, you query all your ad data in one place and build a report that shows the full picture.

Here is how to build it step by step.

What a BigQuery PPC Report Includes

A PPC report in BigQuery typically covers:

• Spend by platform: how much you spent on Meta, Google, TikTok, and LinkedIn in a given period.
• Impressions and clicks: reach and engagement across all platforms.
• Conversions and conversion value: total conversions and revenue attributed to paid ads.
• CPA and ROAS: cost per acquisition and return on ad spend, calculated across all platforms.

Campaign-level breakdown: performance by campaign for each platform.

All of these metrics come from your BigQuery tables, which you populate using Porter Metrics.

Step 1: Load Your PPC Data Into BigQuery

Before you build the report, your ad platform data needs to be in BigQuery. Use Porter Metrics to connect your Meta Ads, Google Ads, TikTok Ads, and LinkedIn Ads accounts.

In Porter, create a new query. Select all four platforms as your data sources. Porter blends them into a single table with consistent column names and pre-calculated blended metrics.

Select these metrics for your PPC table:

Date

Campaign name

Platform (to distinguish which platform each row comes from)

• Spend / Cost
• Impressions
• Clicks
• Conversions
• Conversion value

ROAS (blended, calculated by Porter)

Set your write mode to “update” so your table grows daily without duplicates. Set your schedule to run every morning before your team starts work.

Step 2: Query Your PPC Data in BigQuery

Once your data is in BigQuery, open BigQuery Studio and run queries to build your report.

Total spend and ROAS by platform for the last 30 days:

SELECT
platform,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions,
SUM(conversion_value) AS total_revenue,
ROUND(SUM(conversion_value) / NULLIF(SUM(spend), 0), 2) AS roas
FROM your_dataset.ppc_blended
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY platform
ORDER BY total_spend DESC

This gives you a row for each platform showing total spend, total conversions, total revenue, and ROAS for the last 30 days.

Step 3: Calculate Blended PPC Metrics

The most valuable PPC metrics are the ones that span all platforms. Here is how to calculate them.

Blended ROAS across all platforms:

SELECT
SUM(conversion_value) / NULLIF(SUM(spend), 0) AS blended_roas,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions
FROM your_dataset.ppc_blended
WHERE date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’

Blended CPA across all platforms:

SELECT
SUM(spend) / NULLIF(SUM(conversions), 0) AS blended_cpa,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions
FROM your_dataset.ppc_blended
WHERE date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’

These numbers give you the true blended performance of your paid media investment, not the platform-reported numbers that each channel inflates through attribution overlap.

Step 4: Build the Dashboard in Looker Studio

Connect Looker Studio to your BigQuery PPC table. In Looker Studio, create a new report and select BigQuery as your data source. Navigate to your dataset and select your PPC table.

Build your report with:

A date range control at the top so stakeholders can adjust the time period.

A scorecard for total spend, total conversions, blended ROAS, and blended CPA.

A bar chart showing spend by platform.

A table showing campaign-level performance sorted by spend descending.

A line chart showing daily spend trends over time.

Because the data comes from BigQuery rather than direct platform API connections, your Looker Studio report loads in 2 to 3 seconds instead of 20 to 30 seconds.

Automating Your PPC Report

Once your Porter Metrics pipeline is running on a daily schedule and your Looker Studio report is connected to BigQuery, your PPC report updates automatically every day. No manual exports, no copy-pasting, no waiting.

Your team opens the same Looker Studio URL every morning and sees fresh data from all platforms.