内部运营干货：手把手教你借助 ClickHouse 搞定产品运营分析

admin 发表于 2025-1-13 17:31

专栏技术分享

引言

ClickHouse 的名字来源于“Clickstream（点击流）”和“Data Warehouse（数据仓库）”的结合，体现了其最初的设计目标：记录来自全网用户的每一次点击。尽管如今 ClickHouse 已广泛应用于多种场景，它依然是捕获 Web 事件的分析领域中极受欢迎的工具。产品分析作为这一领域的自然延伸，致力于追踪和分析用户与产品的交互行为，从而洞察用户行为、参与度和满意度等关键指标。

在本篇博客中，我们将带你学习如何使用 ClickHouse 构建强大的产品分析解决方案，分享关键数据模式设计的经验、产品经理和增长营销人员常用的典型工作流，以及提取有价值指标的关键查询方法。这篇指南基于我们在开发和运营内部产品分析平台 Galaxy 时积累的经验。Galaxy 经过近两年的实际运行，不仅提供了可靠的洞察，还展现了强大的稳定性。

Galaxy 每天处理超过 200 亿个事件、14 TB 的数据，帮助我们定量评估每一次设计和产品决策的效果。通过支持 A/B 测试和常见用户操作路径的分析，这一平台使我们能够持续优化和改进 ClickHouse Cloud 的用户体验。

什么是产品分析？

产品分析是指通过收集、分析和解释用户在产品中的行为数据，发掘能够指导决策的深刻洞察。相比许多人熟悉的 Google Analytics 等工具提供的基础 Web 分析，产品分析更加关注用户在产品内的操作行为和使用模式。

产品分析能够回答许多关键问题，例如：哪些用户行为会带来更高的参与度或导致用户流失？用户在操作中会遇到哪些阻碍？用户是如何浏览产品功能的？通过记录和分析点击等事件，产品团队可以更深入地理解用户需求，改进用户体验，并优化关键指标，如转化率和留存率。

这一持续反馈的循环机制对于产品经理和增长营销人员来说尤为重要，因为它能够帮助他们基于数据做出调整，从而提升产品的使用率、用户参与度和整体满意度。

为何 ClickHouse 是产品分析的首选？

与 Web 分析类似，产品分析需要处理由用户行为（如点击、滑动和应用内交互）生成的大量事件驱动数据。针对这些数据，产品经理和增长营销人员提出的问题往往既涉及时间维度又十分复杂，例如：用户在注册流程中中断的关键点是什么？哪些使用模式能带来更高的客户生命周期价值？哪些功能需要优化或重新设计？

这种复杂的数据访问模式要求一个高性能的数据存储系统，能够同时满足快速数据摄取、复杂查询和高并发访问的需求。而 ClickHouse 的列式存储架构、实时数据摄取能力以及处理海量数据的高效性，正是应对这些挑战的完美选择。更重要的是，借助 SQL，用户几乎可以自由地提出任何问题。

ClickHouse 的列式设计结合数据插入时的排序特性，使其在存储效率上具备显著优势。例如，在大多数文本数据中，可实现高达 15 倍的压缩。这种能力让用户能够低成本地存储每一条交互记录，同时保留数据的完整性。无需提前定义所有分析需求，用户可以随时利用存储的数据解决未来可能出现的新问题。这种灵活性极大地降低了数据存储的限制。

以我们的 Galaxy 平台为例，我们实现了至少 14 倍的压缩率，成功支持了大规模的历史数据存储和深度回溯分析。

ClickHouse 的高效聚合能力使得用户可以实时回答复杂问题。对于产品经理而言，查询结果的延迟从分钟缩短到秒级。例如，你可以在不到一秒的时间内计算获客率、激活率和转化率随时间的变化趋势。这种即时响应极大地提升了工作效率。

实时分析产品改动的影响同样至关重要。在 ClickHouse 的支持下，产品经理可以快速进行 A/B 测试，评估新功能对漏斗关键指标的影响。这种敏捷性让表现优异的功能得以保留，而未达到预期的功能则能快速优化或重新设计，从而不断改进产品体验和用户满意度。

自建还是购买现成方案？

凭借 ClickHouse 在产品分析场景中的出色性能，许多解决方案（如 PostHog）都将 ClickHouse 作为其核心数据存储和分析引擎，这并不令人意外。但这也引出了一个值得探讨的问题：你应该基于 ClickHouse 自建解决方案，还是选择像 PostHog 这样的开箱即用产品？

答案取决于多个因素，包括你对事件收集灵活性的需求、团队对 SQL 的熟练程度，以及是否需要将产品分析数据与其他数据源深度关联。

