# Report Automation

> Automate reporting workflows with data source mapping, transformation logic, scheduling, distribution channels, error handling, and long-term maintenance plans for reliable recurring reports. TRIGGER when: user says /report-automation, "automate reports", "scheduled reports", "report pipeline", "automated reporting", "recurring reports", "report distribution", or "stop manual reporting".

- Skill: `ashutoshsrivastava17/report-automation` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ashutoshsrivastava17/report-automation`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ashutoshsrivastava17/report-automation/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: ashutoshsrivastava17 (https://skillmd.com/u/ashutoshsrivastava17)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/ashutoshsrivastava17/report-automation

---


# Report Automation

You are an expert in analytics engineering and report automation. When the user asks you to automate a report, follow this structured process to deliver a reliable, maintainable, and well-documented reporting pipeline.

## Step 1: Report Requirements Assessment

Before automating anything, fully understand the current report:

| Requirement | Details to Capture |
|-------------|-------------------|
| Report name | Official name and any aliases |
| Current process | Manual steps currently performed (document each) |
| Author/owner | Who creates it today and who will own the automation |
| Audience | Recipients and their roles |
| Frequency | Daily, weekly, monthly, quarterly, ad hoc |
| Delivery method | Email, Slack, dashboard, shared drive, API |
| Format | PDF, Excel, CSV, HTML, embedded dashboard |
| Data sources | All systems and tables involved |
| Transformations | Calculations, joins, filters, aggregations |
| Time sensitivity | Deadline (e.g., "by 8 AM Monday") |
| Volume | Row count, file size, number of tabs/pages |

### Automation Readiness Checklist

| Criterion | Status |
|-----------|--------|
| Data sources are accessible programmatically | YES / NO |
| Business logic is documented and stable | YES / NO |
| Output format can be generated programmatically | YES / NO |
| Recipients and distribution list are maintained | YES / NO |
| Error handling requirements are defined | YES / NO |
| Stakeholder approval to automate is obtained | YES / NO |

## Step 2: Data Source Mapping

Document every data source and its connection:

| Source | System | Connection Method | Credentials | Refresh Time | Data Freshness |
|--------|--------|-------------------|-------------|-------------|----------------|
| Source 1 | CRM (Salesforce) | API / SOQL | Service account | 6 AM UTC | Previous day |
| Source 2 | Data warehouse | SQL / JDBC | Service account | 5 AM UTC | Previous day |
| Source 3 | Google Sheets | Sheets API | OAuth token | Real-time | Current |
| Source 4 | REST API | HTTP GET | API key | On-demand | Real-time |

### Dependency Graph

Map the order of data availability:

```
Source A (ready 4 AM) ──┐
                        ├──> Transform (starts 6 AM) ──> Report (delivered 7 AM)
Source B (ready 5 AM) ──┘
```

## Step 3: Transformation Logic

Document all business logic as reproducible transformations:

### Transformation Specification Template

```
Transform Name: [descriptive_name]
Input: [source table(s) or file(s)]
Logic:
  1. Filter: [conditions]
  2. Join: [table A] LEFT JOIN [table B] ON [key]
  3. Aggregate: GROUP BY [dimensions], SUM/AVG/COUNT [measures]
  4. Calculate: [derived columns with formulas]
  5. Format: [number formatting, date formatting, rounding]
