Custom Properties

Overview

custom property refers to a newly created property that is created by secondary processing or uploading a dimension dictionary based on ordinary properties. You can also create custom properties by establishing a Lookup Tables and introducing other extended properties.

When creating custom properties in the current table, the range of properties that can be referenced includes: ordinary properties of the current table, and properties in the table that have established a dimension association with the current table.

The content described in this document is advanced usage functionality of Sensors Analytics, involving many technical details, and is suitable for experienced users of related functions for reference.

Create custom property

Select in turn Data Fusion > Metadata Management > User Table/Event Table > custom property.
Click on the top right corner Create custom property button.
Fill in property Name. required. Property Name is the unique identifier of the property within the system. It cannot exceed 100 characters, must start with English letters, and only supports lowercase letters, numbers, underscores. It cannot include other characters and cannot be the same as reserved field names.In the same table, property names cannot be duplicated (including ordinary properties)
Fill in Property Display Name. required. The display name of the property during use. Cannot be empty, cannot exceed 100 characters or Chinese characters. Property display names cannot be duplicated in the same table (including ordinary properties).
Select Events that can use this property. Only set in create event custom property. If is user custom property, By default, all events can use this custom property. Supports two methods:
1. Meta events that cover at least one property in an SQL expression
2. Meta events that cover all properties in SQL expressions
Select Data Type. required. Only support <String String>、<Number Number>、<Bool Bool>、<Datetime Datetime>、<Set List> data type.
Add Logic for calculating custom properties. There are 3 ways to determine the value of custom properties:
1. SQL: In property reference syntax Fill in the SQL expression fragment for property processing to complete the creation of a custom property. The precautions are as follows:
  1. When create custom property, SQL expression has a maximum input length of 4096 characters.
  2. In the event table, it supports referencing the event and time fields for secondary processing.
  3. Referencing common properties under the current table, the format is table name. table property name, for example: events.product_id ；Reference established Dimension association relationship format of the properties in the data table is table alias. Table property name, for example product.product_price.
2. Copy Property Value: Upload the general properties of the dictionary, then upload the dictionary file and click save. Only support for <String String>、<Number Number>、<Bool Bool> Type and data source is not traits upload properties to the dictionary.
  1. In Sensors Analytics 3.0 and above, it does not support the direct upload of dimension dictionaries for common properties. If you have such needs, you can achieve this through this feature.
  2. When used in the analysis model later, if you want to use the original value, choose the original common property; if you want to use the dictionary value, select the custom property created above.
3. Parse Property Value: To support more business scenarios' data reporting and analysis needs, event custom properties support object array types, which can parse the event properties whose values are JSON arrays into multiple sub-properties for use in subsequent analysis processes.
  1. Note: Only event custom properties support this creation method.
  2. Steps to use:
    1. Report data for common properties of <String> type, where the value is a JSON array.
    2. Create a custom property of <ObjectArray> type with the method "Parse corresponding value from common properties", choose the above common property, and parse it into sub-properties.
    3. During analysis, you can use the created custom properties and their sub-properties for filtering operations.
  3. Scenario example: When placing an order in the e-commerce industry, information such as product name, product category, and order amount can be stored in a common property order_detail of <String> type through JSON, and then parsed into a custom property of object array type.
  4. JSON data example:
```
"distinct_id": "12345", "event_name": "pay_order" "time": 1437280200354, "order_detail":"[{\"product_name\":\"iPhone 13\", \"product_price\": 5000,\"product_type\": \"手机\", \"cnt\": 3, \"order_price\": 15000},{\"product_name\":\"switch\", \"product_price\": 2000, \"product_type\": \"游戏\",\"cnt\": 1, \"order_price\": 2000},{\"product_name\":\"macbookpro\", \"product_price\": 9000, \"product_type\": \"电脑\", \"cnt\": 1, \"order_price\": 9000},{\"product_name\":\"iphone 14\", \"product_price\": 6000, \"product_type\": \"手机\", \"cnt\": 1, \"order_price\": 6000}]"
```
  5. Analysis scenario example: The created object array type custom property can be used in modules such as event analysis, realizing some analysis scenarios that were previously unsupported, such as:
    1. The order count that contains "mobile phone" category products and the product price is greater than "5000"
    2. The order count that contains "switch" and the purchase quantity is greater than "1"
Fill in the Unit/Format remarks. Optional. Length limit is within 16 characters.
Fill in the property example/description. Optional. Length limit is within 200 characters.
Click the Submit button.