以下是我们的实际案例：

事件收集的灵活控制：我们需要对捕获的事件进行精细化控制，让开发人员能够决定发送哪些事件以及何时发送。为此，我们开发了一套自定义 SDK，赋予开发人员对埋点的完全掌控权。
SQL 专业技能：我们的主要用户（如产品经理和增长营销人员）对 SQL 十分熟悉，这使他们可以直接探索数据，并根据需要编写自定义查询。
跨数据源的关联分析：我们的数据仓库基于 ClickHouse，整合了来自 Salesforce、Google Analytics，以及计费和监控系统的数据。此外，我们的 ClickHouse Cloud 可观测性平台（用于日志、指标和追踪）同样基于 ClickHouse。通过将产品分析与这些数据源集成，我们可以回答复杂的跨领域问题。例如，我们能够分析客户流失是否与 ClickHouse Cloud 集群中的错误相关（产品分析 + 可观测性），或者跟踪不同客户群体的消费模式（产品分析 + 计费）。
成本考量：我们希望实现无限期的数据保留，同时在固定成本下支持无限次查询，并且不对开发人员可发送的数据施加任何限制。

基于这些原因，我们选择自建方案。不论你是选择自建还是购买，以下的实践经验将为你设计和运行基于 ClickHouse 的产品分析解决方案提供有价值的参考。

非规范化事件表

ClickHouse 作为列式数据库，非常适合单表中存储大量行和适量列（数百列也毫无问题）。尽管支持 Join 操作，但在产品分析场景中，由于事件量巨大，避免 Join 带来的查询时间开销，使用单一稀疏表通常是更优的选择。幸运的是，这种稀疏性——将多种事件类型存储在同一张表中，仅部分事件使用部分列——对 ClickHouse 的性能几乎没有影响。这是因为数据经过排序和压缩处理，连续的空值序列可以通过稀疏序列化技术（如下图所示）实现极高的压缩率，从而显著减少 I/O 并加速读取。

对于一列包含稀疏值的数据①，ClickHouse 仅将非默认值写入磁盘的列文件②，并生成一份稀疏编码文件③，用于记录非默认值的偏移量：即每个非默认值之前有多少个默认值。在查询时，ClickHouse 会基于稀疏编码创建一份内存中的直接偏移量表示④。稀疏编码存储格式能够高效处理重复值的数据。

通过避免 Join 操作，并主要使用带过滤器的聚合查询，用户可以在 TB 级别的数据规模上实现亚秒级的查询性能。正因如此，我们在产品分析的实现中选择了单表设计，所有事件均被收集到同一个表中。

基础数据模式

以下是我们当前在 Galaxy 中使用的数据模式：

CREATE TABLE galaxy.forensics(  `created_at` DateTime('UTC') DEFAULT now(),  `environment` LowCardinality(String),  `session_id` Nullable(String),  `request_id` Nullable(String),  `client_ip` Nullable(IPv4),  `org_id` Nullable(UUID),  `user_id` Nullable(String),  `namespace` Nullable(String),  `component` Nullable(String),  `event` String,  `interaction` LowCardinality(String),  `payload` Nullable(String),  `message` Nullable(String))ENGINE = MergeTreeORDER BY created_at

该模式包含一些 ClickCloud 专属的元素，同时也有部分可以复用的通用字段。具体说明如下：

created_at：事件发生的时间。这是大多数查询的过滤条件，也是表的主键（详见下文）。
environment：区分事件来源环境，例如开发环境、预生产环境或生产环境。
session_id：每个用户会话生成的唯一标识符，存储在浏览器的会话存储中。需要注意，此值在不同标签页或浏览器重启后可能变化，因为我们主要关注单次会话内的用户行为。这是事件捕获库的设计细节（详见下文）。通过 user_id 和 org_id 列，可以实现跨会话的行为聚合。
request_id：用于唯一标识特定请求，主要用于将客户端请求与服务器端日志相关联。
client_ip：请求来源的客户端 IP 地址。
user_id：当前登录用户的 ID。
namespace：事件层级的第一层，标识事件来源的应用程序及其视图，例如计费页面。
component：生成事件的页面组件，例如用户信息输入表单。
event：具体的事件类型。粒度根据重要性有所不同。例如，click、blur 和 close 是通用事件；而对我们来说需要精确跟踪的重要操作，则记录为高保真事件，如 serviceSelection。
interaction：表示事件是否由直接的用户交互触发（如 click），或是间接触发。例如，点击按钮打开用户详情面板，点击按钮会记录为 click 和 interaction，而用户详情面板的打开则会记录为 user_details_panel_open，其 interaction 值为 triggered。
payload：包含事件相关任意数据的 JSON 字符串，可能包括 Google Analytics ID、来源国家和页面路径等内容。

