Calculated fields

Looker Studio calculated field : Number of occurrences of specific character

May 28, 2019 - by Mehdi Oudjida

The Looker Studio (ex Data Studio) calculated field below returns the number of occurrences of a specific character and use it as a grouping dimension.

Use cases

This formula can be used for dimension values with high cardinality. It is based on the presence of specific meaningful character for splitting, so count grouping purposes:

  • Search query, Search engine keyword…: Counting space character + 1 to get the number of words -> for long tail analysis (example explained below)
  • URLs and paths (all data sets): Counting slash character of path to get depth of page in the site map -> for internal linking strategy, SEO indexation
  • Product Category (Enhanced Ecommerce) from Google Analytics data source: Counting slash character to get depth of products categories tree -> for catalog and product marketing analysis
  • …

Example

In the example below, we count the number of words composing GA site search queries to check if there is a correlation with the related conversion rates.

Calculated field formula

LENGTH(
	REGEXP_REPLACE(My keywords dimension,"[^ ]","")
)+1

Explanations

The formula is made with 2 cascading of Looker Studio functions :

  • REGEXP_REPLACE: We remove all characters not corresponding to our character separator. “[^ ]” regular expression means “not equals to SPACE character”
  • LENGTH: We count the remaining characters, so our separator
  • +1: For this specific example, we need to add 1 for word count purpose (no space character means that there is only 1 word)