Shopfloor KPI Calculation Pack (OEE, Downtime, Throughput, Quality)

Purpose and scope

This calculation pack provides a canonical, implementable specification for core shopfloor KPI streams: OEE, Downtime, Throughput, and Quality. For each KPI you will find:

• Clear definition and formula
• Required data fields (names and types) and minimal validation rules
• Gating rules: what to do when data are missing or invalid
• Aggregation windows and ownership guidance
• Sample SQL / pseudocode for common calculations
• Visualization recipes and practical notes

Treat these definitions as a canonical starting point. Local systems, equipment, and business rules will require validation and tailoring; record any local changes and keep the canonical definition versioned.

Common global rules that apply to all KPIs

• Time windows: calculate and store base events at a short interval (e.g., event-level, cycle-level, or 1–5 minute buckets). Aggregate to hourly, shift, and daily windows for dashboards and escalation.
• Timezone and shift alignment: normalize timestamps to a single site timezone before aggregation. Define shift boundaries explicitly (start/end times) and apply the same boundaries across KPIs.
• Missing or partial timestamps: follow the gating rules below for each KPI. Do not silently drop data without tagging (use a status field such as data_quality = {good, partial, inferred, missing}).
• Ownership: assign a Data Steward for raw events and a Metric Owner (process engineer or supervisor) responsible for each KPI's calculation choices and local exceptions.

1) OEE (Overall Equipment Effectiveness)

Definition

OEE = Availability × Performance × Quality.

Component formulas

• Availability = Operating Time / Planned Production Time
• Performance = (Ideal Cycle Time × Total Count) / Operating Time
• Quality = Good Count / Total Count
• OEE = Availability × Performance × Quality (express as a percentage)

Required data fields

• machine_id (string/int)
• timestamp (UTC-normalized datetime)
• planned_production_start, planned_production_end (datetime) — per shift or per run
• downtime_events: list of {start_time, end_time, reason_code}
• cycle_events or production_counts: list of {timestamp, part_id, count, good_count, cycle_time_ms}
• ideal_cycle_time_ms (numeric) — per part_id or machine/operation

Calculation notes and gating rules

• If Planned Production Time = 0, mark Availability as undefined and show as data missing rather than zero.
• If Operating Time ≤ 0 (e.g., all time recorded as downtime), set Performance = 0 and annotate cause.
• If Total Count = 0, set Quality and Performance to 0 and surface as a data-quality alert; avoid dividing by zero.
• For partial or open downtime events (end_time missing), infer end_time = min(current_time, planned_production_end) and tag as inferred; require manual reconciliation.

Aggregation windows & ownership

• Base interval: event- or cycle-level
• Aggregate to: 5-minute buckets (for fast ops), hourly, shift, day
• Metric Owner: production engineer / line supervisor

Sample pseudocode / SQL (conceptual)

-- Compute Operating Time for a shift
SELECT machine_id,
       SUM(TIMESTAMPDIFF(SECOND, dt.start_time, dt.end_time)) AS total_downtime_seconds
FROM downtime_events dt
WHERE dt.start_time >= :shift_start AND dt.end_time <= :shift_end
GROUP BY machine_id;

-- Operating Time (seconds) = planned_seconds - total_downtime_seconds

-- Performance numerator = ideal_cycle_time_seconds * total_units_produced
-- Performance = performance_numerator / operating_time_seconds
  

Visualization recipe

• Primary KPI tile: OEE (%) for current shift, delta vs prior shift and rolling 7-day average.
• Exploration panel: stacked bar showing Availability, Performance, Quality contributions (each as % of ideal) by shift/day.
• Drill-down: line chart of 5-minute OEE trend with event markers for large downtimes and quality incidents.

Common pitfalls

• Using machine run-time and planned-time inconsistently across teams — define planned production windows centrally.
• Mixing units or ideal cycle definitions across parts — ideal_cycle_time must be keyed to part_id/process.

Worked example

Shift planned time = 8 hours (28,800s). Total downtime = 3,600s. Operating time = 25,200s. Total count = 12,000 parts, Good count = 11,700. Ideal cycle time = 2s.

• Availability = 25,200 / 28,800 = 0.875 (87.5%)
• Performance = (2 × 12,000) / 25,200 = 24,000 / 25,200 = 0.952 (95.2%)
• Quality = 11,700 / 12,000 = 0.975 (97.5%)
• OEE = 0.875 × 0.952 × 0.975 ≈ 0.812 (81.2%)

2) Downtime (Availability visibility)

Definition

Downtime measures the time when equipment is unavailable for production during planned production windows. Downtime is recorded as discrete events with reason codes.

Required data fields

• machine_id
• start_time (datetime)
• end_time (datetime) — may be null while event is open
• reason_code (string) — use controlled taxonomy
• reported_by (user id) and reported_channel (operator, automatic, tablet)

Gating rules

