使用 ClickHouse 增强 Google Analytics 数据

Google Analytics 无处不在，对于大多数营销功能的统计报告至关重要。作为一名加入 ClickHouse 且之前没有任何营销分析经验的人，我发现自己定期以博客的形式撰写内容，我一直认为 Google Analytics (GA4) 提供了一种快速且无缝的方式来衡量网站。因此，当我被要求报告我们内容策略的成功情况时，目的是确保我们制作的内容与您（我们的用户）相关，GA4 看起来是最佳的起点。

我怀着天真的热情，提出了一系列我以为在 GA4 中很容易回答的问题，例如“从博客发布之日起，每个博客的浏览量分布情况如何？”我们营销运营主管 Adrian 的反应“礼貌地”传达了这需要一些时间来制定。意识到我们需要每月报告一次，并看到他忙于其他更重要的任务，我主动提出用自己的时间来帮忙。

对博客表现进行报告很快变成了我每个月最头疼的日子。虽然 Google Analytics 有其优势，尤其是在其易于集成和使用的方面，但很明显它在许多关键方面存在局限性：数据保留、采样、性能和灵活性。与其在 GA4 中苦苦挣扎并害怕每个月的第二个星期一，我们着手将所有 Google Analytics 数据迁移到 ClickHouse，目标是提供灵活、快速且无限保留的分析。

在这篇博客文章中，我们将解释我们的架构，希望其他受此困扰的人可以用它来构建自己的超强版 Google Analytics，只需使用 ClickHouse 和几行 SQL 即可。最重要的是，这一切都可以在使用 ClickHouse Cloud 时以每月不到 20 美元的价格实现！

**灵活性。**Google Analytics 的优势在于其易于与网站集成以及简单的查询界面。这种简单性是有代价的，主要体现在灵活性方面。虽然界面易于使用，但它具有限制性，限制了用户回答更复杂问题的能力，例如“按发布日期对博客的浏览量进行分布式统计？”我们的许多问题还要求使用外部数据集，例如阅读时间和博客主题。虽然我们经常可以通过导出数据并使用 clickhouse local 查询文件或使用 GA4 的 导入数据功能 来克服这些挑战，但这个过程很缓慢且耗时。作为一个认为自己懂点技术的人，我渴望拥有 SQL 的灵活性。

**性能。**Clickhouse.com 访问量很大 - 每天数万人次。虽然这看起来很高，但实际上，对于 ClickHouse 习惯的数据量来说，这个数据量非常小。尽管如此，GA4 界面始终很缓慢，查询加载需要很长时间。这使得对查询进行迭代成为一种极其令人沮丧的体验。而且，作为一个习惯于对数据进行实时分析并获得闪电般快速响应时间的公司，这种性能充其量令人沮丧，最糟糕的是，它导致我们不愿提出有关数据的新问题。

**采样和临时查询。**鉴于相对较低的数据量，令人惊讶的是，Google Analytics 中的查询经常报告数据正在被采样。对我们来说，这种情况发生在发出临时查询（报告看起来更可靠）时，这些查询使用大量维度或跨越较长时间段。GA4 提供了一些 解决方法，包括升级到 Google Analytics 360（每年 150,000 美元！）或仅仅等待很长时间才能获得结果。这些都不适合我们，而且结果不可靠令人非常沮丧，因为我们作为 ClickHouse 用户，习惯了快速、精确的响应。

**数据保留。**默认情况下，GA4 会保留两个月的数据。这可以 增加到 14 个月，如果需要更长时间，则需要 360（同样，每年 150,000 美元）。虽然我们目前没有多年的数据，但我们希望能够使用超过 2 个数据点来识别随时间推移的模式，例如季节性趋势。

虽然 ClickHouse 对我们来说是显而易见的选择，但作为一项自我测试，它也是用于网络分析的事实上的数据库 - “Click”这个词来自 Click Analytics，这是数据库开发的最初类似 Google Analytics 的用例。

作为支持 SQL 的实时数据仓库，ClickHouse 提供了我们所需的查询灵活性。我们几乎所有查询都可以轻松地用 SQL 表示。ClickHouse 字典还提供了完美的解决方案来集成我们的外部数据源，例如博客主题和阅读时间。

