ai_member_xiaoxi/memory/2026-05-06.md

2026-05-06 工作日志

任务1：学而思/讯飞/希沃/京东方月度渠道订单报表

• [李承龙需求] 创建按月汇总四个渠道订单的 Python 脚本
• 脚本路径：/root/.openclaw/workspace/scripts/monthly_channel_order_report.py
• 渠道映射： 仅端内 key_from='app-active-h5-0-0'，sale_channel: 21=学而思, 22=讯飞, 26=希沃, 27=京东方
• 输出格式： 四个渠道各一个 Sheet，每月按天展示（学而思/希沃/京东方只显示有订单日期，讯飞显示全月每天）
• 指标差异：
  • 学而思/希沃/京东方：订单数、GMV、CPS分成（学而思×0.45，希沃×0.4，京东方×0.3）、订单号（分号拼接）
  • 讯飞：订单数、GMV、新增注册（download_channel LIKE '%讯飞%'）、CPA分成（新增注册×7）、订单号
• 退费剔除： NOT EXISTS (bi_refund_order.status=3) AND order_status != 4
• 测试运行： 2026-04 月份，新增注册 537（讯飞），订单合计 15 单，GMV 14585 元
• 订单号格式问题： Excel 科学计数法 → 设置单元格为文本格式解决
• psycopg2 转义： LIKE 中 % 需写成 %%（psycopg2 的 %s 占位符冲突）

任务2：销售线索用户分析报表

• [李承龙需求] 输入包含462个用户ID的Excel，输出每个用户+角色的完整画像报表
• 脚本路径：/root/.openclaw/workspace/scripts/lead_user_analysis.py
• 重要口径修正（李承龙确认）：
  • 体验课定义： 固定为 L1 U00(L01-L05) + L2 U00(L01-L05) 共10节课，不通过 expire_time 区分
  • L1 chapter_id: 343, 344, 345, 346, 348
  • L2 chapter_id: 55, 56, 57, 58, 59
  • 完成时间： 取 bi_user_chapter_play_record 中 play_status=1 记录的最早 updated_at（非 created_at）
• 报表列： 用户ID、角色ID、用户注册时间、角色创建时间、L1序章5节完成时间、L2序章5节完成时间、购买时间、购买课包名称、支付金额、购买渠道key_from、是否退款、退款金额
• 分表规则： bi_user_chapter_play_record_{0..7}，分表号 = user_id % 8
• 重要关联： bi_user_chapter_play_record.user_id = bi_vala_app_character.id（角色ID），非 account_id
• 退款条件： bi_refund_order.status=3 AND bi_vala_order.order_status=4
• 结果： 533行（462个用户 × 533个有效角色），235个完成L1序章，139个完成L2序章，53行有购买，10行有退款
• 踩坑记录：
  • 子会话按旧口径执行后被 kill，最终主会话亲自重写脚本
  • ALL_CHAPTERS 的值是 "L1_U00_L01"，COL_ORDER 是 "L1_U00_L01_完成时间"，不匹配导致全部为空，修复后正确
  • datetime 写入 Excel 需去掉时区信息 dt.tz_localize(None)
  • play_record 分表查询需遍历 0-7 共 8 个分表