理解用户旅程的复杂性
直到最近，要看到完整的用户旅程还相当困难，因为使用传统的可视化方法分析复杂、分支的用户流程几乎是不可能的。我们没有明确的方法来捕捉每个路径并精确指出用户流失的位置，这使得我们的转化分析不完整。
- 主要问题是传统工具无法显示多个分支路径，比如简单的漏斗图，因此经常会错过关键的洞察。
我有机会通过使用 Metabase 新的 桑基图可视化，将数百万条原始事件日志转换为我们从注册到潜在客户生成的转化漏斗的清晰视觉叙述，从而正面应对这一挑战。这一突破不仅揭示了复杂的用户转换，还提供了可操作的洞察，有助于完善我们的转化策略。（更多技术细节，欢迎查看我的 GitHub 仓库）
以下是 Thallys 讨论的整个设置的详细介绍。
用户旅程中的数据挑战
处理数百万条事件记录会带来一系列挑战
- 重复事件。 用户可能会重复触发相同的操作（例如多次点击支持按钮），如果不进行筛选，这可能会夸大我们的指标。
- 时间顺序完整性。 事件必须按照正确的顺序发生，这一点至关重要，例如，注册必须在凭证屏幕查看之前发生，并且凭证屏幕查看必须在任何后续操作之前发生。
- 高效过滤和聚合。 处理大型数据集需要一个强大的 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 查询运行快速可靠。
使用桑基图揭示转化洞察
最终输出是用户旅程的清晰视觉表示，显示了整个转化漏斗以及关键的流失点和转化成功点。这种可视化使团队能够立即洞察我们的转化过程在哪里存在漏洞，以及哪些阶段需要有针对性的改进。
