AGENTS.md 10 KB
实验分桶报表说明
本目录保存 apptype=4 的实验分桶报表。实验组为 rootSessionId 倒数第四位是 0、1 的用户，其余 2-f 共 14 桶为对照组。
统一统计口径
- 分桶字段：
LOWER(SUBSTR(rootSessionId, LENGTH(rootSessionId) - 3, 1))，只保留0-f。 - 实验组：尾号
0、1；对照组：其余 14 个尾号。 - 头部：
pagesource RLIKE 'user-videos-share$'。 - 推荐：
pagesource RLIKE '(detail|category|recommend)$'。 - 全部：不限制
pagesource。 - 曝光：
businesstype='videoView'，统计 PV 和去重midUV。 - 播放：
businesstype='videoPlay'，统计 PV 和去重midUV。 - 分享：
businesstype='videoShareFriend'，统计 PV 和去重midUV。 - 回流：分享记录与同日
topic='click'记录按shareid关联，统计去重点击用户machinecodeUV。 - STR：分享 PV / 曝光 PV。
- ROV：回流 UV / 曝光 PV。
- 人均指标：曝光、播放、分享 PV 或回流 UV除以同桶 DAU。
- DAU 相对变化：实验组每桶平均 DAU相对对照组每桶平均 DAU的变化率，避免直接比较 2 桶总量和 14 桶总量。
- 每个日期输出 20 行：16 行尾号明细、2 行分组聚合、2 行每桶均值。
- 事实值（DAU、PV、UV）保存为整数；比例列按既有实时报表格式输出。
SQL 文件
bucket_source_pv_uv_return_realtime_20260721_apptype4_version1578.sql
查询 2026-07-21 当天的实时数据，限定 apptype=4、versioncode=1578。
- DAU：
loghubods.useractive_log_per5min。 - 曝光、播放、分享：
loghubods.video_action_log_flow。 - 分享与回流：
loghubods.user_share_log_per5min。 - 输出粒度：16 个尾号，每个尾号一行原始事实数据。
- 日期、版本和实时表分区条件写在 SQL 中；复用到其他日期或版本时需要同步修改。
bucket_source_pv_uv_return_offline_20260716_20260720_apptype4_all_versions.sql
查询 2026-07-16 至 2026-07-20 的离线数据，限定 apptype=4，不限制版本号。
- DAU：
loghubods.useractive_log，与实时 SQL 一致，从extparams.rootSessionId取分桶字段。 - 曝光、播放、分享：
loghubods.video_action_log_applet。 - 分享与回流：
loghubods.user_share_log。 - 所有事实按
dt和尾号分别聚合，不跨天去重或合并。 - 原始输出粒度：5 天 × 16 个尾号，共 80 行。
- 输出仍保留版本号字段，统一写为“全部”，以便与限定版本的实时报表对齐。
offline_dau_by_bucket_20260716_20260720_apptype4.sql
只补查五天离线 DAU，用于事实指标已经查询完成时快速重建报表，避免重复执行完整事实 SQL。
- 数据源：
loghubods.useractive_log。 - 分桶字段：
GET_JSON_OBJECT(extparams, '$.rootSessionId')的倒数第四位。 - 输出粒度：日期 × 16 个尾号，共 80 行。
- 不限制版本号。
t0_fission_rate_realtime_20260721_apptype4_version1578.sql
查询 2026-07-21、apptype=4、版本 1578 的实时 T0 裂变率。
- 首层 UV：从
useractive_log_per5min的businesstype='path'记录中解析extparams.userShareDepth=0，排除extparams.rootSourceId以dyyqw开头的企微渠道后按machinecode去重。 - 首层分享 root：分享记录必须与首层 useractive 按同一
machinecode + rootSessionId匹配，并再次排除分享记录中rootsourceid以dyyqw开头的企微渠道。 - 裂变层 UV：首层分享 root 带回的 click 用户，还必须在相同
machinecode + rootSessionId下存在useractive path、userShareDepth>0，并排除企微渠道后按machinecode去重。 - 实时首层用户和分享记录限定版本 1578；回流 click 用户及其裂变 useractive 身份不限制版本。
- T0 裂变率：裂变层 UV / 首层 UV。
- 分桶仍取
rootSessionId倒数第四位。 - 实际 JSON 字段名是
userShareDepth、rootSourceId。
t0_fission_rate_offline_20260716_20260720_apptype4_all_versions.sql
使用与实时 T0 相同的人群、分享 root、裂变 click 和企微排除口径，统计 2026-07-16 至 2026-07-20 的离线数据。
- 数据源：
useractive_log、user_share_log。 apptype=4，不限制首层、分享或回流用户版本，版本号列统一写“全部”。- 所有首层用户、分享、click 和裂变身份均按同一天关联，不跨天归因。
- 原始输出为 5 天 × 16 个尾号，共 80 行。
first_layer_source_metrics_realtime_20260721_apptype4_version1578.sql
查询 2026-07-21、版本 1578 的首层用户头部/推荐/全部曝光、播放、分享和裂变指标。
- 首层人群、首层分享 root、裂变 click 和企微排除口径与实时 T0 SQL一致。
- 视频事件按
mid=machinecode且rootSessionId相同关联首层身份，统计曝光、播放、分享 PV/UV。 - 裂变场景按照首层分享记录的
pagesource归因；全部裂变 UV 对 click 用户整体去重。 - 首层用户和源行为限定版本1578，回流 click 用户及裂变身份不限制版本。
- 原始输出为 16 个尾号事实行。
first_layer_source_metrics_offline_20260716_20260720_apptype4_all_versions.sql
使用与实时首层来源报表一致的口径，查询 2026-07-16 至 2026-07-20 的全版本离线数据。
- 数据源对应替换为
useractive_log、video_action_log_applet、user_share_log。 - 每天独立关联，不跨天归因或去重。
- 原始输出为 5 天 × 16 个尾号，共 80 行。
Python 脚本
run_bucket_source_pv_uv_return_realtime.py
实时报表执行和格式化脚本。
- 默认执行实时 SQL，将英文字段映射为中文字段。
- 生成尾号明细、实验/对照聚合、实验/对照每桶均值。
- 计算头部、推荐、全部三个场景的人均指标、STR、ROV及相对对照组变化率。
- 输出
bucket_source_pv_uv_return_realtime_20260721_apptype4_version1578.csv。 python3 run_bucket_source_pv_uv_return_realtime.py --rebuild：不查询 ODPS，使用现有 CSV 重新计算比率列及列顺序。
run_bucket_source_offline_5day_report.py
五天离线报表执行和格式化脚本，复用实时脚本的字段映射、公式和列顺序，保证离线 CSV 与实时 CSV 完全同构。
- 默认执行完整离线 SQL，并按日期分别生成每天 20 行的结果。
- 输出按日期倒序排列，共 100 行。
- 版本号列保留并统一写为“全部”。
- 输出
bucket_source_full_report_offline_20260716_20260720_apptype4_all_versions.csv。 python3 run_bucket_source_offline_5day_report.py --rebuild-with-dau：保留现有 CSV 中的曝光、播放、分享、回流事实，只执行 DAU 补查 SQL，再重新生成聚合、均值、比率和变化率。使用此模式前必须已有包含 80 行尾号明细的离线 CSV。
两个脚本都依赖 pandas 以及本地运行环境提供的 odps_module.ODPSClient。包含访问凭据的连接配置不得提交到 Git。
run_t0_fission_rate_realtime.py
执行 T0 裂变率实时 SQL并生成中文 CSV。
- 输出 16 行尾号明细，以及实验组、对照组各一行每桶均值，共 18 行。
- 分组均值的首层 UV、裂变层 UV分别为组内尾号的算术平均。
- 分组 T0 裂变率为“平均裂变层 UV / 平均首层 UV”，等价于组内总裂变层 UV / 总首层 UV。
- 实验相对对照组变化率为“实验组 T0 裂变率 / 对照组 T0 裂变率 - 1”。
- 输出
t0_fission_rate_realtime_20260721_apptype4_version1578.csv。
run_t0_fission_rate_offline_5day.py
执行五天离线 T0 SQL，按日期分别生成16桶明细及实验/对照每桶均值。
- 每天 18 行，五天共 90 行，日期倒序。
- 版本号列保留并统一写“全部”。
- 公式、中文列名和列顺序与实时 T0 CSV一致。
- 输出
t0_fission_rate_offline_20260716_20260720_apptype4_all_versions.csv。
run_first_layer_source_metrics.py
统一生成实时和离线首层来源指标 CSV。
python3 run_first_layer_source_metrics.py realtime：执行当天版本1578实时 SQL。python3 run_first_layer_source_metrics.py offline：执行五天全版本离线 SQL。- 每天输出16桶明细、2行分组聚合、2行每桶均值，共20行。
- 每个头部/推荐/全部维度包含曝光、播放、分享 PV/UV，裂变层 UV，三项 PV/首层UV、STR、T0裂变率、裂变层UV/曝光PV及相对对照组变化率。
- 实时、离线输出均为65列且列名、列顺序一致。
输出文件
bucket_source_pv_uv_return_realtime_20260721_apptype4_version1578.csv
2026-07-21、版本 1578 的实时完整报表，共 20 行。
bucket_source_full_report_offline_20260716_20260720_apptype4_all_versions.csv
2026-07-16 至 2026-07-20、全部版本的离线完整报表，共 100 行。列名、列顺序和指标维度与实时 CSV 一致。
t0_fission_rate_realtime_20260721_apptype4_version1578.csv
2026-07-21、版本 1578 的实时 T0 裂变率报表，共 18 行，包含时间、版本、行类型、分组、尾号、首层 UV、裂变层 UV、T0 裂变率及实验相对对照组变化率。
t0_fission_rate_offline_20260716_20260720_apptype4_all_versions.csv
2026-07-16 至 2026-07-20、全部版本的离线 T0 裂变率报表，共 90 行。每天包含 16 桶明细和 2 行分组每桶均值。
first_layer_source_metrics_realtime_20260721_apptype4_version1578.csv
2026-07-21、版本1578的首层用户完整来源报表，共20行、65列。
first_layer_source_metrics_offline_20260716_20260720_apptype4_all_versions.csv
2026-07-16 至 2026-07-20、全部版本的首层用户完整来源报表，共100行、65列，日期倒序。
修改日期或版本时
- 同步修改 SQL 文件中的日期分区条件和输出日期。
- 实时报表需要同步修改
versioncode和输出版本号；离线全版本报表不要增加版本过滤。 - 同步修改 Python 脚本顶部的 SQL、CSV 文件名常量。
- 执行后校验每个日期是否有 16 个尾号、20 行完整结果，事实值是否为整数，离线与实时列名及顺序是否一致。