Glossary: Calculation Functions

This topic describes some of the calculation functions available for building expressions in Loyalty.

Member

These functions get information about the current member.

Member Profile

getMemberValue (‘attribute_name’): Returns the value of ‘attribute_name’ for member. Example:

getMemberValue('state') == 'CA'

getPreferenceValue (‘preference_name’): Returns the value for single-value preference ‘preference_name’

getPreferenceValues (‘preference_name’): Returns the list of values for multi-value preference ‘preference_name’

existPreferenceValue (‘preference_name’, ‘value’): Checks if ‘value’ is one the preference values for ‘preference_name.’ Example:

existPreferenceValue ('preferred_stores', 'San Mateo')

countPreferenceValue (‘preference_name’): Counts the number of preference values for ‘preference_name.’ Example:

countPreferenceValue ('preferred_stores') > 2

Member Tier

getMemberTier (): Returns the name of member’s current tier

isMemberTier (‘tier_name’): Checks if member’s tier name matches ‘tier_name’ parameter.

Member Segments

getMemberSegments (): Returns list of all segments that member belongs to.

isMemberSegment (‘segment_name’): Checks if member belongs to segment ‘segment_name’. This expression should be used for Golden Record segments only. It can work on EDP segments if they are marked as Use as Golden Record. However, this will cause segment updates to run significantly slower and is not recommended.

Member Metrics

getMemberMetric (‘metric_name’): Returns the current life time earned value for metric ‘metric_name’. ‘metric_name’ can be a metric or activity type name. Example:

//Get lifetime earned value for metric point

getMemberMetric('point')

getMemberMetric (‘metric_name’, ‘period’): Returns the earned value for metric ‘metric_name’ over ‘period’. ‘period’ parameter may be either an explicit period or one of the predefined period strings. Examples:

//Get year-to-date earned value for metric point

getMemberMetric('point', 'ytd')

//Get earned value over last 5 days for metric point

getMemberMetric('point', 'last5d')

//Get earned value between 01/01/2015 and 03/31/2015 for metric point

getMemberMetric('point', '01/01/2015-03/31/2015')

getMemberMetric (‘metric_name’, filter, ‘period’): Same as getMemberMetric but computes the earned value of the metric from only activities matching the Groovy expression specified in filter. Example:

//get year=to-date eaned value for metric point including only earnings from activities with spend > 300

getMemberMetric("point", {activity-> activity.spend > 300}, 'ytd')

Current Activity

These calculation functions get information for the current activity (that is, the activity being processed).

Header Attributes

getActivityValue (‘attribute_name’): Return the value of header attribute ‘attribute_name’ for current activity. Example:

getActivityValue ('spend')

Line Item Attributes

These functions apply to activities that include line item attributes such as a purchase activity that includes list of items purchased

countActivityItems (): Number of line items in current activity

countActivityItems (filter): Number of line items in current activity matching filter ‘filter’. Example:

count ({item-> item.sku == 'SKU001'})

sumActivityItems (expr): Compute the expression ‘expr’ (a Groovy closure) for each line item, and return the sum of all of them. Example:

sumActivityItems ({item-> item.price * item.quantity})

sumActivityItems (expr, filter): Compute the expression ‘expr’ for each line item matching filter ‘filter’, and return the sum of all of them. Example:

sumActivityItems ({item-> item.price * item.quantity}, {item-> item.sku == 'SKU001'})

maxActivityItems (expr): Compute the expression ‘expr’ for each line item, and return the maximum value among them.

maxActivityItems (expr, filter): Compute the expression ‘expr’ for each line item matching filter ‘filter’, and return the maximum value among them.

minActivityItems (expr): Compute the expression ‘expr’ for each line item, and return the minimum value among them.

minActivityItems (expr, filter): Compute the expression ‘expr’ for each line item matching filter ‘filter’, and return the minimum value among them.

avgActivityItems (expr): Compute the expression ‘expr’ for each line item, and return the average value of them.

avgActivityItems (expr, filter): Compute the expression ‘expr’ for each line item matching filter ‘filter’, and return the average value of them.

Metrics

These functions apply to the metrics relevant to the current activity.

getMetricsBeforeActivity ('points'): Returns the all time value of the given metric chronologically right before the current activity is processed. Example:

// Number of points before activity

#getMetricsBeforeActivity (‘metric_name’)

