Skip to main content

Using SQL Computed Traits

Updated this week

Computed traits in June let you attach dynamic, calculated values to users or workspaces. These values are updated daily and can be used to define audiences, or better understand user behaviour.

If you’re looking to define more custom metrics as traits — such as engagement levels, average usage patterns, or key product actions — SQL is a powerful way to do it.

📘 Before continuing, make sure you’re familiar with our data schema in June.
How to use SQL to explore your data in June

Writing Computed Traits with SQL

When creating computed traits with SQL, you’re typically summarising behavioural data (from the events table) into a single value per user or group. That value becomes a trait.

To create a computed trait, June expects your SQL query to return two columns:

• A user_id or group_id

• A single computed value, labelled as trait_value

Here are some useful examples to get you started:

✅ Total Number of Conversations Created (Last 7 Days)

SELECT
group_id,
count() AS trait_value
FROM
events
WHERE
name = 'conversation_created'
AND timestamp > now() - toIntervalDay(7)
GROUP BY
group_id

🕒 Average Session Duration Per User (Last 30 Days)

SELECT
user_Id,
AVG(JSONExtractFloat(properties, 'duration_seconds')) AS trait_value
FROM
events
WHERE
name = 'session_ended'
AND timestamp > now() - toIntervalDay(30)
GROUP BY
user_id

🔢 Number of unique users who triggered a “message_sent” event

SELECT
group_id,
COUNT(DISTINCT user_id) AS trait_value
FROM
events
WHERE
name = 'message_sent'
GROUP BY
group_id


If you want to go further and calculate a ratio (e.g. active users vs. total users), you can either:

  • Use a subquery to get the total number of users per group

  • Or use two separate computed traits and compare them within June

Tips for Working with Computed Traits

  • Always alias your computed metric as trait_value

  • Use user_id or group_id depending on whether the trait is user or company/workspace level

  • Filter events by timestamp to keep traits relevant and current

  • Use JSONExtract* functions to access values inside properties

Did this answer your question?