使用 ClickHouse 构建产品分析解决方案

ClickHouse 的名称来源于 “Clickstream”（点击流）和 “Data Warehouse”（数据仓库）的组合。这概括了它最初设计的使用场景：记录来自互联网上所有用户的点击。尽管 ClickHouse 应用的使用场景范围已经多样化，但它在依赖于 Web 事件捕获的分析中仍然非常受欢迎。产品分析是此用例的自然延伸，专注于跟踪和分析用户如何与产品互动，从而深入了解用户行为、参与度和满意度。

在本博客中，我们将指导您使用 ClickHouse 构建强大的产品分析解决方案，分享关于基本数据模式、产品经理和增长营销人员依赖的典型工作流程以及用于提取有价值指标的关键查询的见解。本指南借鉴了我们开发和运行内部产品分析平台 Galaxy 的经验，该平台在近两年的运营中提供了可操作的见解和经过验证的可靠性。

Galaxy 拥有超过 200 亿个事件和 14 TB 的数据，使我们能够定量评估我们所做的每一项设计和产品决策的影响。该平台支持 A/B 测试和常见用户工作流程的衡量，使我们能够不断改进和增强 ClickHouse Cloud 体验。

产品分析是一种收集、分析和解释用户与产品互动方式的数据的做法。这超越了许多人通过 Google Analytics 等工具熟悉的基于 Web 的基本分析，而是专注于产品内的用户操作、模式和行为，以揭示可为决策提供信息的见解。

产品分析有助于回答关键问题，例如：哪些行为与更高的参与度或客户流失相关？用户在工作流程中哪里遇到摩擦？用户如何浏览功能？通过跟踪和分析功能使用情况（通过点击等事件），产品团队可以更好地了解用户需求，改善用户体验，并优化转化率和用户留存率等关键指标。这种持续的反馈循环对于产品经理和增长营销人员来说非常宝贵，因为它使他们能够进行数据驱动的调整，从而推动产品采用、用户参与和整体满意度。

与 Web 分析非常相似，产品分析处理由用户操作生成的大量事件驱动数据——例如应用程序内的点击、滑动和互动。产品经理和增长营销人员随后对这些数据提出的问题往往既具有时间性又很复杂，他们希望分析随时间变化的模式，例如用户在注册工作流程中的哪个环节停止使用产品、哪些使用模式可以预测更高的客户生命周期价值，以及产品的哪些部分可能需要更多指导或从重新设计中受益。

访问模式需要一个高性能的数据存储，该存储可以管理快速事件摄取、复杂查询和高并发性，这使得 ClickHouse 成为理想的选择。其列式存储、实时数据摄取能力以及处理海量数据集的效率使产品团队能够及时获得关于用户行为的可操作见解。重要的是，用户只需编写 SQL 即可提出几乎任何可以想象到的问题！

此外，ClickHouse 的面向列的设计，加上数据值在插入时进行排序的事实，使得压缩率很高，在大多数文本格式上达到 15 倍并不罕见。这些压缩能力对于使用户能够经济高效地存储每个高保真互动至关重要，从而使他们能够捕获丰富的数据并无限期保留，而无需担心存储成本。反过来，这释放了灵活性，因为用户无需预先确定他们希望提出的所有问题；相反，所有数据都可以存储并在以后用于回答未曾预料到的问题。

在我们自己的 Galaxy 环境中，我们实现了至少 14 倍的压缩率，这使我们能够保留广泛的历史数据并支持深入的回溯分析。

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 操作，但产品分析中生成的事件数量通常意味着用户最好避免相关的查询时间开销，而使用单个稀疏表。幸运的是，这种稀疏性（由在同一表中存储多种事件类型引起，其中事件子集可能使用某些列）对 ClickHouse 几乎没有开销，因为值会被排序和压缩。连续的空值序列压缩效果非常好，由于 I/O 减少，因此读取速度很快，这要归功于稀疏序列化等技术，如下图所示。

