Count keywords in strings

Data Pipeline

You can easily count the number of instances a specific word is found in one of your columns.

For example, we would like to count the number of rows in our ‘URL’ column containing the word “test”. We want to count any result containing this word in a row without excluding results that are not exact. To do this, we will use conditional logic with a case statement.

We will need to add a column in the Data Pipeline (here labelled ‘Test’) with the custom formula below:

CASE WHEN “URL” LIKE ‘%test%’ THEN 1 ELSE 0 END

The Custom formula in the Pipeline

You could then further aggregate the results of this column by adding a Group step in the data pipeline to count all values in the ‘Test’ column.

It is also possible to create a flag where every time the word “test” is present in the ‘URL’ column, the word “yes” would appear. If this keyword isn’t present, the word “no” would appear. This can be done with a custom formula such as:

CASE WHEN “URL” LIKE ‘%test%’ THEN ‘yes’ ELSE ‘no’ END

Keyword Count Example result


Related Data Pipeline Help Articles

See more