How to Create a Multi-Client Marketing Report in BigQuery

If you manage marketing for multiple clients, BigQuery lets you consolidate all client data into one database and build reports that work for every client from a single data source. Instead of building separate pipelines and dashboards for each client, you build one system that scales.

Here is how to set it up.

The Multi-Client Challenge

Most marketing agencies manage 10, 20, or 50 clients. Each client has their own Meta Ads account, their own Google Ads account, and their own reporting requirements.

Without BigQuery, the typical workflow is:

Log into each client’s ad platform.

Export data manually or connect each platform to Looker Studio separately.

Build a separate dashboard for each client.

Update each dashboard manually or maintain separate data connections.

This does not scale. As you add clients, the time spent on reporting grows linearly. BigQuery solves this by centralizing all client data in one place with a client identifier on every row.

Two Approaches to Multi-Client Data in BigQuery

There are two ways to organize multi-client data in BigQuery.

Approach 1: One table per client. Each client gets their own dataset and tables. You have a “client_a” dataset and a “client_b” dataset, each with their own Meta Ads and Google Ads tables. This keeps client data completely separate and makes it easy to give each client access to only their own data.

Approach 2: One shared table for all clients. All client data lands in a single table with an “account_name” or “client_name” column that identifies which client each row belongs to. This makes cross-client analysis easier but requires careful access control if clients view their own data.

For most agencies, Approach 1 (separate datasets per client) is the safer choice for data privacy. For internal agency analysis, Approach 2 (shared table) is more convenient.

Step 1: Set Up Multi-Client Data Pipelines With Porter Metrics

In Porter Metrics, create a new query for your first client. Select their data sources (Meta Ads, Google Ads, etc.) and authenticate with their ad account credentials.

Set the BigQuery destination to your agency project. For Approach 1, create a dataset named after the client (for example, “client_a_ads”). For Approach 2, use a shared dataset name (for example, “all_clients_ads”) and include an account name column in your metrics selection.

Repeat this process for each client. Each client gets their own Porter query with their own schedule.

Porter loads each client’s data into the correct dataset and table automatically every day.

Step 2: Build a Cross-Client View in BigQuery

For Approach 2 (shared table), your cross-client analysis is straightforward. You query the shared table and group by client name.

SELECT
account_name AS client,
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.all_clients_meta_ads
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND CURRENT_DATE()
GROUP BY client
ORDER BY total_spend DESC

This returns one row per client with their total spend, impressions, conversions, and CPA for the last 30 days. You see all clients in one table.

For Approach 1 (separate datasets), you use UNION ALL to combine data from multiple client datasets:

SELECT ‘Client A’ AS client, SUM(spend) AS total_spend FROM client_a_ads.meta_ads WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

UNION ALL

SELECT ‘Client B’ AS client, SUM(spend) AS total_spend FROM client_b_ads.meta_ads WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

UNION ALL

SELECT ‘Client C’ AS client, SUM(spend) AS total_spend FROM client_c_ads.meta_ads WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

ORDER BY total_spend DESC

Step 3: Build Client-Specific Dashboards in Looker Studio

In Looker Studio, you build one report template and use data filters to show each client only their own data.

Connect Looker Studio to your BigQuery shared table. Add a filter control for the “client” or “account_name” column. When a client opens the report, they see only their data because the filter is pre-set to their account.

Alternatively, you use Looker Studio’s “Report filters” feature to create separate report views for each client from the same data source, without duplicating the report.

This means you maintain one Looker Studio report template, not 50 separate reports. When you update the template, all client reports update automatically.

Step 4: Automate Everything

With Porter Metrics running on a daily schedule for all clients and Looker Studio connected to BigQuery, your multi-client reporting is fully automated.

Every morning, Porter loads fresh data for all clients into BigQuery. Every client’s dashboard shows updated numbers without any manual work from your team. As you add new clients, you create a new Porter query, add them to your BigQuery project, and their data starts flowing automatically.

This is the infrastructure that lets a small agency team manage reporting for 50 clients without adding headcount.