How to create dimensions with calculated fields in Looker Studio

We have seen how to create calculated fields in Looker Studio and focused on metrics. This was to make it easier to understand by starting with them. Now we will do the same with dimensions.

As a reminder, the difference between a dimension and a metric is that the former are categories (country, product type, movie genre…) and metrics are quantitative values (population, sales, revenue…).

To create dimensions, it’s important first to understand what regular expressions are, some of the most commonly used ones, and examples. Now we will put them into practice by creating custom fields.

Remember, to add a calculated field to the data source, you need to go to ResourceManage added data sourcesEdit in the data source where you want to create the field. In the top right corner, you will find Add FieldAdd a calculated field, and here is where you can create it. You can read more about how to create new fields in the post about custom metrics in Looker Studio.

Let’s jump straight into the examples!

1. Images

Usage

This is used to display an image hosted at a URL. It’s especially useful if you want to showcase the creative asset in your report.

Formula

IMAGE(URL)

Example

IMAGE('https://mkwise.net/wp-content/uploads/2023/04/data_strategy-1.png')

Result

In this example, I only used one URL, but if you have a field with an image for each row, it will display the table with all the images you have.

It’s quite a fun report if your creative assets are animated GIFs!

2. Page with Domain Name

Usage

Sometimes in Google Analytics, we have different domains in the same view. With this solution, you can see the domain name alongside the page. I mention Google Analytics, but there are many data sources where you may have the domain (hostname) and the page separately, and you want to see them together.

Formula

CONCAT(Domain, Page)

Example

CONCAT(Hostname, Page path)

Result

3. Date

Usage

When you want to create a date and have the year, month, and day in separate fields.

Formula

DATE(field with year, field with month, field with day)

Example

DATE(2023, 12, 31)

Result

31-12-2023

4. Keyword Difficulty Level

Usage

When you have the difficulty level for ranking a keyword on Google, but the difficulty is in a format from 1 to 100 and you want to create three categories: High, Medium, and Low.

Formula

CASE 
  WHEN Keyword Difficulty > 80 THEN 'High'
  WHEN Keyword Difficulty < 81 AND Keyword Difficulty > 60 THEN 'Medium'
  ELSE 'Low'
END

Result

Remember that text should always be enclosed in single quotes.

5. Brand Keyword

Usage

You have a list of keywords through which your website can be found on Google and want to differentiate those containing your brand from those that do not.

Formula

REGEXP_CONTAINS(Keyword, 'your brand name')

This will return True for keywords that contain your brand and False for those that do not.

Example

REGEXP_CONTAINS(Keyword, 'Macdonald.*|Mcdonald.*|macdonald.*|mcdonald.*')

You should enter your own brand name in place of “your brand name.” If people write it in different ways, remember that you can use regular expressions with REGEXP_CONTAINS or REGEXP_MATCH.

Result

6. Landing Pages Groups

Usage

This is used to group your landing pages by type of content.

Formula & Example

CASE 
  WHEN REGEXP_CONTAINS(Landing page, '.*/Apparel.*') THEN 'Apparel'
  WHEN REGEXP_CONTAINS(Landing page, '.*/Lifestyle.*') THEN 'Lifestyle'
  WHEN REGEXP_CONTAINS(Landing page, '.*/Stationery.*') THEN 'Stationery'
  WHEN REGEXP_CONTAINS(Landing page, '.*/Accessories.*') THEN 'Accessories'
  WHEN REGEXP_CONTAINS(Landing page, '.*/New.*') THEN 'New'
  ELSE 'Others'
END

Result

7. Extract Domain

Usage

You can use this formula when you have a long list of URLs and want to create a report that only includes the domain names.

Formula & Example

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

Result

8. Extract the Value of a Parameter in the URL

Usage

Often, when searching within a website, the results page contains the search parameter query or q in its URL, and the value of this parameter is our search term. If you want to extract the searched word, you could use this formula.

Formula

REGEXP_EXTRACT(URL, '/results.*query=([a-zA-Z0-9]+).*')


  • query is the search parameter, which may vary between websites.
  • /results is the directory where the searches are located, but this can also vary between sites.
  • .* matches any text between the results directory and the query parameter.
  • [a-zA-Z0-9]+ is used to capture both uppercase and lowercase letters as well as numbers.
  • The part within the parentheses is what the formula will extract ([a-zA-Z0-9]+).

Example

REGEXP_EXTRACT(URL, '/.*sortci=([a-zA-Z0-9]+).*')

In fact, this formula can be used to extract the value of any parameter. In the example, we are extracting the value of sortci, which is the parameter used to sort product listings on a website.

In this case, I haven’t added the directory name (for searches, it was /search) because it is not always the same.

Result


These are some examples of custom dimensions that I have found useful throughout my career. There are thousands more. You can always turn to Google and search for others if you want to learn more. What other custom fields do you think would be useful to create?

If you need any adjustments or further assistance, just let me know!

Leave a Comment