agent-academy-report

# Agent Academy Report Generator Generate a comprehensive feedback analysis report for Agent Academy. The report combines data from Excel survey exports and GitHub issues, analyzes sentiment, generates charts, and produces a single styled PDF. ## Folder Structure The workspace folder should be organized as follows: ``` <workspace>/report/ ├── data/ ← Excel files (.xlsx) with survey responses ├── badges/ ← Badge PNG images (downloaded from GitHub) ├── charts/ ← Generated chart PNG images (output) ├── markdown/ ← Generated markdown files (output) └── pdf/ ← Generated PDF files (output) ``` If the user provides a workspace path, use it. Otherwise, ask for it. ## Overview The report generation has 6 phases: 1. **Extract feedback** from Excel files and GitHub issues 2. **Analyze sentiment** using keyword-based scoring 3. **Generate charts** with matplotlib 4. **Build markdown** files (management summary + detailed analysis) 5. **Export a single PDF** with cover page, management summary, and full analysis 6. **Verify PDF formatting** — visually inspect and fix any layout issues Run the bundled Python scripts in order. Each script is self-contained and reads/writes to the folder structure above. --- ## Phase 1: Extract Feedback Run `scripts/extract_feedback.py`: ```bash python3 <skill-path>/scripts/extract_feedback.py "<workspace-path>/report" ``` This script: - Reads all `.xlsx` files from `<workspace>/report/data/` - Maps filenames to human-readable course names - Extracts feedback text, completion dates, and respondent names/emails - **Name normalization**: If only an email address is available, it converts it to a display name — e.g. `john.doe@example.com` → `"John Doe"` (split on dots/underscores, title-cased). If neither a name nor email is found, the name defaults to `"Anonymous"`. - Skips trivial feedback entries (e.g. "no", "none", "n/a", "ok", single punctuation) - For files without a feedback column (Operative, Recruit), it extracts completion dates only — their feedback comes from GitHub issues - Outputs `<workspace>/report/data/_extracted.json` ### GitHub Issues (Operative & Recruit) After running the extract script, fetch feedback from GitHub issues. **Do not use `mcp_github_mcp_search_issues`** — the search API caps results at 1,000, which is insufficient for Recruit (1,390+ issues). Instead, use `mcp_github_mcp_list_issues` which supports full GraphQL cursor pagination: **Recruit issues:** ``` owner: "microsoft", repo: "agent-academy", labels: ["recruit-completed"], state: "all", perPage: 100 ``` **Operative issues:** ``` owner: "microsoft", repo: "agent-academy", labels: ["operative-completed"], state: "all", perPage: 100 ``` Paginate through **all** pages using the `endCursor` from each response until `hasNextPage` is false. The API returns max 100 per page — Recruit requires ~14 pages, Operative ~4 pages. Do not stop early; collect every issue. For each issue, parse the body for structured sections: - `### Key Learnings and Takeaways` - `### Challenges Faced` - `### Final Thoughts` Combine these sections as the feedback text. The `name` field should be a plain string (the GitHub username) — if the API returns a `user` object like `{"login": "username"}`, extract just the `login` value. Save the combined data (Excel + GitHub) to `<workspace>/report/data/_all_feedback.json`. Each record should have: `{course, date, month, sentiment, text, name, type, source, is_closed}`. - `source`: `"excel"` (set automatically by extract script), `"github_completed"` (badge submission issues), or `"github_issue"` (non-badge issues) - `is_closed`: `true`/`false` — for GitHub records only; indicates whether the issue is closed (closed = badge awarded for completed issues) ### Non-Badge GitHub Issues After collecting badge submission issues, also fetch **all other open issues** that are NOT badge submissions. These are bug reports, questions, and general feedback that should be classified into the correct course. Fetch issues without the completed labels: ``` owner: "microsoft", repo: "agent-academy", state: "all", perPage: 100 ``` Filter out issues that already have `recruit-completed` or `operative-completed` labels. For each remaining issue, classify it to a course using this keyword map (match against issue title and labels, case-insensitive): | Keywords in title/labels | Course | | ---------------------------------- | -------------------------------------------- | | `recruit` | Recruit | | `operative` | Operative | | `commander` | Commander | | `yaml`, `yaml specialist` | Special Ops: YAML Specialist | | `copilot studio` AND `mcp` | Special Ops: Microsoft Copilot Studio MCP | | `learn docs`, `learn mcp` | Special Ops: Microsoft Learn Docs MCP Server | | `cli`, `power platform cli`, `pac` | Special Ops: Power Platform CLI MCP Server | | `badge check` | Cowork Collective: Badge Check | | `compliance` | Cowork Collective: Compliance Packet | | `out of office`, `vacay` | Cowork Collective: Out of Office | If no match is found, skip the issue. Set `source: "github_issue"` and `type: "feedback"` for these records. Use the issue title + body as the feedback text. ### How Non-Badge Issues Are Used Non-badge GitHub issues (`source: "github_issue"`) are **included** in: - Sentiment analysis (scored and themed) - Course grades (affect positive/negative percentages) - Top themes (feed into per-course theme detection) - Representative quotes (can appear as selected quotes) - Next steps (negative themes from these issues drive recommendations) Non-badge GitHub issues are **excluded** from: - Submission/completion charts (completions over time, cumulative, monthly, per-course) - Monthly trend tables (submission counts) - Total completions count in the management summary This is because they are feedback and bug reports, not actual course completions. ### Recruit & Operative Comparison Data The combined `_all_feedback.json` will contain records with different `source` values. The build script uses these to generate a **Submission Pipeline** comparison table for Recruit and Operative: | Metric | How to count | | ------------------------ | ------------------------------------------------------------------ | | GitHub Badge Submissions | Records where `source = "github_completed"` | | Excel Form Submissions | Records where `source = "excel"` | | Badges Awarded | Records where `source = "github_completed"` AND `is_closed = true` | | Other GitHub Issues | Records where `source = "github_issue"` | --- ## Phase 2: Analyze Sentiment Run `scripts/analyze_sentiment.py`: ```bash python3 <skill-path>/scripts/analyze_sentiment.py "<workspace-path>/report" ``` This script: - Reads `<workspace>/report/data/_all_feedback.json` - Scores each feedback item using regex-based sentiment analysis (word-boundary `\b` matching, case-insensitive) - Detects common themes and adds a `themes` array to each record (list of matched theme names, e.g. `["Hands-on & Practical Learning", "MCP & Agent Development Skills"]`) - Computes per-course grades using weighted average (positive=10, neutral=6, negative=2) - Outputs `<workspace>/report/data/_analyzed.json` with sentiment labels, themes, and course stats ### Output Structure The `_analyzed.json` file contains: ```json { "records": [ ... ], // All records with added "sentiment" and "themes" fields "summary": { "total_records", "total_feedback", "positive", "neutral", "negative", "overall_grade" }, "course_stats": { // Per-course: total, positive, neutral, negative, grade, themes (with counts) "Recruit": { "total": 123, "positive": 100, ..., "themes": { "Hands-on & Practical Learning": 45, ... } } } } ``` ### Sentiment Keywords All patterns use `\b` word boundaries for precise matching (e.g. `\bgreat\b` matches "great" but not "greater"). **Positive** patterns include: great, excellent, amazing, awesome, fantastic, wonderful, love/loved, enjoy/enjoyed, helpful, useful, informative, well done, good job, thank/thanks, appreciate, impressive, learned, learning, eye-opening, recommend, looking forward, fun, excited, brilliant, clear, intuitive, practical, hands-on, well-structured, well-organized, good, nice, cool, neat, super, stellar, outstanding, perfect, phenomenal, beginner-friendly, easy to follow, straightforward, engaging, interesting, valuable, insightful, comprehensive, thorough, solid, smooth. **Positive emojis** (also scored as positive): 👍 🎉 🙌 ❤️ 🔥 💯 ⭐ ✨ 😍 😊 🤩 💪 **Negative** patterns include: error(s), issue(s), problem(s), bug(s)/buggy, broke/broken/crash/crashed, fail/failed/failure, didn't work, not work, couldn't, stuck, struggling/struggle, missing, trouble, difficult/difficulties, cumbersome, challenge(s), troubleshoot, confusing/confused, frustrating/frustrated, slow, complex, unclear, improve/improvement, wish, lack/lacking, disappoint/disappointed. **Scoring rule:** If negative matches > positive matches → negative. Else if positive > 0 → positive. Else if negative > 0 → negative. Else → neutral. ### Theme Detection The analyzer detects 10 predefined themes by matching regex keywords against feedback text: | Theme | Keywords (regex) | | ---------------------------------- | --------------------------------------------------------------------------------- | | Hands-on & Practical Learning | hands-on, practical, interactive, step-by-step, real-world, exercise | | Clear Instructions & Documentation | clear, well-document, well-explain, easy to follow, straightforward, instructions | | MCP & Agent Development Skills | mcp, copilot studio, agent, connector, power automate, topic, action | | Setup & Configuration Challenges | setup, install, config, environment, prerequisite, authentication, credentials | | VS Code Integration | vs code, visual studio code, extension, editor, debug | | Valuable Learning Experience | learn, knowledge, understand, skill, experience, educational, insight | | Course Quality & Engagement | well-design, engag, interest, quality, structure, organized, professional | | Errors & Technical Issues | error, bug, crash, broke, fail, fix, workaround | | Improvement Suggestions | improv, suggest, would be nice, could be, hope, wish, add more, future | | Real-World Applicability | real-world, production, business, enterprise, workplace, appl | A feedback item can match multiple themes. Themes are stored as `r['themes'] = [...]` on each record. --- ## Phase 3: Generate Charts Run `scripts/generate_charts.py`: ```bash python3 <skill-path>/scripts/generate_charts.py "<workspace-path>/report" ``` Requires `matplotlib` and `numpy`. Install if needed: `pip3 install matplotlib numpy`. This generates overview charts plus individual per-course charts in `<workspace>/report/charts/` at 180 DPI. **Note:** Submission/completion charts only count actual submissions (`source: "excel"` and `source: "github_completed"`). Non-badge GitHub issues (`source: "github_issue"`) are excluded from these charts — they are only used for sentiment analysis. ### Course Grouping & Colors Charts group courses into 4 categories, each with a consistent color: | Group | Color | Hex | Courses | | ----------------- | ------ | ------- | ------------------------------ | | Recruit | Blue | #3b82f6 | Recruit | | Operative | Teal | #14b8a6 | Operative | | Special Ops | Orange | #f59e0b | All Special Ops missions | | Cowork Collective | Purple | #a78bfa | All Cowork Collective missions | **Display order** (used in all charts): Recruit, Operative, Special Ops: YAML Specialist, Special Ops: Microsoft Copilot Studio MCP, Special Ops: Microsoft Learn Docs MCP Server, Special Ops: Power Platform CLI MCP Server, Cowork Collective: Badge Check, Cowork Collective: Compliance Packet, Cowork Collective: Out of Office. **Short names** used on chart axes (where full names are too long): | Full Name | Short Name | | -------------------------------------------- | ------------------ | | Special Ops: YAML Specialist | YAML Specialist | | Special Ops: Microsoft Copilot Studio MCP | Copilot Studio MCP | | Special Ops: Microsoft Learn Docs MCP Server | Learn Docs MCP | | Special Ops: Power Platform CLI MCP Server | PP CLI MCP | | Cowork Collective: Badge Check | Badge Check | | Cowork Collective: Compliance Packet | Compliance Packet | | Cowork Collective: Out of Office | Out of Office | ### Overview Charts