• Open events with missing end_time: infer end_time = min(shift_end, current_time) for dashboarding but mark as open and require reconciliation.
• Small interruptions below a configurable threshold (e.g., < 1 minute) may be aggregated as transient and optionally excluded from root-cause processes — but include in raw availability for accuracy.
• When reason_code is missing, auto-assign reason_code = UNKNOWN and flag for follow-up within 24 hours.

Sample SQL / pseudocode

-- Downtime duration per event (seconds), handling open events
SELECT id, machine_id,
       COALESCE(TIMESTAMPDIFF(SECOND, start_time, end_time),
                TIMESTAMPDIFF(SECOND, start_time, LEAST(:now, :shift_end))) AS duration_seconds,
       reason_code
FROM downtime_events
WHERE start_time >= :window_start AND start_time <= :window_end;

-- Sum downtime by reason
SELECT reason_code, SUM(duration_seconds) AS total_seconds
FROM ( ... previous query ...) d
GROUP BY reason_code;
  

Visualization recipe

• Stacked bar: downtime by reason_code for the shift/day (top reasons first).
• Heatmap: machine × hour showing downtime density.
• Event list: largest downtime events with link to operator notes and corrective action owner.

3) Throughput

Definition

Throughput is the rate of completed units (or parts) leaving a process per time unit (e.g., units/hour). Use the same unit of measure as production counts. For multi-step processes, define throughput at logical hand-off points.

Required data fields

• machine_id or line_id
• timestamp (datetime) when unit completed or output_count recorded
• part_id
• units_produced (integer) and optionally good_units

Calculation and gating rules

• Throughput rate = SUM(units_produced) / window_duration_hours (e.g., per shift or per hour).
• If units timestamps are missing but shift totals exist, assign the total to the shift bucket and tag as aggregated import.
• For continuous processes, consider using rolling-window rates (e.g., 15-minute moving average) to smooth burstiness.

Sample SQL / pseudocode

-- Hourly throughput
SELECT machine_id, DATE_TRUNC('hour', timestamp) AS hour_bucket,
       SUM(units_produced) AS units_per_hour
FROM production_counts
WHERE timestamp BETWEEN :start AND :end
GROUP BY machine_id, hour_bucket;
  

Visualization recipe

• Line chart of units/hour with target line (takt or planned rate).
• Capacity utilization gauge: throughput / theoretical_max_capacity.
• Scatter: throughput vs downtime to find correlation between low throughput and high downtime periods.

4) Quality (First Pass Yield & Scrap)

Definitions

• First Pass Yield (FPY) = Good Count / Total Count (for the process boundary under consideration)
• Scrap Rate = Scrap Count / Total Count

Required data fields

• part_id, lot_id (if applicable)
• timestamp
• total_count
• good_count
• rework_count (if tracked separately)
• defect_code(s) — controlled taxonomy

Gating rules

• If total_count is zero, report FPY as undefined and surface as a data-quality issue.
• Decide whether reworked units count as good (FPY) or not; document the rule. Best practice: FPY excludes rework (i.e., good_count excludes reworked units produced later).
• When defect codes are missing, use UNKNOWN and require operator follow-up within a defined SLA.

Sample SQL / pseudocode

-- FPY per shift
SELECT machine_id, shift_id,
       SUM(good_count) AS good,
       SUM(total_count) AS total,
       CASE WHEN SUM(total_count)=0 THEN NULL
            ELSE SUM(good_count)::float / SUM(total_count)
       END AS fpy
FROM quality_counts
WHERE timestamp BETWEEN :shift_start AND :shift_end
GROUP BY machine_id, shift_id;
  

Visualization recipe

• FPY trend line with leading indicators (e.g., machine temperature, operator, supplier batch) in hover details.
• Pareto chart of defect_codes showing cumulative % of total defects.
• Table linking low-FPY lots with corrective actions and owners.

Practical implementation guidance

• Keep a canonical metric spec document in your domain and record any local overrides as metadata (who changed it, why, when).
• Maintain controlled taxonomies for reason_code and defect_code. Use short code + description fields.
• Surface data-quality indicators on dashboards (e.g., "inferred downtime", "open events", "aggregated import"). Users should see why a number might be incomplete.
• Validate calculations using small, known datasets before connecting live systems. Create a test harness that runs known inputs and compares expected outputs.

Suggested canonical field names (starter mapping)

Quality checks and alerts

• Alert when any denominator used in a KPI is zero or null (e.g., planned_seconds = 0, total_count = 0).
• Alert when open downtime events exceed a threshold duration without reconciliation.
• Provide a daily data-quality dashboard that lists inferred values, aggregated imports, and missing taxonomy items.

Next steps and tailoring

Use this pack to build a canonical set of metric definitions in your plant. Recommended next steps:

1. Map your raw data sources to the canonical fields above. Record mapping metadata (source system, table, last sync).
2. Run the sample SQL/pseudocode against a subset of historical data and validate outputs with operators and supervisors.
3. Decide and document local gating rules (e.g., how to treat rework or transient stoppages).
4. Implement dashboards with visible data-quality flags and an owner responsible for triage.

Consider packaging this pack into a reusable domain or toolkit for other plants (see CapabilityEnhancementNotes).