Introduction to the CONTAINS_TEXT Function

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

  1. 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.
  2. 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

WEEK
:
Analyzing and Presenting Your Data by Week: The Essential Guide to Using the WEEK Function in Looker Studio
CONCAT
:
Mastering the CONCAT Function in Looker Studio: Syntax, Examples, and Best Practices
SQRT
:
Mastering the SQRT Function in Looker Studio for Enhanced Data Insights and Computation
FLOOR
:
Exploring the FLOOR Function in Looker Studio: A Comprehensive Guide on Rounding Down Numbers in Data Analysis
ABS
:
Mastering the ABS Function in Looker Studio: Guide to Absolute Value Visualization

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

All your data on Looker Studio

Build Looker Studio dashboard easily with your marketing data from all platforms and track your essential KPIs! No-code integration.

Start free trial now!
Looker studio

Optimize your data analysis

Get free Looker Studio dashboard template among a large collection of +50 stunning template! Elevate your data visualization.

Get free templates!