[This topic is pre-release documentation and is subject to change.]
Membership in static segment is defined by specifying either the Profile ID for each member or supplying a segmentation query; membership in a dynamic segment is defined by only by the latter. A segmentation query is a filtering expression that describes the desired membership set. Each expression is composed of one or more sub-expressions that each have the form:
- Each expression has one input, either a Profile or a Relationship type. Each expression may contain an optional filter. Filters can be combined with logical operators.
- An expression has an implied output Profile type, representing the the result set.
- With the exception of the first expression, each expression can use as an input one of the following:
- The same input profile or relationship type as the prior expression
- The output profile type of the prior expression
For example, the following is a simple example of a query with the Salesperson profile type used explicitly as input and implicitly as output, and a single filter expression:
A segmentation clause is stored as a JSON string in the SegmentDefinition property of the Segment entity. Segmentation clauses can also be constructed interactively with the Segment Builder in Customer Insights.
Note: The segmentation query for a static segment is typically run only only, during it's initial construction (or explicit updating). In contrast, the segmentation query for a dynamic segment is reevaluated on a periodic basis, whose time interval is specified by the segmentRefreshRateIntervalMinutes property of the associated Segment.
An expression is constructed of one or more of the following elements.
|PROFILE(profile-type-name)||The starting expression for the query that selects all profiles of the specified profile type.
Limitation: queries using UNION, MINUS, INTERSECT or SEGMENT functions must consistently operate on the same profile type.
|SEGMENT(segment-name)||Selects all profiles belonging to a given segment|
|TRAVERSE(relationship-type-name, FILTER(condition))||Function to select the targets of the specified relationship. Optionally a filter condition can be supplied to the narrow the targets.|
|INTERACTION(interaction-type-name, link-type-name)||Select all profiles linked to interaction. (Both parameters are required.) Similar to profile expression where queries can be run only on single interaction.
Limitation: INTERACTION can be used with FILTER and HAVING expressions but not with TRAVERSE.
|HAVING(condition)||Conditional expression that can be used on aggregated interactions|
|FILTER(condition)||Applied to the current selection to exclude items not meeting the specified condition|
|NOT(condition)||Applied to the current selection to exclude items that meet the specified condition|
|COUNT()||Resolves to the number of items in the current selection.*|
|AVG(propertyName)||Resolves to the average of the specified property values in the current selection.*|
|MIN(propertyName)||Resolves to the smallest value of the specified property in the current selection.*|
|MAX(propertyName)||Resolves to the largest value of the specified property in the current selection.*|
|SUM(propertyName)||Resolves to the summation of the specified property values in the current selection.*|
|UNION||Set operation that joins the resulting sets of two queries|
|EXCEPT||Set operation that removes the results of the second query from results from the first query|
|INTERSECT||Set operation that selects the items common to both queries|
|DATETIMEUTCNOW()||Returns the current date and time (minutes precision) in UTC|
|DATEDIFF(datePart, date, date)||Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate. For complete documentation, see DATEDIFF (Transact-SQL).|
|DATEADD(datePart, number, date)||Returns a specified date with the specified number interval added to a specified datepart of that date. For complete documentation, see DATEADD (Transact-SQL).|
* The aggregation functions COUNT, AVG, MIN, MAX, and SUM can be called on inside of a HAVING expression, typically for a purpose of grouping a conditional expression.
The filtering operations supported in an expression depend upon the the data type of the target property as follows:
|Property Type||Supported Filtering Operations|
|DateTime||equals, greater-than, greater-than-or-equals, less-than, less-than-or-equals, not-equals|
|GUID||equals, not-equals, one-of|
|Numeric (byte, decimal,
double, int, long, short)
|equals, greater-than, greater-than-or-equals, less-than, less-than-or-equals, not-equals|
|String||equals, not-equals, one-of|
The following examples are based on a typical Dynamics 365 Customer Engagement schema.
Select all male contacts living in USA from the ages 15 to 25 and income over $50K:
PROFILE(Contact) .FILTER( Sex == 'M' && Country == 'USA' && Age >= 15 && Age <= 25 && Income > 50000 )
Select all contacts from leads where the lead date is after the end date of the campaign (of the lead’s marketing context):
PROFILE(Campaign) .TRAVERSE(ActivityLeadProcessed_Campaign) .FILTER(StartDate > '2017-03-30')
Select all company customers with at least one active purchase of a credit card where the expiration date is a month from today (useful to set up automated campaigns via rolling queries):
PROFILE(Contact) .FILTER( NumberOfActivePurchasedItems > 0 && Product == 'CreditCard' && DATEDIFF(month, ExpirationDate, DATETIMEUTCNOW()) < 1)
Select all CEO contacts having accounts that bought Product "Xgenerator ":
PROFILE(Contact) .FILTER(Position == 'CEO') .TRAVERSE(HavingAccount) .TRAVERSE(Ordered) .FILTER(Name == 'Product Xgenerator')
Select all contacts that have clicked on any link in email 12345 in the last week:
INTERACTION(RedirectLinkClicked, RedirectLinkClicked_ContactLink) .FILTER(EmailId == '12345') .HAVING(COUNT()>0, UTCDAYS(7))
Select all customers whose birthday is within the next month:
PROFILE(Customer) .FILTER(DATEDIFF(month,Birthday,DATETIMEUTCNOW()) == 1)
Filter on all subscriptions that stem from this same email marketing campaign, but leaving out registrations from the first marketing event:
PROFILE(Campaign) .FILTER(Name == 'Campaign 1') .TRAVERSE(EmailSubscriptionSubmit_Campaign) EXCEPT PROFILE(CampaignEvent) .FILTER(Name == 'Event 1') .TRAVERSE(CampaignEventRegistration_CampaignEvent)
Create a compound segment that consists of segments AllFromSeattle and SubscribedToEvent:
SEGMENT(AllFromSeattle) UNION SEGMENT(SubscribedToEvent)