理解用户旅程的复杂性
直到最近，完整地了解用户旅程仍然相当困难，因为使用传统可视化工具分析复杂、分支的用户流程几乎不可能。我们没有清晰的方法来捕获每条路径并精确找出用户流失的位置，导致我们的转化分析不完整。
- 主要问题是传统工具无法显示多重分支路径（如简单的漏斗图），因此关键洞察常常被遗漏。
我有机会通过 Metabase 新的 桑基图可视化功能，将数百万条原始事件日志转化为清晰的注册到潜在客户生成的转化漏斗视觉叙事，从而直接应对这一挑战。这一突破不仅揭示了复杂的用户转换，还提供了可操作的洞察，有助于我们完善转化策略。（更多技术细节，请查看我的 GitHub 仓库）
以下是塔利斯讨论的整个设置的详细演练。
用户旅程中的数据挑战
处理数百万条事件记录会带来一系列挑战
- 重复事件。 用户可能会重复触发同一操作（例如多次点击支持按钮），如果不进行过滤，这会虚报我们的指标。
- 时间顺序完整性。 事件以正确的顺序发生至关重要，例如，注册必须发生在凭证屏幕被查看之前，而凭证屏幕被查看必须发生在任何后续操作之前。
- 高效过滤和聚合。 处理大型数据集需要强大的 SQL 查询，以在保留正确序列的同时高效地过滤和聚合数据。
解决这些问题对于构建一个可靠的用户旅程桑基图至关重要，该图显示了用户流失和转化发生的位置。
使用 SQL 逐步构建用户旅程
为了分解问题，我使用公共表表达式 (CTE) 将转化漏斗划分为不同的阶段。
步骤 1：早期过滤
我首先使用一个基础 CTE 在早期阶段过滤原始数据，这是处理大型数据集的关键一步。
WITH events_data AS (
SELECT
ke.user_id
,ke.event_timestamp
,mu.user_creation_date
,ke.event_id
,mu.user_origin
,ke.plataform_origin
,mu.covenant
FROM dbdelivery.tb_konsidb_events ke
JOIN dbdelivery.tb_mongo_users mu
ON ke.user_id = mu.user_id
WHERE mu.covenant IN (
'PREFEITURA DE GOIÂNIA',
'GOVERNO DO RIO DE JANEIRO',
'GOVERNO DE AMAZONAS',
'GOVERNO DO PARANÁ'
)
AND event_id IN (
‘registration_completed','registration_completed_sms', 'registration_completed_email','opened_easy_consig_registries_screen', 'opened_registries_screen', 'opened_govpe_registries_screen',
'clicked_more_registry_easy_consig_screen','clicked_easy_consig_registries_support','clicked_registries_screen_support_button', 'clicked_del_registry_easy_consig_screen',
'clicked_finish_btn_easy_consig_screen', 'gave_registry',
...
...
...
'no_lead_covenant_confirmation_screen','no_lead_user_correct_covenant', 'wrong_registry_error_screen_opened','clicked_wrong_registry_screen_retry_btn','clicked_wrong_registry_support_btn','clicked_easy_consig_registries_support','clicked_blocked_benefit_card','splash_screen_view','opened_unavailable_access_screen', 'clicked_support_unavailable_access')
),
步骤 2：分解漏斗
接着，我将转化漏斗划分为不同的阶段（例如，注册、凭证屏幕查看和潜在客户生成）。每个阶段都由其自己的 CTE 表示，从而更容易独立处理和排查每一步。
registration_completed AS (
-- This CTE identifies when users completed the registration process
SELECT
user_id
,user_creation_date
,user_origin
,plataforma
,covenant
,MIN(event_timestamp) AS event_timestamp
FROM events_data
WHERE event_id IN (
'registration_completed',
'registration_completed_email',
'registration_completed_sms'
)
GROUP BY 1, 2, 3, 4, 5
),
此查询捕获每个用户的第一个注册事件，反映了他们旅程的真正开始。
步骤 3：捕获用户旅程中的第一个事件
我使用以下技术来确保只捕获给定阶段中每个用户的第一个事件
saw_credentials_screen AS (
SELECT
user_id
,event_timestamp
,user_creation_date
,user_origin
,plataforma
,covenant
FROM events_data
WHERE evento_id IN ('opened_contracting_psw_screen','credentials_screen_opened')
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp ASC) = 1
),
使用 QUALIFY 结合 ROW_NUMBER 可以确保当用户注册时，即使发生多个注册事件，也只记录最早的一个。
步骤 4：确保时间顺序正确
接下来，漏斗中的每个事件都必须在前一个阶段之后发生。为了强制执行正确的顺序，我使用特定条件连接了 CTE。例如
FROM registration_completed
JOIN saw_credentials_screen
ON registration_completed.user_id = saw_credentials_screen.user_id
AND registration_completed.event_timestamp < saw_credentials_screen.event_timestamp
-- Ensure the user passed through the previous funnel step
这种连接确保用户的凭证屏幕查看事件仅在其注册之后发生时才被计数，从而过滤掉任何不遵循正确序列的数据。
使用 Databricks 和物化视图
为了处理我们庞大的数据集，我利用了我们的多层数据管道。我们的数据从原始数据摄取（Stage 和 Raw）开始，经过清洗和验证（Trusted 和 Delivery）等多个阶段，最终到达 Redshift。最初，我在 Delivery 表上运行查询，这些表已经包含处理过的数据。然而，查询的复杂性使得 Redshift 无法直接处理。
为了解决这个问题，我首先在 Databricks 中处理数据，创建了一个中间的、预聚合的表。然后，我将该表加载到 Redshift 中，并在其上构建了一个物化视图，从而确保我们在 Metabase 中的查询能够快速可靠地运行。
使用桑基图揭示转化洞察
最终输出是用户旅程的清晰可视化表示，显示了包含关键流失点和转化成功的完整转化漏斗。这种可视化使团队立即了解我们的转化流程在哪里出现漏洞以及哪些阶段需要有针对性的改进。
