Acquia CDP

Operators in CDP campaigns

Customer Data Platform (CDP) provides a group of functions known as operators that help you to seamlessly create, transform, and perform calculations on diverse datasets.

When creating campaigns or defining specific audience segments, you can create a rule to define the audience that includes an operator.

String data type operators

With string data type operators, you can transform text and other special characters. These operators streamline the selection process and allow you to modify string values.

The following operators are applicable to the string data type:

Operator

Description

Example

Equal to

Returns records where the source text exactly matches the input text, including case-sensitivity of letters in the text.

“State equal to [CA]” is translated to customersummary.State = 'CA'.

Not equal to

Returns records where the source text does not match the input text, including case-sensitivity of letters in the text. Includes NULL values.

“State not equal to [CA]” is translated to customersummary.State != 'CA'.

In

Returns records where the source text exactly matches one of the input texts, including case-sensitivity of letters in the text.

“State in [CA, MA]” is translated to customersummary.State IN ('CA', 'MA').

Not in

Returns records where the source text does not match any of the input texts, including case-sensitivity of letters in the text. Excludes NULL values.

“State not in [CA, MA]” is translated to customersummary.State NOT IN ('CA', 'MA').

Is empty

Returns records where the source text is NULL or an empty string.

“State is empty” is translated to customersummary.State IS NULL OR customersummary.State = ''.

Is not empty

Returns records where the source text is neither NULL nor an empty string.

“State is not empty” is translated to customersummary.State IS NOT NULL AND customersummary.State != ''.

Contains

Returns customer records where the source text contains the input text, does not include case-sensitivity of letters in the text.

“State contains [CA]” is translated to instr(lower(customersummary.State), 'ca') != 0.

Does not contain

Returns customer records where the source text does not contain the input text, does not include case-sensitivity of letters in the text.

“State does not contain [CA]” is translated to instr(lower(customersummary.State), 'ca') = 0.

Begins with

Returns records where the source text begins with the input text, does not include case-sensitivity of letters in the text.

“State begins with [Cali]” is translated to lower(customersummary.State) LIKE 'cali%'.

Ends with

Returns records where the source text ends with the input text, does not include case-sensitivity of letters in the text.

“State ends with [FORNIA]” is translated to lower(customersummary.State) LIKE '%fornia'.

Matches regex

Returns records where the source text matches the input regular expression. Refer to syntax for help with your regular expression syntax. For more information, see String Functions (Regular Expressions).

“State matches regex [^(.*)FORNIA$]” is translated to customersummary.State RLIKE '^(.*)FORNIA$'.

Does not match regex

Returns records where the source text does not match the input regular expression. Refer to syntax for help with your regular expression syntax. For more information, see String Functions (Regular Expressions).

“State does not match regex [^(.*)FORNIA$]” is translated to customersummary.State NOT RLIKE '^(.*)FORNIA$'.

Integer and decimal data type operators

These operators streamline numeric operations. The numeric data, such as integers and decimals, plays a crucial role in decision-making. To facilitate the manipulation of these data types, CDP provides a set of operators that are tailored to integer and decimal values.

The following operators are applicable to the integer and decimal data type:

Operator

Description

Example

Equal to

Returns records where the source number exactly matches the input number.

Total Revenue equal to [100]” is translated to customersummary.TotalRevenue = 100.

Not Equal to

Returns customer records where the source number does not exactly match the input number. Includes NULL values.

“Total Revenue not equal to [100]” is translated to customersummary.TotalRevenue != 100.

Greater than

Returns records where the source number is greater than but not equal to the input number.

“Total Revenue greater than [100]” is translated to customersummary.TotalRevenue > 100.

Greater than equal to

Returns records where the source number is greater than or equal to the input number.

“Total Revenue greater than equal to [100]” is translated to customersummary.TotalRevenue >= 100.

Less than

Returns records where the source number is less than but not equal to the input number.

“Total Revenue less than [100]” is translated to customersummary.TotalRevenue < 100.

Less than equal to

Returns records where the source number is less than or equal to the input number.

“Total Revenue less than equal to [100]” is translated to customersummary.TotalRevenue <= 100.

Between

Returns customer records where the source number is greater than or equal to the input lower bound number but less than the input upper bound. The lower bound is inclusive and the upper bound is exclusive.

“Total Revenue between [100] and [200]” is translated to customersummary.TotalRevenue >= 100 AND customersummary.TotalRevenue < 200.

In

Returns customer records where the source number exactly matches one of the input numbers.

“Total Revenue in [100, 200]” is translated to customersummary.TotalRevenue IN (100, 200).

Date data type operators

