# 实验分桶报表说明 本目录保存 `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 和去重 `mid` UV。 - 播放：`businesstype='videoPlay'`，统计 PV 和去重 `mid` UV。 - 分享：`businesstype='videoShareFriend'`，统计 PV 和去重 `mid` UV。 - 回流：分享记录与同日 `topic='click'` 记录按 `shareid` 关联，统计去重点击用户 `machinecode` UV。 - 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列，日期倒序。 ## 修改日期或版本时 1. 同步修改 SQL 文件中的日期分区条件和输出日期。 2. 实时报表需要同步修改 `versioncode` 和输出版本号；离线全版本报表不要增加版本过滤。 3. 同步修改 Python 脚本顶部的 SQL、CSV 文件名常量。 4. 执行后校验每个日期是否有 16 个尾号、20 行完整结果，事实值是否为整数，离线与实时列名及顺序是否一致。