Quarterly Business Metrics
Per-quarter shipped revenue, booked sales, backlog, and “soup line” (in-flight but not yet substantially worked) for PSI’s machine projects. Covers 2010-Q1 through current quarter.
| Dataset | psi-analytics/eto/data/psi_quarterly_metrics.csv (99 quarters × 19 columns) |
| Dashboard | psi-analytics/eto/reports/psi_quarterly_metrics.html (self-contained, Chart.js) |
| Executive summary | psi-analytics/eto/reports/psi_exec_summary.html — one-page leader view: 6 KPIs, auto-written conclusions, glossary; print-friendly. Rebuild with python eto\scripts\build_exec_summary.py after refreshing the CSVs |
| Build scripts | eto/scripts/quarterly_metrics.py → CSV · eto/scripts/build_dashboard.py → HTML (in psi-analytics) |
| Refresh | From the repo root: python eto\scripts\quarterly_metrics.py && python eto\scripts\build_dashboard.py |
| Universe | ~2,390 machine projects (4-digit new builds + 5-digit retrofits); internal/stock jobs excluded |
What the dataset answers
- Shipped revenue per quarter — how much PSI billed
- Earned revenue per quarter — production output: value earned in proportion to labor actually burned (percentage-of-completion style; smooths the ship-timing lumpiness)
- Booked sales per quarter — new orders signed in the quarter
- Backlog at end of quarter — projects in flight (booked, not yet shipped), gross value and remaining (unearned) value
- “Soup line” at end of quarter — backlog projects where no substantial work has begun yet (no department has crossed 2% of its total labor)
- Active builds at end of quarter — backlog minus soup line; projects actively being worked
- Lead time — average order-to-ship days for projects shipped in the quarter
- On-time rate — % shipped on or before
PlannedShipDate - Book-to-bill ratio — bookings ÷ shipments; >1.0 means backlog growing
Data sources
| Source | Used for | Refresh cadence |
|---|---|---|
\\ad.ptihome.com\dfs\Data\APPS\Settings\PSI.FileCache\Project1287List.xml | Project universe, OrderDate, ShipDate, OrderValue, OrderTotal, WarrentyStartDate, ProjectStatus | Live — updated by PSI.All WPF apps whenever a project is created or edited |
\\ad.ptihome.com\DFS\Schedule\SS123\LEADTIME\psi_analytics.db · labor_detail table | 2%-of-labor threshold computation per project | Nightly 1:30 AM ET via psi-data-pipeline on PS-PROXY |
This dataset deliberately bypasses the pipeline’s flat comprehensive_dataset.csv — that file filters to shipped projects only (its source otd_dataset.csv drops projects with no ActualShip), which would miss the entire in-flight backlog. We query labor_detail directly (1.38M rows, indexed by job) so backlog projects are covered.
Metric definitions
Shipped Revenue
Sum of OrderTotal (final billed value, including change orders) for projects with effective ship date in the quarter.
ShippedRevenue[Q] = Σ OrderTotal where EffectiveShipDate ∈ Q
EffectiveShipDate = ShipDate || WarrentyStartDate
Effective ship date falls back to WarrentyStartDate because warranty doesn’t start until a machine ships. About 31 historical records have warranty start populated but ShipDate blank — those were missed by date-only logic.
Earned Revenue (production output)
POC-style attribution: each project’s final-billed value (OrderTotal) is spread across quarters in proportion to the shop/engineering hours burned that quarter.
EarnedRevenue[Q] = Σ over projects: Value × (HoursBurned[project, Q] / ExpectedTotalHours[project])
ExpectedTotalHours = actual total shop/eng hours (shipped projects)
= max(Value × median-hrs-per-$, hours-to-date × 1.05) (in-flight)
The hours-per-$ medians come from projects shipped in the trailing 2 years, split new-build vs retrofit (as of 2026-07: ~1,787 hrs/$1M new-build, ~1,214 hrs/$1M retrofit). Shipped projects with no labor data earn 100% at ship. Long-run it reconciles with shipped revenue ($390M earned vs $375M shipped, 2015–2025 — the excess is work done on still-unshipped backlog) and it is meaningfully smoother (quarterly CV 0.32 vs 0.44), which is the point: it answers “how much did the factory produce this quarter?” instead of “which quarter did the truck leave?”.
Why this exists: shipped revenue recognizes a machine’s full value in a single quarter, so one $5M ship makes a quarter look huge and the adjacent ones look weak. Earned revenue is the resolution fix for that attribution problem. It is a production metric, not a finance number — AFTEC budget hours can’t be used for in-flight jobs because comprehensive_dataset.csv is shipped-only (AFTEC Tot_Budget is accurate where present — median actual/budget 0.92 for 2023+ ships — a UniData API pull of budgets for open jobs is the tracked upgrade path).
Booked Sales
Sum of OrderValue (signed value at PO receipt) for projects with OrderDate in the quarter.
BookedSales[Q] = Σ OrderValue where OrderDate ∈ Q
OrderValue vs OrderTotal: 17% of projects have OrderTotal > OrderValue because change orders were added during the build. Booked uses the original signed value; Shipped uses the final billed value. This matches how finance recognizes the deal.
Backlog (end-of-quarter)
Projects that were booked on or before the quarter end and have not yet shipped, excluding data-hygiene cases and stale ghost records:
project ∈ Backlog[Q] iff
OrderDate ≤ Q_end
AND (
EffectiveShipDate > Q_end # will ship after this quarter
OR (no EffectiveShipDate
AND ProjectStatus == 'Active'
AND NOT is_stale_ghost) # not an abandoned data record
)
is_stale_ghost = (
age and dormancy exceed customer-vs-internal thresholds
)
# Thresholds:
# - Customer projects: age > 24 months AND no labor in last 9 months
# - PSI internal projects (AccountName starts with "Progressive Surface"):
# age > 12 months AND no labor in last 4 months
# (Internal projects never get a ShipDate populated — there's no customer delivery
# event — so labor cessation IS the completion signal. Use tighter thresholds.)
Two filters guard against bad data, because PSI doesn’t actually abandon projects:
-
Status filter — ~135 old records in AFTEC have no
ShipDateAND noWarrentyStartDateAND a non-Active status (mostly “Out of Warranty” — they shipped pre-1997, before warranty-start tracking existed). Without the filter, these inflate current backlog ~2×. -
Stale-ghost filter — even among “Active”-status records, some have been sitting with no ship date for years. A project that’s >2 years old with zero labor in the past 9 months is almost certainly shipped-but-never-flagged, or closed without being marked. The filter drops these (currently 11 records as of 2026-05-12). Real stuck projects — even ones on long customer hold — virtually always have some recent labor (rework, follow-up, redbook resolution), so they pass through. The age threshold (24 months) was chosen to be just above the user-stated tolerance: a 21-month lead-time project is “OK, even better,” but anything >2 years dormant is a record-keeping artifact, not real backlog.
Soup line
Backlog projects whose cumulative labor across mech eng (110M), controls eng (110E), machining (102), welding (104), mech assembly (106), and elec assembly (108) has not yet crossed the larger of 40 hours OR 2% of project’s total labor.
project ∈ SoupLine[Q] iff
project ∈ Backlog[Q]
AND CumulativeLabor[project, Q_end] < max(40 hrs, 0.02 × TotalLabor[project])
Why both thresholds: 2% alone matches the PMO/DM_*_P2 convention and works for finished projects where “total” is settled. For in-flight projects where total is still small (e.g., 5 hours logged so far), 2% = 0.1 hours — trivially crossed by the first labor entry — which misclassifies barely-touched projects (proposal review, scope clarification) as “active.” A 40-hour floor (~1 person-week of real engineering effort) prevents that. For a typical ~2000-hour ETO build, 40 hours = the 2% point anyway, so the floor doesn’t change classification of normal projects.
Discovered the hard way (2026-05-12): A pure 2% threshold caused Q1 2026 to show +73 active jump from Q4 2025 because dozens of fresh bookings had logged 0.2-10 hours each in early 2026 — proposal review, not real work — and 2% of 0.2 hrs is trivial. User flagged it as impossible. Adding the 40-hr floor dropped the jump to a believable +46.
The 2% threshold is a small safety margin above zero. Validation against shipped projects shows the choice of threshold (1% vs 2% vs 5% vs 10%) is insensitive — the median project crosses all four within ~3 days of each other, because once labor starts on an ETO project it ramps up quickly. The 2% level is effectively “labor has started, with a 1-2 day buffer against single-hour noise punches.”
| Threshold | p25 days from order | median | p75 |
|---|---|---|---|
| 1% | 15d | 66d | 166d |
| 2% | 18d | 66d | 162d |
| 5% | 22d | 69d | 156d |
| 10% | 26d | 67d | 139d |
The 2% level aligns with the per-department DM_*_P2 convention from the lead time analysis (where it does filter real PMO scheduling noise in single-department columns like 110M). When aggregated across all shop + engineering departments as we do here, the threshold is less about noise filtering and more about a clean “has substantial work begun” signal.
Computed inline by reading labor_detail rows for the project, ordered by date, accumulating hours. The date at which cumulative reaches 2% × total = the project’s “soup exit date.” If Q_end < soup exit, project is in soup line.
Chronology fix (2026-07-21): soup-exit dates were originally computed by accumulating labor rows in SQL string order of the MM/DD/YY date column — month-major across years, i.e. the classic AFTEC date-sort bug. Multi-year projects could cross the threshold “at” a date from the wrong year, which systematically overstated the Queued share of historical backlog. Corrected numbers are materially different: 2025-Q4 EOQ queue is 33 of 81 (41%), not the previously published 71 of 81 (88%).
Remaining Backlog Value (end-of-quarter)
Gross backlog counts a 95%-built machine at full value. Remaining value discounts each backlog project by work already performed:
RemainingBacklogValue[Q] = Σ over backlog: CurrentValue × (1 - min(CumHours[Q_end]/ExpectedTotalHours, 0.95))
Progress is capped at 95% until the machine actually ships; projects with no labor data count fully. This is the honest “future revenue in hand” number — as of 2026-07, $40.1M remaining of $61.2M gross. BacklogCurrentValueEOQ (gross at OrderTotal-fallback) is also emitted so signed change orders on in-flight jobs are visible.
Active Builds
Backlog minus soup line.
Backlog Bridge
backlog_bridge.csv classifies every project entering/leaving the backlog set each quarter: Booked (OrderDate in quarter), Other in (record re-activated), Shipped (effective ship date in quarter), Other out (left without shipping — stale-ghost cleanup, cancellations, data fixes). Values are on the booked-value basis, so Closing = Opening + ins − outs ties exactly to BacklogValueEOQ. If “Other out” is large in a quarter, that quarter’s backlog decline was record hygiene, not real shipments.
Backlog Detail / Burn-Down
backlog_detail.csv is a per-project snapshot of today’s backlog: planned-ship quarter bucket (incl. OVERDUE — past PlannedShipDate and unshipped — and UNSCHEDULED — no planned date in AFTEC), % complete, remaining value, Queued/Active stage, customer, machine, aging. Feeds the dashboard’s scheduled burn-down chart (“when does today’s backlog ship?”).
Book-to-Bill Ratio
B/B[Q] = BookedSales[Q] / ShippedRevenue[Q]
1.0 means backlog is growing; <1.0 means backlog is burning down. In an ETO model with 8–12 month lead times, single-quarter values swing wildly (one big order moves the needle). Read the trailing-4-quarter trend.
Lead Time & On-Time
AvgLeadTime[Q] = mean(EffectiveShipDate - OrderDate) for projects shipped in Q
OnTimePct[Q] = count(ShipDate ≤ PlannedShipDate) / count(with PlannedShipDate) # strict
OnTimePct7d[Q] = count(ShipDate ≤ PlannedShipDate + 7d) / count(with PlannedShipDate) # PSI standard
Two on-time series are emitted. OnTimePct7d applies PSI’s operational standard (ship within 7 days of planned; company bar is 90%) and is the one comparable to other PSI reports. The strict series is kept for continuity. The difference is material — e.g. 2026-Q2 is 52.6% at the PSI standard vs 31.6% strict.
On-Time is measured against ShipDate, not install-complete, by design. Once the machine ships, downstream activity (install scheduling, site readiness, customer training availability, travel coordination) is customer-driven and outside PSI’s control. The metric is “did we hand off the machine on the date we promised” — that’s the only thing PSI’s build org can be fairly held to.
Known limitations of On-Time %:
- Uses the current
PlannedShipDatein AFTEC, which is sometimes re-baselined during the build. True original-promise on-time would require GanttJOB revision history. - A customer-requested hold (where the machine is ready but the customer asks PSI to delay shipment) currently counts against PSI as a “late ship.” The right way to filter this out is to look at the gap between MVI/runoff completion (ops 4800-TEST/STARTUP, 4860-CUSTOMER TRAINING) and the actual ship date — a long gap with no further build labor indicates the hold was customer-side, not PSI execution. That detection isn’t implemented yet.
Post-ship labor is real but separate. About 7.5% of total project labor lands after ShipDate (median 101-day tail, mostly Dept 120 MVI for on-site install + training). This is captured in the underlying labor data but is intentionally not part of the on-time scoreboard — it’s customer-paced work and belongs in capacity planning for the field-service team, not in build-org performance metrics.
Validation anchors
The dataset was sanity-checked against three independent references:
| Anchor | Expected | Actual | Match |
|---|---|---|---|
| Lead-time analysis “81 active jobs” snapshot (ganttJOB.csv, Feb 2026) | ~81 | 81 backlog at 2025-Q4 EOQ (exact match); 97 at 2026-Q2 (after Q4 booking surge) | ✓ |
| Booking-rate × lead-time math: ~85 bookings/yr × ~350 days / 365 | ~82 | 98 (slightly high by 2025-Q4 booking surge — 42 in one quarter) | ✓ |
| Soup-line / Active ratio for healthy ETO shop | ~10-20% fresh | 12 / 86 = 12% soup, 88% active | ✓ |
| Lead-time report’s “77 projects/year for 2022-2025” | ~77/yr | 71, 80, 87, 71 (avg 77) | ✓ |
Before validation: raw OrderDate AND no ShipDate logic gave 184 backlog, ~2× reality. The full filter chain brought it down to 98:
WarrentyStartDatefallback recovered 31 historical projects that shipped but had blankShipDateProjectStatus == 'Active'filter dropped ~135 “Out of Warranty”/“Cancelled” no-ship records (shipped pre-1997, before warranty tracking)- Stale-ghost filter (24 months old + 9 months dormant) dropped 11 more records — mostly install-service tickets that were closed informally and 2 legitimately stuck Linamar projects with 18+ months of zero labor
Schema
CSV columns (all amounts in USD, all counts as whole projects):
| Column | Type | Description |
|---|---|---|
Quarter | text | YYYY-Qn |
QuarterStatus | text | COMPLETE or PARTIAL (current quarter) |
SoupLineConfidence | text | HIGH (≥1 mo old) or CURRENT (this month’s data still settling) |
ShippedRevenue | money | Sum of OrderTotal for projects shipped in quarter |
ShippedCount | int | Count of projects shipped in quarter |
AvgShippedValue | money | ShippedRevenue / ShippedCount |
BookedRevenue | money | Sum of OrderValue for projects booked in quarter |
BookedCount | int | Count of projects booked in quarter |
AvgBookedValue | money | BookedRevenue / BookedCount |
BookToBillRatio | float | BookedRevenue / ShippedRevenue (blank if no ships) |
BacklogValueEOQ | money | $ value of backlog at end of quarter |
BacklogCountEOQ | int | Project count of backlog at end of quarter |
SoupLineValueEOQ | money | $ value of soup-line projects at end of quarter |
SoupLineCountEOQ | int | Count of soup-line projects |
ActiveBuildValueEOQ | money | BacklogValueEOQ - SoupLineValueEOQ |
ActiveBuildCountEOQ | int | BacklogCountEOQ - SoupLineCountEOQ |
AvgLeadTimeDays | int | Mean order-to-ship days for ships in quarter |
OnTimePct | float | % shipped on/before PlannedShipDate (strict) |
OnTimePct7d | float | % shipped within 7 days of PlannedShipDate (PSI standard) |
OnTimeEligible | int | Count of ships with PlannedShipDate populated |
EarnedRevenue | money | POC-style production output (see Earned Revenue) |
RemainingBacklogValueEOQ | money | Backlog value × work not yet performed |
BacklogCurrentValueEOQ | money | Backlog at current contract value (OrderTotal fallback) |
BacklogNew*/BacklogRetro* | money/int | Backlog value+count split, 4-digit new builds vs 5-digit retrofits |
BacklogAge*Value | money | Backlog value by age since booking (<6 / 6-12 / 12-24 / >24 mo) |
BacklogCoverageYears | float | Backlog ÷ trailing-4-quarter shipped revenue — years of work in hand. For the current partial quarter the window is the 4 complete quarters before it. 15-yr range: 0.33 (2021-Q2 COVID trough) to 2.88 (2022-Q2 surge overload, the on-time-collapse precursor); median 0.84 |
RemainingCoverageYears | float | Same ratio using remaining (unearned) backlog value — the stricter read |
Companion files: backlog_bridge.csv (quarter-over-quarter movement, ties exactly), backlog_detail.csv (per-project snapshot of the current backlog), and annual_production.csv (shop/eng hours + distinct jobs per year since 1995, incl. legacy dept 110).
Two findings worth knowing (2026-07-21)
- The wave stopped dispersing because demand stopped pausing. Historically the Q4 booking wave drained over the following Q1–Q3 — but every major drain (2016→17, 2017→18, 2019→20, 2023→24) coincided with bookings pausing to ~$14–17M in that window while shipping outflow stayed capacity-bounded and constant. Since 2021, Q1–Q3 bookings run ~$29–34M, so the backlog no longer recedes on its own.
- 2022–2025 is the longest sustained high-output run in the 30-year labor record — four consecutive years at ≥118% of the 1995–2019 median (67.7k shop/eng hrs/yr), peaking at 106k hours across a record 184 distinct projects in 2023. Earlier peaks (1999, 2008, 2014-15, 2017-18) were 1–2 year spikes flanked by recovery valleys (2003, 2009, 2020-21); this run has had no valley.
Recurring patterns to watch for in the dashboard
Q4 booking surge (numbers corrected 2026-07-21)
Every Q4 in the dataset shows a customer year-end PO-release surge. Bookings cluster into Oct-Dec, then the just-booked projects sit as “Queued” (no substantial labor yet) at Q4 EOQ and convert to “Active” through Q1.
Correction: the queue counts originally published here were computed with the soup-exit chronology bug (see Soup line section) and overstated Queued substantially. Corrected table below.
| Year | Q4 Bookings | Q4 EOQ Queued (of backlog) | Q1 EOQ Queued |
|---|---|---|---|
| 2021 | 52 | 40 / 76 | 24 |
| 2022 | 39 | 31 / 88 | 21 |
| 2023 | 18 | 25 / 86 | 14 |
| 2024 | 25 | 22 / 67 | 19 |
| 2025 | 42 | 33 / 81 | 34 |
The booking concentration recovered sharply in 2025-Q4 (42 bookings, 2.3× 2023-Q4) but is not unprecedented — 2021-Q4 had 52. The structural point stands:
- Every January is the heaviest engineering kickoff month of the year
- Controls Engineering (the bottleneck per lead time analysis) takes the brunt
- Sales/sales-ops should expect the Q4 pile-in and plan around it
What it looks like on the dashboard
In the Backlog Snapshot chart, Q4s show a taller red (Queued) segment that shrinks through Q1-Q2 as projects shift to green (Active). At 2025-Q4 EOQ, 41% of backlog projects were queued (33 of 81) — elevated, but not the “88%” the pre-correction data suggested.
Known limitations
OrderValueandOrderTotalare not inflation-adjusted. A $1M order in 2010 and a $1M order in 2025 are treated as equal.- No margin or cancellation adjustment. Backlog dollars represent contracted value, not expected gross profit. A booked project that later got descoped still appears in historical backlog at its original value.
- No customer / industry / machine-type segmentation in the base CSV. All fields available in
Project1287List.xml(MachineOneName,IndustryClassName,AccountName, etc.) — extendquarterly_metrics.pyto add breakdowns. - Pre-2010 data thins out. Project coverage in
Project1287List.xmldrops off as you go back. The earliest record is from 1981, but consistent population starts around the late 1990s. The script emits quarters back to 2010 by default — earlier data is in the XML but not in the output CSV. - Brand-new projects (booked within ~30 days, no labor yet) correctly appear in soup line. They’ll flip to Active when timesheets start landing. This is expected behavior, not a bug.
- Spare-parts sales are structurally excluded. Project1287List.xml contains machine PROJECT entities only (verified 2026-07-21: 0 spare/parts/consumable records among 3,144 entities; 3-digit legacy jobs are all pre-2002). Parts and consumables revenue lives in AFTEC’s sales-order files and never enters these metrics — this dashboard is project revenue, full stop.
Refresh workflow
The dataset is currently not part of the nightly pipeline — it’s a manually-triggered analysis that pulls from two live network sources. To bring it into the nightly build, see eto/scripts/quarterly_metrics.py and integrate as a step in nightly-data-build.yml on psi-data-pipeline.
Because refresh is manual, the artifacts go stale silently. The 2026-05-27 build sat until 2026-07-21, by which point it understated closed 2026-Q2 shipped revenue by ~$10M (the quarter was PARTIAL when built). Always re-run before presenting the numbers.
# From C:\git\psi-analytics
python eto\scripts\quarterly_metrics.py # refresh CSV (reads live FileCache XML + nightly psi_analytics.db)
python eto\scripts\build_wip_flow.py # refresh WIP/utilization CSV (used by dashboard)
python eto\scripts\build_dashboard.py # rebuild HTML dashboard
start eto\reports\psi_quarterly_metrics.htmlThe XML source updates whenever any PSI.All WPF app writes a project change; the SQLite source refreshes nightly at 1:30 AM ET. Running the refresh any time after 2 AM gets you yesterday’s labor and current-as-of-now order/ship dates.
Related
- Lead Time Analysis — February 2026 — Source of the “81 active jobs” anchor; identifies engineering / purchasing / electrical-assembly as the systemic lead-time bottlenecks
- PSI Data Brain — Complete data source map, including
psi_analytics.dbandlabor_detail - Analytics Methodology — Quality-metric calculations (separate from business metrics)
- Project Explorer — Web app showing per-project lead-time history; uses the same underlying
Project1287List.xml
Created: 2026-05-12 · Paths updated 2026-07-21 after consolidation into the psi-analytics repo
Source: psi-analytics/eto/scripts/quarterly_metrics.py, psi-analytics/eto/scripts/build_dashboard.py