1. Function overview
Virtual attributes refer to secondary processing based on existing common attribute values, specifically by using SQL expressions.
2. Create a virtual attribute
2.1: Manual creation
Place the mouse over the "New Attribute" box, and a floating window for adding virtual attributes will pop up.
In the New Attribute drop-down button: New General Attribute, New Virtual Attribute, Batch Import Creation
You can create virtual attributes of the corresponding types for event attributes and global attributes.
The new entries for common Attribute and virtual Attribute are separate. When editing an existing Attribute, the Attribute type cannot be modified.
Click the "New Virtual Attribute" button to pop up a page for filling in virtual attribute information. The following describes the relevant information to be filled in for creating a new virtual attribute.
Basic information: It contains the attribute key, attribute name, and attribute description.
Attribute value type: The value type returned by an expression of three types: numeric value, string, and string group must be the same as the configuration
Click [Verify SQL] to verify the expression. Click Save. The SQL statement is automatically verified. If the verification passes, the statement is successfully saved. If the verification fails, the statement cannot be saved. For more information about the verification rules, see SQL input rules in the New Virtual Attribute interface.
The default value is the entire organization. After you turn off the switch, all applications in the current organization are displayed. You can select an application.
When the above information is completed, click the "Save" button below and a virtual attribute will be created.
2.2: Batch Import
Virtual attribute support batch import function, fill in the property information in the template, the content to be filled in the same as manually created, the form template as shown in the following figure:
During bulk import, if the attribute type is virtual attribute, the SQL expression must specify a field. Otherwise, the import fails.
The type of the property value must be the same as that of the return value of the SQL expression. Otherwise, the verification fails.
2.3: Virtual attributes management
Support for editing and deleting virtual attribute
Virtual attribute support attribute value management
The Participate in Analysis button allows you to control whether the virtual attribute participate in the analysis in the analysis interface.
3. Scenario examples
The following examples 3.1, 3.3, 3.4 and 3.5 use event_kv_json fields in the view_dwd_aplus_log_event_ri table. The fields store each attribute key and corresponding value of the system in the form of KV key-value pairs. $sys_is the system attribute,$channel_utm_is the channel attribute,$global_is the global attribute, and the rest are event attributes.
Example 3.2 uses the sys_url field in the view_dwd_aplus_log_event_ri table to store the web page path, which consists of the protocol + domain name + path.
3.1 Attribute compute
Example 1: Assume that the current tracking event has the following attributes:< sales_volume> and < Unit_selling_price >. Both the attributes are numeric. To calculate the revenue, use the following expression: Sales Qty × Sales Unit Price. Unit_selling_price
toInt32OrZero(JSONExtractString(event_kv_json,'$sales_volume'))*toInt32OrZero(JSONExtractString(event_kv_json, '$Unit_selling_price'))
3.2 Attribute value extraction
Example 2: The sys_url property of the existing system stores the URL page path (protocol + domain name + path), for example, https://yuming.com/path1/path2 /
To extract the /path1/path2/ page path, you can use the following expression
extract(sys_url, '/[^/]+(/.*)')
3.3 Attribute value merge
Example 3: Assume that the current tracking event has the following attributes: <Country>, <City>, and <Street>. You need to concatenate these strings to "country-city-street". You can use the following expression
CONCAT(JSONExtractString(event_kv_json,'$Country'),'-',JSONExtractString(event_kv_json, '$City'),'-',JSONExtractString(event_kv_json,'$Street'))
3.4 Time date reprocessing
Example 4: If the data stored in the existing attribute <time date> of the current tracking event is 2023-04-01, you need to convert it to 20230401. Use the following expression
replaceOne(JSONExtractString(event_kv_json,'date'),'-','')
3.5 Data type conversion
Example 5: Assume that the current tracking event has the <millisecond> attribute and needs to convert milliseconds to minutes. The type of the attribute is int. Use the following expression
CAST(JSONExtractInt(event_kv_json, 'millisecond') / 1000 / 60 AS Nullable(Int))