About the Segment Editor¶

The Segment Editor is the user interface for building segments in AmpIQ. This editor uses a series of drop-downs, picklists, conditions, and values to define an attribute profile. Refresh segment insights to see how many customers match the profile, and then activate it as a segment.

The Segment Editor is located within the Segments page in Amperity.

Segment names¶

A good segment name is clear and concise, is not longer than necessary, uses patterns to help lists of segments stay organized, and considers how it might be represented in downstream workflows, such as lists of segments in the Campaigns page in Amperity, but also external systems such as Braze, Facebook Ads, Google Ads, Klaviyo, and The Trade Desk.

Important

Users of downstream systems are often not the same set of users who configure and manage segments in Amperity.

If you send a segment named “Untitled segment (43) - 2021-08-13-09-34-35” your downstream users might not know what to do with it.

Be sure to follow good naming patterns to ensure that downstream users can always find your segments when they need them. Add details like “historical”, “daily”, or “test” as appropriate.

Be sure to include the brand name and/or the region name if you have multiple brands or have multiple regions.

Some examples:

“Birthdays_Under_40_CA”
“Birthdays_Under_40_NY”
“High_AOV_Active_Loyal”
“High_CLV_Historical”
“Acme_Daily_Churn”

Prefix a segment that is located in a folder with that folder name as often as possible.

For example, if you have folders named “Braze” and “TikTok” use segment names like “Braze_Birthdays_Under_40_CA” and “TikTok_Birthdays_Under_40” for all segments that exist within those folders.

If your downstream systems will have data from non-Amperity systems, consider using Amperity as the first prefix: “Amperity_Birthdays_Under_40_CA”.

Use leading zeroes if a sequential numbering system is a prefix. For example: 0001, 0002, 0003, …0100 is preferable to 1, 2, 3, …100. This will help ensure that your segments are ordered sequentially in the Amperity Segments page and downstream systems.

Inclusions¶

Inclusions define the set of conditions that determine which customers will belong to an audience. When a customer matches the criteria defined for an inclusion, that customer will be included in the audience.

Exclusions¶

Exclusions define conditions that determine which customers will not belong to an audience. When a customer matches the criteria defined for an exclusion, that customer will be excluded from the audience.

AND vs. OR¶

AND and OR are used in SQL languages to specify how results should be filtered when more than one condition is present.

Use AND to return a smaller (and more specific) list of customers. A customer must match all conditions to belong to the list.
Use OR to return a larger (and more broad) list of customers. A customer may match any condition to belong to the list.

Amperity uses AND and OR to help you choose which type of behavior – larger audiences or smaller audiences – you want to use in your segment.

The AND and OR conditions may be set in two locations:

Within a group of attributes
Between groups of attributes

The default is AND. Use the slider to switch to OR.

The following examples describe how AND and OR conditions work.

	“I want to build an audience that returns customers who have an email address.” In this example, there is only one condition. The audience that is returned contains only customers who have an email address.
	“I want to build an audience that returns customers who have an email address AND customers have opted in to receiving email messages from my brand.” In this example, the audience that is returned – shown as the darker color – is smaller because only a subset of customers for whom you have email addresses have opted in to receiving email messages from your brand.
	“I want to build an audience that returns customers who have an email address OR customers who have phone number.” In this example, your audience grows larger because both conditions are met: email address or phone number. This is shown as both colors and the total audience is the overlap of both conditions.
	“I want to build an audience that returns a customer’s email address OR a customer’s phone number AND customers have opted in to receiving messages from my brand from to their email address OR phone number.” This audience has two groups of attributes: email addresses OR phone numbers AND opt-in status for email addresses OR phone numbers. The first group of attributes – email addresses or phone numbers, as shown in the darker color – should make your audience larger. Few data sets have a perfectly matching set of email addresses and phone numbers across all customers. The second group of attributes – opt-in status for email addresses or phone numbers, as shown in the lighter color – should also be larger as a group than by themselves. You should expect the number of customers who have opted in to receive email or SMS communications to be smaller than the number of customers who have provided email addresses or phone numbers to your brand. These two groups are in-between an AND condition within your segment: email address or phone and opt-in status for email address or phone. Your audience then grows smaller because only a subset of customers for whom you have email addresses or phone numbers have opted in to receiving email or SMS messages from your brand. The smaller audience of opted-in customers for whom you have email addresses and phone numbers is shown by the darker color.

Segment conditions¶

You can add conditions to a segment by selecting them from a list. There are three types of conditions:

Attributes
Customer lists
Purchase behaviors

Attributes¶

Attributes are selected from data tables that exist in your brand’s customer 360 database.

Customer lists¶

