After you set up your connection to your relational database, you can continue with setting up your Loyalty segment templates.
Creating segment templates
Prerequisites
- LoyaltyId
The LoyaltyId field should be configured as a text field with 60 characters. - PlanId
Before you start creating segment templates, make sure you created the LoyaltyId field in Emarsys contact database. You can do that under: Management > Field Editor > Create Field.
This field is used to match the contact identifier from the query, which is called contactid.
For each query, you need to define which plan it should run on. Therefore, set up the your Loyalty Plans with their plan identifiers (PlanId) under Loyalty management > Account settings, and then return to set up template segments.
Writing queries
Go to Add-ons > Relational Data > Segment Templates.
Before you start working with Loyalty segments, check the general guide for segment templates to familiarize yourself with them.
To create a new segment template click on Create Segment Template.
For all queries use the loyalty connection, you created in the previous step.
Queries
Each query has the following elements:
- Template name
- Segment template definition
- Parameters
- Underlying SQL query
- In the following queries, you will see that after the From keyword comes
xxxxx. Here, you need to enter the database table form which you can access the data. You need to exchange it with your Emarsys AccountID. For example, if your Account ID is 123456, then you have to put in12345. - Everywhere you see a placeholder
[], under Segment template definition, you need to define it as a parameter. - If you see a word in bold in the Query, it is a parameter you need to drag into the query window from the right side.
- As multiple plans are possible in Loyalty, all following queries create the PlanId as a Customer defined list with user friendly names, for example:
The left side, in green, is the value that you can later choose in the segments. It is a descriptive name, and makes it clear what plan is meant. On the right hand side is the PlanId as defined by you under Loyalty Account Settings.
Location of Account ID
All queries need mapping between the contact identifier in the query (reference field) to contact identifier in contact database (contact reference field).
This is valid for all queries.
- The reference field is always:
contactId - The contact reference field is always: LoyaltyID
Loyalty [Number] of points/credit to next tier
Template name: Loyalty [Number] of points/credit to next tier
Segment template definition: All users who need a [Number] points or credits or less to advance to the next Tier for [PlanId].
Parameters to configure:
- Number
- Parameter Type: Number
- PlanId
- Parameter Type: Customer defined list
- Subtype: Text
- PlanID can be found under Loyalty Management > Account settings
Underlying SQL query:
SELECT contactId
FROM `xxxxxxx`
WHERE
isMember = TRUE and planId = PlanId
and POINTSTONEXTTIER between 1 and Number
Loyalty Points about to expire
This segment is only relevant for Points and Redemption type plans.
Template name: Loyalty Points about to expire
Segment template definition: Points about to expire for [PlanId]
Parameters to configure:
- PlanId
- Parameter Type: Customer defined list
- Subtype: Text.
Underlying SQL query:
SELECT contactId
FROM `xxxxxx`
where isMember = TRUE and expiressoonpoints>0
and planId = PlanIdLoyalty members joined [Number] of days ago
Template name: Loyalty members joined [Number] of days ago
Segment template definition: Users joined [Number] days ago for [PlanId]
Parameters to configure:
- Number
- Type: Number
- PlanId
- Type: Customer defined list
- Subtype: Text
Underlying SQL query:
SELECT contactId
FROM `xxxxxxx`
WHERE
isMember = TRUE and planId = PlanId
and datediff(CURRENT_DATE(), date(from_unixtime(joindate/1000))) = NumberLoyalty members joined starting from [date]
Template name: Loyalty members joined starting from [date]
Segment template definition: All users joined to Loyalty starting from [Date] for [PlanId]
Parameters to configure:
- Date
- Type: Date
- PlanId
- Type: Customer defined list,
- Subtype: Text.
Underlying SQL query:
SELECT contactId
FROM `xxxxx`
WHERE
isMember = TRUE AND
from_unixtime(joindate/1000) >= Date
and planId = PlanIdLoyalty members - all
Template name: Loyalty members - all
Segment template definition: All loyalty members for [PlanId]
Parameters to configure:
- PlanId
- Type: Customer defined list,
- Subtype: Text
Underlying SQL query:
SELECT contactId
FROM `xxxxxxx`
WHERE
isMember = TRUE and planId = PlanIdLoyalty members of tier [Tier]
Template name: Loyalty members of tier [Tier]
Segment template definition: Members of tier [Tier] for [PlanId]
Parameters to configure:
- Tier
- Type:Text
- PlanId
- Type: Customer defined list
- Subtype: Text.
Underlying SQL query:
SELECT contactId
FROM `xxxxxxx`
where
isMember = TRUE and
TIERNAME = Tier and planId = PlanIdLoyalty members selected on status points/credits for PlanId
Template name: Loyalty members selected on status points/credits for PlanId
Segment template definition: Members having status points/credits [Operator] [Points] for [PlanId]
Parameters to configure:
- Points
- Type: Number
- Operator
- Type: Customer defined list
- Subtype: Operator-Type
- PlanId
- Type: Customer defined list
- Subtype: Text
Operators:
Underlying SQL query
SELECT contactId
FROM `xxxxxxx`
where
isMember = TRUE and planId = PlanId
and STATUSPOINTS Operator PointsLoyalty members selected on balance points
This segment is only relevant for Points and Redemption type plans.
Template name: Loyalty members selected on balance points
Segment template definition: Members having balance points [Operator] [Points] for [PlanId]
Parameters to configure:
- Points
- Type: Number
- Operator
- Type: Customer defined list
- Subtype: Operator-Type
- PlanId
- Type: Customer defined list
- Subtype: Text
Underlying SQL query:
SELECT contactId
FROM `xxxxx`
where isMember = TRUE and planId = PlanId
and BALANCEPOINTS Operator Points