getMetricForActivity (‘metric_name’): Returns the earned metrics value from earn metrics calculation for the current activity for the specified metric. Can only be used after earned metrics have been calculated, e.g. trigger actions. Example:

// Number of points of the current activity

getMetricsForActivity ('points')

getMetricsAfterActivity (‘metric_name’): Returns the resulting all time value of the given metric chronologically right after the current activity’s earned metrics is calculated. Can be used in trigger actions. Equivalent to the sum of the values returned by getMetricsBeforeActivity and getMetricForActivity. Example:

// Number of points after activity

getMetricsAfterActivity ('points')

getMetricsBeforeActivity(‘metric_name:earn’): You could also specify a total type (Earn/Redeem/Expire) by appending it to the metric name after a colon. Example:

// Number of earn points before activity

getMetricsBeforeActivity('points:earn')

Other Examples:

getMetricsBeforeActivity(‘metric_name:redeem’)

getMetricsBeforeActivity(‘metric_name:expire’)

getMetricsForActivity(‘metric_name:earn’)

getMetricsForActivity(‘metric_name:redeem’)

getMetricsForActivity(‘metric_name:expire’)

getMetricsAfterActivity(‘metric_name:earn’)

getMetricsAfterActivity(‘metric_name:redeem’)

getMetricsAfterActivity(‘metric_name:expire’)

Previous Activities

These functions operate on previously processed Member activities.

countHistoryItems (‘activityType’, ‘period’): Return the count of activities of type ‘activityType’ in period ‘period’. Example:

// Number of purchases this year

countHistoryItems ('purchase', 'ytd')

sum/max/min/avgHistoryItems (‘attributeName’, ‘period’): Return the sum, max, min, or average of an attribute in period ‘period.' Example:

// Number of points earned year-to-date.

sumHistoryItems ('point:earn', 'ytd')

sum/max/min/avgHistoryItems (‘activityType’, ‘attributeName’, ‘period’): Return the sum, max, min, or average of values for attribute ‘attributeName’ for activities of type ‘activityType’ in period ‘period’. “attributeName” could be a header attribute for an activity, or a metric. Example:

// Total spend from purchases

sumHistoryItems ('purchase', 'spend', 'ytd')

sumHistoryItems (valueClosure, activityFilter, itemFilter, ‘period’): This is the most powerful version of sumHistoryItems. The valueClosure is a closure that can return arbitrary values using activity attributes. It is provided with the line item (it) and activity (ac) at run time. Example:

// For purchase activities with spending higher than 100, calculate tax paid for line items that cost more than 30 dollars,

sumHistoryItems ({it, ac->it.price * it.quantity * ac.taxtRate}, {it.sl_type == 'purchase' && it.spend > 100}, {it.price > 30}, 'ytd')

countHistoryItemsWithFilter(‘activityType’, filter, ‘period’): Return the count of activities of type ‘activityType’ in period ‘period’ matching filter ‘filter’.

countHistoryItemsFilter (

'purchase',

{activity-> activity.spend > 10},

'ytd')

Lookups

Lookups provide the capability to extract some calculation logic in tables. A lookup table has one or more key columns, and one or more value columns. An example lookup table, where ‘member_tier’ and ‘booking_code’ are keys and ‘bonus’ and ‘point_bonus’ is value:

member_tier	booking_code	bonus	point_bonus
Silver	E	0.10	0.05
Silver	M	0.25	0.15
Gold	E	0.20	0.20
Gold	M	0.45	0.35
default		0.05	0.05

The example assumes ‘bonus’ is a discount rate and ‘point_bonus’ is additional points awarded to different members.

Instead of coding how to compute the bonus based on member_tier and booking_code in rules using expressions, it can be looked up from the above lookup table.

Note: Lookup tables are .csv files, and not .xsl files