Customer lists may be included or excluded. Customer lists can be any of the following: queries that have been made available to the Segments page, other segments that exist in your tenant, or lists of customers that have been uploaded to Amperity.

Purchase behaviors¶

Purchase behaviors are a feature of Amperity that are built on top of standard output for transactions (orders and items). Purchase behaviors require standardized product catalog field names to be present in your standard output for transactions.

Operators¶

An operator defines how results from a column in a data source will be returned.

For example: From your customer 360 table, return all customer records that contain email addresses that are similar to “gmail.com”. The data source is your customer 360 table, the attribute is the email field in that table, and the is like operator allows you to use “gmail.com” to look for values that contain “gmail.com”, and then return a list of customers who have “gmail.com” in their email address.

In SQL, an operator is expressed using the WHERE clause. The following example returns a list of customers who have “gmail.com” in their email address:

SELECT
  email
FROM
  Customer360
WHERE email LIKE "gmail.com"

In the Segment Editor you define operators using a series of picklists that do not require knowledge of SQL or how to define a WHERE clause:

Defining an operator using the Segment Editor.

The list of operators that are available to an attribute in the Segment Editor varies, with each list of operators depending on the attribute’s data type:

Boolean
Date
Datetime
Decimal
Integer
String

Note

This topic is a reference for all operators that are available to all attributes and is organized alphabetically by data type, and then by operator. This topic does not contain specific recommendations for specific attributes (or attribute groups). Refer to the individual attribute reference pages to learn more about the set of operators that are available, including recommended operators and examples of using them.

Boolean¶

A Boolean data type represents true and false outcomes, such as “Has this person purchased more than once?” The answer is “Yes” or “No.”

The following operators are available to all attributes with the Boolean data type:

Operator	Description
is false	false returns customer records that are “false”.
is NULL	is NULL returns customer records that do not have a value. A record that does not have a value cannot be “true” or “false”.
is NOT NULL	is not NULL returns customer records that have a value. A record that has a value is either “true” or “false”.
is true	is true returns customer records that are “true”.

Date¶

A Date data type represents a day, a month, and a year. For example: a birthdate.

A Date data type accepts YYYY-MM-DD.

The following operators are available to all attributes with the Date data type:

Operator	Description
is	is returns customer records with activity that matches the specified calendar date.
is after	is after returns customer records with activity that occurred after the specified calendar date, not including the specified date.
is before	is before returns customer records with activity that occurred before the specified calendar date, not including the specified date.
is between	is between returns customer records with activity that is between the specified calendar dates, not including the specified dates.
is not	is not returns customer records with activity that does not match the specified calendar date.
is not between	is not between returns customer records with activity that is not between the specified calendar dates, not including the specified dates.
is not NULL	is not NULL returns customer records that have a value.
is NULL	is NULL returns customer records that do not have a value.
is on or after	is on or after returns customer records with activity that occurred on or after the specified calendar date, including the specified date.
is on or before	is on or before returns customer records with activity that occurred on or before the specified calendar date, including the specified date.

Datetime¶

A Datetime data type represents a date and time combination. For example: a date and time at which a customer made a purchase.

A Datetime data type accepts “YYYY-MM-DD hh:mm:ss”.

The following operators are available to all attributes with the Datetime data type:

Operator	Description
is	is returns customer records with activity that matches the specified calendar date and time.
is after	is after returns customer records with activity that occurred after the specified calendar date and time, not including the specified date and time.
is before	is before returns customer records with activity that occurred before the specified calendar date and time, not including the specified date and time.
is between	is between returns customer records with activity that is between the specified calendar dates and times, not including the specified dates and times.
is not	is not returns customer records with activity that does not match the specified calendar date and time.
is not between	is not between returns customer records with activity that is not between the specified calendar dates and times, not including the specified dates and times.
is not NULL	is not NULL returns customer records that have a value.
is NULL	is NULL returns customer records that do not have a value.
is on or after	is on or after returns customer records with activity that occurred on or after the specified calendar date and time, including the specified date and time.
is on or before	is on or before returns customer records with activity that occurred on or before the specified calendar date and time, including the specified date and time.

Decimal¶

A Decimal data type represents amounts, percentages, and scores.

A Decimal data type accepts numeric values with a precision of 38 and a scale of 2 (default). Precision is the number of digits that may be in the decimal value. Scale is the number of digits that may be to the right of the decimal point.

Note

Amperity defaults precision and scale for the Decimal data type to 38 and 0: DECIMAL(38,2). The default value for scale may be configured in the feed for each data source.

The following operators are available to all attributes with the Decimal data type:

