Title: Virtual Properties
Locale: en
URL: https://sensorswave.com/en/docs/data-center/data-dictionary/virtual-properties/
Description: Learn how to create virtual properties via SQL expressions to derive new event and user properties without modifying tracking code
Virtual properties allow you to derive new property fields from existing properties using SQL expressions, without modifying any tracking code. Virtual properties do not alter the original data — they are dynamically computed at query time, naturally supporting lookback across all historical data, and can be used directly for filtering and grouping in all analysis models.

## Use Cases

Virtual properties are designed for the following typical scenarios:

- **Merging inconsistent tracking properties**: Historical versions of a tracking field have different names (e.g., `platform_v1` and `platform`). Use `COALESCE` to merge them into a unified property for analyzing data across tracking changes
- **Channel source classification**: Classify UTM source parameters into standard channel categories (e.g., google/baidu → "Search Engine", facebook/weibo → "Social Media") for analyzing traffic sources by category
- **Calculating derived metrics**: Perform arithmetic on existing numeric properties, such as "Actual Payment" = Original Price - Discount Amount, or "Unit Price" = Order Amount / Item Count
- **Extracting information from URLs**: Extract search keywords, domains, paths, and other parameters from page URLs for analyzing user search behavior and traffic sources
- **User lifecycle segmentation**: Calculate account age based on registration time and segment into labels like "New User", "Growth Stage", "Mature User"

## Two Types

Virtual properties come in two types:

| Type | Attached To | Referenceable Properties | Typical Use |
|------|-------------|--------------------------|-------------|
| **Virtual Event Property** | Event | Event properties `{event.xxx}` + User properties `{user.xxx}` | Calculate unit price, extract URL parameters, classify channels |
| **Virtual User Property** | User | User properties `{user.xxx}` only | Calculate user age, generate registration duration labels, concatenate location info |

> **How to choose?** If the derived property relates to a specific event action (e.g., order amount calculation, URL parameter extraction), use a virtual event property. If the derived property is an inherent user characteristic (e.g., age, days since registration), use a virtual user property.

Virtual event properties are globally available across all events. When a referenced tracking property does not exist for a certain event, the expression returns NULL. You can handle null values using `COALESCE`/`IFNULL` functions.

## Core Concepts

### SQL Expressions

Virtual properties define calculation logic through SQL expressions based on [Apache Doris SQL syntax](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/). Expressions support arithmetic operations, string processing, date calculations, conditional logic, and more, but only scalar functions are supported (aggregate functions like `SUM` and `COUNT` are not supported). Maximum expression length is 2048 characters.

If you need functions not listed in this document, refer to the [Doris scalar functions documentation](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/) for the complete function list.

### Property Reference Syntax

Use curly brace syntax to reference existing properties in expressions:

| Syntax | Meaning | Applicable To | Example |
|--------|---------|---------------|---------|
| `{event.property_name}` | Reference an event property | Virtual event properties only | `{event.total_amount}` |
| `{user.property_name}` | Reference a user property (follows query toggle) | Both types | `{user.city}` |
| `{user.property_name@latest}` | Force reference to user's latest property value | Both types | `{user.vip_level@latest}` |
| `{user.property_name@snapshot}` | Force reference to user property snapshot at event time | Virtual event properties only | `{user.vip_level@snapshot}` |

**Behavior of `{user.property_name}` without a suffix**: The value resolution depends on the "Use Latest User Properties" toggle in the query:

- **Toggle off (default)**: `{user.property_name}` is equivalent to `{user.property_name@snapshot}`, using the user property value at the time the event occurred
- **Toggle on**: `{user.property_name}` is equivalent to `{user.property_name@latest}`, using the user's latest property value

> **Note**: `@latest` requires joining the events table with the users table at query time, which slows down queries. Use it only when you specifically need the latest value — for example, when you need the user's current VIP level rather than the level at the time of an order.

### Data Types

Virtual properties support the following data types:

| Data Type | Description | Use Cases |
|-----------|-------------|-----------|
| STRING | String | Text concatenation, category labels |
| NUMBER | Numeric | Calculation results, ratios |
| BOOL | Boolean | Conditional judgment results |
| DATETIME | Date and time | Time calculation results |

## Virtual Event Properties

Virtual event properties are attached to events and can reference both event properties and user properties. They are suitable for calculations based on event context.

### Creating a Virtual Event Property

1. Go to **Data Center** > **Data Dictionary**
2. Switch to the **Virtual Event Properties** tab
3. Click **Create Virtual Event Property**
4. Fill in basic information:
   - **Property Name**: Unique identifier, cannot be modified after creation. The system automatically adds a `$virtual/` prefix
   - **Display Name**: The name displayed in analysis interfaces
   - **Data Type**: Select the return type of the expression
