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

本文字数：17525；估计阅读时间：44 分钟

作者： Chloé Carasso

本文在公众号【ClickHouseInc】首发

引言  

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 的熟练程度，以及是否需要将产品分析数据与其他数据源深度关联。  

以下是我们的实际案例：  

1. 事件收集的灵活控制：我们需要对捕获的事件进行精细化控制，让开发人员能够决定发送哪些事件以及何时发送。为此，我们开发了一套自定义 SDK，赋予开发人员对埋点的完全掌控权。  

2. SQL 专业技能：我们的主要用户（如产品经理和增长营销人员）对 SQL 十分熟悉，这使他们可以直接探索数据，并根据需要编写自定义查询。  

3. 跨数据源的关联分析：我们的数据仓库基于 ClickHouse，整合了来自 Salesforce、Google Analytics，以及计费和监控系统的数据。此外，我们的 ClickHouse Cloud 可观测性平台（用于日志、指标和追踪）同样基于 ClickHouse。通过将产品分析与这些数据源集成，我们可以回答复杂的跨领域问题。例如，我们能够分析客户流失是否与 ClickHouse Cloud 集群中的错误相关（产品分析 + 可观测性），或者跟踪不同客户群体的消费模式（产品分析 + 计费）。  

4. 成本考量：我们希望实现无限期的数据保留，同时在固定成本下支持无限次查询，并且不对开发人员可发送的数据施加任何限制。  

基于这些原因，我们选择自建方案。不论你是选择自建还是购买，以下的实践经验将为你设计和运行基于 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 = MergeTree
ORDER 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 = MergeTree
ORDER BY page
SETTINGS 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,
	payload
FROM raw_galaxy.prod_forensics
WHERE 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 rnk
FROM raw_galaxy.prod_pageloads
WHERE 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）大幅提升了可用性。  

常见查询  

以下是一些解决常见产品分析问题的查询示例，希望能为用户提供参考。这些查询基于前文介绍的模式，在某些情况下还需要依赖外部数据源（例如计费数据）。需要说明的是，这些查询主要注重可解释性，因此未必经过优化以追求简洁和高效。  

定义和筛选用户群组  

用户群组（cohort）指的是一组在特定时间内共享某些特征或行为的用户。通常，这些群组用于分析用户行为随时间变化的模式。对我们而言，用户群组指的是过去一个月内注册 ClickHouse Cloud 的用户。通过这些群组，我们可以跟踪并比较其参与度、激活率和转化率的趋势，与其他月度群组进行对比。  

虽然用户群组可以从计费和计量数据中识别，但在产品分析中也能通过简单的查询实现：  

SELECT created_at, user_id, ifNull(toString(org_id), 'blah') AS org_id
FROM galaxy.forensics
WHERE (environment = 'production') AND (event = 'org-created') AND (org_id IS NOT NULL)

CREATE MATERIALIZED VIEW galaxy.prod_org_creations_mv TO galaxy.prod_org_creations AS
SELECT
	created_at,
      user_id,
	ifNull(toString(org_id), 'blah') AS org_id
FROM galaxy.forensics
WHERE (environment = 'production') AND (event = 'org-created') AND (org_id IS NOT NULL)

由于该结果集在分析中被频繁使用，我们将此查询转化为一个物化视图 prod_org_creations。  

若需将用户群组限制在特定时间段内，只需对 created_at 时间字段进行过滤即可。这样便能在定义的时间范围内（如一个月）计算群组的统计数据，并据此识别业务趋势。  

SELECT * FROM raw_galaxy.prod_orgs_created WHERE toStartOfMonth(created_at) = '2024-08-01'

衡量从获客到激活和转化的时间

对于增长营销人员来说，获客、激活和转化是用户旅程中的三个关键阶段。这些阶段对于推动可持续业务增长至关重要。以下是我们对这些阶段的定义和分析方法：  

• 获客 (Acquisition)：获客是指吸引新用户并促使他们注册的过程。对我们而言，用户在 ClickHouse Cloud 上注册并创建服务即完成了获客阶段。  

• 激活 (Activation)：激活阶段是用户首次体验产品核心价值的时刻。例如，当用户将数据加载到服务中时，即完成了激活。  

• 转化 (Conversion)：转化是用户成为付费客户的过程。对于我们来说，这发生在用户填写其信用卡信息时。  

我们关注的核心指标包括用户在这些阶段之间的转换时间，以及每个阶段的完成率。这类分析通常在月度用户群组的框架下进行。  

为了实现这一分析，我们为上述每个阶段创建了增量物化视图。例如，prod_org_creations 视图记录了用户的获客时间，而 prod_service_creations 和 prod_conversion 视图则分别捕获用户创建服务和成为付费客户的时间。  

