Search K
Appearance
How to Utilize SQL to Explore Data in Palzin Track
Introducing the AI Explorer and SQL Queries
The AI Explorer utilizes the powerful GPT-4 technology to translate specific product-related questions into SQL queries. These queries can then be executed on our comprehensive database. While the AI Explorer is currently in the alpha phase, it's important to acknowledge the possibility of encountering SQL queries that may not seamlessly integrate with our database schema.
In such cases, this guide will provide you with valuable insights into our database schema and the types of queries that can be executed on it. It's essential to note that our analytics database relies on ClickHouse, granting you the ability to access three significant tables: events, all_user_traits, and group_traits. This access enables you to run SELECT queries on these tables and utilize ClickHouse functions that align with the respective data types in use. Typically, you'll work with DateTime, String, and Integer functions for most scenarios.
Database Schema Details
Schema: feeds
This table contains all the EVENTS that users have sent to us.
| channel* | String | Channel name |
| event* | String | Event name |
| user_id | String | User ID |
| description | String | Event description |
| icon | Emoji | Single emoji as the event icon |
| notify | Boolean | Send push notification |
| tags | key/value | Event tags |
| timestamp | Number | unix timestamp (seconds) |
Schema: Identities
This table contains all the users you've sent to us, along with all of their traits and values.
| project* | String | Project name |
| user_id* | String | User ID |
| properties | Key/Value | User Properties |
| computed_properties | Key/Value | Computed Properties |
Schema: web_track_analytics
This table contains all the Web Track Analytics that users have sent to us.
| title | String | Page Title |
| url | String | URL |
| query_parameter | String | URL Query Parameters |
| created_at | DateTime | Date on which it was created |
| identity_id | String | Identity Id of the user |
| channel_name | String | Channel Name |
| tags | key/value | Tags passed if any |
| referrer | String | Referrer Page |
| country | String | Country |
| path | String | Path |
Sample Queries for Exploration
Counting Track Events
SELECT COUNT() FROM events WHERE type = 2
Counting Page Events
SELECT COUNT() FROM events WHERE type = 0
Counting Distinct Users
SELECT COUNT(DISTINCT user_id)
FROM all_user_traits
WHERE notEmpty(user_id)
Retrieving Users Belonging to a Company
SELECT DISTINCT user_id
FROM group_traits
WHERE group_id = 'A'
Retrieving Users with 'staff' Trait Set to True
SELECT
DISTINCT user_id
FROM (
SELECT
user_id,
key,
argMax(value, timestamp) AS _value
FROM all_user_traits
GROUP BY user_id, key
)
WHERE key = 'staff' AND _value = 'true'
Retrieving Companies with 'is_paying' Trait Set to True
SELECT
DISTINCT group_id
FROM (
SELECT
group_id,
key,
argMax(value, timestamp) AS _value
FROM group_traits
GROUP BY group_id, key
)
WHERE key = 'is_paying' AND _value = 'true'
Counting Events by Non-Staff Users within a Date Range
SELECT
COUNT()
FROM events
WHERE timestamp BETWEEN '2023-01-01' AND '2023-02-01'
AND user_id IN (
SELECT
DISTINCT user_id
FROM (
SELECT
user_id,
key,
argMax(value, timestamp) AS _value
FROM all_user_traits
GROUP BY user_id, key
)
WHERE key = 'staff' AND _value != 'true'
)
Event Property Breakdown
SELECT properties_values[indexOf(properties_keys, '')] AS , COUNT(*) as count
FROM events
WHERE name = '' AND has(properties_keys, '')
GROUP BY
ORDER BY count DESC
By utilizing Open.ai, we do not disclose any user-related data. However, we do receive the names of your events and traits.
FAQ:
- What does "is_active" signify in the generated queries?
Simply put, "is_active" determines whether a particular event has been deleted or not.
- Could you provide more information about your schema?
- What type of information can I inquire about using AI?