对于包含稀疏值的 ① 列 s，ClickHouse 仅将非默认值写入磁盘上的 ② 列文件，以及 ③ 包含非默认值偏移量的稀疏编码的附加文件：对于每个非默认值，我们存储在非默认值之前直接存在的默认值数量。在查询时，从该编码创建具有直接偏移量的 ④ 内存表示。稀疏编码的存储变体包含具有重复值的数据。

通过避免 JOIN 操作，并主要利用带有过滤器的聚合来回答查询，用户可以在 TB 级别体验亚秒级查询性能。由于这些原因，我们自己的产品分析实现使用单个表来收集所有事件。

我们当前的 Galaxy 模式如下所示

  
1CREATE TABLE galaxy.forensics
2(
3	`created_at` DateTime('UTC') DEFAULT now(),
4	`environment` LowCardinality(String),
5	`session_id` String,
6	`request_id` String,
7	`client_ip` IPv4,
8	`org_id` UUID,
9	`user_id` String,
10	`namespace` String,
11	`component` String,
12	`event` String,
13	`interaction` LowCardinality(String),
14	`payload` String,
15	`message` String
16)
17ENGINE = MergeTree
18ORDER BY created_at

此模式的某些元素是 Galaxy 特有的，而另一些元素是可重用的。更具体地说

• created_at - 事件发生的时间。这在大多数查询中都会被过滤，因此是主键（见下文）
• environment- dev/staging/production 允许我们区分来自每个环境的事件。
• session_id - 每个用户会话生成。我们生成此 guid 并将其存储在浏览器会话存储中。请注意，这意味着对于用户而言，它在选项卡或浏览器重启之间不一致 - 我们有兴趣衡量会话内的行为。这是我们事件捕获库的实现细节（见下文）。user_id 和 org_id 列允许我们跨会话聚合。
• request_id - 特定请求的唯一标识符。我们主要使用它来将请求与服务器端日志相关联。
• client_ip - 请求客户端的 IP。
• user_id - 登录用户的 ID
• namespace - 事件层次结构中的第一级。这表示事件的来源应用程序以及特定视图，例如计费页面。
• component - 页面上生成事件的组件，例如用于输入用户详细信息的表单。
• event - 特定事件。这些事件具有不同程度的粒度。对于预计不太有趣的动作的通用事件可能是 click、blur、close。对于我们更重视跟踪的动作，我们使用更高保真度的事件，例如 serviceSelection。
• interaction - 事件是否由直接用户交互（例如 click）还是通过用户交互间接 triggered 触发。例如，假设单击按钮会导致用户详细信息面板打开。虽然按钮将具有与 click 事件和交互关联的事件，但将创建一个单独的 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 捕获页面浏览事件的列子集

  
1CREATE TABLE galaxy.prod_pageloads
2(
3	`created_at` DateTime('UTC'),
4	`session_id` String,
5	`user_id` String,
6	`server_ip` String,
7	`page` String,
8	`payload` String
9)
10ENGINE = MergeTree
11ORDER BY page
12SETTINGS index_granularity = 8192
13
14CREATE MATERIALIZED VIEW galaxy.prod_pageloads_mv TO galaxy.prod_pageloads AS 
15SELECT
16	created_at,
17	session_id,
18	user_id,
19	server_ip,
20	extract(JSON_VALUE(payload, '$.properties.page'), '^([^?]+)') AS page,
21	payload
22FROM raw_galaxy.prod_forensics
23WHERE event = 'pageLoad'

