# Best Practices for Creating Custom Properties
Custom properties refer to the properties created using SQL expressions for the secondary computation of properties that have been put in storage. SQL expressions of custom properties adopt Trino grammar. You may visit the Trino Document (opens new window) to obtain Trino grammar and usage of its functions.
Application Scenario 1: Computation of Time Difference
When tracking data, the number of days of the user life cycle is generally not collected as an event property. Hence, user registration time can be added to the event property and the following approach can be applied to obtain the life cycle of events generated by a user. The function date_diff(unit, timestamp1, timestamp2)can be used to compute the number of days between the user property "Registration Time" and the event property "Event Occurrence Time", thereby generating the "Number of Days of User Life Cycle". For example, date_diff('day', date("register_time"), date("#event_time")).
Application Scenario 2: Type Conversion
During the process of analysis, the data type of the above-mentioned reported properties might be inconsistent with expectations. In this case, you can convert the data type of property using the cast (value As type) function. It should be noted that if the property value cannot be coercively converted into the expected type, the value of the new property will be null. For example, cast old_prop_string as int).
Application Scenario 3: Timestamp Conversion
When custom properties like "registration time" are uploaded as a timestamp in the numerical type, the function from_unixtime(unixtime)can be used to convert the property into the time format in order to facilitate filtering and grouping operations in the system. For example, from_unixtime("register_time").
Application Scenario 4: Substring
In certain circumstances, the content reported by a property may be a combination of multiple pieces of information. For example, the value of the "Obtain Rewards" property is converted into a character string "ObtainDiamonds300". In this case, if you want to string out the fixed position of characters from the property and use it as a new property, you can use the following expression: cast(substring("get_reward", 5, 4) as int), where the function substring(string, start, length) is used to string out the segment starting with start with a length oflength in the character string, and the function cast(value AS type) is used to convert the string type into the numerical type for subsequent analysis.
Application Scenario 5: Joint Deduplication
In some game data analysis scenarios, public event properties such as account ID and server ID are usually recorded. However, the system only offers deduplication computation for single properties. If joint depublication needs to be performed based on account ID together with server ID, you can create a custom property based on the following rule: concat(server_id, '@',account_id), where the function concat(string1, ..., stringN)can be used to join multiple textual properties.
Application Scenario 6: Conditional Statement
Archival clearance may be performed during the game test stage. Although the user ID remains unchanged, the event data are independent before and after archival clearance and do not have any inheritance relationship. In addition, the time of archival clearance for different regional servers may vary. In this case, if you want to use a property to discriminate whether a user's event falls before or after the archival clearance, a custom property can be created using the following rule:
case
when "serverid" = 1 and "#event_time" > cast('2020-11-15 10:30:00.000' as timestamp) then 'after archival clearance'
when "serverid" = 2 and "#event_time" > cast('2020-11-22 10:30:00.000' as timestamp) then 'after archival clearance'
else 'before archival clearance'
end
Application Scenario 7: Constant
The IF function can be used to create a custom property with a constant. For example, if you want to display the phasic cumulative sum of revisiting players in the retention model, a custom property with a constant of 1 can be created in order to perform aggregate computation on the property. The rule is as follows: if("#event_time" is not null, 1, 1), where "#event_time" is usually set as a non-null system field.