monthly-report

monthly-report — reporting skill (Phase 9 Wave 1)

LOCAL aggregator: reads master.xlsx logical sheets + last-28-day events.jsonl context (READ-ONLY) → emits a JSON report conforming to schemas/monthly-report.schema.json v1.0 (10 required sections, framing_policy default positive_client, output_formats subset of [html, pdf, notion], data_sources provenance).

This skill follows the convention authority established by skills/planning/master-task-sync/SKILL.md (Phase 8 W-D1 — local aggregation pattern). Per the Phase 9 W-E1 worker brief (REVIZE 1+2+3):

• outputs[] has exactly 3 entries — master.xlsx#none (READ-ONLY confirm), outputs/reports/{date}-monthly.md, and inbox/local/{date}-monthly-{slug}.json. No events.jsonl write.
• events.jsonl is READ-ONLY for last-28-day work-event context; whether reporting runs are themselves audit-worthy events is Q-RP-01, deferred to Phase 14 governance refinement (manager OQ append at closeout).
• master_task is READ-ONLY; this skill never writes back. The next_month_plan section reads top-10 TODO rows.
• mcp_tools.required = [] and mcp_tools.optional = []. The mcp__gsc__search_analytics tool MAY be invoked opportunistically by a future skill body for cross-check, but is not declared here because the LOCAL pipeline must run end-to-end without it (HIGH autonomy + cron-ready).
• budget.uses_paid_mcp = false and estimated_credits = 0 (no DFS, no paid MCP, no Scrapling cloud).

Inputs (frontmatter contract)

Name	Type	Default	Notes
project_slug	string	—	Required. Resolves projects/{slug}/master.xlsx.
period_end	string	last business day of current month	ISO YYYY-MM-DD. period_start = period_end - 27 days.

workspace_root is resolved via PSEO_WORKSPACE_ROOT env var or an explicit override (mirrors workflow_runner / events_writer / sibling reporting skills).

Outputs (artifacts produced)

• master.xlsx#none — declarative READ-ONLY confirmation. The transform contains zero transaction.append / transaction.update calls against master_task (or any other sheet); see acceptance gate #8.
• projects/{slug}/outputs/reports/{period_end}-monthly.md — rendered markdown via string.Template ($variable substitution; mirrors scripts/reporting/render_template.py contract).
• projects/{slug}/inbox/local/{period_end}-monthly-{project_slug}.json — drift-recovery snapshot of the full schema-shaped report dict.

Sheets consumed (READ-ONLY)

Sheet	Producer skill	Section feed
master_task	master-task-sync	next_month_plan (top-10 TODO)
completed_work	done-protocol	tech_seo_done / content_revised / new_content
content_improve	content-decay	pages_up auxiliary
new_content_plan	new-content-plan	new_content auxiliary
gsc_performance	gsc-pull	gsc_positive_trends + pages_up
opportunity	quick-wins	keywords_up
content_decay	content-decay	content_revised auxiliary
tech_seo	tech-audit	tech_seo_done auxiliary
schema	schema-audit	content_revised auxiliary

events.jsonl (last 28 days) is read for contextual work-event enrichment; never written.

10 Required Sections (schemas/monthly-report.schema.json line 31-42)

1. exec_summary — auto-generated from sections 2-7 headlines
2. gsc_positive_trends — current vs prior 28-day deltas
3. keywords_up — top-20 queries with positive position delta
4. pages_up — top-10 pages with positive clicks_delta
5. tech_seo_done — completed_work filtered by category prefix "tech"
6. content_revised — completed_work filtered by category prefix "revis"
7. new_content — completed_work filtered by category prefix "new"
8. competitor_snapshot — Phase 10+ Scrapling S1/S3 (empty shape now)
9. backlink_delta — paid DFS MCP (zero shape; this skill = LOCAL only)
10. next_month_plan — top-10 TODO rows from master_task by priority

framing_policy defaults to positive_client (§22.3) and affects TONE only, never facts (FIX-K K1). The internal framing may foreground declines; both framings carry the SAME numbers in the additive sections below.

Additive optional sections (11–12)

Beyond the 10 required sections the report carries two additive, OPTIONAL sections — NOT in schemas/monthly-report.schema.json sections.required, so older reports stay schema-valid. Both are framing-INVARIANT (byte-identical under positive_client and internal; their builders take no framing_policy):

11. decliners (FIX-K K1) — declining pages/content + the net signed clicks delta. The facts survive framing; only the surrounding tone may differ.
12. measurement_context (GAP-M-W2) — a measurement-honesty annotation, per rules/measurement-discipline.md (core-update overlap + intervention cohorts):
    • core_updates_overlap + measurement_quality ∈ {clean, update_overlap, post_update_settling, insufficient_history}: does the window overlap a Google Search Ranking update (incl. the 7-day settling buffer)? Deltas in an overlapping window MUST NOT be attributed to engine work without this annotation.
    • intervention_outcomes: treated-vs-control quick-win verdicts (difference in pp; n<30 — directional only; no p-values), never a raw treated delta.

Calendar source (overlap read)

The overlap read uses scripts/reporting/update_calendar.py. By DEFAULT the bundled engine seed google-update-calendar.json is the source (no network, cron-ready) — --calendar-path overrides it. An OPTIONAL fresher workspace overlay (shared/cache/google-update-calendar.json, maintained out-of-band by scripts/maintenance/refresh_update_calendar.py) is supplied via --overlay-calendar-path (overlay wins by id). A missing calendar ⇒ measurement_quality = insufficient_history (overlap undeterminable — NEVER fabricated as clean). This keeps the skill LOCAL / no-MCP.