以下是一个示例，展示如何记录用户创建服务的时间：  

CREATE MATERIALIZED VIEW galaxy.prod_service_creations_mv TO galaxy.prod_service_creations
(
   `created_at` DateTime('UTC'),
   `service_id` Nullable(String),
   `org_id` Nullable(UUID)
)
AS SELECT
   created_at,
   JSON_VALUE(payload, '$.properties.service.id') AS service_id,
   org_id
FROM galaxy.forensics
WHERE (environment = 'production') AND (event = 'createdInstance')

通过 WHERE 条件识别付费用户

我们还可以通过稍微调整 WHERE 子句，创建一个物化视图来识别转化为付费用户的记录：  

SELECT created_at, org_id, event, payload,
   JSON_VALUE(payload, '$.data.values[1].auditRecord.payload.previousBillingStatus') as old_billing_status,
   JSON_VALUE(payload, '$.data.values[1].auditRecord.payload.newBillingStatus') as new_billing_status
FROM
   galaxy.prod_forensics
WHERE
   namespace = 'audit'
   and event = 'CHANGE_ORGANIZATION_BILLING_STATUS'
   and new_billing_status = 'PAID'
ORDER BY created_at asc limit 1 by org_id
settings function_json_value_return_type_allow_complex = true

这些物化视图在数据插入时会自动更新。通过基于共同的组织 ID 进行 Join，我们可以计算事件之间的时间差。例如，下列查询通过日期函数计算了组织（账户）创建与服务创建之间的时间分位数，并按月份分组（基于组织的创建时间将指标归属到具体月份）。  

WITH TimeDifferences AS (
   SELECT
       org_table.org_id,
       org_table.org_created_at,
       service_table.service_created_at,
       dateDiff('second', org_table.org_created_at, service_table.service_created_at) AS time_difference_seconds,
       formatDateTime(org_table.org_created_at, '%Y-%m') AS month
   FROM (
       SELECT
           org_id,
           created_at AS org_created_at
       FROM galaxy.prod_orgs_created
   ) AS org_table
   JOIN (
       SELECT
           org_id,
           created_at AS service_created_at
       FROM galaxy.prod_services_created
   ) AS service_table ON org_table.org_id = service_table.org_id
)

SELECT
   month,
   COUNT(DISTINCT org_id) AS orgs_per_month,
   AVG(time_difference_seconds) / 86400 AS average_time_difference_days,
   quantileExact(0.25)(time_difference_seconds) / 60 AS lower_quartile_minutes,
   quantileExact(0.5)(time_difference_seconds) / 60 AS median_time_difference_minutes,
   quantileExact(0.75)(time_difference_seconds) / 60 AS upper_quartile_minutes,
   MIN(time_difference_seconds) / 60 AS min_time_difference_minutes,
   MAX(time_difference_seconds) / 86400 AS max_time_difference_days,
   COUNTIf(time_difference_seconds > 86400) AS count_greater_than_one_day,
   COUNTIf(time_difference_seconds > 604800) AS count_greater_than_one_week,
   COUNTIf(time_difference_seconds > 2419200) AS count_greater_than_one_month  
FROM TimeDifferences
GROUP BY month
ORDER BY month;

追踪用户路径

追踪用户在产品中的行为路径需要分析事件的序列。窗口函数是实现这一目标的关键工具。以下示例分析了过去 14 天内用户的活动轨迹，以了解他们在产品引导和实例创建流程中的进展情况。重点是识别那些跳过某些引导步骤（此处为产品引导步骤）的用户行为模式。  

查询首先计算每个用户创建账户的最早时间（`signup_time`），即首次发生 "user-created" 事件的时间点。同时，使用一个 7 天的时间窗口，通过 windowFunnel 函数评估用户在预定义的引导步骤中的进展。这些步骤包括账户创建、组织设置、访问引导页面、提交新服务创建表单，以及创建实例。  

此外，查询追踪了用户跳过引导步骤的次数（skipOnboardingClick）和实例创建事件（CREATE_INSTANCE 或 createdInstance）的次数。最终结果按 `user_id` 分组，并过滤输出，仅包括在引导漏斗中有实际进展（`level > 0`）且至少跳过一次引导的用户。  