这个较小的表反过来用于加速需要分析页面加载的查询，例如登录和注册。

  
1SELECT
2    created_at AS pageload_created_at,
3    decodeURLComponent(extractURLParameter(JSONExtractString(payload, 'properties', 'page', 'origPath'))) AS originalPath,
4    decodeURLComponent(extractURLParameter(JSONExtractRaw(payload, 'properties'), 'pagePath')) AS pagePath,
5    extractURLParameter(JSONExtractRaw(payload, 'properties'), 'loc') AS loc,
6    extractURLParameter(JSONExtractRaw(payload, 'properties'), 'glxid') AS session,
7    session_id,
8    JSONExtractString(payload, 'country') AS country,
9    payload,
10    ROW_NUMBER() OVER (PARTITION BY extractURLParameter(JSONExtractString(payload, 'properties', 'page'), 'glxid') ORDER BY created_at DESC) AS rnk
11FROM raw_galaxy.prod_pageloads
12WHERE page IN ('https://clickhouse.cloud/signUp', 'https://clickhouse.cloud/signIn')

为了收集事件，我们实施了一个简单的 SDK，以确保事件收集的一致性并易于添加到任何新的 UI 页面或组件。此 SDK 旨在尽可能简化事件收集，确保事件符合模式，同时允许用户附加任何任意 JSON payload。

最简单的检测形式是在页面级别，其中将自动捕获任何 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 事件。在不需要捕获页面的所有事件的情况下，可以单独使用这些函数，例如仅加载事件。

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

在需要针对点击事件进行更有针对性的事件收集的情况下，SDK 提供了 useGalaxyOnClick 函数。此函数接收格式为 <namespace>.<component>.<event_name> 的完全限定事件名称（如上面的页面级事件所制定）。请注意，事件不会立即发送到 Galaxy 后端；相反，它们会在浏览器中缓冲并定期刷新以实现更高效的传输。

最后，我们还重载了控制台函数，以确保任何控制台函数（log、error、warn、debug、info）都作为 Galaxy 事件捕获。

任何 Web 或产品分析解决方案都容易受到攻击者注入嘈杂数据的攻击，因为浏览器正在发送要存储的事件。

我们在 Galaxy 中通过强制所有插入都通过代理层来解决此问题。这施加了严格的速率限制、过滤以确保事件符合模式以及缓冲，以便 ClickHouse 以合理大小的批次接收插入。

尽管如此，我们接受在某些情况下会插入嘈杂数据 - 这些攻击者可能是道德漏洞赏金猎人或恶意攻击者。在这两种情况下，这些数据通常很容易识别，并且使用轻量级 DELETE 定期删除，并将常见模式添加到上游过滤规则中。

虽然来自您产品的页面事件提供了有价值的数据，但它们通常不足以单独从中得出可操作的见解。可以识别典型的用户行为，但要真正评估这些行为是否有利于您的业务，必须将它们与其他数据（例如支出指标）相关联。对于像我们这样的 SaaS 企业来说，这一点尤其关键。例如，我们需要了解某些用户行为序列是否会推动产品使用量的增加，或者相反，是否会导致客户流失。

同样，内容营销团队经常试图确定哪些网站内容能引起用户的共鸣，以及哪些页面能有效地鼓励用户尝试该服务的不同功能。要实现这一点，需要将产品分析数据与 Web 分析数据统一起来，以揭示有意义的关联性。

正如我们之前提到的，使用 ClickHouse 构建您自己的产品分析解决方案使这种统一变得简单直接。ClickHouse 允许您将产品分析与其他数据源相关联。在之前的博客文章中，我们分享了关于我们如何在 ClickHouse 上构建我们自己的数据仓库的见解。此仓库集成了各种有价值的数据集，使我们能够将它们与产品分析相关联，从而揭示有影响力的商业见解。

以下几个来源对我们的日常分析至关重要

