How to Create Custom Dimensions in BigQuery With SQL

A custom dimension is a label you assign to rows in your BigQuery table based on conditions you define. Instead of reporting on raw campaign names, you group campaigns into categories, regions, product lines, or any other classification that makes sense for your reporting.

The SQL tool for creating custom dimensions is CASE WHEN.

What Is a Custom Dimension?

Your ad platform gives you dimensions like campaign name, ad set name, country, and device type. These are fixed. You cannot change them in the platform.

In BigQuery, you create new dimensions by writing logic that assigns labels to rows based on the values in existing columns. For example:

You have 20 campaigns with names like “Brand_US_Q1,” “Performance_LATAM_Q2,” “Retargeting_EU_Q1.” You create a custom dimension called “campaign_type” that assigns the label “Brand” to campaigns whose names start with “Brand,” “Performance” to those starting with “Performance,” and “Retargeting” to those starting with “Retargeting.”

Now you report by campaign type, not by individual campaign name. You see total spend and ROAS for each campaign type across all 20 campaigns.

How CASE WHEN Works

CASE WHEN is the SQL syntax for conditional logic. It works like an IF statement in a spreadsheet.

The basic structure:

CASE

WHEN condition_1 THEN ‘label_1’

WHEN condition_2 THEN ‘label_2’

ELSE ‘other_label’

END AS custom_dimension_name

You evaluate conditions from top to bottom. The first condition that matches assigns the label. If no condition matches, the ELSE value is assigned.

Example: Creating a Campaign Type Dimension

You have a Meta Ads table with a campaign_name column. Campaign names follow a convention: they start with the campaign objective (Brand, Performance, Retargeting, Prospecting).

This query creates a campaign_type custom dimension:

SELECT
date,
campaign_name,
spend,
impressions,
CASE

WHEN campaign_name LIKE ‘Brand%’ THEN ‘Brand’

WHEN campaign_name LIKE ‘Performance%’ THEN ‘Performance’

WHEN campaign_name LIKE ‘Retargeting%’ THEN ‘Retargeting’

WHEN campaign_name LIKE ‘Prospecting%’ THEN ‘Prospecting’

ELSE ‘Other’

END AS campaign_type

FROM your_dataset.meta_ads

WHERE date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’

The LIKE operator with a % wildcard matches any campaign name that starts with the specified string. The result includes a new column, campaign_type, with the assigned label for each row.

Example: Creating a Region Dimension

Your campaigns run in multiple countries. You want to group them into regions for reporting.

SELECT
date,
country,
spend,
CASE

WHEN country IN (‘US’, ‘CA’, ‘MX’) THEN ‘North America’

WHEN country IN (‘BR’, ‘CO’, ‘AR’, ‘CL’, ‘PE’) THEN ‘Latin America’

WHEN country IN (‘GB’, ‘DE’, ‘FR’, ‘ES’, ‘IT’) THEN ‘Europe’

ELSE ‘Other’

END AS region

FROM your_dataset.google_ads
GROUP BY date, country, region
ORDER BY date DESC

This assigns each country to a region. You then GROUP BY region to see total spend per region.

Naming Conventions and Why They Matter

Custom dimensions are most useful when your campaign naming follows a consistent convention. If your campaigns are named consistently (for example, “ObjectiveAudienceRegionQuarter”), you extract any part of the name as a dimension using SQL string functions.

Extract the first word from a campaign name:

SPLIT(campaign_name, ‘_’)[OFFSET(0)] AS campaign_objective

Extract the second word:

SPLIT(campaign_name, ‘_’)[OFFSET(1)] AS campaign_audience

This approach works when your team follows a naming convention. If campaign names are inconsistent, you rely on CASE WHEN to manually map each name to a category.

Establishing a naming convention before you start running campaigns saves significant SQL work later. A convention like “Objective_Audience_Region_Quarter” lets you extract four dimensions from a single column.

Saving Custom Dimensions as Views

Like custom metrics, you save custom dimension logic as a BigQuery view. Every report that queries the view gets the custom dimension automatically. You do not rewrite the CASE WHEN logic in every query.

Create the view once, reference it everywhere.

Custom Dimensions in Porter Metrics

Porter Metrics lets you define custom dimensions in the interface without writing SQL. You create a formula that assigns labels based on column values, and Porter loads the result as a dimension column in your BigQuery table.

This is useful for teams that want consistent dimension labels across all their tables without requiring every analyst to write the same CASE WHEN logic.