SELECT
    user_id,
    minIf(created_at, event = 'user-created') AS signup_time,
    windowFunnel(6048000000000000)(created_at, event = 'user-created', 
      event = 'org-created', (event = 'signin') AND 
      (path(JSON_VALUE(payload, '$."properties"."page"')) = '/onboard'), 
      (component = 'createNewService') AND (event = 'submitButtonClick'), 
      ((component = 'AuditRecordUtils') AND (event = 'CREATE_INSTANCE')) 
      OR (event = 'createdInstance')) AS level,
    countIf((component = 'onboardingLayout') 
      AND (event = 'skipOnboardingClick')) AS skipped_onboarding,
    countIf(((component = 'AuditRecordUtils') 
      AND (event = 'CREATE_INSTANCE')) OR (event = 'createdInstance')) AS created_instance_check
FROM galaxy.prod_forensics
WHERE created_at >= (now() - toIntervalDay(14))
GROUP BY user_id
HAVING (level > 0) AND (skipped_onboarding > 0)

客户留存与流失分析  

获取客户只是业务成功的起点，留住客户同样至关重要。我们通过计算流失率来衡量留存情况，即用户未能持续完成某项关键行为的比例。这些行为通常代表了客户的成功留存。一旦识别出流失用户，或更好地发现有流失风险的用户，就可以将其与其他统计数据关联，定位产品问题，并通过工具（如会话回放）进一步分析原因。  

在我们的实践中，流失率的定义是：客户在一个月内的消费超过阈值 X，但在下个月降到低于阈值 Y（Y 可以与 X 相同）。阈值 X 和 Y 的设定依据用户使用模式，可以针对不同规模的客户进行调整。  

此类分析需要引入外部数据集，例如我们的计费数据。这些数据存储在名为 dbt_marts_general.usage_history 的数据表中，并通过 M3ter 定期同步到我们的数据仓库。在下文示例中，我们使用该表和条件语句计算客户在当前月份和上个月的消费情况。  

WITH 100 as previous_spend, 100 as new_spend
SELECT organization__id AS organization__id,
      max(organization__created_at) AS "Organization Created At",
      argMax(organization__billing_model, timestamp_hour) AS "Current Billing Model",
      argMax(organization__marketplace_name, timestamp_hour) AS "Marketplace",
      argMax(organization__email_domain, timestamp_hour) AS "Email Domain",
      argMax(account__name, timestamp_hour) AS "Account",
      argMax(organization__owner_name, timestamp_hour) AS "Owner",
      sumIf(organization__dollar_usage, toStartOfMonth(timestamp_hour) = toStartOfMonth(now('UTC') - INTERVAL 2 MONTH)) AS "2 Months Ago - MRR",
      sumIf(organization__dollar_usage, toStartOfMonth(timestamp_hour) = toStartOfMonth(now('UTC') - INTERVAL 1 MONTH)) AS "Last Month - MRR"
FROM dbt_marts_general.usage_history
WHERE timestamp_hour >= toDateTime('2023-01-01 00:00:00')
 AND timestamp_hour < toDateTime('2024-11-28 13:00:00')
 AND (organization__email_domain NOT IN ('clickhouse.com', 'clickhouse.cloud', 'clickhouse.com_deleted'))
GROUP BY organization__id
HAVING ("2 Months Ago - MRR" > previous_spend AND "Last Month - MRR" < new_spend) AND ("Current Billing Model" = 'PAYG' OR "Current Billing Model" = 'Other')
ORDER BY "2 Months Ago - MRR" DESC

关联漏斗顶部活动

上述分析主要针对产品内的行为。与此同时，与许多公司一样，我们也希望将产品行为与用户在服务使用前的漏斗顶部活动相关联。例如，通过将产品账户（在我们的场景中是组织）与网站流量数据关联，探索更全面的用户行为模式。  

在 ClickHouse 的案例中，我们使用 Google Analytics 数据表示网站流量，这些数据会定期同步到我们的数据仓库。当用户注册 ClickHouse Cloud 时，其 Google Analytics ID 会通过 URL 传递。Galaxy 的客户端库会捕获此 ID，并将其包含在事件负载中。这种方式使我们能够分析组织注册 ClickHouse Cloud 前后的内容使用情况。提取每个账户的 Google Analytics ID 的关键在于处理事件负载：  

SELECT created_at AS signup_date,
       replaceOne(
           JSONExtractString(
               JSONExtractRaw(JSONExtractRaw(payload, 'data'), 'tracker'),
               'utm_ga'
           ),
           'GA1.1.',
           ''
       ) AS ga
FROM galaxy.prod_forensics
WHERE namespace = 'signup'
ORDER BY created_at DESC

通过这份 Google Analytics ID 列表，我们可以回答例如“用户在创建账户前阅读了哪些内容？”等问题。  