• 计费和计量数据 (m3ter) - 使我们能够将产品使用工作流程与实际支出和增长相关联。
• LMS 数据 - 我们的学习管理系统 (Matrix LMS) 提供有关用户已修课程的详细信息，可用于评估这些课程对使用情况和激活的影响。
• 营销和 CRM (Salesforce) - 将帐户连接到商机，对潜在客户进行资格认证，并为我们的 GTM 团队提供单一的事实来源。
• Web 分析数据 - 我们主要网站的 Google Analytics (GA4) 数据，使用之前的博客文章中详述的过程同步到我们由 ClickHouse 驱动的数据仓库。这提供了显着的优势，包括超出 GA 默认限制的无限数据保留、执行快速灵活的基于 SQL 的查询的能力以及通过避免 GA 对结果进行抽样来确保完全准确性。除了这些优势之外，将 GA 数据与我们的仓库同步使我们能够将网站内容与产品使用情况相关联，从而更深入地了解 Web 互动如何推动用户参与和产品采用。

为了实现这一点，我们使用 S3 作为中间存储层，将数据从我们的产品分析集群同步到我们的数据仓库。这会定期发生，使用与同步我们其他数据源相同的基于 dbt 的流程。因此，我们未来的所有分析都将使用数据仓库执行。这具有确保数据一致性的优势，这是执行产品分析时必不可少的属性。

从历史上看，我们数据仓库的用户会使用 Superset 作为首选可视化工具。这提供了各种各样的可视化效果，这些效果对于产品分析非常有用。

Superset 仪表板示例。注意：为了说明目的，此处展示的是带有虚假数字的示例数据。

虽然 Superset 在创建用于定期报告的仪表板方面表现出色，但它在探索性分析方面有所欠缺 - 这是我们的产品经理和营销团队经常需要的。为了解决这个问题，我们主要依赖 ClickHouse Cloud 的 SQL 控制台，SQL 技能较差的用户可以充分利用其由 LLM 驱动的自然语言到 SQL 的功能。

下面，我们尝试记录一些解决常见产品分析问题的查询，希望这些查询可以作为用户的示例。这些查询特定于前面显示的模式，并且在某些情况下需要外部来源（如计费数据）。请注意，这些查询并非总是经过优化，可解释性优先于简洁性和效率。

队列是一组具有共同特征或操作的用户，通常在特定时间范围内。然后将这些队列用于分析随时间推移的行为。对我们而言，队列是指在过去一个月内注册 ClickHouse Cloud 的用户。这使我们能够跟踪和比较他们的参与度、激活和转化趋势与其他每月队列的趋势。

虽然我们可以从我们的计费和计量数据中识别队列，但这也可以在我们的产品分析中使用一个简单的查询来实现

  
1SELECT created_at, user_id, ifNull(toString(org_id), 'blah') AS org_id
2FROM galaxy.forensics
3WHERE (environment = 'production') AND (event = 'org-created') AND (org_id IS NOT NULL)
4
5CREATE MATERIALIZED VIEW galaxy.prod_org_creations_mv TO galaxy.prod_org_creations AS
6SELECT
7	created_at,
8      user_id,
9	ifNull(toString(org_id), 'blah') AS org_id
10FROM galaxy.forensics
11WHERE (environment = 'production') AND (event = 'org-created') AND (org_id IS NOT NULL)

鉴于此结果集在分析中使用的频率，此查询被转换为物化视图 prod_org_creations。

将这些队列限制为特定期间只需过滤 created_at 时间即可。因此，可以在定义的期间（例如日历月）计算这些队列的统计信息，并用于识别业务趋势。

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

增长营销人员会熟悉获客、激活和转化这些术语。这些阶段代表了用户旅程中的关键点，对于推动可持续业务增长至关重要。以下是我们定义和处理这些阶段的方式

• 获客：这是吸引和获取新用户到您的平台的过程。对我们而言，当用户注册并在 ClickHouse Cloud 中创建服务时，即实现获客。
• 激活：激活是指用户首次体验到您产品的核心价值的时刻。对我们而言，当用户将数据加载到服务中时，就会发生这种情况。
• 转化：转化是指用户成为付费客户的点。对我们而言，当用户输入他们的信用卡详细信息时，就会发生这种情况。

