How to Create a Social Media Report in Google BigQuery

A social media report in BigQuery consolidates your Meta Ads, TikTok Ads, and LinkedIn Ads data into one place. You see total reach, engagement, spend, and conversions across all social platforms in a single query, without switching between platform dashboards.

Here is how to build it.

What a BigQuery Social Media Report Covers

A social media report in BigQuery typically includes:

• Reach and impressions: how many people saw your content across each platform.
• Engagement: clicks, video views, reactions, and shares.
• Spend: total investment per platform and combined.
• Conversions: leads, purchases, or other conversion events attributed to social.
• CPA and ROAS: efficiency metrics across all social channels.
• Creative performance: which ad formats and creatives drive the best results.

All of this data comes from your BigQuery tables, which you populate using Porter Metrics.

Step 1: Connect Your Social Media Platforms to BigQuery

In Porter Metrics, create a new query and select your social platforms as data sources: Meta Ads, TikTok Ads, and LinkedIn Ads.

Select these dimensions and metrics:

• Date
• Platform
• Campaign name
• Ad set name (for granular analysis)
• Spend
• Impressions
• Reach (where available)
• Clicks
• Video views (where available)
• Conversions
• Conversion value

Set your write mode to “update” and schedule daily syncs. Porter blends the three platforms into one BigQuery table with consistent column names.

Step 2: Query Social Media Performance by Platform

Once your data is in BigQuery, run this query to see performance by platform:

SELECT
platform,
SUM(spend) AS total_spend,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
SUM(conversions) AS total_conversions,
ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM your_dataset.social_media
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY platform
ORDER BY total_spend DESC

This gives you one row per platform with all key metrics for the last 30 days. You see at a glance which platform is driving the most conversions and at what cost.

Step 3: Analyze Campaign Performance

To drill into campaign-level performance across all social platforms:

SELECT
platform,
campaign_name,
SUM(spend) AS total_spend,
SUM(impressions) AS total_impressions,
SUM(conversions) AS total_conversions,
ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM your_dataset.social_media
WHERE date BETWEEN ‘2026-02-01’ AND ‘2026-02-28’
GROUP BY platform, campaign_name
ORDER BY total_spend DESC

This shows every campaign from every social platform in one table, sorted by spend. You identify which campaigns are most efficient and which are underperforming across all platforms in a single view.

Step 4: Track Trends Over Time

A trend view shows how your social media performance changes day by day:

SELECT
date,
SUM(spend) AS total_spend,
SUM(impressions) AS total_impressions,
SUM(conversions) AS total_conversions
FROM your_dataset.social_media
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY date
ORDER BY date ASC

This query produces a daily time series that you visualize as a line chart in Looker Studio. You see spend and conversion trends over time and spot patterns or anomalies quickly.

Step 5: Build Your Social Media Dashboard in Looker Studio

Connect Looker Studio to your BigQuery social media table. Build a report with:

A date range selector at the top.

Scorecards for total spend, total impressions, total conversions, and blended CPA.

A bar chart comparing spend by platform.

A table showing campaign performance sorted by CPA ascending (most efficient first).

A line chart showing daily spend and conversion trends.

Your dashboard loads in seconds because the data comes from BigQuery, not from live platform APIs.

Automating Your Social Media Report

Once your Porter Metrics pipeline runs daily and your Looker Studio dashboard is connected to BigQuery, your social media report updates automatically every morning. You share one link with your team and clients. Everyone sees the same fresh data from all platforms.