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-mcp.md · 24000

agent actually invoked, use:

coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name))

The advertised catalog: $mcp_tools_list. Each tools/list response emits a $mcp_tools_list event whose $mcp_listed_tool_names property holds the advertised tool names as a JSON array, with tool_count alongside. This is the denominator for "advertised but never called" analysis. Only about half of tool-call sessions carry a tools-list event, and sessions in exec-wrapper mode advertise just the wrapper (['exec', 'render-ui']), so condition per-tool discovery cuts on sessions where the catalog was actually observed. Extract the array with:

JSONExtract(coalesce(toString(properties.$mcp_listed_tool_names), '[]'), 'Array(String)')

The coalesce(..., '[]') is required: the property accessor is Nullable, and JSONExtract of a Nullable into Array(String) is a ClickHouse type error.

Failures with detail. $mcp_tool_call carries $mcp_is_error plus a semantic $mcp_error_type and, for HTTP failures, $mcp_error_status (see the Source column above: $mcp_error_type is an SDK field that PostHog's own server also stamps itself, while $mcp_error_status is stamped only by PostHog's server, so a separately-instrumented server on the SDK alone won't emit it). posthog:query-mcp-tool-failures groups errored tool calls by these two fields and returns each bucket's raw error_type/error_status; pass those to posthog:query-mcp-tool-failure-occurrences for individual errored calls with the free-text $mcp_error_message (sanitized, truncated to 2048 chars — empty on events captured before PostHog's server started emitting it). $mcp_response stays empty on PostHog's hosted server. PostHog's own tool calls also don't emit $exception events — those only exist for separately-instrumented MCP servers.

Example queries

The SQL below is the fallback for cross-tool rankings and custom cuts. For a single tool's numbers, call the typed tool from the table above instead of re-deriving these.

Error rate of one tool (noncanonical breakdown) (single-tool numbers are posthog:query-mcp-tool-stats; use this for a custom predicate after the governed headline):

SELECT
    count() AS total_calls,
    countIf(toBool(properties.$mcp_is_error)) AS errors,
    round(countIf(toBool(properties.$mcp_is_error)) * 100.0 / count(), 1) AS error_rate_pct
FROM events
WHERE event = '$mcp_tool_call'
    -- effective tool name: new-SDK events put the real tool in $mcp_exec_tool_call_name
    AND coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name)) = '<tool-name>'
    AND timestamp >= now() - INTERVAL 7 DAY

Tool-quality matrix (noncanonical breakdown) (error rate + latency percentiles + reach, one row per tool) — this cross-tool ranking has no typed tool; once you've picked a tool, drill into it with posthog:query-mcp-tool-stats, posthog:query-mcp-tool-failures, or posthog:query-mcp-tool-daily-stats:

SELECT
    -- effective tool name: new-SDK events put the real tool in $mcp_exec_tool_call_name,
    -- so grouping on raw $mcp_tool_name would collapse them under the single-exec wrapper
    coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name)) AS tool,
    count() AS total_calls,
    round(countIf(toBool(properties.$mcp_is_error)) * 100.0 / count(), 1) AS error_rate_pct,
    round(quantile(0.5)(toFloat(properties.$mcp_duration_ms))) AS p50_ms,
    round(quantile(0.95)(toFloat(properties.$mcp_duration_ms))) AS p95_ms,
    uniq(distinct_id) AS users,
    countDistinctIf($session_id, $session_id != '') AS sessions
FROM events
WHERE event = '$mcp_tool_call'
    AND coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name)) != ''
    AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY tool
ORDER BY total_calls DESC

Daily activity (success/error split for a time series) — for one tool's daily series prefer posthog:query-mcp-tool-daily-stats; this all-tools version is the custom cut:

SELECT toDate(timestamp) AS day,
    countIf(NOT toBool(properties.$mcp_is_error)) AS successes,
    countIf(toBool(properties.$mcp_is_error)) AS errors
FROM events
WHERE event = '$mcp_tool_call' AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY day ORDER BY day

Harness (client) bucketing

A "harness" is the friendly product label for the MCP client that made a call — "Claude Agent SDK", "OpenAI Codex", "Cursor", … It is resolved server-side by MCPHarnessBreakdownQueryRunner, the single source of truth (products/mcp_analytics/backend/mcp_harness.py).