通常，我们对用户完成这些阶段所需的时间以及完成每个阶段的用户百分比感兴趣。我们在上面描述的每月队列的上下文中执行此分析。

为了做到这一点，我们创建了增量物化视图，用于表示上述每个步骤。例如，获客通过较早的视图 prod_org_creations 记录。类似的视图 prod_service_creations 和 prod_conversion 捕获何时创建服务以及用户何时成为付费客户。

例如，以下用户捕获何时创建服务。

  
1CREATE MATERIALIZED VIEW galaxy.prod_service_creations_mv TO galaxy.prod_service_creations
2(
3   `created_at` DateTime('UTC'),
4   `service_id` String,
5   `org_id` UUID
6)
7AS SELECT
8   created_at,
9   JSON_VALUE(payload, '$.properties.service.id') AS service_id,
10   org_id
11FROM galaxy.forensics
12WHERE (environment = 'production') AND (event = 'createdInstance')

同样，通过对 WHERE 子句进行少量更改，我们使用以下内容（作为物化视图）来识别转化为付费客户的转化

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

这些视图在插入数据时自动维护，然后可以通过连接通用组织 ID 来衡量事件之间的时间。例如，在下面，我们使用 日期函数 计算组织（帐户）创建与服务创建之间的时间分位数，按月份分组（使用组织的创建时间将指标归因于月份）。

  
1WITH TimeDifferences AS (
2   SELECT
3       org_table.org_id,
4       org_table.org_created_at,
5       service_table.service_created_at,
6       dateDiff('second', org_table.org_created_at, service_table.service_created_at) AS time_difference_seconds,
7       formatDateTime(org_table.org_created_at, '%Y-%m') AS month
8   FROM (
9       SELECT
10           org_id,
11           created_at AS org_created_at
12       FROM galaxy.prod_orgs_created
13   ) AS org_table
14   JOIN (
15       SELECT
16           org_id,
17           created_at AS service_created_at
18       FROM galaxy.prod_services_created
19   ) AS service_table ON org_table.org_id = service_table.org_id
20)
21
22SELECT
23   month,
24   COUNT(DISTINCT org_id) AS orgs_per_month,
25   AVG(time_difference_seconds) / 86400 AS average_time_difference_days,
26   quantileExact(0.25)(time_difference_seconds) / 60 AS lower_quartile_minutes,
27   quantileExact(0.5)(time_difference_seconds) / 60 AS median_time_difference_minutes,
28   quantileExact(0.75)(time_difference_seconds) / 60 AS upper_quartile_minutes,
29   MIN(time_difference_seconds) / 60 AS min_time_difference_minutes,
30   MAX(time_difference_seconds) / 86400 AS max_time_difference_days,
31   COUNTIf(time_difference_seconds > 86400) AS count_greater_than_one_day,
32   COUNTIf(time_difference_seconds > 604800) AS count_greater_than_one_week,
33   COUNTIf(time_difference_seconds > 2419200) AS count_greater_than_one_month  
34FROM TimeDifferences
35GROUP BY month
36ORDER BY month;

跟踪用户在产品中的路径需要检查事件序列。为此，窗口函数至关重要。我们的以下示例分析了过去 14 天的用户活动，以了解他们在新手引导和实例创建工作流程中的进度。具体来说，它旨在识别跳过产品中特定步骤（在本例中为新手引导）的用户的常见行为。

为此，它计算每个用户创建帐户的最早时间 (signup_time)，该时间由事件 'user-created' 的首次出现确定。该查询还使用 windowFunnel 函数，时间窗口为 7 天，用于衡量用户在预定义的系列新手引导操作中取得了多大的进展。这些步骤包括帐户创建、组织设置、导航到新手引导页面、提交表单以创建新服务以及创建实例。

