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-usage-metrics.md

Usage metrics

GroupUsageMetric (system.usage_metrics)

Usage metrics are team-defined numeric measures that render on Customer Analytics profile pages — for example "weekly active users", "events in the last 7 days", or "revenue over the last 30 days". Each metric compiles a HogQL filter expression over the events table, optionally summing a numeric property.

Not group-specific. Despite the model name (GroupUsageMetric) and the presence of group_type_index, metrics are defined at the team level and applied to both groups and persons. They were originally built for group profiles and later reused for person profiles without renaming; every metric a team defines surfaces on every profile type.

Columns

Column | Type | Nullable | Description --- | --- | --- | --- id | String | NOT NULL | Usage metric UUID. team_id | Integer | NOT NULL | group_type_index | Integer | NOT NULL | Legacy; the query runner ignores it and evaluates every metric regardless. Don't filter on it. name | String | NOT NULL | Metric name. format | String | NOT NULL | Display format: 'numeric' or 'currency'. interval | Integer | NOT NULL | Rolling window length, in days, the metric is computed over. display | String | NOT NULL | How the metric is visualized, e.g. 'number' or 'sparkline'. filters | JSON | NOT NULL | JSON event/action filters ({"events": [...], "actions": [...], "properties": [...]}) or data warehouse filters ({"source": "data_warehouse", "table_name": "...", "timestamp_field": "...", "key_field": "..."}). math | String | NOT NULL | Aggregation: 'count' or 'sum'; 'sum' aggregates math_property. math_property | String | NULL | Property aggregated when math is property-based, e.g. sum.

Key relationships

  • Metrics are referenced by the Customer Analytics profile UI for both group and person profiles. There is no direct FK to insights, dashboards, group types, or persons.
  • The stored (team_id, group_type_index, name) unique constraint is an artifact of the group-only era; treat name as unique per team in practice.

Important notes

  • Metric values are not stored here; they are computed on demand by executing filters against the events table for the profile being viewed. An internal bytecode column (not exposed) caches the compiled filter.
  • interval is stored in days. The API accepts only integer day values; there is no sub-day granularity.
  • Do not assume group_type_index filters the scope of metrics — it doesn't. Treat it as historical metadata.

Common query patterns

List all usage metrics for a team:

SELECT id, name, math, interval, display, format
FROM system.usage_metrics
ORDER BY name

Find all sum-math metrics in the team:

SELECT id, name, math_property, interval
FROM system.usage_metrics
WHERE math = 'sum'
ORDER BY name

Group metrics by the rolling window they use:

SELECT interval, count() AS metric_count
FROM system.usage_metrics
GROUP BY interval
ORDER BY interval