使用SQL总结A/B实验结果
今日份知识你摄入了么？
“一个疯狂的科学家在为一个实验编写SQL代码，这是用羊毛编织而成的。”——图片作者DALL-E
我曾在大大小小的科技公司做过应用程序、产品和营销A/B实验。虽然每个实验的特性和目标各不相同，但我为总结这些产品实验的结果而编写的SQL每次都是相同的。它们都是一种设计模式！
建立一个用户级别的表，然后根据该表计算汇总指标。
这种方法对我来说就像是第二天性，但我很惊讶的是我需要经常向其他开始做实验的人展示它，因为没有太多东西可以解释这种方法。所以我写了这本指南！
即使你使用实验平台应用程序（尤其是那些做得好的应用程序，例如Eppo和GrowthBook），我发现，了解这些应用程序如何从不同的源表中提取来计算汇总指标也是至关重要的。你不仅可以更好地了解这些数字的来源，还可以比较手动计算的数字与应用程序中的数字，让你更加确信应用程序的配置是正确的，可以按预期运行。
本指南假定：
用户是你的随机化单元（即，你将用户随机分为控制和测试变量或A/B变量）。有些公司使用其他的随机化单元，如公司、会议或地点。
你有一个实验或特征标志赋值表。此表告诉你每个实验，哪些用户在什么时间被分配到哪个变化。每一行都包含一个用户ID、实验名称、特征标志求值和赋值时间戳。
你有一个包含用户属性（即dim _ users）的基本维度表。对于每个用户ID，都有关于该用户的信息，例如创建日期、城市名称、注册营销渠道、首次订购日期等。
你在用Snowflake。我在这里使用的是他们的SQL，但该代码可以在其他数据仓库上使用，只需稍作更改。
实验
假设我们是一家送冰淇淋的公司！圣代比甜筒带来更多的收入，所以我们有兴趣增加圣代的订单。我们的假设是，我们的顾客希望订购圣代，但需要帮助他们定制他们的甜调酒。所以我们的工程师建立了一个圣代向导，这个功能是我们在实验中测试的。
我们的主要指标（即成功指标）是每个用户下的圣代订单数量，我们预测新的圣代向导会增加这个主要指标。我们监测的其他指标是总订单数、平均订单价值和用户转化率。我们选择这些护栏指标是因为我们想要检查我们是否损害了整体用户体验并减少了我们的订单和收入。
我们的功率分析表明，我们需要进行两周的实验才能达到我们的目标样本量。我们的实验从7月1日到7月15日进行，用户被平均地随机分配到一个对照组（没有向导）或一个测试组（有向导）。
过程
当我得到一个这样的实验时，我的过程是：
建立一个连续性表格，其中包含计算汇总指标所需的所有指标，汇总到用户级别
计算汇总指标
是的，就是这样！计算完汇总指标后，你可以继续进行显著性测试，深入研究，然后开始编写报告。所有这些都将取决于你首先拥有摘要指标。
1. 用户级表
首先，我们需要建立用户级别的表。此表包含于实验中的每个用户的一行、他们的属性以及在实验期间采取的任何相关措施，汇总到用户Grain中。
这个表具有许多强大的功能：
轻松计算汇总指标。（最重要的原因！）
关于谁在实验中，他们看到了哪种变量，以及他们的属性和指标的稳定来源。
开启深入探讨。你可以按特定的用户属性对指标进行切片，也可以探索特定群组中的行为。
分析事件数据。你可以将事件流加入此表，以开始计算事件并构建漏斗分析。
create table experiments.sundae_wizard_users as
-- Get all users exposed to the experimentwith exposed_users as (
select experiment_name, user_id, feature_flag_evaluation, evaluated_at from analytics.fct_experiment_assignments where experiment_name = 'sundae_wizard' and evaluated_at between '2022-07-01' and '2022-07-15'
),
-- If an user id shows up multiple times, it's because they saw more than one variantmultiple_variants as (
select user_id, count(*) as variants from exposed_users group by 1 having variants > 1
),
-- Remove users who saw multiple variantsexposed_users_cleaned as (
select exposed_users.* from exposed_users left join multiple_variants on exposed_users.user_id = multiple_variants.user_id where multiple_variants.user_id is null
),
-- Build your users table, and exclude spam usersusers as (
select exposed_users_cleaned.user_id, iff(exposed_users_cleaned.feature_flag_evaluation, 'test', 'control') as cohort, exposed_users_cleaned.evaluated_at as assigned_at, dim_users.created_at, dim_users.city_name, dim_users.signup_marketing_channel, dim_users.first_order_at from analytics.dim_users inner join exposed_users on dim_users.user_id = exposed_users.user_id where dim_users.is_spam = false
),
-- Last, join order metrics for all orders during the experiment periodfinal as (
select users.*, count(iff(fct_orders.order_type = 'sundae', fct_orders.order_id, null)) as cnt_sundae_orders, count(fct_orders.order_id) as cnt_orders, coalesce(sum(fct_orders.order_value), 0) as total_order_value, cnt_orders > 0 as is_user_converted from users left join analytics.fct_orders on users.user_id = fct_orders.user_id and fct_orders.order_placed_at between '2022-07-01' and '2022-07-15' group by 1, 2, 3, 4, 5, 6, 7
)
select * from final;view rawcreate_user_level_table.sql hosted with ❤ by GitHub 如何建立一个用户级别的表，其中包括你想要在实验中分析的所有用户
在构建用户级表时要记住的一些关键事项：
它们应该列出你想要分析的所有用户，而不是你不想分析的用户，例如垃圾邮件用户或暴露于多种变量的用户。
聚合列应包含实际值，而不是空值。如果用户在实验期间下了零订单，则cnt _ orders值应合并为0，而不应保留为null。这使你可以更安全地计算平均值，因为零值可能会导致返回错误的分母。
用户属性和顺序指标可以随时间变化。你需要你的分析是可复制的——相信我，你会被要求重新运行它！你可以使用ecreate table命令将其保存为持久表。我发现一个专用的模式，即“实验”，对于存储这些表非常有用。
2. 汇总指标
一旦有了用户级别的表，就可以将指标汇总到群组级别（控制与测试）。你可以通过在群组之后添加更多属性并逐行更新群组（即按营销渠道进行切片）来进一步对其进行切片。
下面代码的输出将是你的汇总指标——你的团队将用来判断实验成功与否。
select cohort, count(user_id) as cnt_users, sum(cnt_sundae_orders) as total_sundae_orders, sum(cnt_orders) as total_orders, sum(total_order_value) as total_order_value, sum(is_user_converted::int) as cnt_converted_users, total_sundae_orders / cnt_users as avg_sundae_orders, total_orders / cnt_users as avg_orders, total_order_value / total_orders as avg_order_value, cnt_converted_users / cnt_users as conversion_ratefrom experiments.sundae_wizard_usersgroup by 1order by 1 -- This keeps cohorts in the same order every time you run this code;
╔═════════╦═══════════╦═════════════════════╦══════════════╦═══════════════════╦═════════════════════╦═══════════════════╦════════════╦═════════════════╦═════════════════╗║ cohort ║ cnt_users ║ total_sundae_orders ║ total_orders ║ total_order_value ║ cnt_converted_users ║ avg_sundae_orders ║ avg_orders ║ avg_order_value ║ conversion_rate ║╠═════════╬═══════════╬═════════════════════╬══════════════╬═══════════════════╬═════════════════════╬═══════════════════╬════════════╬═════════════════╬═════════════════╣║ control ║ 4,707 ║ 3,671 ║ 4,848 ║ 42,616 ║ 4,566 ║ 0.78 ║ 1.03 ║ 8.79 ║ 0.97 ║╠═════════╬═══════════╬═════════════════════╬══════════════╬═══════════════════╬═════════════════════╬═══════════════════╬════════════╬═════════════════╬═════════════════╣║ test ║ 4,681 ║ 3,885 ║ 4,868 ║ 43,863 ║ 4,353 ║ 0.83 ║ 1.04 ║ 9.01 ║ 0.93 ║╚═════════╩═══════════╩═════════════════════╩══════════════╩═══════════════════╩═════════════════════╩═══════════════════╩════════════╩═════════════════╩═════════════════╝如何从用户级别的表中计算汇总指标
看起来圣代向导功能可能已经驱动了圣代订单！对照组每个用户有0.78个圣代订单，试验组每个用户有0.83个圣代订单：每个用户增加了0.05个圣代订单。我们需要进行统计测试，以确定这是一个显著的增长，还是只是小插曲。
关于分母的说明
你会注意到我通过写出分子和分母来显式计算平均值，而不是使用avg（）函数。这使得代码和指标定义对其他人更加透明，并减少了（懒惰！）错误和错误指标的可能性。
例如，如果将每个用户的平均圣代订单和每个用户的平均订单分别定义为avg（total _ sundae _ orders）和avg（total _ orders），出于习惯，也可以将平均订单值定义为avg（total _ order _ value）。但这是错误的。
我们使用的是用户级别的表，而不是订单级别的表。用户的计数是在avg（total _ order _ value）中用作分母的值。但是计算平均订单价值的分母是订单数量，而不是用户！右方程式是total _ order _ value/cnt _ orders。
这让我想到了我一直以来最喜欢的一条数据科学推文：
因此，我们最好避免avg（）函数并写出完整的计算指标。
结论
如果你有幸在一个会进行许多实验的地方工作，你会发现自己多次编写的是相同的SQL。自然地，下一步是如何实现自动化。我使用了一系列不同的方法来实现用户级别的表格和汇总指标在未知数量的实验中的自主化：简单而直接处理所有实验和特征标志名称，依靠dbt seeds和dbt macros，内部实验平台，或者使用Eppo或GrowthBook等实验平台应用。
计算汇总指标后的下一步是测试显著性和汇报升量和增量。
然后是最重要的部分是，通过深入研究和良好的数据分析工作，我们弄清楚了我们如何以及为什么得到了这些指标。总结指标和统计显著性只是起点，宝贵的是找出方法和原因！
原文作者：Adam Stone
翻译作者：高佑兮
美工编辑：过儿
校对审稿：Chuang
原文链接：https://medium.com/@foundinblank/using-sql-to-summarize-a-b-experiments-d30428edfb55
本周公开课预告
往期精彩回顾
招贤纳士 | Marketing specialist、Business Development Manager
【Python-数据科学】Pandas Basics速查表（2023）
点击“阅读原文”查看数据应用学院核心课程