这些查询中的大多数包含 ClickHouse 作为列式数据库所优化的聚合，能够在不进行采样的情况下对数百亿行数据提供亚秒级响应时间 - 这远远超过我们在 GA4 中看到的数据规模。

ClickHouse 支持广泛的集成，使报告生成变得更简单。除了 支持 MySQL 接口，允许使用 Looker、Tableau 和 QuickSight 等工具之外，对 Superset 和 Grafana 等工具的原生支持提供了一种开源 BI 体验。最后，我们相信原始数据将被 ClickHouse 很好地压缩，并可能以低成本无限期地保留 - 尤其是在 ClickHouse Cloud 使用对象存储的情况下。

在这一点上，很明显，我们不仅可以解决博客报告的问题。我们的营销团队在报告更广泛的网站指标时遇到了上面提到的相同挑战。

作为一家以数据驱动决策为傲的公司，我们已经有一个专门负责内部数据仓库的团队。如果我们能找到一种简单的方法来共享数据并提供大部分必要的查询，我们就能利用他们现有的技术来加载、管理和可视化数据。

虽然 ClickHouse 是一个理想的 Web 分析数据存储，但我们仍然希望保留 GA4 和 Google 标签管理器的数据收集功能。我们不太愿意编写自己的负责会话跟踪和数据收集的 Javascript 库。理想情况下，我们只需要确保数据以合理的频率导出到 ClickHouse。

我们相信我们上面遇到的问题并非独一无二，因此我们探索了从 Google Analytics 导出数据的方法。Google 提供了多种实现此目的的方法，但大多数方法都有一些限制。最佳解决方案似乎是将数据导出到 BigQuery。与其他解决方案（如数据 API）相比，此方法有几个优势，包括：

• 这将导出原始数据，不会进行抽样。
• Google 允许每天最多导出 100 万个事件，并作为批量数据导出到每日表格。这足以满足我们的需求，并且低于我们目前的阈值。我们可能需要在将来要求 Google 提高此限制。
• 数据可以以流式模式导出到日内表格，以及每天导出一次。日内“实时”表格通常比实时数据滞后几分钟。最重要的是，此导出没有限制！但是，它不包含所有相同的事件（尽管它符合相同的模式） - 阻止对实时数据运行某些查询。有趣的是，这开启了实时仪表盘的可能性！

流式导出约需每 GB 数据 0.05 美元。1 GB 大约相当于 60 万个 Google Analytics 事件。对于大多数用户来说，这应该微不足道。

只要为您的 Google Cloud 帐户启用了 BigQuery，此连接的配置就非常简单，并且有详细的文档。

那么，也许显而易见的问题是，“为什么不直接使用 BigQuery 进行分析？”

成本和性能。我们希望定期运行查询，并创建实时仪表盘，尤其是访问实时数据。虽然 BigQuery 非常适合对复杂查询进行即席分析，但它会根据扫描的数据量收费，导致成本难以预测。相反，ClickHouse Cloud 以固定成本提供这些查询，并使用小型集群（例如，每月不到 200 美元的开发层服务）。此外，BigQuery 通常会产生最小的查询延迟。我们知道 ClickHouse 将提供毫秒级的响应时间，并且更适合扁平模式（只有两个表格）和以聚合为主的查询。有关 BigQuery 和 ClickHouse 之间差异的更多详细信息，请访问此处。

有关如何在 BigQuery 和 ClickHouse 之间迁移数据的详细信息，请访问我们的文档此处。总而言之，我们依靠两个计划的查询将数据导出到 GCS 存储桶中的 Parquet 文件中：一个用于每日表格（格式为 events_YYYYMMDD），另一个用于实时日内表格（格式为 events_intraday_YYYYMMDD）。Parquet 是我们首选的导出格式，因为它具有良好的压缩率、结构化模式，以及在ClickHouse 中快速读取数据的出色支持。用户可以使用计划的 INSERT INTO SELECT 查询（使用 cron 服务和gcs 表函数），或最近发布的S3Queue 将这些数据导入 ClickHouse。

我们将在下面详细介绍此架构。

