AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

querying-posthog-data

Explains how to query PostHog data, defaulting to typed runners for supported product analytics. Read it before you write HogQL/SQL. Also read it before you call execute-sql against PostHog. Use it to find or aggregate PostHog entities. These entities include insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, warehouse data, and persons. Use it for trends, funnels, retention, lifecycle, paths, stickiness, web analytics, error tracking, logs, sessions, and LLM traces. Before you calculate a governed business or telemetry measure, check system.information_schema.metrics for an approved definition. Examples include MRR, activation, billable usage, active organizations, and failure rates. Use the approved definition before you derive a measure from raw events or use a typed domain tool. It also covers HogQL differences, system table schemas, functions, query examples, and schema discovery.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read posthog--querying-posthog-data at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 65

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_people table
  • Calculation History: One-to-many via system.cohort_calculation_history

Important Notes

  • Cohorts can reference other cohorts creating nested dependencies
  • realtime cohorts are cleared to NULL type 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