In the following guide, we will talk about calculated fields, case statements, and how to use custom dimensions in Google Looker Studio (formerly Google Data Studio). We will also touch upon how a Case statement formula can be used for basic data filtering, as well as advanced calculations when combined with different conditions.

Most importantly, I will provide several example formulas that can supercharge your marketing reporting insights immediately, as well as the use cases for their use.

17 Formulas for custom dimensions and custom metrics for Marketing and SEO Looker Studio Dashboards

1. How to Extract the Brand name from a URL dimension via a custom field

When doing competitor analysis at scale or SERP analysis, it will be very useful to visualize your data in a user-friendly way. One such instance would be to trim the URLs you have ranking for a particular set of queries, down to the brand name, in order to visualize Share of Voice per competitor.

In order to do this, you should create a custom dimension using the formula below, titled ‘brand name’

Here is the formula you need to use for this, as illustrated in the screenshot above. You can replace the URL dimension with any dimension from your data source that contains a URL or web address.

TRIM(REGEXP_EXTRACT(REGEXP_REPLACE(REGEXP_REPLACE(URL, "https?://", ""), R"^(w{3}\.)?", ""), "([^/?]+)"))

2. How to Extract Top-Level Directory or a Subdirectory from a URL dimension via a custom field

Extracting the top-level directory can be something useful, not only when analyzing your own website, but also for doing competitor or SERP analysis. It might be useful to find out exactly how your competitors are structuring their top-level directors in the terms that matter to you, and whether there are some insights to be gained from them.

Here is the formula you need to use for this, as illustrated in the screenshot above. Again, you can replace the URL dimension with any dimension from your data source that contains a URL or web address.

REGEXP_EXTRACT(URL , '^https://[^/]+/([^/]+)/' )

If you instead wish to extract the subdirectories, you can use the formula below.

REGEXP_EXTRACT(Landing Page, '/[^/]+/([^/]+)/' )

Extracting a subdirectory in a custom field can be really useful to replicate a behavior flow chart, track internal links, or even observe the relationships between search and traffic metrics on a larger website. I have found it useful as well when auditing bigger websites, too.

3. How to Create Custom Buckets for Position or Ranking Results via Custom Groupping Dimension

Creating custom buckets for position (or rank) results via a custom group can be a great way to communicate the scale of your ranking keywords or URLs, based on user queries. It’s great for being able to visualize a top-level view of ranking performance.

Here is the formula you need to use for this, as illustrated in the screenshot above. Again, here you can replace the ‘Rank Absolute’ dimension in the formula with the Average position per query (from Google Search Console), or the keyword rank (from Nightwatch Rank Tracker, Ahrefs, or Semrush, or any other equivalent).

case 
when REGEXP_MATCH(Rank Absolute, "1|2|3") then "Page 1: Positions 1 - 3" 
when regexp_match(Rank Absolute, "4|5|6|7|8|9|10") then "Page 1: Positions 4 - 10"
when regexp_match(Rank Absolute, "11|12|13|14|15|16|17|18|19|20") then "Page 2: Positions 11 - 20" 
when REGEXP_CONTAINS(Rank Absolute, "[21-50]") then "Positions 21 - 50" 
when regexp_Contains(Rank Absolute, "[51-100]*") then "Positions 51 - 100"
end

This type of formula will enable you to create visualizations like this one when you have competitor SERP ranking data. The chart below shows eight competitors’ distribution in terms of ranking position, specifically answering the following questions:

• Which competitors have the most URLs ranking in page 1 (positions 1-3), page 1 (positions 4-10), etc.?
• From the collected SERP data, what is the distribution of results collected in each position group (e.g. the smallest number of SERPs are collected for positions 51-100).

How to label average positions in groups with Search Console data

The field from the section above only works if you are using a text field (a dimension, as opposed to metric) for the position – like if you’re using the data from a rank tracker like Nightwatch, SERanking, or Oncrawl. An alternative, if you want to use the Average position metric for Google Search Console data would be to use the metrics dimension to build a custom label formula.

Here’s a sample formula to use with your Average Position metric as part of the Google Search console data connector.

CASE
  WHEN Average Position >= 1 AND Average Position < 3 THEN "Money Keywords - Protect"
  WHEN Average Position >= 3 AND Average Position < 10 THEN "High value Keywords - Prioritize improvements"
  WHEN Average Position >= 10 AND Average Position < 20 THEN "Striking Distance Keywords - Improve"
END