要从 BigQuery 导出数据，我们依赖于计划的查询及其导出到 GCS 的能力。

我们发现，每日表格会在格林尼治标准时间下午 4 点左右创建前一天的数据。这意味着当天的数据至少需要 16 个小时才能使用。当天的所有数据都会同时提供，因此当天的最早事件可能会延迟长达 40 个小时！这使得日内数据更加重要。为了安全起见，我们使用 BigQuery 中的以下计划查询在下午 6 点进行导出。BigQuery 中的导出每天最多可免费进行 50 TiB，存储会产生少量费用。

DECLARE export_path string;
DECLARE export_day DATE;
SET export_day = DATE_SUB(@run_date, INTERVAL 1 DAY);
SET export_path = CONCAT('gs://clickhouse-website/daily/', FORMAT_DATE('%Y%m%d', export_day),'-*.parquet');

EXECUTE IMMEDIATE format('EXPORT DATA OPTIONS ( uri = \'%s\', format = \'PARQUET\', overwrite = true) AS (SELECT * FROM `<cloud_project>.<dataset>.events_%s` ORDER BY event_timestamp ASC)', export_path,  FORMAT_DATE('%Y%m%d', export_day));

可以更定期地从日内表格中导出实时数据。目前，我们每小时安排一次导出。我们每小时导出过去 60 分钟的数据。但是，我们会对这个窗口进行偏移，以容纳事件可能出现的延迟以及在 BigQuery 中显示的延迟。虽然通常情况下，这不会超过 4 分钟，但为了安全起见，我们使用 15 分钟。因此，每次运行导出时，我们都会导出从 now-75mins 到 now-15mins 的所有行。这在下图中进行了可视化

以下显示了此计划查询。

DECLARE
export_time_lower TIMESTAMP DEFAULT TIMESTAMP_SUB(@run_time, INTERVAL 75 MINUTE);
DECLARE
export_time_upper TIMESTAMP DEFAULT TIMESTAMP_SUB(@run_time, INTERVAL 15 MINUTE);

CREATE TEMP TABLE ga_temp AS
SELECT *
FROM `<cloud_project>.<dataset>.events_intraday_*` WHERE TIMESTAMP_MICROS(event_timestamp) > export_time_lower
  AND TIMESTAMP_MICROS(event_timestamp) <= export_time_upper;

EXPORT DATA
OPTIONS ( uri = CONCAT('gs://clickhouse-website/ga-', FORMAT_TIMESTAMP("%Y%m%d%H%M%S",TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL 1 HOUR), HOUR), "UTC"), '_*.parquet'),
  format = 'PARQUET', overwrite = true) AS (


SELECT * FROM ga_temp ORDER BY event_timestamp ASC);

每日表格和日内表格的模式相同，并且在此处进行了记录。上述导出过程生成的 Parquet 文件的模式可以在此处找到，以供参考。我们将此模式映射到以下 ClickHouse 模式

