Back to Details

querying-posthog-data

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Querying data in PostHog

在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.
指南包含与
posthog:execute-sql
相同的说明。如果您已阅读过
posthog:execute-sql
，则无需再次阅读本指南。

When to use this skill

何时使用本技能

Finding a specific PostHog entity

查找特定PostHog实体

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.
当用户想要查找在PostHog中创建的特定实体（insights、dashboards、cohorts、feature flags、experiments、surveys、hog flows、data warehouse条目等），或者列表/搜索工具返回的结果过多难以筛选时：
  1. 阅读数据架构下对应的架构参考文档，了解该实体的表和列。
  2. 使用
    posthog:execute-sql
    查询系统表，找到匹配的实体（通常会返回其ID）。
  3. 使用该实体类型对应的专用读取工具（例如
    posthog:insight-get
    、
    posthog:dashboard-get
    ），通过ID检索完整实体。
请勿尝试通过SQL重构实体——
execute-sql
用于发现，读取工具用于检索。

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.
当用户需要分析数据（trends、funnels、retention、paths、sessions、LLM traces、web analytics、errors、logs等），且现有insight架构无法满足需求时：
  1. 在分析查询示例中查找匹配的示例。示例列表并非详尽无遗——可能并非所有场景都有对应示例。如果找到相近的示例（同一领域、类似聚合方式），请阅读该示例；否则跳过此步骤。
  2. 根据用户需求调整找到的示例查询语句，并通过
    posthog:execute-sql
    运行。如果没有合适的示例，则使用数据架构和HogQL参考文档从头编写查询语句。

Data Schema

数据架构

Schema reference for PostHog's core system models, organized by domain:
  • Activity logs
  • Actions
  • Alerts
  • Annotations
  • Batch exports
  • Early Access Features
  • Cohorts & Persons
  • Dashboards, Tiles & Insights
  • Data Warehouse
  • Data Modeling Endpoints
  • Error Tracking
  • Flags & Experiments
  • Hog Flows
  • Hog Functions
  • Integrations
  • LLM analytics reviews
  • Logs
  • 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
以下是按领域分类的PostHog核心系统模型架构参考：
  • Activity logs
  • Actions
  • Alerts
  • Annotations
  • Batch exports
  • Early Access Features
  • Cohorts & Persons
  • Dashboards, Tiles & Insights
  • Data Warehouse
  • Data Modeling Endpoints
  • Error Tracking
  • Flags & Experiments
  • Hog Flows
  • Hog Functions
  • Integrations
  • LLM analytics reviews
  • Logs
  • Notebooks
  • Session Recording Playlists
  • Session Recordings
  • Support Tickets
  • Surveys
  • Usage Metrics
  • SQL Variables
  • read-data-schema工具中跳过的事件
  • 动态用户与事件属性 — 类似
    $survey_dismissed/{id}
    、
    $feature/{key}
    的模式，不会出现在工具结果中

HogQL References

HogQL参考文档

  • 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.
  • 用户属性模式（事件时间 vs 查询时间）。处理
    person.properties.*
    时请阅读本文档，以了解值是历史值还是当前值。
  • Sparkline、SemVer、Session replays、Actions、Translation、HTML标签与链接、文本效果等
  • SQL变量
  • HogQL中的可用函数。重要提示：函数列表较长，建议使用grep等bash命令读取数据。

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)
  • 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)
使用以下示例创建优化的分析查询。
  • Trends（独立用户、特定时间范围、单系列）
  • Trends（多维度拆分的总计数）
  • Funnel（两步、按独立用户聚合、按用户角色拆分、顺序型、14天转化窗口）
  • 转化趋势（Funnel、两步、按独立组聚合、1天转化窗口）
  • Retention（独立用户、未来12周内返回执行事件、重复型）
  • 用户路径（页面浏览、三步、应用路径清理与筛选、最多50条路径）
  • Lifecycle（按页面浏览量统计独立用户）
  • Stickiness（按独立用户的页面浏览量统计、定义为时间区间内至少有一次事件、非累积）
  • LLM trace（生成内容、跨度、嵌入向量、人工反馈、捕获的AI指标）
  • LLM traces列表（通过属性筛选搜索并列出trace、两阶段查询）
  • Web路径统计（路径、访客、浏览量、跳出率）
  • Web流量渠道（直接访问、自然搜索等）
  • 按设备统计Web浏览量
  • Web概览
  • Error tracking（在错误中搜索值并按自定义属性筛选）
  • Logs（按严重程度筛选并搜索关键词）
  • Sessions（列出包含时长、页面浏览量和跳出率的会话）
  • Session replay（列出带活动筛选的录制内容）
  • 团队分类（按计数排序的热门事件、分页）
  • 事件分类（事件属性及示例值）
  • 用户属性分类（用户属性的示例值）