Introduction to the CONTAINS_TEXT Function

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 -


CONTAINS_TEXT(X, text)

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.

More function to use with Looker Studio

DAY

Exploring the DAY Function in Looker Studio: Maximising Insights from Your Date Data

CURRENT_DATETIME

Taking advantage of the CURRENT_DATETIME function in Looker Studio

LOG10

Understanding and Leveraging the LOG10 Function in Looker Studio for Advanced Data Analysis

MINUTE

Utilizing the MINUTE Function in Looker Studio for Precise Temporal Data Analysis and Report Generation

COS

Looker Studio Studio's Magnificent Cosine Functionality

Become a Looker Studio expert
for free

Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.

Get more video lessons