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	`C:\git\psi_quarterly_metrics.csv` (66 quarters × 19 columns)
Dashboard	`C:\git\psi_quarterly_metrics.html` (self-contained, Chart.js)
Build scripts	`C:\git\quarterly_metrics.py` → CSV · `C:\git\build_dashboard.py` → HTML
Refresh	`python C:\git\quarterly_metrics.py && python C:\git\build_dashboard.py`
Universe	2,376 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
Booked sales per quarter — new orders signed in the quarter
Backlog at end of quarter — projects in flight (booked, not yet shipped)
“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.

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 ShipDate AND no WarrentyStartDate AND 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.

Active Builds

Backlog minus soup line.

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(projects with PlannedShipDate populated)

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 PlannedShipDate in 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:

WarrentyStartDate fallback recovered 31 historical projects that shipped but had blank ShipDate
ProjectStatus == '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`
`OnTimeEligible`	int	Count of ships with `PlannedShipDate` populated

Recurring patterns to watch for in the dashboard

Q4 booking surge, intensifying year over year

Every Q4 in the dataset shows a customer year-end PO-release surge. Bookings cluster into Oct-Dec, then the just-booked projects sit in the queue as “Queued” (no labor yet) at Q4 EOQ. Engineering kicks off on them through Q1, and they convert to “Active” in the early-year quarters.

Year	Q4 Bookings	Q4 EOQ Queue (projects)	Q4→Q1 active conversions
2021	(high)	76	+48
2022	(high)	85	+58
2023	18	76	+49
2024	25	54	+31
2025	42 (record)	71	+46

The Q4 booking concentration is intensifying — 2025-Q4’s 42 bookings is the highest in the dataset, 2.3× 2023-Q4. Combined with constant engineering capacity, this means:

Every January is the heaviest engineering kickoff month of the year
Controls Engineering (the bottleneck per lead time analysis) takes the brunt
The pattern is structural, not incidental — sales/sales-ops should expect it and plan around it

What it looks like on the dashboard

In the Backlog Composition chart, every Q4 shows a tall red (Queued) segment that collapses by Q1 as projects shift to green (Active). The bigger the booking surge, the more dramatic the swing. 2025-Q4 is the clearest example: 71 Queued / 10 Active = 88% of backlog is fresh-not-started.

Known limitations

OrderValue and OrderTotal are 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.) — extend quarterly_metrics.py to add breakdowns.
Pre-2010 data thins out. Project coverage in Project1287List.xml drops 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.

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 C:\git\quarterly_metrics.py and integrate as a step in nightly-data-build.yml on psi-data-pipeline.

# Refresh CSV (reads live FileCache XML + nightly psi_analytics.db)
python C:\git\quarterly_metrics.py
 
# Rebuild HTML dashboard
python C:\git\build_dashboard.py
 
# Open
start C:\git\psi_quarterly_metrics.html

The 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.

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.db and labor_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 Source: C:\git\quarterly_metrics.py, C:\git\build_dashboard.py

PSI Knowledge Base

Explorer

quarterly-business-metrics

Quarterly Business Metrics

What the dataset answers

Data sources

Metric definitions

Shipped Revenue

Booked Sales

Backlog (end-of-quarter)

Soup line

Active Builds

Book-to-Bill Ratio

Lead Time & On-Time

Validation anchors

Schema

Recurring patterns to watch for in the dashboard

Q4 booking surge, intensifying year over year

What it looks like on the dashboard

Known limitations

Refresh workflow

Graph View

Table of Contents

Backlinks

PSI Knowledge Base

Explorer

quarterly-business-metrics

Quarterly Business Metrics

What the dataset answers

Data sources

Metric definitions

Shipped Revenue

Booked Sales

Backlog (end-of-quarter)

Soup line

Active Builds

Book-to-Bill Ratio

Lead Time & On-Time

Validation anchors

Schema

Recurring patterns to watch for in the dashboard

Q4 booking surge, intensifying year over year

What it looks like on the dashboard

Known limitations

Refresh workflow

Related

Graph View

Table of Contents

Backlinks