When creating custom properties, which fields are supported and whether fields are required or optional are controlled by Field display configuration.

Manage custom properties

On the custom property list page, you can search, filter, sort, view, edit, delete, and enable/disable the created custom properties.

custom property list page

Supports searching, filtering, and sorting custom properties.

Edit custom Property

In the property list, click operation column edit button, custom properties can be edited, but property names and data types do not support editing.

Enable/Disable custom Property

In the property list, click operation column Enable/Disable button, can Enable/Disablecustom property. After disabling the property, the property will no longer be displayed in various dropdown components in tag segmentation, SenseData analysis, intelligent operation applications, and resources created using this property will also be unavailable.

Common Usage Scenarios

property calculation

Example 1: Suppose two properties are buried when the point is buried:<commodity_price commodity_price> and <final_price of transaction final_price>, all are numerical types, when we want to judge the difference between the price of the commodity and the final price we can directly create a new custom event property <net income Net Income>. At this point, we can use addition, subtraction, multiplication and division operations.

SQL Expression: events.commodity_price - events.final_price

Example 2: We need to calculate the "total principal and interest of financial products" based on existing properties. In this case, we can use the "power function".

SQL Expression: events.capital + events.capital * pow(events.rate_of_interest, events.duration)

property Extraction

Example 1: For example, if we have an event property called $url and we want to extract the property "q=" from $url to analyze it as "search_keyword", we can create a new custom event property using the following expression.

SQL Expression: parse_url(events.$url, 'QUERY', 'q')

parse_url: Parses the URL and returns the extracted value of the specified part. The value to be extracted can be specified as PROTOCOL, HOST, PATH, REF, AUTHORITY, FILE, USERINFO, or QUERY. Normally, we only need to enter the specified URL and the specific part to be extracted as two parameters. However, when the part to be extracted is QUERY, we can enter a third parameter to specify the KEY in the QUERY key-value pair and obtain the specified parameter value.

Example 2: For example, we have a custom event property called error_info_map (string type) which is reported as a JSON string, as shown:

error_info_map: '{\"error\": {\"reason\": \"timeout\", \"code\": 3001}}'

If we want to parse the value of "reason" from this JSON string and use it as an property for analysis, we can create a new custom property using the following expression.

SQL Expression: GET_JSON_OBJECT(events.error_info_map, '$.error.reason')

GET_JSON_OBJECT: Parses the JSON object value from the JSON string according to the specified JSON selector, and the obtained value is of string type.(GET_JSON_OBJECT Impala Official Documentation)

If the given string does not conform to JSON format or the JSON selector does not match any value, NULL will be returned.

The following operators are supported in the JSON selector:

$：Indicates the root node
.: Indicates the child node
[]: Indicates the value index of an array
*: Indicates the wildcard for an array or child node

About JSON Selector, please refer to the official document.

Property Merging

We assume that we have two properties when we embed points: item_id and item_id_1. In fact, they have the same meaning, and we hope to merge them when using them. You can also use the custom property function to define.

SQL Expression: coalesce(events.item_id, events.item_id_1)

coalesce is a standard SQL function that returns the first non-NULL value among all parameters. In this way, when we use new_item_id for analysis, we can achieve the purpose of property merging.

Union Deduplication

In Sensors Analytics, we support the calculation of unique numbers for a certain property, but we do not directly support deduplication of two or more properties. If you have such a requirement, you can also use custom properties to achieve it by defining a new property whose value is the combination of multiple properties that need to be deduplicated.