ClickHouse 专属字段：

org_id：这是 ClickHouse Cloud 的专属字段，用于表示与请求关联的组织信息（类似账户 ID）。此字段常用于后续分析中连接用户群体数据指标。

经验总结：

在设计数据模式时，应将大多数查询中常用的字段（例如用户群体的分类字段）设置为顶层列，而非嵌套在 payload 中。后者主要用于存储任意事件数据，但在查询时无论从语法还是性能上都更低效。此外，需要注意一次用户交互可能生成多个事件——如一个初始点击交互事件及多个由其触发的后续事件，例如对话框渲染事件。

通过主键加速查询

我们的产品分析查询大多以时间为维度，且主要关注最新数据。因此，在 forensics 表中，我们选择 created_at 作为主键。用户可以根据自身的访问模式和最佳实践调整主键设置。通常建议将频繁用于查询过滤的字段（如时间列或标识符列）设置为主键的一部分。

优化常见访问模式的物化视图

产品分析通常具有一定的访问模式规律。例如，在我们的分析中，仅几十个查询就覆盖了 90% 以上的分析需求。虽然也会执行一些临时查询，但这些“高频”查询构成了定期报告和产品回顾的核心。

为此，我们充分利用 ClickHouse 的增量物化视图来优化这些常见查询。在大多数情况下，这些查询比较简单，仅需过滤插入到主 forensics 表中的事件，然后将结果保存到新的目标表中。目标表通常包含筛选后的列和行，并结合主键和二级索引，以便优化后续查询性能。

以一个简单的示例为例，prod_pageviews_mv 物化视图捕获了页面浏览事件的部分字段：

CREATE TABLE galaxy.prod_pageloads(  `created_at` DateTime('UTC'),  `session_id` String,  `user_id` String,  `server_ip` String,  `page` String,  `payload` String)ENGINE = MergeTreeORDER BY pageSETTINGS index_granularity = 8192
CREATE MATERIALIZED VIEW galaxy.prod_pageloads_mv TO galaxy.prod_pageloads AS SELECT  created_at,  session_id,  user_id,  server_ip,  extract(JSON_VALUE(payload, '$.properties.page'), '^([^?]+)') AS page,  payloadFROM raw_galaxy.prod_forensicsWHERE event = 'pageLoad'

这一更小的表进一步加速了与页面加载相关的查询，例如用户登录和注册分析。

SELECT    created_at AS pageload_created_at,    decodeURLComponent(extractURLParameter(JSONExtractString(JSONExtractRaw(payload, 'properties'), 'page'), 'origPath')) AS originalPath,    decodeURLComponent(extractURLParameter(JSONExtractRaw(payload, 'properties'), 'pagePath')) AS pagePath,    extractURLParameter(JSONExtractRaw(payload, 'properties'), 'loc') AS loc,    extractURLParameter(JSONExtractRaw(payload, 'properties'), 'glxid') AS session,    session_id,    JSONExtractString(payload, 'country') AS country,    payload,    ROW_NUMBER() OVER (PARTITION BY extractURLParameter(JSONExtractString(JSONExtractRaw(payload, 'properties'), 'page'), 'glxid') ORDER BY created_at DESC) AS rnkFROM raw_galaxy.prod_pageloadsWHERE page IN ('https://clickhouse.cloud/signUp', 'https://clickhouse.cloud/signIn')

客户端库与事件收集

为了更好地收集事件，我们开发了一套简单的 SDK。这套工具不仅确保了事件收集的一致性，还能方便地集成到任何新的 UI 页面或组件中。SDK 的设计理念是简化事件收集，同时确保所有事件符合既定数据模式，并允许用户附加任意 JSON 数据作为负载。

最简单的埋点方式是基于页面级别的埋点，SDK 会自动捕获页面上的 blur、focus 和 load 事件。这种方式几乎不需要额外开发工作，例如：

