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.
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
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
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)