CREATE OR REPLACE TABLE default.ga_daily
(
	`event_date` Date,
	`event_timestamp` DateTime64(3),
	`event_name` String,
	`event_params` Map(String, String),
	`ga_session_number` MATERIALIZED CAST(event_params['ga_session_number'], 'Int64'),
	`ga_session_id` MATERIALIZED CAST(event_params['ga_session_id'], 'String'),
	`page_location` MATERIALIZED CAST(event_params['page_location'], 'String'),
	`page_title` MATERIALIZED CAST(event_params['page_title'], 'String'),
	`page_referrer`  MATERIALIZED CAST(event_params['page_referrer'], 'String'),
	`event_previous_timestamp` DateTime64(3),
	`event_bundle_sequence_id` Nullable(Int64),
	`event_server_timestamp_offset` Nullable(Int64),
	`user_id` Nullable(String),
	`user_pseudo_id` Nullable(String),
	`privacy_info` Tuple(analytics_storage Nullable(String), ads_storage Nullable(String), uses_transient_token Nullable(String)),
	`user_first_touch_timestamp` DateTime64(3),
	`device` Tuple(category Nullable(String), mobile_brand_name Nullable(String), mobile_model_name Nullable(String), mobile_marketing_name Nullable(String), mobile_os_hardware_model Nullable(String), operating_system Nullable(String), operating_system_version Nullable(String), vendor_id Nullable(String), advertising_id Nullable(String), language Nullable(String), is_limited_ad_tracking Nullable(String), time_zone_offset_seconds Nullable(Int64), browser Nullable(String), browser_version Nullable(String), web_info Tuple(browser Nullable(String), browser_version Nullable(String), hostname Nullable(String))),
	`geo` Tuple(city Nullable(String), country Nullable(String), continent Nullable(String), region Nullable(String), sub_continent Nullable(String), metro Nullable(String)),
	`app_info` Tuple(id Nullable(String), version Nullable(String), install_store Nullable(String), firebase_app_id Nullable(String), install_source Nullable(String)),
	`traffic_source` Tuple(name Nullable(String), medium Nullable(String), source Nullable(String)),
	`stream_id` Nullable(String),
	`platform` Nullable(String),
	`event_dimensions` Tuple(hostname Nullable(String)),
	`collected_traffic_source` Tuple(manual_campaign_id Nullable(String), manual_campaign_name Nullable(String), manual_source Nullable(String), manual_medium Nullable(String), manual_term Nullable(String), manual_content Nullable(String), gclid Nullable(String), dclid Nullable(String), srsltid Nullable(String)),
	`is_active_user` Nullable(Bool)
)
ENGINE = MergeTree
ORDER BY (event_timestamp, event_name, ga_session_id)

此模式代表我们的初始迭代，用户可能希望进行调整。通常，用户可能希望将这些数据以视图的形式呈现给他们的业务用户，以方便他们使用，或者为特定列创建物化视图，以简化（并加快）语法访问，例如，在上述示例中，我们已经将 ga_session_id、page_location、page_title 和 page_referer 列创建了物化视图。

我们已经根据访问模式和下面的查询选择了我们的排序键。

有经验的 ClickHouse 用户会注意到 Nullable 的使用，这通常是在 ClickHouse 中表示空值的低效方法。我们目前保留这些值，因为在将 event_params 转换为更易于访问的 Map(String,String) 时需要它们。将来，我们计划为 Parquet 文件添加模式提示，以允许我们关闭默认情况下将 Parquet 列设为可空，即 schema_inference_make_columns_nullable = 0。我们预计这种 Nullable 开销将很小，因为 ClickHouse 的数据量很低。

我们可以使用 gcs 函数和以下 INSERT INTO SELECT 语句将数据从 Parquet 文件插入此模式中。此语句对两个表格都适用。

INSERT INTO ga_daily SELECT
  event_date::Date as event_date,
  fromUnixTimestamp64Micro(event_timestamp) as event_timestamp,
  ifNull(event_name, '') as event_name,
  mapFromArrays(
	arrayMap(x -> x.1::String, event_params),
	arrayMap(x -> arrayFilter(val -> isNotNull(val),array(untuple(x.2:: Tuple(string_value Nullable(String), int_value Nullable(String), float_value Nullable(String), double_value Nullable(String))))::Array(Nullable(String)))[1], event_params)
  )::Map(String, String) as event_params,
  fromUnixTimestamp64Micro(ifNull(event_previous_timestamp,0)) as event_previous_timestamp,
  event_bundle_sequence_id,
  event_server_timestamp_offset,
  user_id,
  user_pseudo_id,
  privacy_info,
  fromUnixTimestamp64Micro(ifNull(user_first_touch_timestamp,0)) as user_first_touch_timestamp,
  device,
  geo,
  app_info,
  traffic_source,
  stream_id,
  platform,
  event_dimensions,
  collected_traffic_source,
  is_active_user
FROM  gcs('https://storage.googleapis.com/clickhouse-website/ga-*.parquet','<key>','<secret>')

此处的多数列都是直接映射。但是，我们将 event_params 列从格式 Array(Tuple(key Nullable(String), value Tuple(string_value Nullable(String), int_value Nullable(Int64), float_value Nullable(Float64), double_value Nullable(Float64)))) 转换为 Map(String,String)，以简化查询语法。

