Segment Queries

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.

Overview

This chapter addresses the following;

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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the Valid Email Addresses

Email Address Type

This query returns all Customers whose email type matches your selection.

Figure 15-20 Email Address Type

This figure shows the 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

This figure shows the 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

This figure shows the Logical Attribute Value

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

This figure shows the Data Attribute Value

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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the Value of Spending in 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the Program X, Program Level Y, based on Last Use 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

This figure shows the 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

This fgure shows the 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

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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the 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

This figure shows the Purchased Item from Location Group in Last Y Days.