Prefer the typed tool. For "which harnesses use our MCP, and how reliably?", call the posthog:query-mcp-harness-breakdown tool. It returns calls / errors / error-rate / sessions per harness and accepts the same dateRange / properties / filterTestAccounts filters as the dashboard, so results match the UI exactly — no hand-written bucketing needed. It also accepts an optional toolName to scope the breakdown to one effective tool — but note that scoping also restricts the result to new-SDK events ($mcp_source = 'posthog_mcp_analytics'), so old-SDK and third-party calls for that tool are excluded and a harness can be undercounted. For a one-tool harness cut across all SDK sources, use execute-sql. Anything the typed tools don't express drops to execute-sql below.

Use execute-sql for custom cuts the typed tool doesn't cover (share-of-users, latency percentiles, per-tool, a trends breakdown). Resolution is two steps: resolve a normalized token from the strongest signal available, then bucket it. An event carries only raw signals, over exactly three properties — the x-anthropic-client header ($mcp_vendor_client, with the legacy mcp_vendor_client coalesced for historical rows) is the only thing separating Anthropic's pooled surfaces (Cowork / Claude.ai / Claude Design); Claude Code's build (cli / sdk / vscode / desktop) rides in the User-Agent ($mcp_client_user_agent, also the generic last fallback); and clientInfo.name arrives as $mcp_client_name. The SQL below mirrors harness_label_sql / HARNESS_TOKEN_SQL in mcp_harness.py; keep them in step until a materialized $mcp_harness property exists. (HogQL has no WITH <expr> AS alias, so the normalized name h is computed in a subquery, not a CTE.)

Share of users by harness (answers "what % of my users are on Claude Code"):

SELECT
    harness,
    uniq(distinct_id) AS users,
    round(uniq(distinct_id) * 100.0 / (
        SELECT uniq(distinct_id) FROM events
        WHERE event = '$mcp_tool_call' AND timestamp >= now() - INTERVAL 30 DAY
    ), 1) AS pct_of_users