虽然我们内部的数据仓库拥有自己的自定义数据加载机制，但 ClickHouse 用户可以使用计划的 INSERT INTO SELECT（例如，使用简单的 cron 或通过Cloud Scheduler），或使用最近发布的 S3Queue 表引擎 来复制上述架构。我们将在下面演示。

CREATE TABLE ga_queue
ENGINE = S3Queue('https://storage.googleapis.com/clickhouse-website/daily/*.parquet', '<key>', '<secret>', 'Parquet')
SETTINGS mode = 'unordered', s3queue_enable_logging_to_s3queue_log=1, s3queue_tracked_files_limit=10000

CREATE MATERIALIZED VIEW ga_mv TO ga_daily AS
SELECT
  event_date::Date as event_date,
  fromUnixTimestamp64Micro(event_timestamp) as event_timestamp,
  ifNull(event_name, '') as event_name,
  mapFromArrays(
	arrayMap(x -> x.1::String, event_params),
	arrayMap(x -> arrayFilter(val -> isNotNull(val),array(untuple(x.2:: Tuple(string_value Nullable(String), int_value Nullable(String), float_value Nullable(String), double_value Nullable(String))))::Array(Nullable(String)))[1], event_params)
  )::Map(String, String) as event_params,
  fromUnixTimestamp64Micro(ifNull(event_previous_timestamp,0)) as event_previous_timestamp,
  event_bundle_sequence_id,
  event_server_timestamp_offset,
  user_id,
  user_pseudo_id,
  privacy_info,
  fromUnixTimestamp64Micro(ifNull(user_first_touch_timestamp,0)) as user_first_touch_timestamp,
  device,
  geo,
  app_info,
  traffic_source,
  stream_id,
  platform,
  event_dimensions,
  collected_traffic_source,
  is_active_user
FROM ga_queue

可以在此处找到此功能的更多示例。

将来，我们还计划添加对使用ClickPipes 从对象存储中增量加载数据的支持：ClickHouse Cloud 的原生数据摄取服务引擎，它使加载数据变得像点击几个按钮一样简单。

将所有数据迁移到 Clickhouse 的主要顾虑之一是，能否从 Google 在导出中提供的原始数据中复制 Google Analytics 提供的指标。虽然 Google 文档记录了一些查询，但他们没有提供针对新用户、活跃用户、总用户、回访用户或总会话数等标准报告概念的等效查询。为了让营销部门能够使用 ClickHouse 解决方案，我们需要能够提供与历史上报告的数字相似的查询。

以下查询显示了我们目前使用的查询及其与 GA4 报告的数字之间的误差范围。此差异是在一个月的时间范围内计算出来的。请注意，并非所有查询都可以在实时日内数据上执行，因为有些必需的列没有提供。我们将在下面说明这一点。

指标	ClickHouse 查询	与 GA4 的差异	是否支持日内数据
总用户数	SELECT event_date, uniqExact(user_pseudo_id) AS total_users FROM ga_daily WHERE event_name = 'session_start' GROUP BY event_date ORDER BY event_date ASC	-0.71%	是
活跃用户数	SELECT event_date, uniqExact(user_pseudo_id) AS active_usersFROM ga_dailyWHERE ((event_name = 'session_start') AND is_active_user) OR (event_name = 'first_visit')GROUP BY event_dateORDER BY event_date ASC	-0.84	否。is_active_user 未填充
新用户数	SELECT event_date, count() AS new_usersFROM ga_dailyWHERE event_name = 'first_visit'GROUP BY event_dateORDER BY event_date ASC	0%	是
回访用户数	SELECT event_date, uniqExact(user_pseudo_id) AS returning_usersFROM ga_dailyWHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date)GROUP BY event_dateORDER BY event_date ASC	+1.03%	否。is_active_user 未填充
总会话数	SELECT event_date, uniqExact(ga_session_id, '_', user_pseudo_id) AS session_idFROM ga_dailyGROUP BY event_dateORDER BY event_date ASC	-0.56%	是

虽然我们一直在努力缩小上述差异，但上述差异对于今后的报告来说是可接受的。我们欢迎改进上述查询。

