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-lifecycle.md

Lifecycle (unique users by pageviews)

SELECT
    groupArray(start_of_period) AS date,
    groupArray(counts) AS total,
    status
FROM
    (SELECT
        if(equals(status, 'dormant'), negate(sum(counts)), negate(negate(sum(counts)))) AS counts,
        start_of_period,
        status
    FROM
        (SELECT
            periods.start_of_period AS start_of_period,
            0 AS counts,
            status
        FROM
            (SELECT
                minus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(number)) AS start_of_period
            FROM
                numbers(dateDiff('day', toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toStartOfInterval(plus(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(1)))) AS numbers) AS periods
            CROSS JOIN (SELECT
                status
            FROM
                (SELECT
                    1)
            ARRAY JOIN ['new', 'returning', 'resurrecting', 'dormant'] AS status) AS sec
        ORDER BY
            status ASC,
            start_of_period ASC
        UNION ALL
        SELECT
            start_of_period,
            count(DISTINCT actor_id) AS counts,
            status
        FROM
            (SELECT
                min(events.person.created_at) AS created_at,
                arraySort(groupUniqArray(toStartOfInterval(events.timestamp, toIntervalDay(1)))) AS all_activity,
                arrayPopBack(arrayPushFront(all_activity, toStartOfInterval(created_at, toIntervalDay(1)))) AS previous_activity,
                arrayPopFront(arrayPushBack(all_activity, toStartOfInterval(toDateTime('1970-01-01 00:00:00'), toIntervalDay(1)))) AS following_activity,
                arrayMap((previous, current, index) -> if(equals(previous, current), 'new', if(and(equals(minus(toTimeZone(current, 'UTC'), toIntervalDay(1)), previous), notEquals(index, 1)), 'returning', 'resurrecting')), previous_activity, all_activity, arrayEnumerate(all_activity)) AS initial_status,
                arrayMap((current, next) -> if(equals(plus(toTimeZone(current, 'UTC'), toIntervalDay(1)), toTimeZone(next, 'UTC')), '', 'dormant'), all_activity, following_activity) AS dormant_status,
                arrayMap(x -> plus(toTimeZone(x, 'UTC'), toIntervalDay(1)), arrayFilter((current, is_dormant) -> equals(is_dormant, 'dormant'), all_activity, dormant_status)) AS dormant_periods,
                arrayMap(x -> 'dormant', dormant_periods) AS dormant_label,
                arrayConcat(arrayZip(all_activity, initial_status), arrayZip(dormant_periods, dormant_label)) AS temp_concat,
                arrayJoin(temp_concat) AS period_status_pairs,
                period_status_pairs.1 AS start_of_period,
                period_status_pairs.2 AS status,
                person_id AS actor_id
            FROM
                events
            WHERE
                and(notEquals(properties.$process_person_profile, 'false'), greaterOrEquals(events.timestamp, minus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1)), toIntervalDay(1))), less(events.timestamp, plus(toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1)), toIntervalDay(1))), equals(event, '$pageview'))
            GROUP BY
                actor_id)
        GROUP BY
            start_of_period,
            status)
    WHERE
        and(lessOrEquals(start_of_period, toStartOfInterval(assumeNotNull(toDateTime('2025-12-10 23:59:59')), toIntervalDay(1))), greaterOrEquals(start_of_period, toStartOfInterval(assumeNotNull(toDateTime('2025-12-03 00:00:00')), toIntervalDay(1))))
    GROUP BY
        start_of_period,
        status
    ORDER BY
        start_of_period ASC)
GROUP BY
    status
LIMIT 50000