此外，该查询跟踪用户选择跳过新手引导的次数 (skipOnboardingClick)，并计算实例创建事件的数量 (CREATE_INSTANCE 或 createdInstance)。最后，它按 user_id 对结果进行分组，并过滤输出以仅包含在新手引导漏斗中取得进展（由大于 0 的级别指示）且至少跳过一次新手引导的用户。

  
1SELECT
2    user_id,
3    minIf(created_at, event = 'user-created') AS signup_time,
4    windowFunnel(6048000000000000)(created_at, event = 'user-created', 
5      event = 'org-created', (event = 'signin') AND 
6      (path(JSON_VALUE(payload, '$."properties"."page"')) = '/onboard'), 
7      (component = 'createNewService') AND (event = 'submitButtonClick'), 
8      ((component = 'AuditRecordUtils') AND (event = 'CREATE_INSTANCE')) 
9      OR (event = 'createdInstance')) AS level,
10    countIf((component = 'onboardingLayout') 
11      AND (event = 'skipOnboardingClick')) AS skipped_onboarding,
12    countIf(((component = 'AuditRecordUtils') 
13      AND (event = 'CREATE_INSTANCE')) OR (event = 'createdInstance')) AS created_instance_check
14FROM galaxy.prod_forensics
15WHERE created_at >= (now() - toIntervalDay(14))
16GROUP BY user_id
17HAVING (level > 0) AND (skipped_onboarding > 0)

获取客户仅代表任何成功业务的第一步 - 留住客户同样重要。我们可以通过客户流失率来衡量这种留存率，其中“流失”客户是指未被留存的客户。衡量客户流失率涉及计算在一段时间内停止或减少特定操作的用户百分比，其中此操作表示成功的留存。一旦可以识别出流失客户（或更好的是，有流失风险的客户），我们可以将其与其他统计信息相关联，并识别产品中的问题 - 可能会使用会话重放等其他工具。

在我们的案例中，我们将客户流失定义为客户在一个月的消费额高于阈值 X，但在下个月降至阈值 Y（可以与 X 相同）以下的情况。X 和 Y 的值取决于使用情况概况，并且可以根据不同的客户规模进行调整。

此分析是需要外部数据集的一个示例 - 特别是我们的计费数据。此表在我们的数据仓库中以 dbt_marts_general.usage_history 的形式提供，并定期从 M3ter 同步。我们在下面使用此表以及条件函数来计算当前月和上个月的客户消费额。

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

上述分析侧重于产品内分析。如上所述，与许多公司一样，我们的目标是将产品行为与服务使用前的漏斗顶端活动相关联。为此，我们需要将产品帐户（在我们的案例中为组织）与网站流量相关联。

对于 ClickHouse 而言，后一个数据集由 Google Analytics（谷歌分析）表示，我们定期将其同步到我们的数据仓库。在注册 ClickHouse Cloud 时，用户的 Google Analytics ID（谷歌分析 ID）会通过 URL 传递。此 ID 由 galaxy 的客户端库使用，并包含在事件负载中。这使我们能够进而分析注册 ClickHouse Cloud 的组织对内容的过去和未来使用情况。提取每个帐户的 Google Analytics ID 需要我们处理负载。

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

有了这个 Google Analytics ID 列表，我们可以回答诸如“人们在创建帐户之前阅读哪些内容？”之类的问题。

  
1WITH gas AS
2	(
3    	SELECT replaceOne(JSONExtractString(JSONExtractRaw(JSONExtractRaw(payload, 'data'), 'tracker'), 'utm_ga'), 'GA1.1.', '') AS ga
4    	FROM raw_galaxy.prod_forensics
5    	WHERE (namespace = 'signup') AND (ga != '')
6    	ORDER BY created_at DESC
7	)
8SELECT
9	title,
10	uniq(user_pseudo_id) AS users,
11	countIf(event_name = 'page_view') AS views
12FROM dbt_marts_google_analytics.events_daily
13WHERE (user_pseudo_id IN (gas)) AND (title != '')
14GROUP BY title
15ORDER BY users DESC
16LIMIT 10

