Overview

When creating segments using custom field data, it’s important to understand how “empty” states can impact your search results. Custom fields behave differently than system fields like first-name, depending on whether they have a value, are empty, or don’t exist in the database at all. These differences stem from how our database stores and retrieves custom field data, which can sometimes create unexpected results when building segments.

This article explains the nuances of how custom fields are stored, the states they can exist in, and how to build queries and segments that accurately reflect your data. For simplicity, we’ll use favorite-color as an example throughout the guide. As you read, consider applying these concepts to one of your own custom fields to make the examples more relevant to your needs.

Common Issue: Contacts Are Missing from Segment

When using custom field data with the "Not In" operator, you might notice that certain contact records are unexpectedly missing.

This happens because the Not In operator only evaluates custom fields that exist in the database. Your segment will not include records where the custom field is completely missing in this configuration (explained in the "Why Some Fields May Not Exist" section below).

You must combine "Not In" with the "Does Not Exist" operator to ensure these records are included. We cover this process in detail below.

Understanding Custom Field States

Custom fields like favorite-color can behave differently based on how data is stored or entered. These fields don’t always contain a value, and in some cases, they might not even exist in the database. This distinction is crucial for understanding query results and why some records appear (or don’t) in your filtered data.

To help clarify, a custom field can be in one of three possible states:

State	Explanation	Example
Exists with a Value	The field is present and contains data.	A student with `favorite-color = blue` will match a query for "favorite color is blue."
Exists but Empty	The field is present but has no data/null value.	A student with a `favorite-color` field that has no data/null value exists in the database but hasn't specified a color preference.
Does Not Exist*	The field is absent from the database.	A student without `favorite-color` does not have the field stored in their database record, although it may appear in the user interface. Read more on this below.

*Why Some Fields May "Not Exist"

Although you can see custom fields like favorite-color on the contact profile, they don’t always exist in the database. Here’s why:

Efficient Data Storage: Data is stored in a way that optimizes performance. Custom fields without data are sometimes excluded from the database and only added when a value is entered. If the value is removed later, the field remains in the database as empty/null.
User Interface vs. Database: The contact profile interface (what you see) shows all custom fields to maintain usability, even if they don’t exist in the database.

Query Operators and Their Behavior

When filtering custom fields, choosing the right operator for your specific goal is important. Each operator handles “empty” and “nonexistent” states differently, so understanding their behavior ensures your query captures the correct contact records.

Let’s explore the commonly used operator with examples of a favorite-color custom field:

“Not In” Operator

Behavior

The not in operator excludes records that match specified values while including those that have no data/null value.

Includes
- Records where the field exists but doesn’t match specified values
- Records where the field exists but has no data/null value
- Records where the field does not exist
Excludes
- Records where the field matches the specified value

Example Segment

favorite-color not in red

This segment will:

Include students whose:
- favorite-color field exists but is not "red"
- favorite-color field exists but has no data/null value
- favorite-color field doesn't exist in the database
Exclude students whose:
- favorite-color field is "red"

"Exists" Operator

Behavior

The exists operator finds records where a field is present and contains any value.

Includes
- Records where the field exists and contains any value
Excludes
- Records where the field exists but has no data/null value
- Records where the field does not exist

Example Segment

favorite-color exists

This segment will:

Include students whose:
- favorite-color field exists and contains any color value
Exclude students whose:
- favorite-color field exists but has no data/null value
- favorite-color field doesn't exist in the database

“Does Not Exist” Operator

Behavior

The does not exist operator finds records where a field is either missing from the database or has no data/null value.

Includes
- Records where the field does not exist in the database
- Records where the field exists but has no data/null value
Excludes
- Records where the field exists and contains any value

Example Query

favorite-color does not exist

This segment will:

Include students whose:
- favorite-color field is missing from the database
- favorite-color field exists but has no data/null value
Exclude students whose:
- favorite-color field exists and contains any value

“Is Empty” Operator

Behavior

The is empty operator finds records where a field exists but has no data/null value.

Includes
- Records where the field exists but has no data/null value
Excludes
- Records where the field does not exist
- Records where the field contains any value

Example Query

favorite-color is empty

This segment will:

Include students whose:
- favorite-color field exists but has no data/null value
Exclude students whose:
- favorite-color field doesn't exist in the database
- favorite-color field contains any value

Common Filtering Scenario

Understanding how to filter custom field data requires careful consideration of how the data is stored and retrieved. Different scenarios may require different operators—or combinations of operators—to ensure your results are accurate and complete. This is especially important when dealing with records where fields are empty or don't exist in the database.

Here is the most common scenario and the filtering strategy to handle it:

Excluding Specific Values While Including Missing Data

Use Case

When planning student outreach campaigns, you may want to exclude students with certain preferences while ensuring you don't miss anyone with incomplete data.

For example, you're planning a series of campaigns and want to exclude students who selected "red" as their favorite color while including those who haven't provided their color preference.

Filter(s)

favorite-color not in red -OR- favorite-color does not exist

This combination will find:

Students whose favorite-color exists with any value except "red"
Students whose favorite-color exists but has no data/null value
Students whose favorite-color field doesn't exist in the database

The combination with does not exist is necessary because not in only evaluates existing fields in the database.

Filters Overview

Filters: Constraint Operators

Calculated Segments

Filters: Property Types

Field Management