Cheetah Digital Loyalty Phoenix UDF Guide

1. in_period()

Checks if the given date is within the evaluated period based on the given expression.

• Parameters
  • Date - the date in question.
  • String - time period expression that will be converted to a start date and end date.
    See Time Period Expressions.
• Return
  • Return type is boolean.
  • Returns true if the date in question is within the start and end date, false otherwise.
• Usage
  • in_period(<timestamp>,’<expression>’)
• Notes
  • We can add “Explain” before the actual query to get the start and end date evaluated by the function.
  • When used in “WHERE” clause, please make sure to always provide the right-hand side value (true or false).
• Example

SELECT count(*)
FROM ACTIVITY_TABLE 
WHERE in_period(sl_activity_ts, 'last1m') = TRUE 

2. age()

Computes the age of the given date with respect to the current date.

• Parameters
  • Date - the date whose age will be evaluated.
  • String - Possible values: year, month, day. Defaults to year if the given value is invalid.
• Return
  • Return type is integer.
  • Returns the age of the given date with respect to the given time unit.
• Usage
  • age(<timestamp>,’<year|month|day>’)
• Example

SELECT count(*)
FROM MEMBER_TABLE
WHERE age(member_since, 'month') < 1 

3. get_age_group()

• Parameter
  • Date - the date whose age group will be evaluated (this is in terms of years).
• Return
  • Return type is string
  • Returns the age group of the given date.
  • Age grouping is currently static:
    • <15
    • 15 - 24
    • 25 - 34
    • 35 - 44
    • 45 - 54
    • 55 - 64
    • 65+
    • Other
• Usage
  • get_age_group(<timestamp>)
• Example

SELECT get_age_group(birthdate) AS "Age Group", 
       count(*) 
FROM MEMBER_TABLE 
GROUP BY "Age Group"

4. get_engagement_level()

Matches the given integer/count to an engagement level/grouping

• Parameter
  • Integer - Activity Count
• Return
  • Return type is string
  • Returns the engagement level/grouping.
  • The engagement grouping is currently static:
    • 0
    • 1 - 4
    • 5 - 9
    • 10 - 14
    • 15+
• Usage
  • get_engagement_level(<activity count>)
• Example

SELECT current_tier AS "Tier",
       get_engagement_level(count(*))
FROM MEMBER_TABLE 
GROUP BY "Tier" 

5. sl_sign()

Based on the earn_type, a positive or negative multiplier is returned

• Parameter
  • String the earn type of an activity
• Return
  • Return type is integer
  • Returns 1 if earn type is “earn”, -1 if “expire” or “redeem”, and 0 otherwise.
• Usage
  • sl_sign(earn_type)
• Example

SELECT SUM(sl_sign(earn_type) * %PrimaryMetric) AS "Balance"
FROM ACTIVITY_TABLE

6. get_region()

Get the region of the state.

• Parameter
  • String - the state
• Return
  • Return type is string
  • Returns the region of the given state, currently only US regions are supported.
  • Supported Regions:
    • West
    • Southwest
    • Southeast
    • Midwest
    • Northeast
• Usage
  • get_region(mailing_state)
• Example

SELECT get_region(mailing_state) AS "Region",
       count(*) 
FROM MEMBER_TABLE
GROUP BY "Region" 

7. get_all_time_balance()

Takes a decimal array whose size is 3, as parameter. It computes the all-time balance while assuming that the first element if the all-time earn value, the second element is the all-time redeem value, and the third element is the all-time expire value.

• Parameter
  • Decimal Array - the metric in which to compute the all-time balance from
• Return
  • Return type is decimal
  • Returns array[0] - array[1] - array[2]
• Usage
  • get_all_time_balance(<metric name>)
• Example

SELECT SUM(COALESCE(get_all_time_balance(points),0)) AS "Balance" 
FROM MEMBER_TABLE