SaaS 企业致力于优化激活率和转化率。这通常需要对产品引导和使用流程进行一些实验，并需要衡量关键指标变化的影响。有条不紊地执行此操作需要一个 A/B 测试框架，在该框架中，不同的用户会接触到功能或流程的不同变体，并衡量他们的行为以评估每种变体对关键指标的影响。在 ClickHouse，我们使用开源实验平台 GrowthBook 来管理和分析这些实验。GrowthBook 符合我们对开源工具的偏好，它提供了与我们现有分析堆栈无缝集成的灵活性，同时保持了对我们实验框架的透明度和控制。

在我们的实施中，每个实验以及用户接触到的特定变体都捕获在事件的负载字段中。这使我们能够以精细的程度跟踪和分析用户行为，将实验数据与关键绩效指标（如激活和转化）直接关联起来。

例如，我们使用简单的过滤器识别参与特定实验的组织以及他们接触到的变体。然后可以对这些组织进行先前的分析。

  
1SELECT
2   f.org_id,
3   f.user_id AS user_id,
4   JSONExtractString(f.payload, 'experimentId') AS experiment_id,
5   JSONExtractString(f.payload, 'variationId') AS variation_id
6FROM
7   galaxy.prod_forensics f
8WHERE
9   f.namespace LIKE '%growth%'
10   AND JSONExtractString(f.payload, 'experimentId') = 'pricing-service-creation';

正如您从我们之前的查询中看到的那样，我们的大部分产品分析通常侧重于少于 50 列的子集。我们展示了一些使用产品分析以外的来源的示例，例如连接计费数据和 Google Analytics 数据。对于更复杂的分析，我们最多使用我们数据仓库中的另外 10 个表或物化视图。为了简化分析，我们创建了一个统一且可用的数据视图。

这个 growth 视图，实际上是一个大型的 LEFT JOIN，是大多数分析任务的基础，它显着简化了上述许多查询，并允许不太熟练的用户通过 Superset 可视化来回答问题。例如，计算获取、激活和转化的月度统计数据变成一个简单的查询。

  
1SELECT
2   toStartOfMonth(organization__created_at) AS cohort_month,
3   count() AS total_organizations,
4   sum(organization__service_created) AS total_services_created,
5   sum(organization__data_added) AS total_data_added,
6   sum(organization__cc_added) AS total_cc_added
7FROM
8   dbt_marts_growth.growth
9WHERE
10   organization__created_at >= today() - interval 6 month
11GROUP BY
12   cohort_month
13ORDER BY
14   cohort_month ASC;

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

这篇博客介绍了使用 ClickHouse 构建产品分析解决方案的过程，涉及模式设计、常用查询、物化视图以及集成其他数据集等关键方面。我们分享了我们构建内部分析平台 Galaxy 的过程中的见解，该平台使我们能够从超过 200 亿个事件中获得可操作的见解。

构建您自己的产品分析堆栈可提供无与伦比的灵活性和控制力。它允许您自定义数据收集、执行高级分析以及与您更广泛的数据生态系统无缝集成。但是，这种灵活性也带来了对数据库管理、数据建模和 SQL 专业知识的需求。

同样重要的是要认识到，这篇博客仅提供了可能性的一个缩影。产品分析是一个广阔的领域，具有细致入微的需求，这些需求因组织而异。虽然我们分享了常见查询和工作流程的示例，但这些只是起点。每个用例都可能需要独特的方法，而更深层次的挑战，如数据治理、用户归因和多触点分析，通常需要量身定制的解决方案。

对于那些愿意投资构建自己的解决方案的人来说，回报可能是巨大的——不仅提供更好的见解，还提供竞争优势。我们希望这篇博客能激发您探索 ClickHouse 以满足您的产品分析需求，并使您能够设计一个为您的业务目标量身定制的系统。