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. 查看「数据Schema」下对应的Schema参考，了解该实体的表和列。
  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 Schema不符合需求时：
  1. 在「分析查询示例」中查找匹配的示例。示例列表并不详尽——可能没有覆盖所有场景。如果有相近匹配（同一领域、相似聚合方式），请阅读该示例；否则跳过此步骤。
  2. 调整找到的示例查询以适应用户需求，并通过
    posthog:execute-sql
    运行。如果没有合适的示例，则结合「数据Schema」和「HogQL参考」从头编写查询。

Data Schema

数据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核心系统模型的Schema参考，按领域分类：
  • 活动日志
  • 操作
  • 告警
  • 注释
  • 批量导出
  • 早期访问功能
  • Cohorts & 用户
  • Dashboards、Tiles & Insights
  • 数据仓库
  • 数据建模端点
  • 错误追踪
  • Flags & 实验
  • Hog Flows
  • Hog Functions
  • 集成
  • LLM分析评审
  • 日志
  • 笔记本
  • 会话录制播放列表
  • 会话录制
  • 支持工单
  • 调查
  • 使用指标
  • SQL变量
  • 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、会话重放、操作、翻译、HTML标签和链接、文本效果等
  • SQL变量
  • HogQL中的可用函数。重要提示：列表较长，建议使用bash命令（如grep）读取数据。

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