PUSH Data Weekly Report Analyzer

Overview

Automate the full pipeline of PUSH delivery data analysis: fetch the latest Excel from email (or accept a path), filter by operational module, compute 11 analysis dimensions, and generate a styled HTML report with optimization recommendations.

Prerequisites

Before first use, configure the following (all must be set via environment variables or local config — never commit secrets):

A template config file is included at scripts/email_config.example.json (no real secrets — see the _comment field for guidance).

Trigger Conditions

Activate this skill when the user says any of:
- "push分析" / "周报分析" / "投递数据分析"
- "PUSH文案效果" / "点击率分析"
- "功能运营周报" / "活动运营分析"
- "push分析 功能运营" / "push分析 活动运营"

Parameters

If the user specifies a module in the command (e.g., "push分析 活动运营"), use that value. Otherwise default to "功能运营".

Workflow

Step 1: Acquire PUSH Data Excel

Fetch the latest PUSH delivery data Excel file from email via IMAP.

Email search criteria (configurable via environment variables):
- EMAIL_USERNAME — 登录账号 (e.g. xxx@139.com)。必填，建议存到本机 .env 或系统 keychain，绝不要写进代码。
- EMAIL_AUTH_CODE — 邮箱授权码 (139 邮箱在设置里生成)。必填，同上。
- EMAIL_IMAP_HOST (可选, 默认 imap.139.com)
- EMAIL_IMAP_PORT (可选, 默认 993)
- EMAIL_SENDER_KEYWORDS (可选, 逗号分隔, 默认空 = 任意发送者)
- EMAIL_SUBJECT_KEYWORDS (可选, 逗号分隔, 默认 push投递数据,投递数据,投放效果分析,push)
- EMAIL_INSECURE_TLS (可选, 1/true/yes 时禁用证书验证；仅诊断用，默认严格校验)
- Take the most recent matching email

Attachment selection:
1. If multiple attachments exist, prefer the one whose filename contains "投放效果分析"
2. Otherwise, select the newest .xlsx file
3. Save to the workspace directory

Prerequisite: Email IMAP access must be available. If email access is unavailable, ask the user to provide the Excel file path directly.

Fallback: If no new email found today, use the most recent available email (last Friday or earlier).

Important: The skill does not ship with hardcoded credentials. Before running, set the env vars above. See scripts/email_config.example.json for a template.

Step 2: Install Dependencies

Before running the analysis script, ensure Python dependencies are available:

# Any Python >= 3.10 works. Create an isolated venv next to the skill:
python -m venv .venv
# Linux / macOS
. .venv/bin/activate && pip install pandas openpyxl jieba
# Windows (PowerShell / cmd)
# .venv\Scripts\activate && pip install pandas openpyxl jieba

If dependencies are already installed, skip this step.

Step 3: Run Analysis Script

Execute the core analysis script with the Excel path and module parameter:

# Run from the skill directory (<skill_dir>/scripts/push_analysis.py):
python scripts/push_analysis.py \
  <excel_path> \
  --module <module> \
  --output <output_path>

Output: An HTML report file saved to the specified output path (or the workspace directory by default).

Step 4: Present Report

1. Use preview_url to display the generated HTML report to the user.
2. Summarize key findings: overall CR, top/bottom performing copy, strategy recommendations.
3. Offer to send the report via email if the user requests it.

Step 5: Handle Adjustments

If the user requests changes (e.g., different analysis dimensions, adjusted thresholds), modify the script parameters or re-run with a different module. The script handles all 11 analysis sections automatically.

Analysis Dimensions