getLookupValue (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the matching value for column ‘value_column.' Example:

getLookupValue(

'bonus_lookup',

[getMemberTier(), getActivityValue('booking_code')],

'bonus')

getLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the list of matching values for column ‘value_column’. Difference from getLookupValue is that in this case, multiple matches are possible. Example:

getLookupValues(

'bonus_lookup',

[getMemberTier()],

'bonus')

inLookup (‘match_value’, ‘lookup_name’, array_of_keys, ‘value_column’): Check if match_value is one of the values in column ‘value_column’ in the lookup table ‘lookup_name’ matching keys in the array ‘array_of_keys.' Example:

inLookup(

'0.10',

'bonus_lookup',

[getMemberTier()],

'bonus')

//Essentially, same as

match_value in getLookupValues (

lookup_name,

array_of_keys, value_column)

countLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the count of matching values for column ‘value_column.'

sumLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the sum of matching values for column ‘value_column’.

maxLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the maximum of matching values for column ‘value_column’.

minLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the minimum of matching values for column ‘value_column’.

avgLookupValues (‘lookup_name’, array_of_keys, ‘value_column’): Lookup the lookup table ‘lookup_name’ based on ‘array_of_keys’ and return the average of matching values for column ‘value_column’.

Analytics-centric Functions

A few additional functions are used mainly in analytics queries.

sum/max/min/avgHistoryItems (‘attributeName’, filter, ‘period’): Return the sum/max/min/average of values for attribute ‘attributeName’, with activities in a given period, further filtered by a filter closure. The function in ‘previous activities’ section with the same name can only filter activity type, is a special version of this function.

sumHistoryItems (valueClosure, filter, ‘period’): This version of the ‘sumHistoryItems’ is even more flexible. The valueClosure is a closure that can return arbitrary values using activity attributes. Examples:

// Total spend from purchases with spending greater than 100 dollars

sumHistoryItems ('spend', {it.sl_type == 'purchase' && it.spend > 100}, 'ytd')

// Total discount from purchases with spending greater than 100 dollars, assumed discountRate is a header attribute for each purchase

sumHistoryItems ({it.spend * it.discountRate},

{it.sl_type == 'purchase' && it.spend > 100}, 'ytd')

groupHistoryItems (‘attributeName’, groupBys, filter, ‘period’): Operate on all activities in a given period ‘period’ filtered by ‘filter’. Sum up the attribute ‘attributeName’ group by one or multiple activity attributes, return a map of Key/Value pairs. Each element in the groupBy list can be an activity header attribute or ‘activity_date’, ‘activity_month’, ‘activity_year.' Example:

// Returns spending on each day in the last 3 months, as a map from Date to spending

groupHistoryItems ('spend', ['activity_date'],

{it.sl_type == 'purchase'}, 'last3m')

Member Functions

Member functions provide a mechanism to encapsulate more involved scenarios in a function that can then be easily consumed in Earn Rules, Tier Rules, Segmentation conditions, etc. Member functions can take parameters and return results.

Notes:

A special parameter ‘rc’ (stands for Rule Context) is implicitly passed to the function.
All calculation functions should be accessed through ‘rc.’
All variables should be explicitly declared with the Groovy ‘def’ construct.

Member Function Example

In this scenario, we need to get the ‘store_id’ attribute of the activity, and lookup ‘stores’ lookup. If the lookup tells us that ‘reward_type’ is alcohol, we need to return the difference of ‘total_amount’ and ‘tax’ attributes of the activity (commonly known as subtotal). If the ‘reward_type’ is not alcohol, we cannot give credit for alcohol purchases and need to deduct them from the subtotal. We do that by looking up each line item’s ‘product_id’ attribute in the ‘product_items’ lookup. We sum the ‘quantity’ * ‘price’ for all the items with ‘alcohol’ as ‘yes’ to calculate the total amount of alcohol items. We then deduct it from the subtotal.

def activity = rc.getActivity();

def sub_total = activity.total_amount - activity.tax

if (rc.getLookupValue ('stores', [activity.store_id], 'reward_type') == 'alcohol') return sub_total

//For visit states subtract alcoholic items

def items = getActivityItems(activity);

def alcohol_total = items.sum {

item ->

def alcohol = rc.getLookupValue ('product_items', [item.product_id], 'alcohol')

(alcohol == 'yes') ? item.quantity * item.price_per_unit : 0;

}

return sub_total - alcohol_total

Supporting Details

Objects Available in Execution Context

While being executed, a variable ‘rc’ (stands for Rule Context) is made available to you. From rc you can access activity and even the line items list.

Examples:

// Access activity

rc.activity

// access activity items

rc.activity.items or directly though

rc.lineitems

Specifying Metric

There are four variants of each metric: earn, redeem, expire, and balance. By default, we return balance value for a metric. To ask for a specific variant of the metric value, simply append the variant to the end of the metric name separated by :.

Examples:

// Year-to-date points redeemed

sumHistoryItems ('point:redeem', {true}, 'ytd')

// Year-to-date points earned

sumHistoryItems ('point:earn', {true}, 'ytd')

Specifying Time Periods

Time period may be specified as:

MM/dd/yyyy-MM/dd/yyyy: Period between the first date and the second date, inclusive
MM/dd/yyyy or MM/dd/yyyy-: Period since first date
alltime: all time
ytd: Year to date
prev<n>ytd: Year to date of previous n years e.g. prev1ytd. If today is 05/05/2017, then it would be 01/01/2016-05/05/2016. Used for period-to-period comparison.
mtd: Month to date
prev<n>mtd: Same period as mtd but of previous n months e.g. prev1mtd. If today is 05/05/2017, then it would be 04/01/2017-04/05/2017. Used for period-to-period comparison.
wtd: Week to date, starts on a Monday
prev<n>wtd: Same period as wtd but of previous n weeks e.g. prev1wtd. If today is 05/05/2017 (Friday), then it would be 04/24/2017-04/28/2017 (Monday to Friday). Used for period-to-period comparison.
qtd: Quater to date
prev<n>qtd: Same period as qtd but of previous n quarters e.g. prev1qtd. If today is 05/05/2017, then it would be 01/01/2017-02/05/2017 since previous quarter is from January to March. Used for period-to-period comparison.
last<n>d: last n days; e.g. last5d
last<n>w: last n weeks; e.g. last3w
last<n>m: last n months; e.g. last2m
prev<n>d: previous n days; e.g. prev5d. The 5 days before the last 5 days. Used for period-to-period comparison.
prev<n>w: previous n weeks; e.g. prev5w. The 5 weeks before the last 5 weeks. Used for period-to-period comparison.
prev<n>m: previous n months; e.g. prev5m. The 5 months before the last 5 minths. Used for period-to-period comparison.

The last 5 days includes today; the last 5 months starts on 06/20/2015 if today is 11/19/2015. the last 5 weeks starts 34 (5 * 7 - 1) days ago. All times are 12am midnight. Date is in UTC.

Groovy Closures

The expressions wrapped in “{}” (and using “->”) are Groovy closures. Among other things, closure in Groovy language allows passing variables into a block of code from surrounding context. Most uses above of closures are where the rule invocation needs to inject a variable, such as item (line item) or activity when processing activities.

In summary, closure follows the syntax:

{closureParameters -> statements}

Expressions dealing with activities, inject one parameter for referencing the activity. Therefore, they are of type:

{activity -> activity.spend > 300}

Expressions dealing with activity line items, inject one parameter for referencing the line item. Therefore, they are of type:

groovy {item -> item.sku == 'SKU0001'}

Note: For single closures, parameter and “->” can be omitted and the implicit parameter “it” may be used. However, for readability, using parameters may be preferred. For example, {it.spend > 300} and {it.sku == 'SKU0001'} are equivalent to previous 2 examples.

Useful Groovy Expressions

Checking if item in list:

'ca' in ['ca', 'ny', 'az']

Commonly used date expressions

Get current date and time:

def now = DateTime.now();

Getting the start of day:

def now = DateTime.now();

def today = now.withTimeAtStartOfDay();

Getting the date N days/months/years before a certain date:

def now = DateTime.now();

def lastYear = now.minusYears(1);

def yesterday = now.minusDays(1);

def 3monthsAgo = now.minusMonths(3);

Getting the date N days/months/years after a certain date:

def now = DateTime.now();

def nextYear = now.plusYears(1);

def tomorrow = now.plusDays(1);

def 3monthsAhead = now.plusMonths(3);

Comparing two dates:

def now = DateTime.now();

def lastYear = now.minusYears(1);

now.isAfter(lastYear);

now.isBefore(lastYear);

Converting a date to a formatted string:

def formattedDate = DateTime.now().toString("MM/dd/yyyy");

Checking if today is N days before/after a certain date:

def now = DateTime.now().withTimeAtStartOfDay();

def startOfYear = now.withDayOfYear(1).withTimeAtStartOfDay();

def todayIs30DaysAfterNewYear = startOfYear.plusDays(30) == now;

def todayIs5DaysBeforeNewYear = startOfYear.minusDays(5) == now;

Converting the timezone of a DateTime object:

DateTime.now().withZone('Asia/Manila')

DateTime.now().withZone('CET')

getMemberValue('date_attribute').withZone('CET')