Setting the file. One moment.
Skill 103 · Querying PostHog Data
Subchapter 103.36
references/models-cohorts.mdMarkdown5 KBView on GitHub
Cohorts are groups of persons used for segmentation and targeting.
| 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. |
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
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
}
]
}
}raw_cohort_people tablesystem.cohort_calculation_historyrealtime cohorts are cleared to NULL type if they exceed 20M personsAudit trail for cohort calculation jobs.
| 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. |
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
[
{
"query": "SELECT ...",
"query_id": "abc123",
"query_ms": 1234,
"memory_mb": 256,
"read_rows": 1000000,
"written_rows": 5000
}
]system.cohorts (main cohort definition)
├── <- system.cohort_calculation_history.cohort_id
└── persons through `IN COHORT`Find cohorts by name:
SELECT id, name, count, is_static
FROM system.cohorts
WHERE name ILIKE '%paying%' AND NOT deletedGet cohort with member count:
SELECT c.id, c.name, c.count, c.last_calculation
FROM system.cohorts c
WHERE c.id = 123List 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 100List 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 10Find 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