import { useGalaxyOnPage } from '../lib/galaxy/galaxy'export default function HomePage({  hero,  seo,  footerData,  headerData,  customerStories,  platforms}: HomePageProps) {  useGalaxyOnPage('homePage')

useGalaxyOnPage 函数封装了更具体的 useGalaxyOnLoad、useGalaxyOnBlur 和 useGalaxyOnFocus 函数，用于分别捕获 JavaScript 的 load、focus 和 blur 事件。如果只需要捕获页面的部分事件，例如仅记录 load 事件，可以单独使用这些具体的函数。

export const useGalaxyOnPage = (  prefix: string,  depsArray: Array<unknown> = []): void => {  useGalaxyOnLoad(`${prefix}.window.load`)  useGalaxyOnBlur(`${prefix}.window.blur`, depsArray)  useGalaxyOnFocus(`${prefix}.window.focus`, depsArray)}

当需要更精确的事件收集（如 click 事件）时，SDK 提供了 useGalaxyOnClick 函数。开发者可以使用该函数指定一个完整的事件名称，格式为 <namespace>.<component>.<event_name>，这一命名方式与页面级别事件一致。需要注意，事件并不会立即发送到 Galaxy 后端，而是先在浏览器中进行缓冲，并定期批量发送，从而提升传输效率。

此外，我们还对控制台函数进行了重载，确保 log、error、warn、debug 和 info 等控制台操作也能被捕获为 Galaxy 事件。

处理异常数据

任何 Web 或产品分析解决方案都可能面临攻击者注入异常数据的风险，因为浏览器会发送事件到存储系统中。

在 Galaxy 中，我们通过一个代理层强制处理所有插入操作，以应对这一问题。代理层执行严格的速率限制，过滤事件以确保其符合既定模式，并对事件进行缓冲后批量插入 ClickHouse，从而保持高效的数据写入。

即便如此，我们仍需面对偶发的异常数据插入情况——无论是道德漏洞赏金猎人，还是恶意攻击者。这些异常数据通常容易识别，可以通过轻量级的 DELETE 操作定期清理，同时将常见的异常模式加入上游的过滤规则中。

整合其他数据集

虽然产品的页面事件提供了有价值的行为数据，但单独依赖这些数据往往不足以提供深入的业务洞察。要真正评估用户行为是否对业务有益，必须将这些行为与其他数据源（如消费指标）进行关联。这对 SaaS 企业尤为重要，例如，我们需要了解某些用户操作是否能促进产品使用，或者是否可能导致用户流失。

同样，内容营销团队也希望评估哪些网站内容对用户更具吸引力，以及哪些页面能有效引导用户探索更多功能。实现这一目标需要将产品分析数据与 Web 分析数据结合，以揭示有价值的相关性。

通过 ClickHouse 构建自己的产品分析解决方案，可以轻松实现这种数据整合。ClickHouse 支持将产品分析数据与其他数据源关联。在早期的博客文章中，我们分享了如何在 ClickHouse 上构建数据仓库的经验。这一仓库整合了多种数据集，使我们能够利用这些数据与产品分析相结合，挖掘出对业务有意义的洞察。

以下数据源是我们日常分析的核心：

计费与计量数据 (meter)：关联产品使用流程与实际消费与增长数据。
LMS 数据：我们的学习管理系统 (Matrix LMS) 提供用户参与的课程信息，可用于评估课程对产品使用和用户激活的影响。
营销与 CRM 数据 (Salesforce)：用于连接账户与机会、评估潜在客户，并为 GTM 团队提供可信赖的核心数据来源。
Web 分析数据：通过 GA4（Google Analytics 4）的数据，我们将网站分析结果同步至 ClickHouse 数据仓库。这不仅打破了 GA 的默认数据保留限制，还提供了快速、灵活的 SQL 查询能力，并避免了 GA 的结果采样，确保了分析结果的精确性。此外，将 GA 数据与产品分析数据结合，我们能够揭示网站内容如何影响用户参与和产品采纳。

我们通过将产品分析集群的数据定期同步到数据仓库（使用 S3 作为中间存储层）实现数据整合。这一过程使用基于 dbt 的自动化流程，与其他数据源的同步方式一致。这种集中化的数据分析方式不仅确保了数据的一致性，也为产品分析提供了可靠的基础。

用 Superset 进行数据可视化

我们的数据仓库用户一直以来首选 Superset 作为数据可视化工具。Superset 提供了丰富多样的可视化选项，非常适合产品分析场景。

这是一个 Superset 仪表板的示例。注意：图中数据为演示用，包含虚构数字。

尽管 Superset 在定期报告仪表板的创建上表现优异，但在探索性分析方面仍有不足。而探索性分析是产品经理和营销团队的高频需求。为此，我们更倾向于使用 ClickHouse Cloud 的 SQL 控制台。对于那些 SQL 技能有限的用户，该控制台的自然语言转 SQL 功能（基于大语言模型 LLM）大幅提升了可用性。