Intervention outcomes wiring

Before rendering, locate quick-wins cohort snapshots (_state/metrics/quickwin-cohorts/{date}-cohort.json) at least 21 days old, pair each with the newest GSC inbox payload (inbox/gsc/{date}-search_analytics-{slug}.json), compute treated-vs-control outcomes, and pass the JSON array to monthly_report.py --cohort-results:

python3 scripts/reporting/intervention_outcome.py \
    --cohort-dir projects/{slug}/_state/metrics/quickwin-cohorts/ \
    --post projects/{slug}/inbox/gsc/{date}-search_analytics-{slug}.json \
    --today {date} --min-age-days 21 \
    --output projects/{slug}/_state/metrics/{date}-cohort-results.json

python3 scripts/reporting/monthly_report.py \
    --project-slug {slug} --workspace-root "$PSEO_WORKSPACE_ROOT" \
    --period-end {date} \
    --cohort-results projects/{slug}/_state/metrics/{date}-cohort-results.json \
    --output-dir projects/{slug}/outputs/reports/

Absent cohort files ⇒ intervention_outcomes = [] (the section says "kohort verisi yok"); never invent an outcome. This wiring writes NOTHING to master.xlsx and emits NO events.jsonl row — the skill stays READ-ONLY.

DURUR conditions (8 + base)

Stop and flag the manager — do not patch, do not fall back.

1. FramingPolicyEnumViolation — value not in {positive_client, internal}.
2. OutputFormatEnumViolation — value not in {html, pdf, notion}.
3. DataSourceEnumViolation — value not in 7-value enum.
4. MissingSectionError — assembler dropped a required section.
5. ProjectIdShapeError — project_id fails ^[a-z][a-z0-9-]*$.
6. WorkbookMissingError — projects/{slug}/master.xlsx not found.
7. TemplateRenderError — string.Template.substitute raised KeyError (missing $key) or ValueError (bad syntax).
8. MonthlyReportError (base) — invalid period_end, openpyxl missing, etc. Always surfaced with a descriptive message.

Audit Event Emit — DEFERRED (Q-RP-01, Phase 14+)

This reporting skill is READ-ONLY: it does not write to append-only _state/events.jsonl. An audit-trail event_kind=audit row (per rules/events-writer.md Section 4c) was proposed under Q-RP-01 but is deferred to Phase 14+ governance refinement and is NOT wired — the frontmatter (safe_auto_execute: true, outputs[] excludes events.jsonl), the /pseo-monthly command, and events-writer.md all already encode this deferral.

Do NOT add a runnable events_writer.append_audit(...) block here: on an autonomous run it would mutate append-only state, contradicting the READ-ONLY contract this skill, its frontmatter, and its paired test all assert. When Q-RP-01 actually resolves, wire the emit through the sanctioned writer under operator review — never as an inline skill block.

Cross-references

• Schemas: schemas/monthly-report.schema.json (10 required sections + framing_policy enum + output_formats enum + data_sources enum), schemas/master-excel.schema.json (sheet shapes for the 9 consumed sheets), schemas/skill-frontmatter.schema.json (this frontmatter), schemas/events.schema.json (events.jsonl shape; READ-ONLY contract).
• Rules: rules/measurement-discipline.md R-137 (core-update overlap annotation — measurement_context.core_updates_overlap), R-138 (intervention cohort tagging — measurement_context.intervention_outcomes, treated-vs-control).
• Cross-modules (IMPORT-only): scripts/reporting/render_template.py (Phase 1 mirası, string.Template substitution), scripts/reporting/update_calendar.py (R-137 overlap read; engine seed ∪ overlay), scripts/reporting/intervention_outcome.py (R-138 treated-vs-control outcome; run before this skill, fed via --cohort-results).
• Transform: scripts/reporting/monthly_report.py.
• Template: templates/reports/monthly-report.template.md.
• Tests: tests/skills/test_monthly_report.py (8-10 tests; schema validate output JSON + sentinel + edge case + forbidden-token grep).
• Pattern reference: scripts/planning/master_task_sync.py (Phase 8 W-D1 local aggregation pure-transform pattern; reused verbatim).

Discipline checklist

• TODO/fallback YASAK — every DURUR raises, none silently downgrade.
• Schema-first — frontmatter validates against schemas/skill-frontmatter.schema.json Draft 7. Output JSON validates against schemas/monthly-report.schema.json Draft 7.
• Plugin-agnostik — no slug literals; project_slug flows through every path; transform has 0 hardcoded slug words.
• ADR-013: Use when / Also use when / Do not use when are STRING content inside description, not separate fields.
• Cross-module IMPORT discipline — render_template.py is referenced by contract (string.Template), not modified.
• D-003 helper IMPORT NOT applicable — local aggregation, no DFS payload is consumed (intentionally absent — documented above).
• F1: artifact paths use master.xlsx (lowercase, schema-shaped); this skill writes ZERO sheets — master.xlsx#none is declarative.
• F5: any string-typed CLI outputs are stringified explicitly.
• Append-only state — events.jsonl is READ-ONLY here. Q-RP-01 defers the "are reports audit-worthy" question to Phase 14.
• Idempotency — same inputs (modulo generated_at) → byte-identical report dict (asserted by test_monthly_report.py smoke test).
• LOCAL aggregation budget envelope: uses_paid_mcp=false, estimated_credits=0, safe_auto_execute=true (cron-ready).