PostHog 问卷调查诊断 SQL 指南基于 HogQL 只读查询排查「显示/发送」缺口与问题响应【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog导读本文以 PostHog 开源仓库中 Surveys 调试技能的官方诊断参考diagnostic-queries.md为核心骨架整理出一整套面向「调查问卷没收到响应、响应数量异常、响应内容不完整」等问题的只读 HogQL 诊断查询集。所有查询均通过 PostHog MCP 的execute-sql在客户项目上运行只读、不改数据覆盖从投放层gating 标志位、静态分群、事件层shown/sent/dismissed/abandoned 漏斗、到内容层逐题答案、作答率、提交延迟的全链路排查。读完本文你将掌握 10 类可直接复制的诊断查询并理解getSurveyResponse()这一产品自用 HogQL 函数的底层实现原理与正确用法。适用前提与数据模型运行前需要明确几个前提查询通过 PostHog MCP 的execute-sql执行属于只读查询可安全用于生产项目诊断。每个查询中的SURVEY_ID、TEAM_ID、CUTOFF变更时间点、WINDOW_START统计窗口起点需按实际情况替换SURVEY_ID可在产品后台的 Survey 配置中获取。涉及的表有三个命名差异是常见坑events事件表问卷的survey shown/survey sent/survey dismissed/survey abandoned等事件都落在这里static_cohort_people静态分群成员表注意不是person_static_cohort——那是 ClickHouse 侧的底层表名HogQL 对外暴露的名称是static_cohort_peoplepersons用户表用于关联邮箱等 person 属性。查询中大量出现properties.$survey_id、properties.$survey_submission_id、properties.$survey_completed、properties.$survey_questions等$前缀属性它们由 Web / 移动端 SDK 在采集问卷事件时写入。以下诊断场景一一展开。每一节都给出可直接运行的 SQL、判定口径以及「看到什么结论是什么」的解读。场景一改动前后「shown vs sent」对比——区分「无人可见」与「可见但少交」这是排查的第一步也是「一个响应都没有 vs 响应变少」的分诊器先判断问题出在投放层用户根本没看到问卷还是渲染/提交层看到了但没交上来。SELECT countIf(event survey shown AND timestamp toDateTime(CUTOFF)) AS shown_before, countIf(event survey shown AND timestamp toDateTime(CUTOFF)) AS shown_after, countIf(event survey sent AND timestamp toDateTime(CUTOFF)) AS sent_before, countIf(event survey sent AND timestamp toDateTime(CUTOFF)) AS sent_after FROM events WHERE properties.$survey_id SURVEY_ID AND timestamp toDateTime(WINDOW_START)CUTOFF是配置改动生效的时间点WINDOW_START要早于它保证前后两个窗口都有数据。判定口径如果sent/shown比例在改动前后保持稳定都低或都高说明「能看到的人」和「提交的人」的比例没变问题出在上游的 eligibility谁能看到问卷而非渲染/提交环节反之若 shown 正常但 sent 骤降则要往提交环节查。注意改动前后的时间窗口长度几乎不会相等必须按窗口时长归一化如换算成每天的平均值再对比否则绝对计数对比没有意义。场景二gating 标志位返回值与 group 上下文当「用户没看到问卷」时先查投放用的 feature flag 到底返回了什么以及会话里有没有设置 group 上下文。问卷的投放通常由一个内部 targeting 标志位控制标志位按 group 聚合时必须在调用前执行过posthog.group()。SELECT distinct_id, timestamp, properties.$feature_flag_response AS flag_response, properties.$groups AS groups_in_session, person.properties.email AS email FROM events WHERE event $feature_flag_called AND properties.$feature_flag FLAG_KEY AND timestamp toDateTime(WINDOW_START) ORDER BY timestamp DESC LIMIT 50判定口径如果flag_response全部为false且$groups为空groups_in_session为 null 或空对象基本可以断定是「按 group 聚合的标志位但代码里没有调用posthog.group()」——这是 group 型投放最常见的遗漏。场景三静态分群是否真的填充含国家分布如果投放目标是静态分群static cohort先确认这个分群到底有没有人避免「以为投给了 1 万人实际群里只有 0 人」。SELECT cohort_id, count() AS persons, countIf(person.properties.$geoip_country_code DE) AS in_DE FROM static_cohort_people WHERE team_id TEAM_ID AND cohort_id IN (IDS) GROUP BY cohort_idIDS是需要检查的分群 ID 列表可一次查多个in_DE是示例性的国家维度拆分方便核对分群的地理构成是否符合预期把DE换成任意国家码即可复用。这条查询直接印证了文档开头的表名提示这里用的是 HogQL 层暴露的static_cohort_people而不是 ClickHouse 底层表名person_static_cohort。场景四按问卷统计真实触达定位「受影响的是哪些问卷」一次改动可能影响多份问卷与其逐个查不如直接扫全部统计每份问卷在CUTOFF前后的 shown 量找出真正受影响的问卷。SELECT properties.$survey_id AS survey_id, countIf(timestamp toDateTime(CUTOFF)) AS shown_before, countIf(timestamp toDateTime(CUTOFF)) AS shown_after, uniqIf(distinct_id, timestamp toDateTime(CUTOFF)) AS users_after FROM events WHERE event survey shown AND timestamp toDateTime(WINDOW_START) GROUP BY survey_id HAVING shown_before 0 OR shown_after 0 ORDER BY shown_before DESC输出是所有「前后至少一边有 shown」的问卷shown_before DESC排序让原先触达量大的问卷排在前面——大问卷出问题的影响面最大优先排查。users_after用uniq(distinct_id)统计改动后的独立用户触达作为绝对量参考。场景五部分响应收集是否开启过survey sent事件上带有$survey_completed是否完成和$survey_submission_id提交 ID两个关键属性。当后台显示「提交数」与事件数对不上时先查历史窗口内completed的分布SELECT coalesce(toString(properties.$survey_completed), (not set)) AS completed, count() AS events, uniq(properties.$survey_submission_id) AS submissions, min(timestamp) AS first_seen, max(timestamp) AS last_seen FROM events WHERE event survey sent AND properties.$survey_id SURVEY_ID AND timestamp now() - INTERVAL 180 DAY GROUP BY completed ORDER BY events DESC解读口径只要存在completed false的行就说明该时间窗口内问卷的enable_partial_responses曾被设置为true该配置项在 products/surveys/backend/api/survey.py 中以serializers.BooleanField暴露序列化器上允许为 null。部分响应模式下原始的survey sent行包含 UI 折叠掉的中间保存记录因此events与submissions之间的差距有多大很大程度就是部分保存造成的。但(not set)并不天然等于「旧 SDK」。$survey_completed和$survey_submission_id只有 Web SDK 会写React Native 的sendSurveyEvent两个都不写其余移动端 SDK 不支持部分响应api类型的问卷由客户自己的代码埋点属性完全由客户决定。因此(not set)可能意味着Web SDK 版本低于 1.240.0、非 Web SDK、或手写的survey sent事件。在把它当作版本信号之前先看事件的lib属性确认来源。场景六完整事件漏斗含放弃把问卷生命周期里的事件一次性拉全看每个环节丢了多少人SELECT event, count() AS events, uniq(distinct_id) AS people, uniq(properties.$survey_submission_id) AS submissions FROM events WHERE properties.$survey_id SURVEY_ID AND event IN (survey shown, survey sent, survey dismissed, survey abandoned) AND timestamp now() - INTERVAL 180 DAY GROUP BY event ORDER BY events DESC解读口径survey sent行上events高于submissions是部分响应模式的正常形态——部分模式下每题触发一次survey sent原始事件数天然虚高。因此对比口径应改为submissionsvspeople若submissions明显高于people说明存在重复作答常见诱因是schedule: always每次满足条件就展示主要面向 widget 问卷绕过了内部 targeting 标志位用户同一会话/多会话内多次提交。该语义在 products/surveys/backend/api/survey.py 的 schedule 字段帮助文本中有明确说明。反过来的注意点people是uniq(distinct_id)同一人分布在多个 distinct_id 上会把它放大所以这个对比是「低估重复」而不是「凭空制造重复」——看到异常高的 submissions 反而更值得警惕。场景七逐题答案提取——首选getSurveyResponse()基于问卷 JSON这是「读响应内容」的核心推荐写法。优先使用getSurveyResponse(index, questionId)它是产品自身在响应结果表中使用的 HogQL 函数定义于 posthog/hogql/functions/survey.py调用方在 products/surveys/backend/responses/尤其是 fetch_rows.py响应 API 就是用它为每题生成答案列再按提交聚合。为什么要用这个函数从源码看它做了两件手写 SQL 容易做错的事双键 coalesce现代 SDK 把答案存在 UUID 键$survey_response_question_id下历史数据则在旧的索引键$survey_response第 0 题或$survey_response_index第 N 题下。survey.py 中_build_id_based_key与_build_index_based_key分别构造这两种键_build_coalesce_expr用nullif(..., )coalesce依次兜底L89-L102保证旧格式的响应也不会丢。多选展开传第三个参数true时走_build_multiple_choice_exprL140-L166用if(JSONHas(...) AND length(...) 0, id_value, index_value)返回数组并展开。约束index 与 id 都必须是字面量常量——源码 L20-L23 对第一参数显式校验ast.Constant且必须是合法整数否则抛出QueryError。这也解释了为什么文档要求从 survey JSON 的questions[]里按顺序取 index 与 id。标准查询从问卷 JSON 的questions[]数组中按顺序取每题的 index 与 UUIDSELECT timestamp, coalesce(person.properties.$email, person.properties.email) AS email, getSurveyResponse(0, Q1_UUID) AS q1_rating, getSurveyResponse(1, Q2_UUID) AS q2_single_choice, getSurveyResponse(2, Q3_UUID, true) AS q3_multiple_choice, properties.$survey_completed AS completed, properties.$survey_submission_id AS submission_id FROM events WHERE event survey sent AND properties.$survey_id SURVEY_ID AND timestamp now() - INTERVAL 180 DAY ORDER BY timestamp DESC LIMIT 60临时抽查也可以直接读原始属性但由于键名带连字符必须用反引号包裹properties.$survey_response_uuid且注意它只能读到 UUID 键格式的数据读不到旧索引键格式。场景八无问卷 JSON 时的降级方案——unroll$survey_questions拿不到问卷 JSON 时可以直接展开事件上的$survey_questions。它是覆盖完整问题列表的{id, question, response}对象数组数组顺序与问卷题目顺序位置对应。SELECT timestamp, properties.$survey_submission_id AS submission_id, properties.$survey_completed AS completed, arrayMap(x - JSONExtractString(x, question), JSONExtractArrayRaw(ifNull(toString(properties.$survey_questions), []))) AS questions, arrayMap(x - JSONExtractRaw(x, response), JSONExtractArrayRaw(ifNull(toString(properties.$survey_questions), []))) AS responses FROM events WHERE event survey sent AND properties.$survey_id SURVEY_ID AND timestamp now() - INTERVAL 180 DAY ORDER BY timestamp DESC LIMIT 60关键陷阱properties.$survey_questions的类型是Nullable(String)必须包ifNull(toString(...), [])——否则 ClickHouse 会直接拒绝查询报错Nested type Array(String) cannot be inside Nullable type。这也是文档在注释里专门点出ifNull的原因。场景九每题的作答率——「不完整」是否只是分支逻辑所致用户抱怨「很多人没答完」先别急着背锅把分支题的答案与下游题目的作答情况交叉制表。如果「没答下游」的用户恰好都选了触发分支的那个答案那么数据是正常的「不完整」只是分支规则的预期结果。SELECT getSurveyResponse(BRANCH_IDX, BRANCHING_Q_UUID) AS branch_answer, count() AS submissions, countIf(coalesce(getSurveyResponse(DOWN_IDX, DOWNSTREAM_Q_UUID), ) ! ) AS answered_downstream FROM events WHERE event survey sent AND properties.$survey_id SURVEY_ID AND coalesce(toString(properties.$survey_completed), true) ! false AND timestamp now() - INTERVAL 180 DAY GROUP BY branch_answer ORDER BY branch_answercoalesce(toString(properties.$survey_completed), true) ! false先排除掉明确标记为未完成的部分响应行(not set)视为完成即旧 SDK/非 Web SDK 提交的行也计入。这里getSurveyResponse的作用比浏览场景更关键如果用手写 UUID 属性去读旧索引键格式的答案读取结果为空会被countIf计为「未作答」从而放大你正试图证伪的「不完整」比例。场景十shown → sent 延迟——排查误触提交survey shown事件在弹窗变为可见时触发发生在surveyPopupDelaySecondsproducts/surveys/backend/api/survey.py 中定义于 appearance 序列化器之后因此 shown 到 sent 的时间差就是用户在弹窗上的真实停留时长。多选题问卷若出现大量 10 秒内完成的行通常指向skipSubmitButton配合居中弹窗的配置——用户随手点了两下就提交了而非认真作答。SELECT session, submission_id, shown_at, sent_at, dateDiff(second, shown_at, sent_at) AS seconds_to_submit FROM ( SELECT $session_id AS session, event, timestamp AS sent_at, coalesce(nullIf(properties.$survey_submission_id, ), toString(uuid)) AS submission_id, max(if(event survey shown, timestamp, NULL)) OVER ( PARTITION BY $session_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS shown_at FROM events WHERE properties.$survey_id SURVEY_ID AND event IN (survey shown, survey sent) AND timestamp now() - INTERVAL 180 DAY ) WHERE event survey sent AND shown_at IS NOT NULL ORDER BY seconds_to_submit ASC LIMIT 1 BY submission_id LIMIT 60这个查询有三处设计要点值得细读配对规则每次提交必须与它之前最近一次survey shown配对而不是与本次会话第一次 shown 配对。survey shown不携带$survey_submission_id没有共享键可以 join所以用窗口函数把「最后一次展示时间」沿时间轴向前填充max(...) OVER (PARTITION BY $session_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)。为何不能只用$session_id分组schedule: always的问卷可以在同一会话内被展示并作答两次minIf会把第一次展示与第一次响应配对后丢弃其余——用窗口函数前向填充则能完整保留每次展示。窗口函数不能被同一层WHERE引用所以外层过滤必须包一层子查询。部分响应模式下的语义ORDER BY seconds_to_submit ASCLIMIT 1 BY submission_id保留每个 submission 最早的一行此时度量的是首次作答时间多快点了第一下适合排查误触但不是「完成时间」不要把它重新标注成完成耗时。没有 submission id 的事件非 Web SDK、1.240.0 之前的 Web回退到事件 UUID作为独立行保留。场景十一每条响应对应的 Replay 链接——围观有争议的提交当客户对某条提交「是不是故意的」有争议时直接看录屏最有说服力SELECT timestamp, distinct_id, coalesce(person.properties.$email, person.properties.email) AS email, properties.sessionRecordingUrl AS replay_url, properties.$survey_submission_id AS submission_id, $session_id FROM events WHERE event survey sent AND properties.$survey_id SURVEY_ID AND timestamp now() - INTERVAL 180 DAY ORDER BY timestamp DESC LIMIT 1 BY coalesce(nullIf(properties.$survey_submission_id, ), toString(uuid)) LIMIT 60使用须知每条survey sent/dismissed/abandoned事件都带sessionRecordingUrl但它只指向一个 session id。Replay 默认是关闭的且默认云存储保留期 30 天短于本查询的 180 天窗口——录屏可能从未采集、也可能已过期。引用链接前务必先打开验证。LIMIT 1 BY把部分响应模式下每题一发的survey sent折叠为每个 submission 最新的一条与结果表去重逻辑一致保证「一个响应一条链接」而不是「一次中间保存一条链接」。coalesce回退到事件 UUID使 1.240.0 之前无$survey_submission_id的事件各自成行而不被折叠。诊断工作流总结10 个查询对应一套自顶向下的排查顺序层级问题使用查询投放层问卷根本没显示场景二gating 标志位、场景三静态分群、场景四按问卷统计触达事件层显示了但没提交场景一shown vs sent 前后对比、场景六完整漏斗内容层提交了但内容不对场景七/八逐题答案、场景九分支与作答率、场景十提交延迟佐证层这条响应真的是人点的场景十一Replay 链接关键纪律先归一化再对比窗口时长、事件数 vs 提交数、先用产品自己的函数再手写getSurveyResponse兼容双键格式、先验证再下结论(not set)不代表旧 SDK、Replay 链接要打开确认。遵循这套纪律绝大多数「问卷没响应 / 响应不对」的工单都能在几分钟内用一条只读 SQL 定位到根因。延伸阅读调试技能文档目录products/surveys/skills/debugging-surveys/HogQL 函数定义posthog/hogql/functions/survey.py响应结果 API 的取数实现含getSurveyResponse用法与按提交聚合逻辑products/surveys/backend/responses/fetch_rows.pySurvey 序列化器enable_partial_responses、surveyPopupDelaySeconds、schedule字段定义products/surveys/backend/api/survey.py【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考