As this will be saved as a calculated metric, not a dimension, you will be a bit limited in terms of the visualizations. I think the best thing to use it within would be a table, as shown below. This is also great for easy exports and manipulation of the data in Google Sheets.

The only limitation here would be that you can’t use this calculated metric in a filter to create a bit of a more cleaner look, but you can only use it in sorting, as shown below. You can also create a boolean custom field for each category, with the logic “if it matches X label, then 1 else 0”, and then apply a tickbox control above the table, so users can enable or disable these categories.

4. How to Group URLs by page section

When you are working with a big website, or a site, operated by a siloed organization where different teams are responsible for different parts of the website, it’s important to be able to split the data into page sections.

This can also be very useful when trying to identify technical issues, such as issues in Core Web Vitals performance, which can sometimes be caused by images used section-wide, or section-specific templates. Check out this filter in action in my Core Web Vitals auditor Looker Dashboard.

Here is the formula for quick copy-pasting below. Of course, replace the ‘Address’ dimension with any that contains a URL, and make sure to only include dimensions in the Regex fields that are contained in the URLs. Some adjusting is needed to ensure this is custom and relevant to your site.

case 
when REGEXP_CONTAINS(Address, "blog") then "Blog Posts"
when regexp_contains(Address, "white-papers|news|case-studies|insights") then "White papers, Case studies, News, Insights"
when regexp_contains(Address, "careers|about-us|contact|job-opportunities|clients|reviews|our-work|our-team|join-team") then "Branded Pages"
when regexp_contains(Address, "hospitality-travel-tourism|expertise|economic-development|energy|higher-education|technology|financial-services") then "Services Pages"
when regexp_contains(Address, "tag") then "Tag Pages"
when Regexp_contains(Address, "/page") then "Paginated"
else "Unclassified"

5. How to Segment URLs based on Language via a Custom Dimension with a Case/When Formula

Often, when you are working with an international website, which has multiple versions of pages in different languages, you might want to report on performance separately.

To do this, you need to create a custom case/when formula that will filter the language versions of URLs in the different languages, providing for them a user-friendly label that you can use in a filter. That way, you can enable your dashboard users to decide which URLs to see in the report.

Here is a screenshot of how this can be achieved below:

Here is the formula in text format. Again, the landing page dimension from the example can be replaced with any Address or URL dimension in your data source. The language versions specifications will, of course, also depend on the site you are reporting on.

CASE 
WHEN CONTAINS_TEXT(Landing Page,"/es/") THEN "Spanish"
WHEN CONTAINS_TEXT(Landing Page,"/fr/") THEN "French"
WHEN CONTAINS_TEXT(Landing Page,"/de/") THEN "German"
WHEN CONTAINS_TEXT(Landing Page,"/it/") THEN "Italian"
else "English" end 

6. How to Label your keywords or the queries you rank for based on search intent

This custom dimension is inspired by my Search Intent Classification dashboard, where I’ve used filters to filter through keywords data and only show those keywords, which match the provided condition.

This, however, is a somewhat limiting approach as it does not show the keywords, which don’t match the specific condition, leaving many of them outside of the visualizations.

Instead, we can create a custom field, using a case/when formula, where we can segment the keywords based on a regex filter, providing the dashboard user with the possibility to display keywords indicating a particular intent, or those that don’t fall into either of the categories.

Bear in mind that this is only the most basic form of this classification, and can be further enhanced (like, a lot!) with specific words that indicate these search intents for your particular industry. Explore linked resources, if you want to read more about how to do search intent classification better, or internationally or in different industries.

And here is the formula for this below:

case
when REGEXP_CONTAINS(Query, "how|why|does|which|when|who|which|guide|tutorial|learn|examples|resource|ideas|tips|difference between|strategies|best|tips|ways|guide|tutorial|resource|ideas|which|when|learn|examples|example|directory|resources|pictures|samples|plans|news|basics|concepts|troubleshoot|issue|resolve|risks|beginners|beginner|upgrade|improve|optimize|case study|get|best practices|templates|template|pros|cons|about|actionable|analysis|articles|ask|audit|avoid|become|beginner’s guide|beginner's|benefits|blogs|build|calculate|campaign|choose|common|consider|cost|create|deal with|decrease|definition|definitive|definite guide|earn|easy|essential|events|explainer video|factors|facts|find|fix|get better|get more|graph|great|grow|hacks|highest rated|hot|ideal|importance of|increase|infographic|information|know|many|meaning|measure|meme|metrics|mistakes|most|myths|opportunity|popular|practices|Prices|questions|quotes|remove|set up|shortcuts|stats|strategy|study|successful|tactics|techniques|terms|that get|things|time|trends|tricks|types|unknown|updates|used|well|tutorials|whether|whose|worst|framework|checklist|process|plan|test|formula|glossary|scripts|sample|survey|exercises|report|certification|webinar|calendar|quiz|directories|form|things to do|to do|design|designs|not|working|spinning|draining") then "Informational Intent"
when regexp_contains(Query, "best|top|vs|review|cheap|comparison|builder|builders|maker|creator|editor|editors|generator|plugin|plugins|services|service|\bapp\b|\bapps\b|estimator|checker|planner") then "Commercial Intent"
when REGEXP_CONTAINS(Query, "Buy|price|cheap|expensive|recommendation|recommended|near me|firm|coupon|order|purchase|pricing|buy|price|cheap|expensive|recommendation|recommended|near me|stores|store\b|\bfirms\b|\bfirm\b|coupon|enterprise|top|calculator|wordpress|chrome extension|purchase|appliance|\bfree\b|affordable|best range|ratings|online|courses|course|appliances|discount|cheapest|for sale") then "Transactional Intent"
when REGEXP_CONTAINS(Query, "brand|brand name variation|www|.com") then "Navigational"
when regexp_contains(Query, "Alabama|AL|Alaska|AK|Arizona|AZ|Arkansas|AR|California|CA|Colorado|CO|Connecticut|CT|Delaware|DE|Florida|FL|Georgia|GA|Hawaii|HI|Idaho|ID|Illinois|IL|Indiana|IN|Iowa|IA|Kansas|KS|Kentucky|KY|Louisiana|LA|Maine|ME|Maryland|MD|Massachusetts|MA|Michigan|MI|Minnesota|MN|Mississippi|MS|Missouri|MO|Montana|MT|Nebraska|NE|Nevada|NV|New Hampshire|NH|New Jersey|NJ|New Mexico|NM|New York|NY|North Carolina|NC|North Dakota|ND|Ohio|OH|Oklahoma|OK|Oregon|OR|Pennsylvania|PA|Rhode Island|RI|South Carolina|SC|South Dakota|SD|Tennessee|TN|Texas|TX|Utah|UT|Vermont|VT|Virginia|VA|Washington|WA|West Virginia|WV|Wisconsin|WI|Wyoming|WY|") then "Localised- US"
END

