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
orgroup_id
depending on whether the trait is user or company/workspace levelFilter events by timestamp to keep traits relevant and current
Use
JSONExtract*
functions to access values inside properties