我们的数据被加载到我们的内部数据仓库中，该仓库包含许多具有大量资源的数据集，因此难以对运行 ClickHouse 增强型 GA 解决方案的成本进行准确评估。但是，我们的初始测试是在 ClickHouse Cloud 开发层服务中进行的。这将存储空间限制为 1 TiB，并提供总共 4 个 vCPU 和 16 GB 内存，分布在两个节点上，对于大多数组织来说，这足以运行上述解决方案。

下面，我们展示了基于此服务运行我们解决方案的可能成本，并评估了可能存储的 GA4 数据量。我们还展示了查询性能仍然非常快。我们的样本数据涵盖了 42 天的时间段。

SELECT
	table,
	formatReadableQuantity(sum(rows)) AS total_rows,
	round(sum(rows) / 42) AS events_per_day,
	formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
	formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
	round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.parts
WHERE (table LIKE 'ga_daily') AND active
GROUP BY table
ORDER BY sum(rows) ASC

┌─table────┬─total_rows───┬─events_per_day─┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ ga_daily │ 5.75 million │     	136832 │ 525.34 MiB  	│ 4.56 GiB      	│  8.88 │
└──────────┴──────────────┴────────────────┴─────────────────┴───────────────────┴───────┘

1 row in set. Elapsed: 0.008 sec.

上述显示，42 天的未压缩数据大小约为 4.6 GiB，每天约有 135k 个事件。但是，磁盘上的空间被压缩到只有 525 MiB。我们假设日内表格消耗的空间量类似。因此，这可能会允许开发层服务存储 = 420429 ((1048576/(525*2)) * 421) 天，也就是 1100 年的 GA4 数据。这可能足以满足我们的用例！*

用户应该能够根据每天的事件数推断出上述结果。即使是比 clickhouse.com 大 100 倍的网站，也应该能够在一个开发层实例中存储 10 年的数据。

*这是在进一步优化模式（例如，移除 Nullable）之前的结果。

GA4 的 BigQuery 导出服务不支持历史数据导出。这阻止我们在现阶段进行广泛的查询测试（我们将在基于实际使用情况时进行分享），将下面的查询限制在 42 天（从 BigQuery 开始迁移数据到 ClickHouse 的时间）。这足以满足我们的用例，因为我们的大多数查询涵盖了一个月的时间段，而分析历史趋势的查询比较少见。下面的查询针对我们网站 blog 区域 10 月份的总用户数、回访用户数和新用户数进行查询，并按天对结果进行分组。

总用户数

SELECT
	event_date,
	uniqExact(user_pseudo_id) AS total_users
FROM ga_daily
WHERE (event_name = 'session_start') AND ((event_timestamp >= '2023-10-01') AND (event_timestamp <= '2023-10-31')) AND (page_location LIKE '%/blog/%')
GROUP BY event_date
ORDER BY event_date ASC

31 rows in set. Elapsed: 0.354 sec. Processed 4.05 million rows, 535.37 MB (11.43 million rows/s., 1.51 GB/s.)
Peak memory usage: 110.98 MiB.

回访用户数

SELECT event_date, uniqExact(user_pseudo_id) AS returning_users
FROM ga_daily
WHERE (event_name = 'session_start') AND is_active_user AND (ga_session_number > 1 OR user_first_touch_timestamp < event_date) AND ((event_timestamp >= '2023-10-01') AND (event_timestamp <= '2023-10-31')) AND (page_location LIKE '%/blog/%')
GROUP BY event_date
ORDER BY event_date ASC

31 rows in set. Elapsed: 0.536 sec. Processed 4.05 million rows, 608.24 MB (7.55 million rows/s., 1.13 GB/s.)
Peak memory usage: 155.48 MiB.

新用户数

SELECT event_date, count() AS new_users
FROM ga_daily
WHERE event_name = 'first_visit' AND ((event_timestamp >= '2023-10-01') AND (event_timestamp <= '2023-10-31')) AND (page_location LIKE '%/blog/%')
GROUP BY event_date
ORDER BY event_date ASC

31 rows in set. Elapsed: 0.320 sec. Processed 4.05 million rows, 411.97 MB (12.66 million rows/s., 1.29 GB/s.)
Peak memory usage: 100.78 MiB.