FROM (
    SELECT
        distinct_id,
        multiIf(
            h = 'claude-code claude-desktop', 'Claude Desktop',
            h = 'claude-code claude-vscode', 'Claude Code (VS Code)',
            startsWith(h, 'claude-code sdk'), 'Claude Agent SDK',
            startsWith(h, 'claude-code'), 'Claude Code',
            h IN ('claude-ai', 'anthropic/claudeai', 'claude-user'), 'Claude.ai',
            h = 'anthropic/api', 'Anthropic API',
            h = 'cowork', 'Cowork',
            h = 'claude-design', 'Claude Design',
            h = 'openai-mcp chatgpt', 'ChatGPT',
            h = 'openai-mcp agent builder', 'OpenAI Agent Builder',
            h = 'openai-mcp responses api', 'OpenAI Responses API',
            -- Codex has two spellings: the `codex-mcp-client` clientInfo.name caught by
            -- the prefix below, and this User-Agent surface. This branch must precede the
            -- generic `openai-mcp` prefix, which would otherwise report it as "OpenAI".
            h = 'openai-mcp codex', 'OpenAI Codex',
            startsWith(h, 'openai-mcp'), 'OpenAI',
            startsWith(h, 'codex'), 'OpenAI Codex',
            startsWith(h, 'grok'), 'Grok',
            startsWith(h, 'cursor'), 'Cursor',
            startsWith(h, 'visual studio code'), 'VS Code',
            h = 'windsurf', 'Windsurf',
            startsWith(h, 'replit'), 'Replit',
            startsWith(h, 'lovable'), 'Lovable',
            h = 'manus', 'Manus',
            h = 'coderabbit', 'CodeRabbit',
            startsWith(h, 'notion'), 'Notion',
            startsWith(h, 'linear'), 'Linear',
            position(h, 'librechat') > 0, 'LibreChat',
            startsWith(h, 'pi-client'), 'Pi',
            startsWith(h, 'kimchi'), 'Kimchi',
            startsWith(h, 'antigravity'), 'Antigravity',
            h = 'poke', 'Poke',
            h = 'opencode', 'opencode',
            startsWith(h, 'kiro'), 'Kiro',
            startsWith(h, 'desktop-commander'), 'Desktop Commander',
            h = 'posthog-cli', 'PostHog CLI',
            -- Ranked top-N lists name an unrecognized client verbatim instead
            -- (`harness_label_or_token_sql`); "Other" is for callers that need the label
            -- confined to the bounded set, e.g. one aggregated into a per-row array.
            'Other'
        ) AS harness
    FROM (
        SELECT
            distinct_id,
            trim(replaceRegexpAll(lower(
                coalesce(
                    -- Vendor header: the SDKs emit $mcp_vendor_client; the unprefixed
                    -- mcp_vendor_client is the legacy name on historical rows from
                    -- PostHog's own server. Both spellings of the value occur.
                    multiIf(
                        lower(coalesce(nullIf(toString(properties.$mcp_vendor_client), ''), nullIf(toString(properties.mcp_vendor_client), ''))) IN ('claudecode', 'claude-code'), 'claude-code',
                        lower(coalesce(nullIf(toString(properties.$mcp_vendor_client), ''), nullIf(toString(properties.mcp_vendor_client), ''))) IN ('claudeai', 'claude-ai'), 'claude-ai',
                        lower(coalesce(nullIf(toString(properties.$mcp_vendor_client), ''), nullIf(toString(properties.mcp_vendor_client), ''))) = 'cowork', 'cowork',
                        lower(coalesce(nullIf(toString(properties.$mcp_vendor_client), ''), nullIf(toString(properties.mcp_vendor_client), ''))) IN ('claudedesign', 'claude-design'), 'claude-design',
                        NULL
                    ),
                    if(lower(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)')) = 'claude-code',
                       trim(concat(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)'), ' ', extract(toString(properties.$mcp_client_user_agent), '[(]([^,)]+)'))),
                       NULL),
                    -- grok.com Connectors carries `grok-` only in the UA; its clientInfo.name
                    -- is the generic "connectors-manager", so promote the grok UA above it.
                    if(startsWith(lower(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)')), 'grok'),
                       trim(concat(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)'), ' ', extract(toString(properties.$mcp_client_user_agent), '[(]([^,)]+)'))),
                       NULL),
                    -- Kimchi likewise names itself only in the UA (clientInfo.name is pi-mcp's generic one).
                    if(startsWith(lower(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)')), 'kimchi'),
                       trim(concat(extract(toString(properties.$mcp_client_user_agent), '^([^/]+)'), ' ', extract(toString(properties.$mcp_client_user_agent), '[(]([^,)]+)'))),
                       NULL),
                    nullIf(nullIf(toString(properties.$mcp_client_name), ''), 'mcp'),
                    nullIf(trim(concat(
                        extract(toString(properties.$mcp_client_user_agent), '^([^/]+)'),
                        ' ',
                        extract(toString(properties.$mcp_client_user_agent), '[(]([^,)]+)')
                    )), ''),
                    ''
                )
            ), '\\s*\\(via mcp-remote[^)]*\\)\\s*', '')) AS h
        FROM events
        WHERE event = '$mcp_tool_call' AND timestamp >= now() - INTERVAL 30 DAY
    )
)
GROUP BY harness
ORDER BY users DESC

The multiIf above is the canonical bucket list. The denominator is total distinct users, so per-harness shares can sum past 100% (one user may use several harnesses). Swap the outer aggregate for other harness cuts — count() for call volume, quantile(0.95)(toFloat(properties.$mcp_duration_ms)) for latency. For query-trends, pass the inner multiIf(...) over the normalized client name as a HogQL breakdown to get the same buckets in a trends series.

Tool co-occurrence (which tool tends to run right before a given tool, within a session) — prefer posthog:query-mcp-tool-neighbors (neighborDirection: before/after); this SQL is the recipe behind it, for custom window logic:

SELECT prev_tool AS tool, count() AS co_occurrences
FROM (
    SELECT $session_id AS conv_id,
        coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name)) AS tool,
        lagInFrame(coalesce(nullIf(toString(properties.$mcp_exec_tool_call_name), ''), toString(properties.$mcp_tool_name)))
            OVER (PARTITION BY $session_id ORDER BY timestamp
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_tool
    FROM events
    WHERE event = '$mcp_tool_call' AND timestamp >= now() - INTERVAL 7 DAY
)
WHERE tool = '<tool-name>' AND prev_tool != '' AND prev_tool != tool
GROUP BY prev_tool ORDER BY co_occurrences DESC LIMIT 5

Swap lagInFrame for leadInFrame to get the tool that runs after.