These operators simplify date- and time-based operations. The date- and time-based data plays a crucial role in various business filters. CDP stores the date-time data as UNIX Epoch timestamps. To make these operations seamless, accessible, and intuitive, CDP provides a set of operators specifically tailored to date and date-time or timestamp data types. These operators are user-friendly and enable effortless action.

The following operators are applicable to the date data type:

Operator

Description

Example

Equal operators

Returns customer records where the source date exactly matches the input date.

“Last Transaction Date equal to [3/4/2019] PST” is translated to:

  • customersummary.LastTransactionDate >= March 4, 2019 12:00:00 AM PST

  • customersummary1.LastTransactionDate <= March 4, 2019 11:59:59.999 PM PST

  • customersummary.LastTransactionDate >= 1551686400000

  • customersummary1.LastTransactionDate <= 1551772799999

After operators

Returns customer records where the source date is after the input date.

“Last Transaction Date after [3/4/2019] PST” is translated to:

  • customersummary.LastTransactionDate > March 4, 2019 11:59:59.999 PM PST

  • customersummary.LastTransactionDate > 1551772799999

Before operators

Returns customer records where the source date is before the input date.

“Last Transaction Date before [3/4/2019] PST” is translated to:

  • customersummary.LastTransactionDate < March 4, 2019 12:00:00 AM PST

  • customersummary.LastTransactionDate < 1551686400000

In the last operators

Returns customer records where the source date is within the specified weeks, days, hours, or minutes of the current date and time.

“Last Transaction Date in the last [3] [Days]” is translated to:

  • customersummary.LastTransactionDate > (currentDateTimeInMilliseconds (3d * 24h * 60m * 60s * 1000ms))

  • customersummary.LastTransactionDate > (unix_timestamp()*1000 - 259200000)

At least… ago operators

Returns customer records where the source date is at least weeks, days, hours, or minutes before the current time.

“Last Transaction Date at least [3] [Hours] ago” is translated to:

  • customersummary.LastTransactionDate < (currentDateTimeInMilliseconds (3h * 60m * 60s * 1000ms))

  • customersummary.LastTransactionDate < (unix_timestamp()*1000 - 10800000)

Between operators

Returns customer records where the source date is in between the specified dates. Lower bound and upper bound dates are inclusive.

“Last Transaction Date between [2/4/2019] and [3/4/2019] PST” is translated to:

  • customersummary.LastTransactionDate >= February 4, 2019 12:00:00 AM PST

  • customersummary.LastTransactionDate < March 4, 2019 11:59:59.999 PM PST

  • customersummary.LastTransactionDate >= 1549267200000

  • customersummary.LastTransactionDate < 1551772799999

In the range of… ago operators

Returns customer records where the source date is within the specified lower bound and upper bound of weeks, days, hours, or minutes before the current time.

“Last Transaction Date in the range of [2] and [10] [Weeks] ago” is translated to:

  • customersummary.LastTransactionDate >= (currentDateTimeInMilliseconds (10w * 7d * 24h * 60m * 60s * 1000ms))

  • customersummary.LastTransactionDate < (currentDateTimeInMilliseconds (2w * 7d * 24h * 60m * 60s * 1000ms))

  • customersummary.LastTransactionDate >= (unix_timestamp()*1000 - 6048000000)

  • customersummary.LastTransactionDate < (unix_timestamp()*1000 - 1209600000)

In the next operators

Returns customer records where the source date is within the input weeks, days, hours, or minutes after the current time.

“Credit Card Expiration Date in the next [3] Weeks” is translated to:

  • customersummary.c_creditCardExpirationDate > currentDateTimeInMilliseconds

  • customersummary.c_creditCardExpirationDate <= (currentDateTimeInMilliseconds + (3w * 7d * 24h * 60m * 60s * 1000ms))

  • customersummary.c_creditCardExpirationDate > (unix_timestamp()*1000)

  • customersummary.c_creditCardExpirationDate <= (unix_timestamp()*1000 + 1814400000)

In the future range of operators

Returns customer records where the source date is within the specified lower bound and upper bound of weeks, days, hours, or minutes after the current time.

“Credit Card Expiration Date in the future range of [2] and [10] [Weeks] ” is translated to:

  • customersummary.c_creditCardExpirationDate >= (currentDateTimeInMilliseconds + (2w * 7d * 24h * 60m * 60s * 1000ms))

  • customersummary.c_creditCardExpirationDate <= (currentDateTimeInMilliseconds + (10w * 7d * 24h * 60m * 60s * 1000ms))

  • customersummary.c_creditCardExpirationDate >= (unix_timestamp()*1000 + 1209600000)

  • customersummary.c_creditCardExpirationDate <= (unix_timestamp()*1000 + 6048000000)

Did not find what you were looking for?

If this content did not answer your questions, try searching or contacting our support team for further assistance.

Acquia Help

Filter by product:

Acquia CDP common questions