Diving headfirst into the world of Looker Studio(formerly Google Data Studio), we come across an impressive function called 'CONTAINS_TEXT'. This function is an integral tool in the GDS toolkit. Its main task is to search for a specific sequence of characters within a text field or towards an expression and then respond with either true or false output based on whether the said sequence is found.
How Does the CONTAINS_TEXT Function Work?
Like every function, CONTAINS_TEXT has its own unique syntax that defines its structure.
Now let's dissect the syntax for a clearer understanding.
The syntax for this function is -
Here, 'X' is representative of any field or expression that seeking to be examined, while 'text' is the specific character sequence or value that you're searching for within 'X'.
It is critical to note that 'text' in this syntax is case sensitive.
Examples of the CONTAINS_TEXT Function
Now that we've broken down the function, let's illustrate how you can use CONTAINS_TEXT with a few examples, keeping it interesting with a sales metrics context:
Suppose, we are looking at a dataset including sales revenues from various e-commerce campaigns. If we want to filter out campaigns that have 'BlackFriday' in their names, we could write:
CONTAINS_TEXT(Campaign, "BlackFriday")
Google Data Studio would then pull out all campaigns containing 'BlackFriday' in their names.
Exploring further, let's say, you're trying to find out whether the sales in 'Q4' have been higher than those in 'Q3'. You might use the CONTAINS_TEXT function as follows:
CONTAINS_TEXT(Quarter, "Q4")
This will inform you whether 'Q4' is mentioned in the specified field or not.
Limitations of the CONTAINS_TEXT Function
While CONTAINS_TEXT can deliver powerful and insightful results, it's also important to keep its limitations in mind. The key one is that CONTAINS_TEXT is case sensitive. Therefore, if you are looking for 'BlackFriday' and the field contains 'blackfriday', it will return 'false'.
Therefore, be careful to ensure consistency in your text case when using this function.
Handy Tips for Using the CONTAINS_TEXT Function
- To get around the case sensitivity rule, consider creating a new calculated field where all text is in the lower or upper case, and use CONTAINS_TEXT on this new field.
- If you want to find whether a field contains any of a list of words, an OR operator can be used in combination with CONTAINS_TEXT. For example, if you want to see if a Sales Overview contains either 'profit' or 'loss', use the formula: CONTAINS_TEXT(Overview, "profit") OR CONTAINS_TEXT(Overview, "loss").
By understanding its wide range of applications, strengths, as well as limitations, you can harness the full potential of the CONTAINS_TEXT function within Google Data Studio to extract meaningful insights from your dataset.
.png)