5. Write the SQL expression:
   - Enter the calculation logic in the expression editor
   - Use the property selector to insert property references, avoiding manual typing
6. Click **Validate** to check expression syntax and property references
7. After validation passes, you can copy the generated sample SQL or click **Go to Custom SQL** to query and verify whether the calculation results meet expectations
8. Click **Save**

### Example 1: Merging Inconsistent Tracking Properties

**Scenario**: After a tracking schema upgrade, the platform property was renamed from `platform_v1` to `platform`. You need a unified property to analyze data spanning before and after the change.

```sql
COALESCE({event.platform}, {event.platform_v1})
```

Data type: STRING. `COALESCE` returns the first non-null value — new data uses `platform`, old data uses `platform_v1`.

### Example 2: UTM Source Channel Classification

**Scenario**: Classify UTM source parameters into standard channel categories for analyzing user traffic distribution by category.

```sql
CASE
  WHEN {event.utm_source} IN ('google', 'baidu', 'bing') THEN 'Search Engine'
  WHEN {event.utm_source} IN ('facebook', 'weibo', 'wechat') THEN 'Social Media'
  WHEN {event.utm_source} IN ('email', 'newsletter') THEN 'Email Marketing'
  WHEN {event.utm_source} IS NULL THEN 'Direct'
  ELSE 'Other'
END
```

Data type: STRING.

### Example 3: Calculating Actual Payment

**Scenario**: The order event has `original_price` and `discount_amount` properties. You need to derive "Actual Payment" for analysis.

| Field | Value |
|-------|-------|
| Property Name | actual_payment |
| Display Name | Actual Payment |
| Data Type | NUMBER |
| SQL Expression | `{event.original_price} - IFNULL({event.discount_amount}, 0)` |

`IFNULL` handles orders without discounts to avoid NULL causing empty calculation results.

### Example 4: Extracting Search Keywords from URL

**Scenario**: Extract the search keyword parameter (e.g., `?q=phone`) from page URLs to analyze user search behavior and keyword distribution.

```sql
EXTRACT_URL_PARAMETER({event.page_url}, 'q')
```

Data type: STRING. Per the [Doris documentation](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/string-functions/extract-url-parameter/), `EXTRACT_URL_PARAMETER` reads a query parameter by name; `PARSE_URL` is for URL components using the two-argument form only (see **URL Parsing** below).

## Virtual User Properties

Virtual user properties are attached to users and can only reference user properties. They are suitable for deriving calculations based on user characteristics.

### Creating a Virtual User Property

1. Go to **Data Center** > **Data Dictionary**
2. Switch to the **Virtual User Properties** tab
3. Click **Create Virtual User Property**
4. Fill in basic information:
   - **Property Name**: Unique identifier, cannot be modified after creation. The system automatically adds a `$virtual/` prefix
   - **Display Name**: The name displayed in analysis interfaces
   - **Data Type**: Select the return type of the expression
5. Write the SQL expression:
   - Only user properties `{user.xxx}` can be referenced; event properties are not allowed
6. Click **Validate** to check expression syntax and property references
7. After validation passes, you can copy the generated sample SQL or click **Go to Custom SQL** to verify
8. Click **Save**

### Example: User Lifecycle Segmentation

**Scenario**: Calculate account age based on registration time and segment into different lifecycle stages for comparative analysis.

```sql
CASE
  WHEN DATEDIFF(NOW(), {user.register_time})  0, 'Income', 'Expense')` |
| `COALESCE(a, b, ...)` | Return first non-null value | `COALESCE({event.platform}, {event.platform_v1})` |
| `IFNULL(a, default)` | Null replacement | `IFNULL({event.channel}, 'Direct')` |
| `NULLIF(a, b)` | Return NULL if equal | `NULLIF({event.status}, '')` |

### Date Functions

| Function | Description | Example |
|----------|-------------|---------|
| `DATE_FORMAT(dt, fmt)` | Format date | `DATE_FORMAT({event.create_time}, '%Y-%m')` |
| `DATEDIFF(expr1, expr2)` | Days between dates (`expr1` − `expr2`; see [Doris docs](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/date-time-functions/datediff/)) | `DATEDIFF(NOW(), {user.register_time})` |
| `NOW()` | Current time | `NOW()` |
| `UNIX_TIMESTAMP(dt)` | Convert to timestamp | `UNIX_TIMESTAMP({event.time})` |

### Type Casting

| Function | Description | Example |
|----------|-------------|---------|
| `CAST(x AS type)` | Type conversion | `CAST({event.price} AS BIGINT)` |

