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/example-paths.md

User paths (pageviews, three steps, applied path cleaning and filters, maximum 50 paths)

SELECT
    last_path_key AS source_event,
    path_key AS target_event,
    COUNT(*) AS event_count,
    avg(conversion_time) AS average_conversion_time
FROM
    (SELECT
        person_id,
        path,
        conversion_time,
        event_in_session_index,
        concat(toString(event_in_session_index), '_', path) AS path_key,
        if(greater(event_in_session_index, 1), concat(toString(minus(event_in_session_index, 1)), '_', prev_path), NULL) AS last_path_key,
        path_dropoff_key
    FROM
        (SELECT
            person_id,
            joined_path_tuple.1 AS path,
            joined_path_tuple.2 AS conversion_time,
            joined_path_tuple.3 AS prev_path,
            event_in_session_index,
            session_index,
            arrayPopFront(arrayPushBack(path_basic, '')) AS path_basic_0,
            arrayMap((x, y) -> if(equals(x, y), 0, 1), path_basic, path_basic_0) AS mapping,
            arrayFilter((x, y) -> y, time, mapping) AS timings,
            arrayFilter((x, y) -> y, path_basic, mapping) AS compact_path,
            indexOf(compact_path, NULL) AS target_index,
            if(greater(target_index, 0), arraySlice(compact_path, target_index), compact_path) AS filtered_path,
            arraySlice(filtered_path, 1, 3) AS limited_path,
            if(greater(target_index, 0), arraySlice(timings, target_index), timings) AS filtered_timings,
            arraySlice(filtered_timings, 1, 3) AS limited_timings,
            arrayDifference(limited_timings) AS timings_diff,
            concat(toString(length(limited_path)), '_', limited_path[-1]) AS path_dropoff_key,
            arrayZip(limited_path, timings_diff, arrayPopBack(arrayPushFront(limited_path, ''))) AS limited_path_timings
        FROM
            (SELECT
                person_id,
                path_time_tuple.1 AS path_basic,
                path_time_tuple.2 AS time,
                session_index,
                arrayZip(path_list, timing_list, arrayDifference(timing_list)) AS paths_tuple,
                arraySplit(x -> if(less(x.3, 1800), 0, 1), paths_tuple) AS session_paths
            FROM
                (SELECT
                    person_id,
                    groupArray(timestamp) AS timing_list,
                    groupArray(path_item) AS path_list
                FROM
                    (SELECT
                        events.timestamp,
                        events.person_id,
                        ifNull(if(equals(event, '$pageview'), replaceRegexpAll(ifNull(properties.$current_url, ''), '(.)/$', '\\1'), event), '') AS path_item_ungrouped,
                        replaceRegexpAll(path_item_ungrouped, '^https:\\/\\/[a-z-]+\\.posthog\\.com', 'https://<region>.posthog.com') AS path_item_0,
                        replaceRegexpAll(path_item_0, '\\/project\\/\\d+', '/project/<team_id>') AS path_item_1,
                        replaceRegexpAll(path_item_1, '\\/ai-observability\\/traces\\/[0-9a-f\\-]+', '/ai-observability/traces/<trace_id>') AS path_item_2,
                        replaceRegexpAll(path_item_2, '\\/ai-observability\\/sessions\\/[0-9a-f\\-]+', '/ai-observability/sessions/<session_id>') AS path_item_3,
                        replaceRegexpAll(path_item_3, '\\/ai-evals\\/evaluations\\/[0-9a-f\\-]+', '/ai-evals/evaluations/<evaluation_id>') AS path_item_4,
                        replaceRegexpAll(path_item_4, '\\/ai-evals\\/datasets\\/[0-9a-f\\-]+', '/ai-evals/datasets/<dataset_id>') AS path_item_cleaned,
                        NULL AS groupings,
                        multiMatchAnyIndex(path_item_cleaned, NULL) AS group_index,
                        (if(greater(group_index, 0), groupings[group_index], path_item_cleaned) AS path_item) AS path_item
                    FROM
                        events
                    WHERE
                        and(and(ilike(toString(properties.$pathname), '%ai-%'), notILike(toString(properties.$pathname), '%docs%')), and(greaterOrEquals(events.timestamp, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))), lessOrEquals(events.timestamp, assumeNotNull(toDateTime('2025-12-10 23:59:59')))), notEquals(events.event, '$feature_flag_called'), equals(event, '$pageview'))
                    ORDER BY
                        events.person_id ASC,
                        events.timestamp ASC)
                GROUP BY
                    person_id)
            ARRAY JOIN session_paths AS path_time_tuple, arrayEnumerate(session_paths) AS session_index)
        ARRAY JOIN limited_path_timings AS joined_path_tuple, arrayEnumerate(limited_path_timings) AS event_in_session_index))
WHERE
    notEquals(source_event, NULL)
GROUP BY
    source_event,
    target_event
ORDER BY
    event_count DESC,
    source_event ASC,
    target_event ASC
LIMIT 50

Wildcard groups are a paid feature. Wildcard groups (pathGroupings) — glob-like patterns using * to collapse similar paths into a single node, also usable in exclusions — are part of "Advanced paths", which is only available on paid plans. On the free plan these controls are hidden or disabled in the paths UI. If a user on the free plan asks to add wildcard groups (including in exclusions), explain that this requires upgrading to a paid plan rather than suggesting workarounds.