querying-posthog-data

name

querying-posthog-data

description

Required reading before writing any HogQL/SQL or calling execute-sql against PostHog. Use whenever the user wants to search, find, or do complex aggregations PostHog entities (insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, data warehouse, persons, etc.) and query analytics data (trends, funnels, retention, lifecycle, paths, stickiness, web analytics, error tracking, logs, sessions, LLM traces). Covers HogQL syntax differences from ClickHouse SQL, system table schemas (system.*), available functions, query examples, and the schema-discovery workflow.

Querying data in PostHog

The guidelines contain the same instructions as posthog:execute-sql. If you've already read posthog:execute-sql, you don't need to read them again.

When to use this skill

Finding a specific PostHog entity

When the user wants to find a specific entity created in PostHog (insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, data warehouse items, etc.), or when a list/search tool returns too many results to narrow down:

1. Read the appropriate schema reference under Data Schema to understand the entity's table and columns.
2. Use posthog:execute-sql to query the system table and find the matching entity (typically returning its ID).
3. Use the dedicated read tool for that entity type (e.g. posthog:insight-get, posthog:dashboard-get) to retrieve the full entity by ID.

Don't try to reconstruct the entity from SQL — execute-sql is for discovery, the read tool is for retrieval.

Querying analytics data

When the user wants analytics data (trends, funnels, retention, paths, sessions, LLM traces, web analytics, errors, logs, etc.) and the existing insight schemas don't fit the request:

1. Look for a matching example under Analytics Query Examples. The list is not exhaustive — there may not be an example for every scenario. If one is a close fit (same domain, similar aggregation), read it; otherwise skip this step.
2. Adapt the example query (if one was found) to the user's request and run it via posthog:execute-sql. If no example fit, compose the query from scratch using the Data Schema and HogQL References.

Data Schema

Schema reference for PostHog's core system models, organized by domain:

• Activity logs
• Actions
• Alerts
• Annotations
• APM / tracing (posthog.trace_spans)
• Batch exports
• Early Access Features
• Cohorts & Persons
• Customer analytics custom properties (system.custom_property_definitions)
• Dashboards, Tiles & Insights
• Data Warehouse
• Data Modeling Endpoints
• Error Tracking
• Flags & Experiments
• Heatmaps (heatmaps data + system.heatmaps_saved)
• Hog Flows
• Hog Functions
• Integrations
• AI observability events (posthog.ai_events)
• AI observability reviews
• Logs (logs data plane + saved views and alerts)
• MCP analytics ($mcp_tool_call events)
• Metrics (posthog.metrics)
• Notebooks
• Session Recording Playlists
• Session Recordings
• Support Tickets
• Surveys
• Usage Metrics
• SQL Variables
• Skipped events in the read-data-schema tool
• Dynamic person and event properties — patterns like $survey_dismissed/{id}, $feature/{key} that don't appear in tool results

HogQL References

• Person property modes (event-time vs query-time). Read when working with person.properties.* to understand if values are historical or current.
• Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more
• SQL variables.
• Available functions in HogQL. IMPORTANT: the list is long, so read data using bash commands like grep.

Analytics Query Examples

Use the examples below to create optimized analytical queries.

• Trends (unique users, specific time range, single series)
• Trends (total count with multiple breakdowns)
• Funnel (two steps, aggregated by unique users, broken down by the person's role, sequential, 14-day conversion window)
• Conversion trends (funnel, two steps, aggregated by unique groups, 1-day conversion window)
• Retention (unique users, returned to perform an event in the next 12 weeks, recurring)
• User paths (pageviews, three steps, applied path cleaning and filters, maximum 50 paths)
• Lifecycle (unique users by pageviews)
• Stickiness (counted by pageviews from unique users, defined by at least one event for the interval, non-cumulative)
• LLM trace (generations, spans, embeddings, human feedback, captured AI metrics)
• LLM traces list (searching and listing traces with property filters, two-phase query)
• Web path stats (paths, visitors, views, bounce rate)
• Web traffic channels (direct, organic search, etc)
• Web views by devices
• Web overview
• Error tracking (search for a value in an error and filtering by custom properties)
• Logs (filtering by severity and searching for a term)
• Cross-signal correlation (metric exemplar → trace → logs)
• Sessions (listing sessions with duration, pageviews, and bounce rate)
• Session replay (listing recordings with activity filters)
• Team taxonomy (top events by count, paginated)
• Event taxonomy (properties of an event, with sample values)
• Person property taxonomy (sample values for person properties)