For example, if we want to calculate the number of unique times that different users browse different products (that is, a user's browsing of the same product is not counted repeatedly, but browsing different products needs to be counted), we can define a custom property as follows:

SQL Expression: concat(cast(events.user_id as string), events.product_id)

concat: Union Deduplication, concatenates all string-type parameters into a string-type value.

High-Precision Decimal

By default, Sensors Analytics' NUMBER type supports only 3 decimal places after the decimal point. If you need to support high-precision types, you can report the property to Sensors Analytics as a string type and then create a high-precision decimal custom property of the NUMBER type.

Send the content that needs to support high-precision to Sensors Analytics as a string type to avoid loss of precision. At this time, we can convert this string into a custom property of high-precision type through custom property.

SQL Expression: cast(events.big_number as decimal(38,16))

Note: Uploading an empty string is not allowed. Otherwise, select * cannot be used in custom queries, and the "data verification" tool cannot be used

Data Verification

Sensors Analytics supports creating a Bool type property to check the data quality of a certain property value using a regular expression. The expression is as follows:

SQL expression: cast ( case when regexp_like(users.user_name, '[a-zA-Z0-9._ //-//[//](){}]{1,15}') then 1 else 0 end as bigint)

regexp_like(string source, string pattern[, string options]) : Check whether the source string contains regular expressions in pattern

Time and date reprocessing

When using time and date functions, only TIMESTAMP type can directly use Impala's time and date functions. So if Date/Datetime type properties are used as parameters for time and date functions, we need to use the EPOCH_TO_TIMESTAMP function to convert the properties to TIMESTAMP type.

Example 1: To analyze the data based on the occurrence time of each event as a grouping item, or to restrict certain roles in permissions to only access "data in the past N days". In this case, we need to create a new custom event property based on the time in the "analysis model" and "role-data permission".

SQL expression: UNIX_TIMESTAMP(events.time)*1000

Example 2: By default, Sensors Analytics supports time aggregation methods such as day, week, and month. If you want other time aggregation methods, you can also use a custom property. For example, if you want to analyze the data according to "Day of Week X", you can extract a day_of_week property from the time property:

SQL expression: dayofweek(EPOCH_TO_TIMESTAMP(events.register_time))

dayofweek: The number obtained by this function, 1 represents Sunday, 7 represents Saturday, and 2-6 represent Monday to Friday. Similarly, the expression extract(hour from time) can be used to extract the hour part of the time.

Example 3：Sensors Analytics supports extracting custom properties from user properties in the users table, such as obtaining the age property based on the user property Birthday. After creating the "custom user property", the expression can be used.

SQL expression: cast (extract(year FROM now()) -extract(year FROM EPOCH_TO_TIMESTAMP(users.Birthday)) as int)

extract: Extract the numeric time domain, such as year, month, date, hour, minute, second/millisecond (year, month, day, hour, minute, second, millisecond), from the TIMESTAMP value, and return the integer value of the time domain.

Note: It is not possible to associate user properties in the user table and event properties in dimension tables or event tables.

More Expressions Reference:

adddate: Add a given number of days to a TIMESTAMP value.
SQL expression: adddate(EPOCH_TO_TIMESTAMP(users.birthday), INT/BIGINT days)
datediff : Returns the number of days between two timestamps.
SQL expression: datediff(EPOCH_TO_TIMESTAMP(events.enddata), EPOCH_TO_TIMESTAMP(events.startdate))

Data Type Conversion

Example 1:Create a <Datetime> type custom property

Create a Datetime type custom property, requiring the custom property expression result to be a bigint type timestamp, and set the custom property type to <Datetime> type.

For example, if an oldtimestamp was initially recorded as a numeric type with a stored timestamp, you can use a custom property to create a new <Datetime> type property for easier date comparison analysis. If the custom property is set on a related dimension table, the related dimension table must store timestamps.

SQL Expression: cast(events.oldtimestamp as bigint)

Example 2:Create a <Bool> type custom property

Create a Bool type custom property, requiring the custom property expression result to be a bigint type with a value of 0 or 1, and set the custom property type to <Bool> type.

For example, to distinguish valid payments from all payment records, you can create a valid payment custom property. If the order payment amount is 0 or null, the custom property's value is No; if the payment amount is anything else, the custom property's value is Yes.

SQL Expression: cast((case when (events.ActualPaidAmount is null or events.ActualPaidAmount=0) then '0' else '1' end) as bigint)

Validation Rules and Common Errors

When numerical calculations involve two properties, both properties must have the same data type;
The operators on both sides of a <Number> type custom property must be numerical operators;
The data type and SQL expression return value must be consistent;
After creating a custom property, you cannot report a regular property with the same name;
When referencing a regular property in the current table, the format must be table_name.property_name, such as events.product_price;
Not supported referencing of regular properties sourced from tags;
If you need to reference fields in a data table that has an established dimension relationship, the format must be table_alias.column_name, such as if an event table is related to a product table and the product table is aliased as commodity, to create a custom property for the event using a field from the product table, you should input commodity.manufacturer.

Permissions

If you have the "View Main Table" and "View Event Table" permissions, you can enter the corresponding page to view custom properties.

If you have the "Manage Main Table" and "Manage Event Table" permissions, you can manage custom properties of user tables and event tables.