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/hogql-extensions.md

HogQL syntax extensions

These functions are unique to HogQL and not available in standard ClickHouse.

Visualization

sparkline(array)

Creates a tiny inline graph from an array of integers. Useful for visualizing trends in table cells.

-- Basic sparkline
SELECT sparkline(range(1, 10)) FROM (SELECT 1)

-- 24-hour pageview sparkline per URL
SELECT
    pageview,
    sparkline(arrayMap(h -> countEqual(groupArray(hour), h), range(0,23))),
    count() as pageview_count
FROM (
    SELECT
        properties.$current_url as pageview,
        toHour(timestamp) AS hour
    FROM events
    WHERE timestamp > now() - interval 1 day AND event = '$pageview'
) subquery
GROUP BY pageview
ORDER BY pageview_count desc

Version handling

sortableSemVer(version_string)

Converts a SemVer version number into a sortable format for ordering purposes.

SELECT DISTINCT properties.$lib_version
FROM events
WHERE event = '$pageview' AND timestamp >= now() - INTERVAL 1 DAY
ORDER BY sortableSemVer(properties.$lib_version) DESC
LIMIT 10

Session replays

recordingButton(session_id)

Creates a clickable button to view the session replay for a given session ID.

SELECT
    person.properties.email,
    min_first_timestamp AS start,
    recordingButton(session_id)
FROM raw_session_replay_events
WHERE min_first_timestamp >= now() - INTERVAL 1 DAY
    AND min_first_timestamp <= now()
ORDER BY min_first_timestamp DESC
LIMIT 10

Actions

matchesAction(action_name)

Filters events that match a named action. Actions are named event combinations defined in PostHog.

SELECT count()
FROM events
WHERE matchesAction('clicked homepage button')

Localization

languageCodeToName(code)

Translates a language code (e.g., 'en', 'fr') to its full language name.

SELECT
    languageCodeToName('en') AS english,  -- English
    languageCodeToName('fr') AS french,   -- French
    languageCodeToName('pt') AS portuguese, -- Portuguese
    languageCodeToName('ru') AS russian,  -- Russian
    languageCodeToName('zh') AS chinese   -- Chinese

HTML rendering

HogQL supports limited HTML tags for rich output in table visualizations. For security, no attributes are supported except for <a> tags.

Supported tags

  • Structure: <div>, <p>, <span>, <pre>, <code>
  • Text formatting: <em>, <strong>, <b>, <i>, <u>
  • Headings: <h1>, <h2>, <h3>, <h4>, <h5>, <h6>
  • Lists: <ul>, <ol>, <li>
  • Tables: <table>, <thead>, <tbody>, <tr>, <th>, <td>
  • Other: <blockquote>, <hr>

Links with <a>

Create clickable links. URLs in Table visualization are automatically clickable, but use <a> for custom link text.

SELECT
    properties.$pathname,
    <a href={f'https://posthog.com/{properties.$pathname}'} target='_blank'>Link</a> as link
FROM events
WHERE event = '$pageview'

Embeddings

embedText(text, model_name)

Converts a text string into an embedding vector at query compile time. Both arguments must be string literals — you cannot pass column references.

SELECT cosineDistance(
    embedding,
    embedText('users seeing checkout errors', 'text-embedding-3-small-1536')
) as distance
FROM document_embeddings
WHERE
    model_name = 'text-embedding-3-small-1536'
    AND timestamp >= now() - INTERVAL 30 DAY
ORDER BY distance ASC
LIMIT 10

Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'.

Text effects

Special tags for visual effects in table output.

<blink>

Makes text blink.

SELECT <span>is this <blink>{event}</blink> real?</span> FROM events

<marquee>

Makes text scroll horizontally.

SELECT <marquee>scrolling text!</marquee> FROM events

<redacted>

Hides text until hovered over.

SELECT <redacted>hidden until hover</redacted> FROM events

Combined example

SELECT
    <span>is this <blink>{event}</blink> real?</span>,
    <marquee>so real, yes!</marquee>,
    <redacted>but this one is hidden</redacted>
FROM events

Funnel functions

The three variants differ only in how the breakdown property column is typed.

aggregate_funnel / aggregate_funnel_array / aggregate_funnel_cohort

7 arguments:

  1. num_steps (Int) — total number of funnel steps
  2. conversion_window_limit (Int) — max seconds between first and last step
  3. breakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_N
  4. funnel_order_type (String) — ordered, unordered, or strict
  5. prop_vals (Array) — breakdown property values to aggregate over
  6. optional_steps (Array(Int)) — 1-indexed step numbers marked as optional
  7. events_array (Array(Tuple)) — pre-sorted array of (timestamp, uuid, breakdown_prop, steps) tuples per person

Returns an array of tuples: (step_reached, breakdown_value, timings, event_uuids, steps_bitmask).

aggregate_funnel_trends / aggregate_funnel_array_trends / aggregate_funnel_cohort_trends

8 arguments:

  1. from_step (Int) — 1-indexed start step for conversion measurement
  2. to_step (Int) — 1-indexed goal step for conversion measurement
  3. num_steps (Int) — total number of funnel steps
  4. conversion_window_limit (Int) — max seconds between first and last step
  5. breakdown_attribution_type (String) — one of first_touch, last_touch, all_events, or step_N
  6. funnel_order_type (String) — ordered, unordered, or strict
  7. prop_vals (Array) — breakdown property values to aggregate over
  8. events_array (Array(Tuple)) — pre-sorted array of (timestamp, interval_start, uuid, breakdown_prop, steps) tuples per person

Returns an array of tuples: (interval_start, success_bool, breakdown_value, event_uuid).