使用 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”这个词来自点击分析，这是最初开发该数据库的类似 Google Analytics 的用例。

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

这些查询中的大多数都包含聚合，ClickHouse 作为面向列的数据库对此进行了优化，能够在数十亿行数据上提供亚秒级响应时间，而无需抽样——远远超出我们从 GA4 中看到的规模。

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

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

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

虽然 ClickHouse 将是网络分析数据的理想数据存储，但我们仍然希望保留 GA4 和 Google Tag Manager 的数据收集功能。我们对编写我们自己的 Javascript 库来负责会话跟踪和数据收集没什么兴趣。理想情况下，我们可以简单地确保数据以合理的频率导出到 ClickHouse。

我们相信，我们上面遇到的痛苦不太可能是独一无二的，因此我们探索了从 Google Analytics 导出数据的方法。Google 提供了 多种方法来实现此目的，其中大多数方法都存在一些限制。最好的解决方案似乎是将数据导出到 BigQuery。与其他解决方案（例如 Data API）相比，这具有许多优势，包括

• 这将导出原始数据，而无需抽样。
• Google 允许每天最多 100 万个事件以 批处理方式导出到每日表。这足以满足我们的需求，并且低于我们当前的阈值。将来我们可能需要请求 Google 增加此限制。
• 数据可以 流式传输模式导出到当日内表 以及每日导出。当日内“实时”表通常比实时数据滞后几分钟。最重要的是，此导出没有限制！但是，它并不包含所有相同的事件（尽管它符合相同的模式）——阻止某些查询在实时数据上运行。有趣的是，这开启了实时仪表板的可能性！

流式导出大约产生每千兆字节数据 0.05 美元的费用。1 千兆字节相当于大约 600,000 个 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 中的导出量每天最多 50TiB 是免费的，存储费用很小。

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 列从格式转换为 Map(String,String)，转换为 Array(Tuple(key Nullable(String), value Tuple(string_value Nullable(String), int_value Nullable(Int64), float_value Nullable(Float64), double_value Nullable(Float64))))，以便使查询在语法上更简单。

虽然我们的内部数据仓库 有其自己的自定义机制 用于加载数据，但 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 开发层服务中进行的。这会将存储限制为 1TiB，并在两个节点上提供总共 4 个 vCPU 和 16GiB RAM，对于大多数组织来说，足以运行上述解决方案。

下面，我们根据此服务介绍了运行我们的解决方案的可能成本，并评估了可能存储的 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.6GiB，每天约有 135k 个事件。但是，磁盘上的空间被压缩到仅 525MiB。我们假设我们的当日内表消耗相似的空间量。因此，这可能允许开发层服务存储 = 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 的导出成本。每千兆字节数据 0.05 美元。1 千兆字节相当于大约 600,000 个 Google Analytics 事件，或者是上述样本数据的 5 倍。因此，我们假设这是可以忽略不计的，即对于规模大 10 倍的站点来说 < 1 美元。
• BigQuery 中数据的存储。前 1 TiB/月是免费的；此后，按需定价约为每 TiB 6 美元。鉴于上述数据量，用户不应在此处产生费用，如果担心，可以 在 N 天后使 BigQuery 中的数据过期。

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

概览仪表板

随时间变化的最受欢迎的博客文章

主要流量来源

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

字典为我们提供了数据的内存键值对表示形式，针对低延迟查找查询进行了优化。我们可以利用这种结构来提高一般查询的性能，其中 JOIN 特别受益，其中 JOIN 的一边表示适合内存的查找表。更多详细信息请参见 此处。

最后，考虑到并非每个人都对 SQL 感到满意，并且本着一切都需要生成式 AI 才能变得酷炫且值得做的精神，我决定启动一个副项目，看看我们是否可以通过自然语言回答 Google Analytics 问题。想象一下这样的世界：您可以简单地输入“显示我去年关于 X 页面的新用户数”，然后您神奇地获得了一个图表。是的，雄心勃勃。可能只是一个童话故事。敬请期待。

我们介绍了一种使用 ClickHouse 增强 Google Analytics 数据的简便方法，以每月不到 20 美元的价格提供灵活、快速且无限期保留的分析。