Operator	Description
is	is returns customer records with values that match the specified amount or percentage.
is between	is between returns customer records with values that are between the specified amounts or percentages, not including the specified amounts or percentages.
is greater than	is greater than returns customer records with values that are greater than the specified amount or percentage, not including the specified amount or percentage.
is greater than or equal to	is greater than or equal to returns customer records with values that are greater than or equal to the specified amount or percentage, including the specified amount or percentage.
is in list	is in list returns customer records with values that match the amounts or percentages that are specified in the list.
is less than	is less than returns customer records with values that are less than the specified amount or percentage, not including the specified amount or percentage.
is less than or equal to	is less than or equal to returns customer records with values that are less than or equal to the specified amount or percentage, including the specified amount or percentage.
is not	is not returns customer records with values that do not match the specified amount or percentage.
is not between	is not between returns customer records with values that are not between the specified amounts or percentages, not including the specified amounts or percentages.
is not in list	is not in list returns customer records with values that do not match the amounts or percentages that are specified in the list.
is not NULL	is not NULL returns customer records that have a value.
is NULL	is NULL returns customer records that do not have a value.

Integer¶

An Integer data type represents quantities, such as items ordered, frequency of purchase, number of orders, clicks, and page visits.

An Integer data type accepts numeric values with an implied scale of zero.

The following operators are available to all attributes with the Integer data type:

Operator	Description
is	is returns customer records with values that match the specified quantity.
is between	is between returns customer records with values that are between the specified quantities, not including the specified quantity.
is greater than	is greater than returns customer records with values that are greater than the specified quantity, not including the specified quantity.
is greater than or equal to	is greater than or equal to returns customer records with values that are greater than or equal to the specified quantity, including the specified quantity.
is in list	is in list returns customer records with values that match the quantities that are specified in the list.
is less than	is less than returns customer records with values that are less than the specified quantity, not including the specified quantity.
is less than or equal to	is less than or equal to returns customer records with values that are less than or equal to the specified quantity, including the specified quantity.
is not	is not returns customer records with values that do not match the specified quantity.
is not between	is not between returns customer records with values that are not between the specified quantities, not including the specified quantity.
is not in list	is not in list returns customer records with values that do not match the quantities that are specified in the list.
is not NULL	is not NULL returns customer records that have a value.
is NULL	is NULL returns customer records that do not have a value.

String¶

A String data type represents many things, such as identifiers, names (of people, places, and products), phone numbers, etc.

A String data type accepts A-Z, a-Z, underscores, and hyphens.

The following operators are available to all attributes with the String data type:

Operator	Description
contains	Returns customer records with values that match a string of characters.
does not contain	Returns customer records with values that do not match a string of characters.
ends with	Returns customer records with values that end with the specified characters.
is empty	Returns customer records that do not have a value in this field.
is exactly	Returns all customer records with values that match the specified characters.
is not empty	Returns customer records that have a value in this field.
is not exactly	Returns customer records that do not match the specified characters.
starts with	Returns customer records that start with the specified characters.

NULL and NOT NULL values¶

A field with a NULL value is a field with a value that is empty. A NULL value is different than a value of zero, a blank value, or a value that contains only spaces.

An attribute can only have a NULL value if it remained empty after the record was created. With NULL, you can insert a new record or update a record without adding a value to this field and the field will be saved with a NULL value.

A field with a NOT NULL value is a field with a value that is not empty. A value exists in the field, including values of zero, blank values, and value that contain only spaces.

Examples

For example, if you have 100 customers, 99 of which have provided a value for their phone number (even if some of those phone numbers are invalid phone numbers), 1 of which has not provided any value at all. The customer record that does not have a phone number is empty and is a NULL value.

Amperity returns 1 record when the “is NULL” operator is used and 99 records when the “is not NULL” operator is used, even if 2 of them contained spaces instead of numbers and 4 of them were “555-555-1234”.

A practical example is a segment of 50 customers where the ‘Opt in’ field distinguishes customers based on their preferences for promotional emails. This field specifies that the customer has opted out of promotional emails but has consented to receive other types of messages. Here, 46 customers opted in, so the field has a value of NOT NULL. In this segment 3 customers did not opt in. They also have Not NULL in this field. A customer didn’t respond, so the field has NULL for this customer.

Relative dates¶

A relative date is determined at the time a segment is run, where today is the day on which the segment is run. For example: yesterday, 30 days ago, 14 days ago, or 1 year ago. The list of relative date values includes a series of common ranges, but you may also type in a more specific range, such as 2 months ago or 5 days ago.

Relative date values

