I recently worked on a frustrating but all‑too‑common problem: operations teams swearing the line is fine while finance reports a 4–7% unexplained yield loss every month. On the floor, PLCs stream cycle‑by‑cycle telemetry that shows sensors, counts, and reject events in sub‑second detail. In the back office, the ERP produces tidy batch reports that roll up quantities, scrap, and cost—on a daily or monthly cadence. Reconciling the two is rarely straightforward, but it's essential if you want to close real yield gaps instead of chasing ghosts.

Why these mismatches happen

Before jumping into solutions, it helps to understand the root causes. In my experience, the most common reasons are:

  • Different definitions of yield and scrap. PLCs often count parts at a particular point (e.g., after inspection station A). ERP may count at packing or after rework, and may treat reworked parts differently.
  • Timestamps and time zones. PLC events are timestamped at the OT layer; ERPs use business day boundaries and sometimes local server time—mismatches in timezone or clock drift create alignment errors.
  • Batch and lot identification gaps. PLCs may not capture ERP batch IDs, or the MES mapping is partial. Without a common key you can't join the datasets reliably.
  • Aggregation and sampling differences. Telemetry is high‑frequency; ERPs are aggregated. Naive aggregation of telemetry can double‑count reworked parts or miss rejects flagged later.
  • Human processes and manual entries. Manual adjustments in ERP (e.g., scrapped material written off weeks later) won't appear in real‑time OT data.
  • Data quality issues. Missing PLC logs, duplicate transactions, network outages, and timeouts create gaps.

    • Knowing these categories helps you design a targeted reconciliation strategy instead of scrambling through noise.

    Principles I follow when reconciling telemetry and ERP

    There are a few principles I insist on for every reconciliation project:

  • Define shared KPIs and precise semantics up front. Agree what "yield", "scrap", "rework", "OK part" mean, and where each is counted.
  • Create a single source of truth for runtime identity. Use MES or a transaction broker to attach batch IDs, lot IDs, and operator IDs to PLC events.
  • Prefer deterministic mapping over probabilistic joins. If you can attach keys at source—do it. For cases where you can't, build confident heuristics with timestamps and volumes, but flag matches with confidence scores.
  • Automate reconciliation and surfacing of exceptions. Humans should handle investigations, not rote comparison tasks.
  • Keep an audit trail. Every adjustment must be traceable: who made it, why, and what source data supported it.

    • Practical architecture I recommend

    My go‑to pattern is: PLC → Edge/IIoT gateway → MES/event bus → Time‑series store + transactional ETL → ERP interface and reconciliation engine. Key elements:

  • Edge/IIoT gateway (Ignition, Kepware, or vendor PLC gateways): normalizes tags, stamps events with consistent UTC timestamps, and attaches machine state metadata.
  • MES or event broker: assigns a canonical production_order_id or batch_id to events as they enter the line. This is the critical join key.
  • Time‑series and relational stores (OSIsoft PI, InfluxDB, SQL Server): keep both high‑frequency telemetry and transactional events for audit and analysis.
  • Reconciliation engine (ETL + analytics): daily jobs that aggregate telemetry by batch_id and compare against ERP batch reports, computing delta, confidence, and root‑cause flags.
  • Visualization and alerting: dashboards that show matched batches, unexplained variances over threshold, and triage notes.

    • Step‑by‑step process I run on projects

    Here's a pragmatic sequence I use to take a messy site to repeatable reconciliation within 6–8 weeks for a pilot line.

  • Workshop definitions (week 1): get engineering, quality, operations, and finance in the room. Define yield semantics and acceptance points.
  • Instrument identity (week 1–2): ensure MES or an operator input assigns the ERP batch/production order to the line. If MES doesn't exist, use barcodes/RFID scanned at start of line and propagated to PLC logs via gateway.
  • Timestamp and clock sync (week 1–3): enforce NTP time sync across PLCs, edge devices, MES, and ERP interfaces. Convert everything to UTC in pipelines.
  • Data capture and storage (week 2–4): export PLC counters and reject events to a time‑series DB; store events as (batch_id, timestamp, event_type, tag, value).
  • Define reconciliation rules (week 3–5): e.g., telemetry_ok = parts_passed_at_final_inspection - telemetry_rejects; ERP_ok = ERP_reported_good; delta = ERP_ok - telemetry_ok. Define acceptable thresholds by product.
  • Automate daily reconciliation (week 4–6): run ETL that outputs a matched set of batches with deltas and confidence. Flag exceptions and surface them to quality team for investigation.
  • Close the loop (ongoing): add corrective actions (e.g., update PLC logic, change measurement point, fix ERP process) and track their impact in subsequent reconciliations.

    • How I handle ambiguous matches

    Not every telemetry record will contain a batch_id. For those, I use a layered approach:

  • Temporal windowing: match telemetry to ERP batches when volumes align within a time window (e.g., telemetry counts between 08:10–08:25 match ERP batch started at 08:00 with expected run time 20 minutes).
  • Confidence scoring: assign scores based on proximity of timestamps, quantity match, and machine ID. Only auto‑accept matches above a threshold; route the rest to a human reviewer.
  • Reconciliation feedback: when a human corrects a mapping, feed that back to adjust heuristics and improve future automatic matches.

    • Tools and scripts I typically use

    I don't believe a single vendor is mandatory—I've built solutions with Ignition at the edge, OSIsoft PI for time‑series, a light MES (e.g., Apriso or in some cases a custom Node.js service), and SAP as ERP. For reconciliation and analytics I use Python/Pandas scripts or an ELT job in dbt/SQL that runs nightly. For smaller sites, a spreadsheet plus a database can work as an intermediate step, but avoid manual copy/paste for the long term.

    KPIs and reports I push for

    You want metrics that drive action. I recommend:

  • Batch match rate: percent of ERP batches successfully matched to telemetry with high confidence.
  • Average delta in yield per batch: mean and median of (ERP_yield - telemetry_yield).
  • Exception backlog: count and age of unmatched or unexplained variance cases.
  • Root cause distribution: percentage of exceptions due to timing, missing batch_id, manual ERP adjustments, or measurement point mismatch.

    • Quick checklist (ready to paste into a project plan)

    ActionOwnerTarget
    Agree yield definitionsOps / QualityWeek 1
    Ensure batch_id capture at line startMES / ITWeek 2
    Enable NTP sync on PLCs & serversOT / ITWeek 2
    Stream PLC events to time‑series DBOT / EdgeWeek 3
    Build nightly reconciliation jobData/AnalyticsWeek 4–6
    Deploy dashboards & exception workflowsOps / QAWeek 6

    When teams follow a repeatable pattern—standardized timestamps, reliable batch identity, clear semantics, automated matching, and an exception workflow—unexplained yield gaps shrink fast. The goal isn't to prove one system "right" and the other "wrong"; it's to create a shared, auditable narrative of what's actually happening on the line so operations, quality, and finance can act on the same facts.