Work Order Assessment

Analyze facility maintenance work order data to identify patterns, inefficiencies, and automation opportunities. This is the analytical backbone of the Spreadsheet-to-System Audit.

When to Use

• Client provides a work order export (CSV, Excel, or pasted data)
• Evaluating whether a facility team is using their CMMS effectively
• Preparing the assessment portion of a QCA audit deliverable
• Benchmarking a facility's maintenance operations against industry standards

What to Assess

1. Data Completeness Score

For each work order, check for the presence and quality of these fields:

Field	Weight	What "Complete" Means
Title/Description	20%	More than 5 words, describes the actual issue
Category/Type	15%	Uses a consistent taxonomy, not "Other" or blank
Priority	15%	Assigned at creation, not all the same level
Assigned To	10%	Named individual or team, not blank
Location	10%	Specific (building + floor + suite/area), not just building name
Due Date	10%	Set based on priority SLA, not arbitrary
Completion Date	10%	Filled in when closed, not blank on closed WOs
Cost/Parts	5%	Actual costs recorded, not $0 on completed work
Photos/Attachments	5%	Before/after photos for work over $500

Scoring:

• 90-100%: Excellent. Data supports analytics and decision-making.
• 70-89%: Adequate. Some gaps limit reporting capability.
• 50-69%: Poor. Significant manual effort needed for any analysis.
• Below 50%: Critical. The CMMS is being used as a to-do list, not an asset management tool.

2. Categorization Consistency

Analyze how work orders are categorized:

• How many distinct categories are used?
• What percentage fall into "Other," "General," or uncategorized?
• Are similar issues categorized differently? (e.g., "HVAC," "A/C," "Air Conditioning," "Climate" all meaning the same thing)
• Is there a clear taxonomy, or is it freeform text?

Benchmark: Mature FM operations use 10-15 primary categories with consistent subcategories. If you see 50+ categories or 30%+ in "Other," the taxonomy needs work.

3. Priority Distribution

Analyze the distribution of priority levels:

• What percentage are marked Critical/Emergency?
• Is everything the same priority? (common: everything is "Normal" or everything is "High")
• Does priority correlate with response time? (if Critical WOs take 5 days, priority is meaningless)

Benchmark (healthy distribution):

• Critical: 2-5%
• High: 10-15%
• Medium: 40-50%
• Low: 30-40%

Red flags:

• 20%+ Critical: Priority inflation, everything is "urgent"
• 90%+ same priority: Priority field is not being used meaningfully
• No Critical at all: Life safety items may not be getting flagged

4. Response Time Analysis

Calculate time between:

• Request to Acknowledgment: When was the requester notified someone is on it?
• Request to Dispatch: When was a technician or vendor assigned?
• Request to Completion: Total lifecycle of the work order
• Completion to Close: Administrative lag (how long WOs sit "completed" before someone closes them)

Benchmarks (commercial office):

• Critical: < 15 min response, < 4 hr resolution
• High: < 2 hr response, < 24 hr resolution
• Medium: < 4 hr response, < 48 hr resolution
• Low: < 1 day response, < 5 day resolution

5. Repeat Issue Detection

Look for patterns that suggest reactive maintenance instead of preventive:

• Same location + same category appearing 3+ times in 90 days
• Same equipment/asset with recurring failures
• Seasonal patterns (HVAC surge every spring, plumbing every winter)

Output format for repeats:

## Repeat Issues Detected

| Location | Category | Occurrences (90 days) | Total Cost | Root Cause Candidate |
|----------|----------|-----------------------|------------|---------------------|
| Suite 200 | HVAC | 5 | $3,200 | Aging RTU, recommend capital replacement evaluation |
| Lobby | Plumbing | 3 | $1,800 | Recurring drain backup, recommend camera inspection |

6. Vendor Performance

If vendor data is available:

• Average response time by vendor
• Cost per work order by vendor
• Completion rate (% of assigned WOs completed on time)
• Callback rate (vendor had to return for same issue within 30 days)

7. Automation Opportunity Score

Based on the assessment, identify the top 3 processes that would benefit most from automation:

Common automation opportunities in FM:

Opportunity	Signal in Data	Estimated Time Savings
Auto-triage incoming requests	Inconsistent categorization, priority inflation	15-30 min per request
Preventive maintenance scheduling	High repeat issue rate, reactive patterns	5-10 hrs/month
Tenant communication automation	Long acknowledgment times, no status updates	3-5 hrs/week
Vendor dispatch automation	Slow dispatch times, manual phone/email routing	2-4 hrs/week
Work order closeout reminders	Long completion-to-close lag	1-2 hrs/week
Cost tracking and reporting	Missing cost data, manual monthly reporting	4-8 hrs/month

Output Format

# Work Order Assessment Report

## Executive Summary
[2-3 sentences: overall health of maintenance operations, biggest finding, top recommendation]

## Data Quality Score: [X/100]
[Breakdown by field with specific examples of gaps]

## Key Findings

### Finding 1: [Title]
- **What we found:** [Specific observation with numbers]
- **Why it matters:** [Business impact]
- **Recommendation:** [Actionable step]

### Finding 2: [Title]
...

### Finding 3: [Title]
...

## Metrics Dashboard

| Metric | Current | Benchmark | Gap |
|--------|---------|-----------|-----|
| Data Completeness | X% | 90%+ | Y% |
| Avg Response Time (Critical) | X hrs | < 0.25 hrs | Y hrs |
| Avg Resolution Time (All) | X days | < 3 days | Y days |
| Repeat Issue Rate | X% | < 10% | Y% |
| Priority Distribution | [describe] | 5/15/45/35 | [describe] |

## Top 3 Automation Opportunities

1. **[Opportunity]** - Est. savings: [X hrs/month or $X/month]
2. **[Opportunity]** - Est. savings: [X hrs/month or $X/month]
3. **[Opportunity]** - Est. savings: [X hrs/month or $X/month]

## Recommended Next Steps
1. [Immediate action, no cost]
2. [Quick win, minimal investment]
3. [Strategic improvement, requires planning]

Important Notes

• Never fabricate data points. If the export is missing fields, say so. "Cost data not available in export" is better than guessing.
• Frame findings as opportunities, not failures. "Your team could save 10 hours/month by automating triage" beats "Your triage process is broken."
• Always include at least one no-cost recommendation. Not everything requires a paid engagement.
• If the data quality is below 50%, the primary recommendation should be data cleanup before any automation work. Automating bad data just produces bad results faster.