Value	Description
Tomorrow	Starts at 12:00:00 AM of the day after the current day. For example, if the current day is “Thursday 01 / 12 / 2023” then tomorrow is “Friday 01 / 13 / 2023”.
Today	Starts at 12:00:00 AM (or at the current time) on the current day and continues for 24 hours. For example, if the current day is “Thursday 01 / 12 / 2023” then today is “Thursday 01 / 12 / 2023”.
Yesterday	Starts at 12:00:00 AM of the day before the current day. For example, if the current day is “Thursday 01 / 12 / 2023” then yesterday is “Wednesday 01 / 11 / 2023”.
N days ago	Starts at 12:00:00 AM of the day N days before the current day. For example, if the current day is “Thursday 01 / 12 / 2023”, then: 7 days ago is “Thursday 01 / 05 / 2023” 14 days ago is “Thursday 12 / 29 / 2022” 30 days ago is “Tuesday 12 / 13 / 2022” 60 days ago is “Sunday 11 / 13 / 2022” 90 days ago is “Friday 10 / 14 / 2022”
1 month ago	Starts at 12:00:00 AM of the same day of the month that is 1 month before the current month. For example, if the current day is “Thursday 01 / 12 / 2023” then 1 month ago is “Monday 12 / 12 / 2022”.
1 year ago	Starts at 12:00:00 AM of same day of the year that is 1 year before the current year. For example, if the current day is “Thursday 01 / 12 / 2023” then 1 year ago is “Wednesday 01 / 12 / 2022”.

Tip

You can compare segments by % of Purchasers, % of Revenue, Revenue/Purchaser by changing the option under the down arrow in the Compare by: field.

Segment insights¶

Segment insights show you answers to the following questions:

How many customers are in your segment?
How many of these customers have been active in the past year?
How much did these customers spend in the past year?
How many of these customers have a contactable email address, phone number, or physical address?

When you start building a segment, the values for segment insights represent 100% of your customers and revenue across all categories.

As you refine your segment by adding more attributes you can use the Refresh button to update the answers to those four questions.

For example:

Use segment insights to understand the value of your segment.

Segment insights include the following categories:

Unique Customers shows the number of unique customers who are in the segment, where a unique customer is represented by a unique Amperity ID.
1-year Active Customers shows how many unique customers have made a purchase within the past year.
1-year Segment Revenue shows the total revenue for all purchases made by active customers within the past year.
Reachable Customers shows the number of unique customers who have at least one contactable email address, phone number, or physical mailing address.

Note

Segment insights are available when your customer 360 database contains certain tables.

Unique Customers, 1-year Active Customers, and 1-year Segment Revenue require access to the Transaction Attributes Extended and Customer 360 tables.
Reachable Customers requires access to the Customer Attributes table.

Databases and tables¶

You can use any database table that is available to the Segments page to build attribute groups. The tables are shown in the bottom right corner of the Segment Editor.

You may change the selected database by choosing another one from the drop-down menu.
The list of tables is refreshed to show the tables in that database.

Use your customer 360 database to build segments.

Important

Tables must be configured to be available to the Segments page. This must be done by a member of your team who manages databases and tables from the Customer 360 page.

SQL Editor¶

The Segment Editor is an optional interface that allows you to build an attribute profile using Presto SQL. Start with a SELECT statement that returns the Amperity ID, and then apply a series of WHERE statements to define one (or more) attribute groups that match specific conditions and values.

The Segment Editor has the following requirements:

The only field that can be returned by the SELECT statement is amperity_id.
All conditions and values must be contained within a WHERE clause.
A WHERE clause must use one of the following conditions: AND or OR.

Example SQL segment

For example, a segment that uses the OR condition to return customers whose first name begins with “Mi”, last name begins with “Smi”, and who reside in California:

SELECT
  "amperity_id"
FROM
   "Customer_360"
WHERE
  (
    (LOWER("given_name") like '%mi%')
     OR LOWER("state") = 'ca'
     OR (LOWER("surname") like '%smi%')
  )

The following example is identical to the previous example, but shows each condition in its own WHERE clause, using UNION ALL between each clause to group the results together:

SELECT
  "amperity_id"
FROM
   "Customer_360"
WHERE
  (
    (
      "amperity_id" IN (
        SELECT
          "t0"."amperity_id"
        FROM
          "Customer_360" "t0"
        WHERE
          ((LOWER("t0"."given_name") like '%mi%'))
        UNION ALL
        SELECT
          "t1"."amperity_id"
        FROM
          "Customer_360" "t1"
        WHERE
          (LOWER("t1"."state") = 'ca')
        UNION ALL
        SELECT
          "t2"."amperity_id"
        FROM
          "Customer_360" "t2"
        WHERE
          ((LOWER("t2"."surname") like '%smi%'))
      )
    )
  )