The AI explorer uses GPT-4 to translate questions about your product into SQL queries that can be executed on our database. While the AI explorer is in alpha phase, it's possible that it will sometimes generate SQL queries that don't really work with our database schema.
When that happens, you can refer this guide to get a sense of our database schema and the types of queries you can run on it.
We use ClickHouse as our analytics database, and give you read access to 3 tables: events, users, and companies. This means that you can run SELECT queries on these tables and use ClickHouse functions depending on the data types of the columns you're working with. In most cases, you will use DateTime, String, and Integer functions.
Where to find it
You can find the SQL editor on your dashboard by clicking "Add" then "Add SQL" in the top right corner, like this:
Schema
events
This table contains all the TRACK and PAGE events that you've sent to us.
type | Integer |
name | String |
timestamp | DateTime |
group_id | String |
user_id | String |
properties | String |
context | String |
users
This table contains all the users you've sent to us, along with all of their traits and values, and all the groups associated with them.
companies
This table contains all the companies you've sent to us, along with all of their traits and values, and all the users associated with them.
Example queries
Count all track events
SELECT COUNT() FROM events WHERE type = 2
Count all page events
SELECT COUNT() FROM events WHERE type = 0
Count all distinct users
SELECT COUNT(DISTINCT user_id)
FROM users FINAL
WHERE notEmpty(user_id)
Get all users who belong to a company
SELECT DISTINCT user_id
FROM users FINAL
WHERE has(gids, toIntOrHash('A'))
Get all users where trait staff is true
SELECT DISTINCT user_id
FROM users FINAL
WHERE finalizeTraits(traits)['staff'] = 'true'
Get all groups where trait is_paying is true
SELECT DISTINCT group_id
FROM companies FINAL
WHERE group_type = 1 AND finalizeTraits(traits)['is_paying'] = 'true'
Get all auto-companies where trait is_paying is true
SELECT DISTINCT group_id
FROM companies FINAL
WHERE group_type = 0 AND finalizeTraits(traits)['is_paying'] = 'true'
☝️ Use group_type = 0 when running SQL queries on automatically generated companies, and group_type = 1 if you want to query your own custom groups instead.
Count the events performed by non-staff users between a date range
SELECT
COUNT()
FROM events
WHERE timestamp BETWEEN '2023-01-01' AND '2023-02-01'
AND user_id IN (
SELECT user_id
FROM users FINAL
WHERE finalizeTraits(traits)['staff'] != 'true'
)
Count the events sent with a specific property
SELECT COUNT()
FROM events
WHERE (name = 'signed_up') AND (JSONExtractString(properties, 'source') = 'invited')
🔴 We do not share any user data with OpenAI. We just share the names of your events and the names of your traits 🔴
FAQ:
- What exactly does is_active mean in the generated queries?
It's simply a condition that checks whether that particular event was deleted or not