references/models-cohorts.md
Cohorts & Persons
Cohort (system.cohorts)
Cohorts are groups of persons used for segmentation and targeting.
Columns
Column | Type | Nullable | Description
--- | --- | --- | ---
id | Integer | NOT NULL | Cohort id.
team_id | Integer | NOT NULL |
name | String | NOT NULL | Cohort name.
description | String | NOT NULL | Cohort description.
deleted | Integer | NOT NULL | 1 if the cohort has been deleted, 0 otherwise.
filters | JSON | NOT NULL | JSON definition of the cohort's membership filters.
groups | JSON | NOT NULL | Legacy JSON cohort group definitions (superseded by filters).
query | JSON | NOT NULL | JSON HogQL query backing the cohort, if defined as a query.
created_at | DateTime | NOT NULL | When the cohort was created.
last_calculation | DateTime | NOT NULL | When cohort membership was last recalculated.
version | Integer | NOT NULL | Monotonic version bumped on each recalculation.
count | Integer | NOT NULL | Number of people currently in the cohort.
is_static | Integer | NOT NULL | 1 if the cohort is a fixed static list, 0 if dynamically calculated from filters.
Cohort Types
Type | Description
static | Manually uploaded/managed list of persons
person_property | Based on person properties (e.g., email contains "example.com")
behavioral | Based on events performed (e.g., "viewed pricing page in last 30 days")
realtime | Can be evaluated in real-time (< 20M persons)
analytical | Complex queries with temporal/sequential logic via HogQL
Filters Structure Examples
Behavioral filter (performed event):
{
"properties": {
"type": "OR",
"values": [
{
"key": "address page viewed",
"type": "behavioral",
"value": "performed_event",
"negation": false,
"event_type": "events",
"time_value": "30",
"time_interval": "day"
}
]
}
}
Person property filter:
{
"properties": {
"type": "OR",
"values": [
{
"key": "email",
"type": "person",
"value": ["@example.com"],
"negation": false,
"operator": "icontains"
}
]
}
}
Cohort reference filter (nested cohorts):
{
"properties": {
"type": "OR",
"values": [
{
"key": "id",
"type": "cohort",
"value": 8814,
"negation": false
}
]
}
}
Key Relationships
- Persons: Many-to-many via
raw_cohort_peopletable - Calculation History: One-to-many via
system.cohort_calculation_history
Important Notes
- Cohorts can reference other cohorts creating nested dependencies
realtimecohorts are cleared toNULLtype if they exceed 20M persons- Static cohorts are populated via CSV upload or API
- Dynamic cohorts are recalculated periodically
Cohort Calculation History (system.cohort_calculation_history)
Audit trail for cohort calculation jobs.
Columns
Column | Type | Nullable | Description
--- | --- | --- | ---
id | String | NOT NULL | Calculation run UUID.
team_id | Integer | NOT NULL |
cohort_id | Integer | NOT NULL | Cohort that was recalculated; joins to cohorts.id.
count | Integer | NOT NULL | Number of people in the cohort after this calculation.
started_at | DateTime | NOT NULL | When the calculation started.
finished_at | DateTime | NOT NULL | When the calculation finished.
error_code | String | NOT NULL | Error code if the calculation failed; empty on success.
Error Codes
Code | Description
capacity | System busy
interrupted | Socket timeout
timeout | Query timeout (> 1200s)
memory_limit | Memory exceeded
query_size | Query too large
invalid_regex | Regex compilation error
incompatible_types | Type mismatch
no_properties | No filters defined
validation_error | Generic validation error
Queries Structure
[
{
"query": "SELECT ...",
"query_id": "abc123",
"query_ms": 1234,
"memory_mb": 256,
"read_rows": 1000000,
"written_rows": 5000
}
]
Entity Relationships Diagram
system.cohorts (main cohort definition)
├── <- system.cohort_calculation_history.cohort_id
└── persons through `IN COHORT`
Common Query Patterns
Find cohorts by name:
SELECT id, name, count, is_static
FROM system.cohorts
WHERE name ILIKE '%paying%' AND NOT deleted
Get cohort with member count:
SELECT c.id, c.name, c.count, c.last_calculation
FROM system.cohorts c
WHERE c.id = 123
List persons in a cohort (via events):
By cohort ID:
SELECT DISTINCT person_id, person.properties.email
FROM events
WHERE person_id IN COHORT 123
LIMIT 100
List people in a cohort by its name:
select count()
from persons
where id IN COHORT 'Case-sensitive cohort name'
Check cohort calculation history:
SELECT id, started_at, finished_at, count, error_code
FROM system.cohort_calculation_history
WHERE cohort_id = 123
ORDER BY started_at DESC
LIMIT 10
Find people in a cohort of a specific version:
SELECT
tuple(coalesce(toString(properties.email), toString(properties.name), toString(properties.username), toString(id)), toString(id)),
id,
created_at
FROM
persons
WHERE
in(id, (SELECT
person_id
FROM
raw_cohort_people
WHERE
and(equals(cohort_id, 212606), equals(version, 2))))
ORDER BY
id ASC
LIMIT 101
OFFSET 0