以上展示了所有查询都在 0.5 秒内完成。我们表的排序键可以进一步优化，如果需要进一步提升性能，用户可以自由使用诸如 物化视图 和 投影 等功能。

在以下定价中，我们假设使用约 100GiB 的存储空间，即容量的 10%。这实际上相当于 clickhouse.com 110 年的数据，一个规模是其 10 倍的网站 10 年的数据，或者是一个规模是其 100 倍的网站 1 年的保留数据。实际上，如上所示，由于 ClickHouse Cloud 使用了对象存储，存储成本在总成本中所占比例微不足道，更大的网站可以轻松地存储多年，并且仍然保持在 20 美元以下。

我们假设集群每天总共运行 2 小时。这应该足以每小时加载当日数据和每天导出数据，以及好奇的营销部门执行的其他临时查询。如下所示，更大的每日导出可以在 5 秒内插入。

请注意，如果使用全部 1TiB 的存储空间，每月最多花费 193 美元。实际上，如果集群未被使用，它将处于空闲状态（仅产生存储费用），从而降低成本。

INSERT INTO ga_daily SELECT
	...
FROM gcs('https://storage.googleapis.com/clickhouse-website/daily/20231009-000000000000.parquet', '<key>', '<secret>')

0 rows in set. Elapsed: 5.747 sec. Processed 174.94 thousand rows, 15.32 MB (30.44 thousand rows/s., 2.67 MB/s.)

每小时计算成本：$0.2160 每月存储成本：$35.33 * 0.1 = $3.53 每天活跃时间：2 小时

总成本：（每天 2 小时 * 0.2160 * 30 天）+ 3.53 = $16.50

那就是用 ClickHouse 增强 Google Analytics 每天只需花费 16 美元！

这里还有一些额外的成本，我们认为这些成本应该可以忽略不计。这些包括

• 从 GA4 到 BigQuery 的导出成本。每 GB 数据 0.05 美元。1 GB 大致相当于 600,000 个 Google Analytics 事件或 5 倍以上样本数据。我们假设这是可以忽略不计的，例如，对于一个规模是其 10 倍的网站，成本不到 1 美元。
• BigQuery 中的数据存储。每月第一个 1 TiB 是免费的；之后，按需定价大约为每 TiB 6 美元。鉴于以上体积，用户不应该产生费用，并且可以 在 BigQuery 中过期数据，如果担心可以设置 N 天后过期。

ClickHouse 支持各种可视化工具，从 Tableau 到 QuickSight。对于我们的内部数据仓库，我们使用 Superset 和 官方 ClickHouse 连接器。凭借大量的可视化选项，我们发现这是一个非常棒的解决方案，足以满足我们的需求。我们建议将表作为 物理数据集 公开，以便 Superset 可以编写查询并在所有模式列上应用过滤器。

概览仪表盘

随着时间的推移，最受欢迎的博客文章

主要的流量来源

我们剩下的工作主要围绕确保内部数据仓库中存在数据集，我们可以使用这些数据集来丰富我们的分析。例如，许多页面都使用主题进行分类，因此我们可以根据这些数据进行使用分析。这些数据非常小，主要用于查询时的查找。我们计划通过字典来管理这些数据，一旦我们可以通过我们的 CMS 以优雅的方式公开这些数据。

字典为我们提供了一种数据内存中键值对表示，针对低延迟查找查询进行了优化。我们可以利用这种结构来提高查询的整体性能，特别是 JOIN 操作，其中 JOIN 的一侧表示一个适合内存的查找表。更多详细信息 这里。

最后，考虑到并非每个人都熟悉 SQL，并且为了让一切都需要是生成式 AI 才能变得酷炫和值得做，我决定启动一个副项目，看看我们是否可以通过自然语言来回答 Google Analytics 问题。想象一个世界，你只需键入“向我展示过去一年的关于 X 的页面的新用户”，你就会神奇地获得一张图表。是的，雄心勃勃。也许只是一个童话故事。敬请期待。

我们提供了一种简单的增强 Google Analytics 数据的方法，使用 ClickHouse 可以实现灵活、快速、无限保留的分析，每月不到 20 美元。