• Trust
  • Submit a request

    • Need assistance with your Emarsys product?
    We'll get you the help you need!

     

    Emarsys client

     

    Emarsys client
    SAP for Me Login

    You can contact our support team via the SAP for Me.

    The Emarsys component: CEC-EMA

    		Login to the Help Portal and Submit your request. 
    1. Emarsys
    2. Emarsys Loyalty
    3. Loyalty Implementation & Activation

    Implementation:: Setting up Loyalty segment templates
    Updated

    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 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.

    Loyalty RDS Table

    The Loyalty table includes field types which will be used in the below mentioned queries. You can build your own query based on this table:

    Field Name

    Comment

    Field Type

    contactId

    This is the contact’s id in Loyalty. It has the same value as the LoyaltyID in the Emarsys contactDB.

    string

    planId

    The planId to which the contact belongs.

    string

    groupId

    In case the contact belongs to a Loyalty group this is the groupID the contact is in.

    string

    isMember

    Is the contact a Loyalty member or not.

    string

    joinDate

    Unix timestamp in millisec of when contact joined loyalty.

    integer

    tierName

    The contact’s tier name.

    string

    tierEntryAt

    Unix timestamp in millisec of when contact entered the tier.

    integer

    balancePoints

    The number of points to spend.

    float

    pendingPoints

    The number of pending points.

    float

    expiresSoonPoints

    The number of points that expire (According to the configuration under Loyalty Management).

    float

    statusPoints

    The number of status points.

    float

    pointsToNextTier

    The number of status points required to reach the next tier.

    float

    purchasesToNextTier

    The number of purchases required to reach the next tier.

    integer

    redeemedPoints

    The number of points redeemed.

    		 integer

    Queries

    Each query has the following elements:

    1. Template name
    2. Segment template definition
    3. Parameters
    4. 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 in 12345.
    • 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
    Click to copy

    Loyalty Points about to expire

    This segment is only relevant for Points and Redemption type plans, it refers to the Spend Points and not Status Points.

    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 = PlanId
    Click to copy

    Loyalty 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))) = Number
    Click to copy

    Loyalty 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 = PlanId
    Click to copy

    Loyalty 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 = PlanId
    Click to copy

    Loyalty 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 = PlanId
    Click to copy

    Loyalty 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 Points
    Click to copy

    Loyalty 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
    Click to copy

    Loyalty tier expires in [number] days

    Template name: Loyalty tier expires in [number] days

    Segment template definition: All users whose tier expires in [NumberOfDays] Days, belong to the [TierName] tier and currently have between [MinNumberPoints] and [MaxNumberPoints] (status+pending) points for plan [PlanId]

    Parameters to configure:

    • NumberOfDays
      • Type: Number
    • TierName
      • Type: Text
    • MinNumberPoints
      • Type: Number
    • MaxNumberPoints
      • Type: Number
    • PlanId
      • Type: Text

    Underlying SQL query:

    SELECT contactid
FROM `xxxxxxx`
WHERE isMember =  TRUE
and date_sub(date_add(Date(from_unixtime(tierEntryAt/1000)), interval 24 MONTH ),interval numberOfDays day)= Date(NOW())  
and tiername=  tierName
and (statusPoints + pendingPoints) between minNumberPoints  and maxNumberPoints
and planId = PlanID
    Click to copy
    Was this article helpful?
    0 out of 0 found this helpful
    Return to top