This formula will enable you to get a lot more detailed (especially when combined with the Brand/non-brand custom dimension (coming up at # 10) with your Google Search Console data reporting, achieving views like this one:

7. How to Create a Custom Frequency Dimension using a Date Field

Often, in your data source, you might have a date dimension, however, this alone can be difficult to capture the scale, specifically when there are certain time-related thresholds, which are data-specific, via which your stakeholders might better understand the data.

Here is an example of this below:

Here is the code snippet for this dimension. You can replace the ‘Last Crawl’ Dimension with any date-type field of your data source, and adjust the labels accordingly.

case 
when DATE_DIFF(TODAY(), Last Crawl) < 30 then "Crawled Last 30 days"
when DATE_DIFF(TODAY(), Last Crawl) < 60 then "Crawled Last 60 days"
when DATE_DIFF(TODAY(), Last Crawl) < 90 then "Crawled Last 90 days"
when DATE_DIFF(TODAY(), Last Crawl) < 180 then "Crawled Last 180 days"
else "Crawled Last 180+ Days"
end

To see this field in action, check out my URL Inspection API Looker Studio Dashboard.

8. How to compare two dimensions from two different datasets via a Custom Dimension in Looker Studio

In this section, we will learn how to use the CASE statement to compare two dimensions from two different datasets in Looker Studio.

Comparative analysis in Looker Studio could become quite complex, especially considering the limitations of data blending. In the following section, I’d like to show you a simple way to compare two datasets, with the aim of uncovering differences between them.

So, in this case, you will have two identical (in terms of structure) datasets, (in the screenshots I’ve used two extracts from Screaming Frog SEO Spider). Both datasets contain the same several fields, with the only difference being that one represents the mobile version of the site and the other represents the desktop version of the site, as viewed by the crawler.

You will need to blend the data first, using the URL address as a join key. We add the two fields, for which we want to compare the entries and name the blended dataset accordingly.

Then, we will create a table, showing the three dimensions side by side. Simply select the table from the visualizations, and add the join key field (in our example — the address) and the two other fields (in our example — the mobile and desktop versions of the site).

Now, for building a custom dimension, we will add a ‘Comparison’ dimension. This is where we will use the CASE formula. Select your table and click on ‘Add a dimension’ to add a new custom dimension to it.

In the formula use the format:

CASE
when Dimension 1 = Dimension 2 then 'text'
else 'other text'
END

In our case the formula would look like this:

CASE
when Meta Robots Mobile = Meta Robots Desktop then 'pass'
else 'fail'
END

This formula will compare the data from the two fields and return the desired text for the provided conditions.

The default aggregation will be COUNT DISTINCT, however, we already know (based on the formula) that we have two distinct values. Instead, we want to change the default aggregation to COUNT. This will allow us to view how many rows have met the given condition and how many have not.

Adding a control with a fixed-size list, using the created dimension is a great way to visualize the data, as well as allow the user to interact with the data on the table by selecting what they want to see. Adding a control with a fixed-size list.

There are some limitations of blending data and creating a custom dimension on a blended data source, such as:

• You cannot compare more than five data sources at once
• this custom dimension cannot be used across multiple visualizations, since it is part of a blended data source
• you cannot add a filter on top of a calculated custom dimension in a blended data source, however this can be easily amended in the case/when formula by making it a bit more advanced

If we want to filter out the null values, where they are aligned between the two data sets as a pass. We will use a CASE statement with an additional is null condition. This is referred to as a logical AND function.

Our formula will look like this:

CASE
  WHEN Meta Robots Mobile is null and Meta Robots desktop is null THEN "pass"
  when Meta Robots Mobile = Meta Robots Desktop then 'pass'
  ELSE "fail"
END

9. How to build a QoQ / YoY comparative analysis report with custom Date Range Filtering

Another use case of this type of blended report is YoY or QoQ reporting, which is explained in this Looker studio sample report, created by Nimantha. Essentially, it’s exactly the same idea with the difference that here, we will be blending 4-5 data sources, limiting via the settings the data dimension for each of them.

The end result can be a pivot table, where you can show a year-over-year difference of a quarterly performance report. In the example below, this is combined with the page section breakdown, discussed in a previous section.

10. How to split keywords to branded and non-branded, using a custom CASE formula

This is a useful formula to have, not only when doing search or website analytics, but in any form of data source analysis that involves a keyword or query dimension. It can also be used in keyword research, to separate navigational queries from other queries.

Make sure to include all brand name variations into your formula. In the example below, you can replace the Keyword dimension with a Query dimension (from Google search Console) or Keyword (from Multi-Channel Funnels report).

case
when REGEXP_CONTAINS(Keyword, 'brandname|brand^|brand name') then "Branded"
when REGEXP_CONTAINS(Keyword, '(not provided)|(not set)|(unavailable)') then "Not specified"
else "Non-Branded"
end

11. How to categorize Titles, Meta Descriptions and H1 headings to show On-Page Quality Labels

In order to achieve this view, we will incorporate three different fields:

• Title Tag Evaluation

CASE
  WHEN Title Length >= 50 AND  Title Length <= 60 THEN "Title - Perfect"
  WHEN  Title Length >= 40
AND  Title Length<= 50 THEN "Title - good"
  WHEN  Title Length < 40 THEN "Title - too short"
  WHEN  Title Length> 60 THEN "Title too long"
END

Note that the formula if you’re doing this in GA4, would be slightly different:

CASE
  WHEN length(Page title) >= 50 AND  length(Page title) <= 60 THEN "Title - Perfect"
  WHEN  length(Page title) >= 40
AND  length(Page title)<= 50 THEN "Title - good"
  WHEN  length(Page title) < 40 THEN "Title - too short"
  WHEN  length(Page title)> 60 THEN "Title too long"
END

Here’s the visualization this can yield.

• H1 Tag Evaluation

CASE
  WHEN H1 Length >= 50 AND H1 Length <= 60 THEN "H1 - Perfect"
  WHEN H1 Length >= 40
AND H1 Length <= 50 THEN "H1- good"
  WHEN H1 Length < 40 THEN "H1 - too short"
  WHEN H1 Length > 60 THEN "H1 - too long"
END

• Meta Description Tag Evaluation

CASE
  WHEN Meta Description Length >= 150 AND Meta Description Length <= 160 THEN "MD - Perfect"
  WHEN Meta Description Length >= 140
AND Meta Description Length <= 150 THEN "MD- good"
  WHEN Meta Description Length < 140 THEN "MD - too short"
  WHEN Meta Description Length > 160 THEN "MD - too long"
END

This type of field and visualization can be very useful when you have incorporated a technical crawl in your Looker Studio Report. Such reports, from tools like Sitebulb, Oncrawl, or Screaming Frog typically include a Title Length, H1 Length, and Meta Description length dimensions, which can be used for this custom field.

This will enable you to review quickly the opportunities from a holistic standpoint for improving on-page elements, but it can also allow you to deep-dive into individual ‘offending pages’ using controls and tables, which will allow you to quickly export all URLs that have a too long/short Meta Description, Title, or H1.

12. How to create custom categories for traffic type and country.

You can do this by using a logical AND function with a CASE statement for additional filtering. This dimension is useful for executive level-reporting and for building page-level or report-level filters for important markets.

CASE
WHEN Country ISO Code = "US" AND Medium = "cpc"
THEN "US - Paid"
ELSE "other"
END 

13. How to visualize free versus paid traffic

You can do this by creating a custom field that makes a distinction between traffic that has been paid for, like CPC traffic, and other traffic.

This is achieved via a custom dimension that checks for inequality between fields. Of course, this can be more granular, if needed. Like adding sponsored backlinks, and such.

CASE WHENMedium != "cpc" THEN "free"
ELSE "paid"
END

14. How to Classify numeric dimension values into discrete buckets

This dimension can be super useful, when trying to build user personas, based on purchasing value in an e-commerce store. By building buckets, you can filter other behavioural and traffic acquisition data for this segment and make better decisions on how to target these users.

CASE
    WHENAmount < 20 THEN "Small"
    WHENAmount >= 20 andAmount < 100 THEN "Medium"
    WHENAmount >= 100 THEN "Large"
END

15. How to create custom labels for YouTube videos, based on the titles and video length

You can do this by using a custom dimension to evaluate a logical AND/OR condition and use a regular expression (REGEXP) match.

CASE
    WHEN REGEXP_MATCH(Video Title, ".*Google Analytics*")
    ANDis_livestream = TRUE
    ORVideo Length > 600
    THEN "GA Livestream or long video"
END

16. How to evaluate a goal parameter

This one evaluates a dimension, based on another custom dimension. Typically useful, when you have a target of sorts.

For example, if you are running a campaign, the aim of which is conversions, you’d be able to create a custom dimension with the target number of conversions, then evaluate the recorded conversions relative to the target.

CASE WHENConversions >=Target Conversion parameter THEN 1
ELSE 0
END

17. How to create a landing page field to blend GA4 and Google Search Console data

For blending data from Google Analytics 4 (GA4) and Google Search Console in Google Data Studio (now Looker Studio), where you need to match the landing page URLs, you’ll create two custom dimensions. There are two ways of doing this:

• append the domain to the GA4 landing page paths, or

In GA4, landing pages are often provided as paths starting with “/”, e.g., /how-to-incorporate-machine-learning-into-your-serp-analysis. To create a custom dimension that prepends a domain to this path, you can use the following formula:

CONCAT("https://lazarinastoy.com", Landing Page)

Assuming Landing Page is the field name in GA4 for the landing page path, this formula combines the domain https://lazarinastoy.com with the landing page path. Make sure to replace "https://lazarinastoy.com" with the actual domain of your website and Landing Page with the exact field name used in your GA4 data source.

• extract the path from the full URLs given by Google Search Console

In Google Search Console, landing pages are given as full URLs, e.g., https://lazarinastoy.com/the-ultimate-guide-to-pytrends-google-trends-api-with-python/. To create a custom dimension that extracts the path from these URLs, you can use the following formula, which also removes the trailing slash, as GA4 stores values without one:

REGEXP_REPLACE(REGEXP_EXTRACT(Landing Page, 'https?://[^/]+(/[^?#]+)'), '/$', '')

Here, Landing Page URL is the field name in the Google Search Console data source for the full URL. This formula uses a regular expression to extract everything after the domain, including the leading “/”. Make sure to replace Landing Page URL with the exact field name used in your Google Search Console data source.

Frequently Asked Questions about Custom Dimensions and CASE/WHEN Formulas

Additional Resources

• Regex Generator Tool by Sam Underwood for Google Looker Studio
• Google’s documentation on calculated fields
• Data Studio for SEOs: Reporting Automation Tips – Weekly SEO With Lazarina Stoy
• (additional) Useful Calculated Fields and Functions in Google Data Studio