Best practices for creating custom properties
A custom property is a property created by performing secondary calculations on stored property fields with an SQL expression. The SQL expressions of custom properties use Trino syntax. For Trino syntax and function usage, see the Trino documentation.
Scenario 1: Time difference calculation
The number of lifecycle days of a user is usually not collected as an event property during tracking. Instead, you can add the user's registration time to event properties and then get the user's lifecycle stage at the time of the event as follows. Use the date_diff(unit, timestamp1, timestamp2) function to calculate the number of days between the user property Registration time and the event property Event time, and generate the custom user property User Lifecycle Days, for example: date_diff('day', date("register_time"), date("#event_time")).
Scenario 2: Type conversion
In practice, the type of a reported property may differ from what you expect. You can use the cast(value AS type) function to convert the property type. Note that if a property value cannot be cast to the expected type, the new property value is empty. Example rule: cast(old_prop_string as int)
Scenario 3: Timestamp conversion
When a tracked property such as the registration time is uploaded as a numeric timestamp, you can use the from_unixtime(unixtime) function to convert it to a time format so that you can filter and group by it in the system. Example rule: from_unixtime("register_time")
Scenario 4: Substring extraction
In some cases, the reported value of a property may combine multiple pieces of information. For example, the value of the "Get reward" property is passed as the string "Gems300". If you want to extract the characters at a fixed position of this property as a new property, use the expression cast(substring("get_reward", 5, 4) as int). The function substring(string, start, length) extracts the segment of length length starting at position start of the string, and the cast(value AS type) function converts the extracted segment to a numeric type for further analysis.
Scenario 5: Combined deduplication
In game data analysis, super properties such as the account ID and server ID are often recorded, but the system currently only supports deduplication by a single property. To deduplicate by both the account ID and server ID, you can create a custom property with the rule concat(server_id, '@',account_id), where the function concat(string1, ..., stringN) concatenates multiple text properties.
Scenario 6: Conditional logic
During game testing, data may be wiped. Although a user's ID stays the same before and after a wipe, the event data before and after the wipe is independent and has no inheritance, and different servers may be wiped at different times. If you want a property that distinguishes whether a user's event occurred before or after the wipe, you can create the following custom property:
case
when "serverid" = 1 and "#event_time" > cast('2020-11-15 10:30:00.000' as timestamp) then 'After wipe'
when "serverid" = 2 and "#event_time" > cast('2020-11-22 10:30:00.000' as timestamp) then 'After wipe'
else 'Before wipe'
end
Scenario 7: Constants
You can use the IF function to create a custom property with a constant value. For example, if you want to also display the cumulative sum of returning users by stage in a retention model, create a custom property with the constant value 1 and then aggregate this property. The rule is as follows:
if("#event_time" is not null, 1, 1). Here, "#event_time" can generally be any system field that is never empty.