WITH gas AS
	(
    	SELECT replaceOne(JSONExtractString(JSONExtractRaw(JSONExtractRaw(payload, 'data'), 'tracker'), 'utm_ga'), 'GA1.1.', '') AS ga
    	FROM raw_galaxy.prod_forensics
    	WHERE (namespace = 'signup') AND (ga != '')
    	ORDER BY created_at DESC
	)
SELECT
	title,
	uniq(user_pseudo_id) AS users,
	countIf(event_name = 'page_view') AS views
FROM dbt_marts_google_analytics.events_daily
WHERE (user_pseudo_id IN (gas)) AND (title != '')
GROUP BY title
ORDER BY users DESC
LIMIT 10

使用 GrowthBook 进行 A/B 测试

对于 SaaS 企业来说，优化激活率和转化率是关键目标。这通常需要对产品引导和使用流程进行实验，以评估变化对核心指标的影响。要系统化地开展这类实验，需要一个 A/B 测试框架。通过向不同用户展示功能或流程的不同版本，并分析其行为，可以衡量每种版本对关键指标的作用。在 ClickHouse，我们使用 GrowthBook 这一开源实验平台来管理和分析实验。GrowthBook 不仅与我们的开源工具偏好相符，还能够灵活地与现有分析系统无缝集成，同时确保实验框架的透明性和控制力。  

在我们的实现中，每个实验以及用户接触的具体版本都会记录在事件的 `payload` 字段中。这使我们能够深入追踪用户行为，将实验数据直接与关键绩效指标（如激活和转化）关联起来。  

例如，我们可以通过简单的筛选，识别参与特定实验的组织以及它们所接触的版本。然后可以进一步分析这些组织的行为和结果。  

SELECT
   f.org_id,
   f.user_id AS user_id,
   JSONExtractString(f.payload, 'experimentId') AS experiment_id,
   JSONExtractString(f.payload, 'variationId') AS variation_id
FROM
   galaxy.prod_forensics f
WHERE
   f.namespace LIKE '%growth%'
   AND JSONExtractString(f.payload, 'experimentId') = 'pricing-service-creation';

统一的数据视图

正如之前的查询所示，我们的产品分析通常集中于少量（少于 50 列）的数据子集。此外，我们还展示了如何结合计费数据和 Google Analytics 数据进行更深入的分析。在更复杂的场景下，我们可能会使用数据仓库中额外的 10 张表或物化视图。为简化分析流程，我们创建了一个统一且易用的数据视图。  

这一增长数据视图（实质上是一个大型 LEFT JOIN）构成了大多数分析任务的基础，显著简化了上述许多查询流程，使得不熟悉 SQL 的用户也可以通过 Superset 的可视化功能获取答案。例如，计算获客、激活和转化的月度统计可以轻松转化为一个简单的查询：  

SELECT
   toStartOfMonth(organization__created_at) AS cohort_month,
   count() AS total_organizations,
   sum(organization__service_created) AS total_services_created,
   sum(organization__data_added) AS total_data_added,
   sum(organization__cc_added) AS total_cc_added
FROM
   dbt_marts_growth.growth
WHERE
   organization__created_at >= today() - interval 6 month
GROUP BY
   cohort_month
ORDER BY
   cohort_month ASC;

此视图被物化为一张表，可通过 dbt（利用 INSERT INTO SELECT）或 ClickHouse 中的可刷新物化视图定期重建。

未来方向与总结

本篇博客介绍了如何使用 ClickHouse 构建产品分析解决方案，深入探讨了模式设计、常见查询、物化视图，以及整合外部数据集等关键内容。我们还分享了构建内部分析平台 Galaxy 的经验，该平台处理了超过 200 亿条事件，并帮助我们获得了宝贵的业务洞察。  

构建自有的产品分析堆栈提供了高度的灵活性和掌控力。它可以让你根据需求定制数据收集方式，开展深度分析，并与更广泛的数据生态系统实现无缝集成。然而，这种灵活性也要求团队具备数据库管理、数据建模和 SQL 的专业技能。  

需要强调的是，本篇博客仅展示了产品分析可能性的冰山一角。产品分析领域广阔且复杂，不同的组织有不同的需求。虽然我们分享了一些常见查询和工作流的示例，但这些仅是起步，每个具体场景可能需要独特的方法。而诸如数据治理、用户归因、多触点分析等更深层次的挑战，则往往需要定制化的解决方案。  

对于那些愿意投入构建自有产品分析解决方案的团队来说，这项努力将带来丰厚的回报——不仅能够获得更深入的洞察，还能为业务发展提供强大的竞争优势。我们希望本篇博客能够激发你探索 ClickHouse 在产品分析中的潜力，并帮助你设计一个专属于你业务目标的系统。  

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com