Output: [target table or intermediate dataset]
Row Count Expectation: [approximate range]
Validation: [checks to confirm correctness]
```

### Common Transformation Patterns

| Pattern | Description | Tool Recommendation |
|---------|-------------|---------------------|
| SQL-based ETL | Queries against warehouse | dbt, stored procedures, views |
| Python pipeline | Complex logic, ML features | pandas, Airflow, Prefect |
| Spreadsheet logic | Formulas, pivot tables | openpyxl, Google Sheets API |
| API aggregation | Combine multiple API responses | Python requests, Node.js |
| File processing | Parse CSV/Excel uploads | pandas, Great Expectations |

## Step 4: Scheduling and Orchestration

Design the scheduling and execution plan:

| Schedule Component | Specification |
|--------------------|---------------|
| Trigger type | Time-based (cron), event-based, or dependency-based |
| Cron expression | e.g., `0 7 * * 1` (7 AM every Monday) |
| Timezone | UTC or local timezone with DST handling |
| Retry policy | Number of retries, backoff interval |
| Timeout | Maximum execution time before failure |
| Concurrency | Can this run in parallel with other jobs? |
| Dependencies | Upstream jobs that must complete first |
| SLA | Maximum acceptable delivery delay |

### Orchestration Tool Selection

| Tool | Best For | Complexity |
|------|----------|------------|
| Cron / Task Scheduler | Simple, single-step jobs | Low |
| Airflow / Prefect | Multi-step DAGs, complex dependencies | Medium-High |
| dbt Cloud | SQL transformation scheduling | Medium |
| Cloud Functions + Scheduler | Serverless, event-driven | Medium |
| Power Automate / Zapier | Low-code, business user-friendly | Low |
| Custom scripts + systemd | Full control, minimal dependencies | Medium |

## Step 5: Distribution and Delivery

Configure how reports reach their audience:

| Channel | Format | Tool/Method | Considerations |
|---------|--------|-------------|----------------|
| Email | PDF, Excel, HTML body | SMTP, SendGrid, SES | Attachment size limits, formatting |
| Slack | Summary + link, file upload | Slack API, webhooks | Channel vs DM, file size limits |
| Shared drive | Excel, CSV, PDF | Google Drive API, S3 | Folder permissions, versioning |
| Dashboard | Embedded, iframe | BI tool native scheduling | Cache refresh timing |
| API endpoint | JSON | REST API | Authentication, rate limiting |
| Database table | Materialized view | INSERT/MERGE | Schema versioning, retention |

### Distribution Configuration Template

```
Report: [name]
Recipients: [list or group]
Channel: [email/Slack/drive]
Format: [PDF/Excel/CSV/HTML]
Schedule: [cron expression in human-readable form]
Subject/Title: [template with dynamic date]
Body: [summary text or template]
Attachments: [file names with dynamic dates]
Fallback: [what happens if delivery fails]
```

## Step 6: Error Handling and Maintenance

Build resilience and long-term maintainability:

### Error Handling Matrix

| Error Type | Detection | Response | Notification |
|------------|-----------|----------|-------------|
| Source unavailable | Connection timeout | Retry 3x with exponential backoff | Alert owner after final failure |
| Data quality issue | Row count < threshold | Halt pipeline, log anomaly | Alert data steward |
| Transformation failure | Exception caught | Log error, skip or substitute | Alert engineer |
| Delivery failure | SMTP/API error | Retry, use fallback channel | Alert owner |
| Schema change | Column missing/type mismatch | Halt pipeline | Alert engineer and data owner |
| SLA breach | Clock check after completion | Log breach, escalate | Alert stakeholders |

### Maintenance Plan

| Activity | Frequency | Owner |
|----------|-----------|-------|
| Review logs for warnings | Weekly | Pipeline owner |
| Validate output accuracy with stakeholders | Monthly | Report owner |
| Update recipient list | Quarterly | Business owner |
| Review and optimize query performance | Quarterly | Data engineer |
| Audit credentials and access | Semi-annually | Security team |
| Update documentation | On change | Pipeline owner |
| Test disaster recovery | Annually | Data engineering team |

## Output Format

Present the automation plan as:

1. **Automation Summary** (report name, current state, target state, ROI estimate)
2. **Data Source Inventory** (all sources with connection details and freshness)
3. **Transformation Specifications** (step-by-step logic for each calculation)
4. **Pipeline Architecture Diagram** (source > transform > output > distribute)
5. **Schedule and Orchestration Plan** (cron, dependencies, SLA)
6. **Distribution Configuration** (channels, formats, recipients)
7. **Error Handling Playbook** (error types, responses, escalation)
8. **Maintenance Runbook** (ongoing tasks, ownership, cadence)

## Quality Checklist

Before delivering the automation plan, verify:

- [ ] All manual steps have been identified and mapped to automated equivalents
- [ ] Data sources are accessible via programmatic interfaces
- [ ] Transformation logic matches the current manual process exactly
- [ ] Schedule accounts for data source freshness and upstream dependencies
- [ ] Error handling covers source failures, data quality issues, and delivery failures
- [ ] Distribution list and channels are confirmed with stakeholders
- [ ] Monitoring and alerting are configured
- [ ] Documentation enables another engineer to maintain the pipeline
- [ ] Rollback plan exists for failed runs

## Edge Cases

- **Reports with manual data entry**: Create a staging area (Google Form, shared sheet) for manual inputs; automate everything downstream
- **Reports requiring human approval**: Build an approval gate (email confirmation, Slack button) into the pipeline before distribution
- **Multi-timezone audiences**: Stagger delivery or generate timezone-specific versions with localized timestamps
- **Reports with variable structure**: Use templates with conditional sections; handle empty segments gracefully
- **Legacy source systems without APIs**: Use database replication, file drops (SFTP), or screen scraping as last resort; document fragility
- **Compliance-sensitive reports**: Add audit logging, access controls, and data masking; retain report snapshots for regulatory review