Aggregation Traits

Aggregation Traits let you compute common metrics using a visual builder — no SQL required. You select a source table, choose an aggregation function, pick the column to aggregate, and optionally add filters. SignalSmith generates and executes the SQL behind the scenes.

When to Use Aggregation Traits

Aggregation Traits are ideal when your metric can be expressed as a single aggregation function over one table:

• “Count of orders per customer”
• “Sum of revenue per account”
• “Average session duration per user”
• “Most recent login date per user”
• “Number of distinct products purchased per customer”

If you need multi-table joins, CTEs, window functions, or other advanced SQL features, use a SQL Trait instead.

Creating an Aggregation Trait

1. Navigate to Segment > Traits and click Create Trait
2. Select Aggregation Trait as the type
3. Choose the entity type this trait applies to (e.g., User)
4. Configure the aggregation using the visual builder (described below)
5. Set the output data type (usually auto-detected based on the function)
6. Configure the evaluation schedule
7. Click Save

The Visual Builder

The aggregation trait builder has four sections:

Source Table

Select the warehouse table to aggregate over. The dropdown lists all tables accessible through your connected warehouse. You can search by table name or schema.

The source table must contain a column that maps to the entity type’s primary key. For example, if you’re building a trait for the User entity type with key user_id, the source table must have a user_id column (or a column you can map to it).

Aggregation Function

Choose one of the supported functions:

Aggregation Column

For all functions except Count, you must select the column to aggregate. The column picker shows the name and detected data type for each column in the source table.

• For Sum and Average, select a numeric column
• For Min and Max, select a numeric, date, or timestamp column
• For Count Distinct, select any column

Entity Key Column

Select which column in the source table contains the entity identifier. This column is used in the GROUP BY clause to produce one value per entity instance.

Filters (Optional)

Add one or more filter conditions to restrict which rows are included in the aggregation. Each filter has:

• Column — The column to filter on
• Operator — Comparison operator (equals, not equals, greater than, less than, contains, is null, is not null, etc.)
• Value — The comparison value

Multiple filters are combined with AND logic. For example, to count only completed orders from the last 90 days:

Relative date values like “90 days ago”, “30 days ago”, and “1 year ago” are supported and automatically computed at evaluation time.

Examples

Total Order Count

• Entity type: User
• Source table: orders
• Function: Count
• Entity key: user_id
• Filters: status equals completed

Generated SQL:

SELECT user_id, COUNT(*) AS trait_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id

Total Revenue

• Entity type: User
• Source table: orders
• Function: Sum
• Aggregation column: order_total
• Entity key: user_id

Generated SQL:

SELECT user_id, SUM(order_total) AS trait_value
FROM orders
GROUP BY user_id

Average Order Value

• Entity type: User
• Source table: orders
• Function: Average
• Aggregation column: order_total
• Entity key: user_id
• Filters: status equals completed

Generated SQL:

SELECT user_id, AVG(order_total) AS trait_value
FROM orders
WHERE status = 'completed'
GROUP BY user_id

Most Recent Login

• Entity type: User
• Source table: login_events
• Function: Max
• Aggregation column: login_timestamp
• Entity key: user_id

Generated SQL:

SELECT user_id, MAX(login_timestamp) AS trait_value
FROM login_events
GROUP BY user_id

Distinct Products Purchased

• Entity type: User
• Source table: order_items
• Function: Count Distinct
• Aggregation column: product_id
• Entity key: user_id

Generated SQL:

SELECT user_id, COUNT(DISTINCT product_id) AS trait_value
FROM order_items
GROUP BY user_id

Viewing the Generated SQL

After configuring your aggregation, you can click View SQL to see the exact query SignalSmith will execute. This is useful for:

• Verifying the query logic matches your intent
• Debugging unexpected trait values
• Understanding how filters translate to WHERE clauses

The generated SQL is read-only — if you need to modify it, convert the trait to a SQL Trait.

Limitations

• Aggregation traits operate on a single source table. For multi-table computations, use SQL Traits.
• Filter values are static or relative dates. For dynamic filters based on other traits, use SQL Traits.
• The GROUP BY is always on the entity key column. For more complex grouping logic, use SQL Traits.

Next Steps

• SQL Traits — Write custom SQL for advanced computations
• Formula Traits — Combine aggregation traits with expressions
• Trait Evaluation — How traits are scheduled and computed