15 Segment Queries
When creating an Unstratified or a Stratified Segment, it is important to know what you need to enter/select in each field, whether there is a particular format for the data entered, and what the query is going to return. If you are not sure what Customer Engagement is doing, you may get an error on the query or the results may not be what you expect.
This appendix explains what is required for each query template.
The following conventions apply to data entry when creating a segment:
-
Dates must be in the MM/DD/YYYY format.
-
When entering numbers, do not use commas, dollar signs, or decimal places unless specifically instructed to do so.
-
When entering text, do not use any punctuation unless instructed otherwise.
Segment Query Categories
The following is a list of the Segment Query categories:
-
Customer Demographics
-
Customer Contact
-
Customer Attributes
-
Purchase Activity
-
Basic Programs
-
Segment Elements
-
Promotion Activity
-
Strategic Segments
-
Loyalty Segments
These categories are available at the Segment Query step of the Create Segment wizard, and control the customers selected for the Stratified or Unstratified Segment.
The following is a list of the Stratified Query categories:
-
Loyalty Summary
-
Transaction Criteria
-
Line Item Detail
These categories control how the customers are broken out into strata for a Stratified Segment, and are available at the Strata Query step of the Create Segment Wizard if a Strata Type of Loyalty or Transaction was selected at the Strata Field step.
The Strata Query step is not available when a Strata Type of Customer was selected at the Strata Field step. Also, this step is not available for an Unstratified Segment.
Segment Queries
The following categories are available at the Segment Query step of the Create Segment wizard, and control the customers to select for the Stratified or Unstratified Segment.
Customer Demographics
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment
Customer ID
This query returns active and valid Customers whose Customer ID matches your input.
Figure 15-1 Customer ID
Customer City
This query returns active and valid Customers whose primary and active address is in the city you have specified.
Figure 15-2 Customer City
Customer State
This query returns active and valid Customers whose primary and active address is in the state you have specified.
Figure 15-3 Customer State
Postal Code
This query returns active and valid Customers whose primary and active address is in the postal code (zip code) you have specified. Use only five digits for US zip codes.
Figure 15-4 Postal Code
Postal Code Vicinity
This query returns active and valid Customers whose addresses are within a radius of a specified number of miles of a specified location.
Figure 15-5 Postal Code Vicinity
Customer Birthdate
This query returns Customers whose birthday matches the parameters you input. The date you input must be a valid date ([default] = current date).
Figure 15-6 Customer Birthdate
Customer Birth Month
This query returns active and valid Customers whose birth month matches your selection ([default] = “January”).
Figure 15-7 Customer Birth Month
Home Location
This query returns active and valid Customers whose Home Location matches your entry. The Home Location you enter must be a valid location for your organization.
Figure 15-8 Home Store
Signup Location
This query returns all Customers whose signup location matches the one you specify. The location you enter must be a valid location for your organization.
Figure 15-9 Signup Location
Customer Last Update Date
This query returns active and valid Customers whose last update date matches what you have entered. The date must be a valid date.
Figure 15-10 Customer Last Update Date
Signup Date
This query returns active and valid Customers whose signup date matches what you have entered. The date must be a valid date.
Figure 15-11 Signup Date
Create Date
This query returns active and valid Customers whose create date matches what you have entered. The date must be a valid date.
Figure 15-12 Create Date
Active Customers
This query returns all active and valid Customers. This query has no parameters to configure
Figure 15-13 Active Customers
Customer Contact
This section
Mail Contact Permission
This query returns all Customers who have given permission to be contacted by mail.
Figure 15-14 Mail Contact Permission
Address Type Code
This query returns all Customers whose address type matches what you entered.
Figure 15-15 Address Type Code
Phone Contact Permission
This query returns all Customers who have given permission to be contacted by telephone.
Figure 15-16 Phone Contact Permission
Phone Type
This query returns all Customers whose phone type matches your selection.
Figure 15-17 Phone Type
Email Contact Permission
This query returns all Customers who have given permission to be contacted by email.
Figure 15-18 Email Contact Permission
Valid Email Addresses
This query returns all Customers whose email address has been validated or not validated.
Figure 15-19 Valid Email Addresses
Email Address Type
This query returns all Customers whose email type matches your selection.
Figure 15-20 Email Address Type
Customer Attributes
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment.
String Attribute Values
This query returns all Customers with a string attribute whose value matches your entry.
Figure 15-21 String Attribute Values
Logical Attribute Values
This query returns all Customers with a logical attribute that has been assigned the selected value.
Figure 15-22 Logical Attribute Values
Date Attribute Values
This query returns all Customers with a date attribute that has been assigned a date between the entered values.
Figure 15-23 Date Attribute Values
Purchase Activity
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment.
Lifetime Sales
This query returns all Customers whose lifetime sales amount matches what you have entered.
Figure 15-24 Lifetime Sales
Lifetime Returns
This query returns all Customers whose lifetime returns amount matches what you have entered.
Figure 15-25 Lifetime Returns
Year-To-Date Sales
This query returns all Customers whose year-to-date sales amount matches what you have entered.
Figure 15-26 Year-to-Date Sales
Year-To-Date Returns
This query returns all Customers whose year-to-date returns amount matches what you have entered.
Figure 15-27 Year-to-Date Returns
Customer First Purchase Date
This query returns active and valid Customers whose first use date matches the date criteria entered. The date must be a valid date.
Figure 15-28 Customer first Purchase Date
Customer Last Purchase Date
This query returns active and valid Customers whose last use date matches the date criteria entered. The date must be a valid date.
Figure 15-29 Customer Last Purchase Date
Number of Visits Between Dates
This query returns all active Customers who made x visits between the dates specified. Both dates must be filled in and valid.
Figure 15-30 Number of Visits Between Dates
Value of Spending in the Last X Days
This query returns all the Customers who have spent a designated amount or more within the last X number of days.
Figure 15-31 Value of Spending in last X Days
Value of Spending Between Dates
This query returns all the Customers who have spent a designated amount or more within a designated range of dates.
Figure 15-32 Value of Spending Between Dates
Fixed Period Activity
This query returns active and valid Customers who have completed transactions between the business dates you have specified. Both dates must be filled in and valid.
Figure 15-33 Fixed Period Activity
Purchase in the Last X Months
This query returns all Customers who have made a purchase within the number of months you specify.
Figure 15-34 Purchase in the Last X Months
Purchase Within X Days
This query returns all Customers who have made a purchase within the number of days you specify.
Figure 15-35 Purchase Within X Days
Department in X Months
This query returns all Customers who have made a purchase from Department X within Y months.
Figure 15-36 Department in X Months
Class in X Months
This query returns all Customers who have purchased an item in the class you specified in the last X months.
Figure 15-37 Class in X Months
Returns in X Days
This query returns active and valid Customers who have made one or more returns in the number of days you have specified.
Figure 15-38 Returns in X Days
Transactions from Location Group in Last X Days
This query returns the list of Customers who have performed a transaction at a location within a configured location group in the last X days.
Figure 15-39 Transactions from Location Group in Last X Days
Purchased Item from Location Group in Last X Days
This query returns Customers who have purchased one or more items from a location within a location group in the last X days.
Figure 15-40 Purchase Item from Location Group in Last X Days
Purchased Item with String Type Attribute in Last X Days
This query returns Customers who have purchased, within the last X days, an Item assigned a certain string-type Item Attribute with an indicated value.
Figure 15-41 Purchase Item with String Type Attribute in Last X Days
Purchased Item with Date Type Attribute in Last X Days
This query returns Customers who have purchased, within the last X days, an Item assigned a certain date-type Item Attribute with an indicated value.
Figure 15-42 Purchased Item with Date Type Attribute in Last X Days
Purchased Item with Logical Type Attribute in Last X Days
This query returns Customers who have purchased, within the last X days, an Item assigned a certain logical-type Item Attribute with an indicated value.
Figure 15-43 Purchased Item with Logical Type Attribute in Last X Days
Purchased Item with Numeric Type Attribute in Last X Days
This query returns Customers who have purchased, within the last X days, an Item assigned a certain numeric-type Item Attribute with an indicated value.
Figure 15-44 Purchased Item with Numeric Type Attribute in Last X Days
Basic Programs
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment.
Big Spenders
This query returns all active Customers who have spent at least X dollars in at least Y visits. The amount spent must be a number between 0 and 1,000,000. The number of visits must be a number between 0 and 999.
Figure 15-45 Big Spenders
Reactivation Program
This query returns active and valid Customers who have shopped X times but not in the last Y months. The number of times shopped must be a number between 0 and 1000 and the number of months must be a number between 0 and 120
Figure 15-46 Reactivation Program
Happy Birthday
This query returns all active Customers who have a valid address, have given permission to be contacted by mail, and whose birthday is in the month you selected ([default] = January)
Figure 15-47 Happy Birthday
Happy Anniversary
This query returns all active Customers who have a valid address, have given permission to be contacted by mail, and whose signup date is in the month you selected ([default] = January.
Figure 15-48 Happy Anniversary
Thank You!
This query returns active and valid Customers who have spent at least X dollars in the last Y days. The amount spent must be a number between 1 and 1,000,000 and the number of days must be a number between 1 and 1000 (i.e. 100 dollars in the last 30 days)
Figure 15-49 Thank You
Cross Sell Program
This query returns active and valid Customers who have purchased item X in the last Y days. The number of days must be a number between 1 and 1000
Figure 15-50 Cross Sell Program
Big Spender - Location
This query returns active Customers who have spent at least X dollars in location Y. The amount spent must be a number between 0 and 1,000,000. The location can be any valid location in your organization. You can only specify one (1) location
Figure 15-51 Big Spender Store
Anniversary Program
This query returns active Customers who have purchased item X between Y and Z days ago. The Purchased Item can be any valid item in your organization. The first number of days (Y) must be an integer between 0 and 1000 and the second number of days (Z) must be an integer between 1 and 1000.
Figure 15-52 Anniversary Program
Segment Elements
This section
Customers in Segment
This query returns all Customer who are in the Segment ID specified.
Figure 15-53 Customers in Segment
Customers in Segment Strata Level
This query returns all Customers in a specified strata level within a Segment.
Figure 15-54 Customers in Segment Strata Level
Promotion Activity
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment
Promotion Responders
This query returns all active Customers who have responded to the promotion specified (if Yes is selected in the dropdown selection) or not responded to the promotion (if No is selected). The promotion can be any valid promotion in your organization
Figure 15-55 Promotion Responders
Campaign Responders
This query returns all active Customers who have responded to the campaign specified (if Yes is selected in the dropdown selection) or not responded to the campaign (if No is selected). The campaign can be any valid campaign in your organization.
Figure 15-56 Campaign Responders
Strategic Segments
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment
Customer Status
This query returns valid Customers whose status matches the one you have selected. Your options are “Active” [default] and “Inactive”.
Figure 15-57 Customer Status
Mail File
This query returns all active Customers who have given permission to be contacted by mail and have a valid address.
Figure 15-58 Mail File
Mail File By Home Location
This query returns all active Customers whose home location is or is not the one you have specified, have given permission to be contacted by mail, and have a valid address.
Figure 15-59 Mail File by Home Location
Phone File
This query returns all active Customers who have given permission to be contacted by telephone and have a valid telephone number that is not their Fax number.
Figure 15-60 Phone File
Phone File By Home Location
This query returns all active Customers whose home location is or is not the one you have specified, have given permission to be contacted by phone, and have a valid telephone number that is not their Fax number.
Figure 15-61 Phone File by Home Location
Email File
This query returns all active Customers who have given permission to be contacted by email and have a valid email address
Figure 15-62 Email File
Email File By Home Location
This query returns all active Customers whose home location is or is not the one you have specified, have given permission to be contacted by email, and have a valid email address.
Figure 15-63 Email File by Home Location
Loyalty Segments
In the following queries, you can find the Program IDs by selecting Account -> Card Definition from the Application Menu. The resulting list page will list the Programs. Within the Card Definition, select the Card Prefix associated with the Program; Customer Engagement will then display the associated Programs and Program IDs.
You can find the Level ID by selecting a Loyalty Program from the Program List page (see previous paragraph) and clicking Edit-Level. The resulting page will list the levels along with their Level IDs for the selected program.
This category is available at the Segment Query step of the Create Segment wizard, and controls the customers to select for the Stratified or Unstratified Segment
Program X, Program Level Y, based on First Use Date
This query returns all active Customers who participate in Program X at Level Y and who first used their Loyalty Account within the last Z Days [default], Weeks, or Months
Figure 15-64 Program X, Program Y, Based on First Use Date
Program X, Program Level Y, based on Last Use Date
This query returns all active Customers who participate in Program X at Level Y and who last used their Loyalty Account within the last Z Days [default], Weeks, or Months
Figure 15-65 Program X, Program Y, Based on Last Date
Loyalty Points Earned YTD
This query returns all Customers whose year-to-date earned points balance matches the entered data.
Figure 15-66 Loyalty Points Earned YTD
Loyalty Points Earned LTD
This query returns all Customers whose lifetime-to-date earned points balance matches the entered data.
Figure 15-67 Loyalty Points Earned LTD
Any Program, Member/Non-Member
This query returns all Customers who have Cards/accounts that are or are not part of any Loyalty Program.
Figure 15-68 Any Program, Member/Non-Member
Program X, Program Level Y, Member/Non-Member
This query returns all Customers who have Cards/accounts that are part of Program X and Program Level Y. Program Level Y must be an integer between 0 and 99999.
Figure 15-69 Program X, Program Level Y, Member/Non-Member
Program X, Program Level Y, Loyalty Activity by Location in Last Z Days
This query returns all active Customers who have Cards/accounts that are part of Program X and Program Level Y and who have a loyalty transaction of the type indicated from the location indicated (Location Attribute Name and Value) within the last number of days indicated and whose Loyalty Point total matches your selection.
Figure 15-70 Program X, Program Level Y, Loyalty Activity by Location in Last Z Days
Any Program, Loyalty Activity by Location in Last X Days
This query returns all Customers who have Cards/accounts that are part of any Program and who have a loyalty transaction of the type indicated from the location indicated (Location Attribute Name and Value) within the last number of days indicated, and whose Loyalty Point total matches your selection.
Figure 15-71 Any Program, Loyalty Activity by Location in Last X Days
Strata Queries
These categories control how the customers are broken out into strata for a Stratified Segment, and are available at the Strata Query step of the Create Segment Wizard if a Strata Type of Loyalty or Transaction was selected at the Strata Field step.
The Strata Query step is not available when a Strata Type of Customer was selected at the Strata Field step. Also, this step is not available for an Unstratified Segment.
Loyalty Summary
These options are available at the Strata Query step if you select Strata Type = Loyalty at the Strata Field step.
Program Id X Program Level Y Active/Inactive
This query returns all the Customers who are members of a Program at a certain Program Level who are either Active or Inactive.
Figure 15-72 Program Id X Program Level Y Active/Inactive
Loyalty Activity Type by Date Range
This query returns all the Customers who are members of a Program at a certain Program Level who have performed a certain account activity within a specified date range.
Figure 15-73 Loyalty Activity Type by Date Range
Loyalty Activity Type in Last X Days
This query returns all the Customers who are members of a Program at a certain Program Level who have performed a certain account activity within a certain number of days in the past.
Figure 15-74 Loyalty Activity Type in Last X Days
Loyalty Activity Type YTD
This query returns all the Customers who are members of a Program at a certain Program Level who have performed a certain account activity within the year to date.
Figure 15-75 Loyalty Activity Type YTD
Loyalty Activity Type LTD
This query returns all the Customers who are members of a Program at a certain Program Level who have performed a certain account activity within the lifetime to date of the account.
Figure 15-76 Loyalty Activity Type LTD
Transaction Criteria
These options are available at the Strata Query step if you select Strata Type = Transaction and Based on = Header at the Strata Field step
Location
This query returns the list of Customers who performed a transaction at a certain location.
Figure 15-77 Location
Transactions in last X Months
This query returns the list of Customers who performed a transaction within a certain number of days in the past.
Figure 15-78 Transactions in last X Months
Transaction Date Range
This query returns the list of Customers who performed a transaction within a certain date range.
Figure 15-79 Transaction Date Range
Subtotal Amount
This query returns the list of Customers who have had a subtotal of a certain, defined value.
Figure 15-80 Subtotal Amount
Transaction from Location Group in Last X Days
This query returns the list of Customers who have performed a transaction at a location within a configured location group in the last X days.
Figure 15-81 Transaction from Location Group in Last X Days
Line Item Detail
These options are available at the Strata Query step if you select Strata Type = Transaction and Based on = Detail at the Strata Field step.
Location
This query returns the list of Customers who performed a transaction at a certain location.
Figure 15-82 Location
Transactions in last X Months
This query returns the list of Customers who performed a transaction within a certain number of days in the past.
Figure 15-83 Transactions in Last X Months
Transaction Date Range
This query returns the list of Customers who performed a transaction within a certain date range.
Figure 15-84 Transaction Date Range
Item Department
This query returns the list of Customers who performed a transactions containing an item belonging to a certain item department.
Figure 15-85 Item Department
Item Class
This query returns the list of Customers who performed a transaction containing an item belonging to a certain item class.
Figure 15-86 Item Class
Item Style
This query returns the list of Customers who performed a transaction containing an item belonging to a certain item style.
Figure 15-87 Item Style
Returned Item
This query returns the list of Customers who have either returned or not returned items in a transaction.
Figure 15-88 Returned Item
Item Price
This query returns the list of Customers with a transaction containing an item of a certain price.
Figure 15-89 Item Price
Item Quantity
This query returns the list of Customers with a transaction containing a certain quantity of any item.
Figure 15-90 Item Quantity
Item ID
This query returns the list of Customers who performed a transaction containing a certain item.
Figure 15-91 Item ID
Purchased Item from Location Group in Last Y Days
This query returns the list of Customers who purchased an item within a certain set of items, from a certain set of locations, within a certain number of days in the past.
Figure 15-92 Purchased Item from Location Group in Last Y Days