How to Create Custom Metrics in BigQuery With SQL

A custom metric is a calculation you run on your BigQuery table to produce a metric that does not exist as a column in the original data. Your ad platforms give you spend, impressions, clicks, and conversions. Custom metrics let you calculate CPA, ROAS, CTR, and any other formula your team needs.

Here is how to build custom metrics in BigQuery using SQL.

What Is a Custom Metric?

When your marketing data lands in BigQuery, each row contains the raw numbers your ad platform reports: spend, impressions, clicks, conversions, conversion value. These are your source columns.

A custom metric is a formula that combines two or more of those columns to produce a new number. For example:

Cost per acquisition (CPA) = spend / conversions

Return on ad spend (ROAS) = conversion_value / spend

Click-through rate (CTR) = clicks / impressions

Cost per click (CPC) = spend / clicks

Conversion rate = conversions / clicks

None of these are columns in your raw data table. You create them with SQL.

How to Write a Custom Metric in SQL

You define a custom metric in the SELECT part of your SQL query. You write the formula and give it a name using the AS keyword.

Here is a query that calculates CPA from a Meta Ads table:

SELECT
date,
campaign_name,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions,
SUM(spend) / SUM(conversions) AS cpa
FROM your_dataset.meta_ads
WHERE date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY date, campaign_name
ORDER BY cpa ASC

This query returns one row per day per campaign, with total spend, total conversions, and the calculated CPA. Campaigns with the lowest CPA appear first.

A Real Example: Conversion Comparison

A common use case for custom metrics is comparing conversion performance across campaigns. You want to know which campaigns deliver the best CPA and which deliver the worst.

Here is a query that ranks campaigns by CPA for a given month:

SELECT
campaign_name,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions,
ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM your_dataset.meta_ads
WHERE date BETWEEN ‘2026-02-01’ AND ‘2026-02-28’

AND SUM(conversions) > 0

GROUP BY campaign_name

ORDER BY cpa ASC

The NULLIF(SUM(conversions), 0) prevents a division-by-zero error when a campaign has zero conversions. ROUND(…, 2) rounds the CPA to two decimal places.

The result: a ranked list of campaigns by CPA, from most efficient to least efficient. This is the analysis that tells you where to shift budget.

Comparing Metrics Across Platforms

Custom metrics become more valuable when you compare the same metric across different platforms. For example, you want to compare CPA from Meta Ads vs Google Ads.

SELECT
‘Meta Ads’ AS platform,
SUM(spend) AS total_spend,
SUM(conversions) AS total_conversions,
ROUND(SUM(spend) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM your_dataset.meta_ads
WHERE date BETWEEN ‘2026-02-01’ AND ‘2026-02-28’

UNION ALL

SELECT
‘Google Ads’ AS platform,
SUM(cost) AS total_spend,
SUM(conversions) AS total_conversions,
ROUND(SUM(cost) / NULLIF(SUM(conversions), 0), 2) AS cpa
FROM your_dataset.google_ads
WHERE date BETWEEN ‘2026-02-01’ AND ‘2026-02-28’
ORDER BY cpa ASC

This query returns two rows: one for Meta Ads and one for Google Ads, each with total spend, total conversions, and CPA. You see at a glance which platform delivers a lower CPA.

Saving Custom Metrics as Views

If you use the same custom metric formula in multiple reports, save it as a BigQuery view. A view is a saved SQL query that you reference like a table. Every time you query the view, BigQuery runs the underlying SQL and returns the calculated result.

To create a view in BigQuery Studio:

Write your query with the custom metric formulas.

Click “Save” and select “Save as view.”

Name the view (for example, “campaign_cpa_monthly”).

Reference the view in future queries: SELECT * FROM your_dataset.campaign_cpa_monthly.

Views keep your custom metric formulas in one place. When you update the formula, every report that references the view automatically uses the updated calculation.

Custom Metrics Without SQL: Porter Metrics

If you want custom metrics in your BigQuery table without writing SQL, Porter Metrics lets you define custom formulas in the interface. You build the formula using the available columns, and Porter loads the result as a column in your BigQuery table.

This is useful for teams that want consistent metric definitions across all their tables without relying on every analyst to write the same SQL formula correctly.