The script performs 11 analysis sections (matching the user's specification):

1. Basic Metrics — total tasks, avg title/content length, simple & weighted CR
2. Top vs Bottom Comparison — top 8 / bottom 8 by CR, with title, content, sentence type
3. Core Difference Analysis — common features of high-CR copy, common problems of low-CR copy, with data-grounded gap summary (Top avg CR vs Bottom avg CR, ratio)
4. Title Structure — length buckets, opening words, sentence types vs CR
5. Content Patterns — content mode classification, ending symbols, number usage, emoji position
6. Word Frequency — top words by occurrence, efficient vs inefficient word classification
7. Task Typology (选型分析) — single vs periodic task distribution, plan category breakdown (categories = cleaned plan names, e.g., 阅读/漫剧/云朵中心/AI-场景-趣味), click-volume contribution per selection (点击量贡献占比), dynamic CR-impact insights (最优/最差选型CR差距倍数、点击贡献集中度、拖累项识别、周期vs单次对比), test-push separation (正式/测试推送分开统计), A/B test identification, push timing analysis
8. Module Diagnosis — sub-category breakdown with data-driven 3-layer diagnosis (数据事实 facts / 高低对比归因 attributions / 行动建议 actions), per-item diagnostic text referencing concrete CR comparisons, best/worst real copy comparison, per-scenario 改造模板 (每个子场景单独配「改造前/改造后/策略」三件套，改造后文案严格匹配子场景，例如 AI相机下细分为扫描文档/拍照修图/拍照解题/格式转换/拍照问AI/口算批改/拍照翻译/提取文字 等子场景，每个子场景给出 4 条差异化参考文案)
9. Strategy Matrix — 8 strategy tags (数字量化/行动召唤/个性化/好奇钩子/场景唤醒/功能预告/利益点/紧迫感) with relative effect
10. Execution Checklist — execution rules only. The old generic 「文案模板库」 block (categorized as 热点借势 / 干货指南 / 情感共鸣 / 资讯快报 / 趣味互动, etc.) was removed in v5.2 — it duplicated Section 8's plan-name-driven templates and used a non-themed generic pool that pulled copy like "手机空间不够？" into a 内容运营 阅读图书 card.
11. Function Plans — per-function CR, data volume, share of module, strategy level, dynamic data-driven diagnosis, multi-scenario templates, dynamically adjusted target CR range, best/worst real copy comparison, weekly schedule, execution checklist, data-backed global optimization expectation. The per-function 文案模板 (scenarios) uses the same plan-name theme priority chain as Section 8 (group's actual plan names vote → _PLAN_THEME_TEMPLATES → classic SCENARIO_LIBRARIES → _SCENARIO_TEMPLATES fine sub-scenario pool → module _default → generic fallback), so each plan-theme group gets themed, differentiated copy. v5.3 — Scene titles are predefined anti-substring hooks: the 53 entries in _PLAN_THEME_TEMPLATES (and the 2 same-sub-label 合同 entries in SCENARIO_LIBRARIES[扫描文档] / SCENARIO_LIBRARIES[拍照翻译]) carry an explicit after_title that does NOT appear as a substring of their own after_content. Examples: 片单→「高分速看」/ 大片→「大片上新」/ 剧荒→「追剧清单」/ 漫剧片场→「新作速刷」/ 微信→「立即备份」/ 一人一面-签到→「待领取」/ 流量→「速到账」. The fallback _derive_title_from_copy(copy, scene_label) likewise enforces substring-exclusion (问句 → 非首段短句 → 尾部截取 → 内容首段语义 → 14-前缀池按 scene_label 哈希分桶). The legacy placeholder brand string no longer reaches the rendered output.

Key principle (v2+): 诊断必须基于实际数据 — all diagnosis references concrete numbers (task count, share %, CR ratio vs overall, intra-group best/worst gap, feature differences between top-3 and bottom-3 copy). Hard-coded generic phrases like "15条推送平均CR极低" or "文案表现低于均值，建议优化" are removed. If a small sample (<4 items) makes conclusions unreliable, the report explicitly says so.

Module diagnosis sub-scenarios: Improvement templates are matched to the fine-grained sub-scenario (e.g., AI相机 → 扫描文档/拍照修图/拍照解题/格式转换/拍照问AI/口算批改/拍照翻译/提取文字; 自动备份 → 换机/内存/回忆/微信/办公). Each module has its own SCENARIO_TEMPLATES library. Within a sub-category (e.g., AI相机 has 8 items), the report surfaces up to 4 distinct sub-scenario representatives — the worst-CR item from each detected sub-scenario group — and shows its dedicated before/after rewrite + 策略. This avoids the prior failure mode where AI相机 items with sub-scenarios 拍照问AI/口算批改 would fall through to a generic 自动备份-style rewrite.

Plan-name theme priority (v5+): The 计划名称 (plan name) is the ops team's selection identifier and takes highest priority in both diagnosis grouping and improvement templates. _extract_plan_theme() strips experiment suffixes (-对照组/-试验组N/-各组总计), crowd annotations (【已开启∩...】), strategy prefixes (【周期】【次日留存-N】【大数据标签】) and test markers (测试：/正式：/【测试】) to derive the plan theme. _PLAN_THEME_TEMPLATES then maps themes to rewrites that preserve the original content theme (e.g., plan 阅读 → book-recommendation copy, plan 漫剧 → anime-update copy, plan 云朵中心 → cloud-center task copy) instead of replacing it with generic cloud-drive functional copy. Sub-category grouping in module diagnosis uses theme-pool labels first (阅读图书/影视推荐/漫剧番剧/云朵中心活动/一人一面...), falling back to module keywords_map, then 其他. Each module also has a content-aware _default fallback pool so unmatched plans still get module-appropriate copy (内容运营 → 内容消费向, 活动运营 → 活动向, 用户运营 → 留存促活向). Push text with template variables (${title:...}/${content:...}) is cleaned at data load.

Module Diversity (模块差异化)

Each module uses a dedicated module profile (MODULE_PROFILES): different sub-category keywords, analysis focus, and attention notes:

• 功能运营 — 功能认知·场景教育·使用率转化 (brand recognition, functional jargon vs user language, scene triggers)
• 活动运营 — 活动吸引力·参与门槛·紧迫感 (benefit clarity, urgency, participation friction)
• 内容运营 — 内容价值·话题热度·情绪共鸣 (headline appeal, value density, emotion triggers)
• 用户运营 — 留存促活·生命周期管理·个性化触达 (retention timing, personalization, sign-in incentives, test-vs-production push separation)

Function/plan cards render differently based on data: functions with data show metrics strip + 3-layer diagnosis + best/worst real copy; functions without data show cold-start templates only.

Script Details

scripts/push_analysis.py

The core analysis engine. Key features:
- Flexible column mapping: Auto-matches Excel headers by alias (see references/analysis_methodology.md for full alias list)
- Graceful degradation: Missing columns or jieba library do not crash the script
- Chinese text processing: Word segmentation via jieba (fallback to n-gram), sentence type classification, emoji detection
- Chinese stock convention: Red = high CR (good), Green = low CR (bad)
- Dynamic HTML generation: Full styled report with cards, tables, color labels, insight blocks

CLI Usage

python push_analysis.py <excel_path> [--module MODULE] [--output OUTPUT]

references/analysis_methodology.md

Reference document containing:
- Field mapping alias tables
- Strategy tag definitions and trigger keywords
- Sentence type classification rules
- Content pattern classification rules
- Emoji position detection rules
- Stopwords list
- Title length buckets
- Sub-category detection keywords
- Chinese color convention explanation
- Module adaptation guide

Load this reference when adjusting analysis rules or extending the skill to new modules.

assets/report_style_reference.html

Static HTML file documenting the report's CSS design system (colors, components, class names). Use as reference when modifying report styling.

Module Adaptation

The module parameter controls the analysis target. All analysis logic (Sections 1-10) runs for all modules, but each module uses its module profile (MODULE_PROFILES in push_analysis.py):

• 功能运营: Full Section 11 output including AI相机/自动备份/录音笔记/MClaw/证件照/拍照修图/拍照问AI/扫描文档/格式转换 etc. with detailed templates
• 活动运营 / 内容运营 / others: Section 8 auto-adapts with module-specific sub-category keywords (福利抽奖/限时抢购/任务互动 for 活动运营; 热点借势/干货指南/情感共鸣 for 内容运营); Section 11 auto-generates from detected sub-categories

To add support for a new module, edit MODULE_PROFILES (keywords_map / focus_points / attention_notes / scene_themes) and optionally add curated scenario templates to func_templates in _generate_function_templates().

Automation Setup

Create a recurring automation for weekly PUSH reports:

Name: 每周功能运营PUSH周报
Schedule: Every Monday at 09:00
Prompt: push分析 功能运营

Use the automation_update tool with:
- scheduleType: "recurring"
- rrule: "FREQ=WEEKLY;BYDAY=MO;BYHOUR=9;BYMINUTE=0"
- prompt: "push分析 功能运营"

Error Handling

• No email found: Inform user, suggest providing Excel path directly
• Excel column mismatch: Script auto-adapts via alias matching; if critical columns missing, report shows 0/empty for affected sections
• Empty data after filter: Script generates a minimal report with "no data" notice
• jieba not installed: Falls back to n-gram word extraction (lower quality but functional)
• pandas/openpyxl not installed: Script prints install instructions and exits

Output Format

The generated HTML report includes:
- Blue-to-purple gradient header with module name and timestamp
- Card-based layout with 11 analysis sections
- Color-coded CR values (red=high, green=low, yellow=average)
- Comparison tables for top/bottom copy
- Insight callout boxes with recommendations
- Responsive design (mobile-friendly)
- Footer with generation time and data range