### URL Parsing

| Function | Description | Example |
|----------|-------------|---------|
| `PARSE_URL(url, part)` | Extract a URL part (`part` is case-insensitive); see [PARSE_URL](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/string-functions/parse-url/) | `PARSE_URL({event.url}, 'HOST')` |
| `EXTRACT_URL_PARAMETER(url, name)` | Value of query parameter `name` (first if duplicated); see [EXTRACT_URL_PARAMETER](https://doris.apache.org/docs/3.x/sql-manual/sql-functions/scalar-functions/string-functions/extract-url-parameter/) | `EXTRACT_URL_PARAMETER({event.url}, 'q')` |

Common `part` values include `PROTOCOL`, `HOST`, `PATH`, `QUERY`, `REF`, `FILE`, `PORT`, etc. For a single query key, use `EXTRACT_URL_PARAMETER`, not a three-argument Hive-style `PARSE_URL` call.

## Validation and Preview

### Expression Validation

Click the **Validate** button to check:

- Whether SQL syntax is correct
- Whether referenced properties exist
- Whether property data types are compatible with the expression
- Whether disallowed functions (such as aggregate functions) are used
- Whether event properties are incorrectly referenced in virtual user properties

If validation fails, specific error messages and locations will be displayed.

### Verify in Custom SQL

After validation passes, the system generates a sample SQL statement containing the virtual property expression. You can:

- **Copy SQL**: Copy the generated SQL to your clipboard for execution in other tools
- **Go to Custom SQL**: Click the button to jump directly to the Custom SQL query page, where you can verify whether the virtual property's calculation results meet expectations using real data

This is the recommended approach for confirming expression logic correctness before saving a virtual property.

## Editing and Deleting

### Editing

1. Find the target property in the virtual properties list
2. Click **Edit**
3. Modifiable: display name, data type, SQL expression
4. Not modifiable: property name, property type (event/user)
5. Click **Save** after modification

> **Note**: After modifying an expression, all analysis results using this virtual property will immediately be calculated using the new logic, including historical data.

### Deleting

1. Click **Delete**
2. The system checks whether any charts, dashboards, or cohorts reference this property
   - If there are no references, confirm to delete
   - If references exist, the referencing assets will be listed. You can choose to cancel or force delete
3. After force deletion, assets referencing this property will become invalid

## Using in Analysis Models

Virtual properties are displayed with a `[Virtual]` tag in analysis interfaces and are used the same way as regular properties:

- **As filter conditions**: Filter events or users that meet specific conditions
- **As grouping dimensions**: Group analysis results by virtual property values
- **Applicable to**: Event Analysis, Funnel Analysis, Retention Analysis, User List, User Sequence, Cohorts

## Limitations

| Limitation | Description |
|------------|-------------|
| Aggregate functions | `SUM`, `COUNT`, `AVG`, `MAX`, `MIN` and other aggregate functions are not supported |
| Subqueries | `SELECT` subqueries are not supported |
| Nested references | Referencing another virtual property within a virtual property is not supported |
| Virtual user property reference scope | Can only reference user properties `{user.xxx}`; event properties are not allowed |
| Expression length | Maximum 2048 characters |
| Security restrictions | Expressions undergo security checks; DDL/DML statements, subqueries, and system functions are prohibited |

## FAQ

### What is the difference between `@latest` and `@snapshot`?

- `@snapshot`: Uses the user property value at the time the event occurred. For example, the user's VIP level when they placed an order
- `@latest`: Uses the user property's latest value. For example, the user's current VIP level. Queries are slower because they require JOINing the users table

Without a suffix, the resolution follows the "Use Latest User Properties" toggle in the query. When the toggle is off (default), it is equivalent to `@snapshot`; when on, it is equivalent to `@latest`. `@snapshot` is only available in virtual event properties.

### Are virtual user property values computed in real time?

Yes. Values are recalculated based on current user property values each time a query runs. For example, "User Age" changes automatically over time, and "Days Since Registration" increases daily.

### What if expression validation reports "property does not exist"?

Check that the referenced property name is spelled correctly. It is recommended to use the property selector to insert property references, avoiding spelling errors from manual input.

### Why did query results change after modifying a virtual property?

Virtual properties are dynamically computed at query time. After modifying an expression, queries across all time ranges will use the new logic. This is expected behavior, not a data anomaly.

### Why can't I select event properties when creating a virtual user property?

This is expected behavior. Virtual user properties can only reference user properties `{user.xxx}`. If you need to create a derived field based on event properties, create a virtual event property instead.

---

**Last updated**: April 7, 2026