Weld Shop Utilization — OP 70 / OP 71
Direct vs indirect hours for PSI’s weld/fab department (dept 104), scored per week and averaged by month, back to 1992. Answers “how much of our welders’ time is booked to a work order rather than to overhead, and how has that moved as the crew changed?”
| Datasets | psi-analytics/eto/data/welder_utilization_weekly.csv (1,802 weeks) · welder_utilization_monthly.csv (421 months) · welder_efficiency_annual.csv · welder_roster_validation.csv |
| Report | psi-analytics/eto/reports/welder_utilization.html (self-contained, Chart.js) |
| Build scripts | eto/scripts/welder_utilization.py → CSVs · eto/scripts/build_welder_report.py → HTML |
| Refresh | From the repo root: python eto\scripts\welder_utilization.py && python eto\scripts\build_welder_report.py (~2 min; streams a 367 MB CSV) |
| Scope | Department 104; welding proper is OP 70 (BENCH WELDING) + OP 71 (FLOOR WELDING) |
Read this first — why this analysis does not use labor_detail
psi_analytics.db.labor_detail cannot produce a utilization metric. The pipeline builds it as labor by project, so it keeps only timecard rows that carry a Job number. Every indirect row is dropped at extract: BREAKTIME, TRAINING, MISC, CLEAN & SWEEP, LEAD PERSON, MEETINGS, MAINTENANCE, DOWNTIME — all of which appear in AFTEC with a blank Job and the department indirect work order 5010-104.
Those dropped rows are exactly the denominator of a billable rate. The size of the hole:
| Source | Dept-104 employee-week hours (median, 2023+) |
|---|---|
labor_detail (job-charged only) | 26.5 |
raw tslabor2.csv (full timecard) | 37.4 |
So this analysis streams the raw AFTEC export instead. See PSI Data Brain for the wider source map.
The raw AFTEC export directory
\\ad.ptihome.com\DFS\DATA\unixshare\dgwreportfiles\ holds the ~31 pipe-delimited AFTEC tables that psi-data-pipeline/pipeline/sync_aftec_raw.py pushes to Azure SQL for Power BI. Three matter here and are useful well beyond this page:
| File | What it gives you |
|---|---|
tslabor2.csv | The full timecard — 2.83M rows since 1990, direct and indirect. 367 MB; stream it, don’t load it. |
employeecount.csv | Monthly headcount by department since 1989 (Emp No, MonthEndDate, Dept). The authoritative roster/FTE denominator. |
wc-capacity.csv | Declared weekly work-center capacity by op code — currently OP 70 = 125 hrs/wk, OP 71 = 100 hrs/wk (halved on holiday weeks). Forward-looking only (starts at the current week), so it is a present-day reference, never a historical denominator. |
The headline metric is direct vs indirect
Direct = any hour booked to a work order / job. Indirect = the overhead bucket, no job number at all (WO 5010-104): clean & sweep, training, breaktime, misc, lead person, meetings, maintenance, downtime.
DirectPct = direct hrs / hours present <- the headline
IndirectPct = 1 - DirectPct
Direct then breaks down by what kind of job absorbed it. This is a breakdown for interest, not a billability judgment — all three are direct work on a work order:
| Bucket | Rule | Lifetime hours |
|---|---|---|
| Direct — customer | Job is a Project1287 project for an outside account | 352,194 |
| Direct — stock | Job ends in -Stk (M01304-Stk, 100HE-Stk, BLDG4-Stk, B4THRM-Stk) — make-to-stock parts and PSI’s own Building-4 equipment builds | 16,739 |
| Direct — internal | Job is a Project1287 project whose AccountName starts “Progressive Surface” | 10,911 |
| Indirect | No Job at all (WO 5010-104) | 144,861 |
CustomerPctOfDirect tracks the mix inside direct — it fell to 64% in 2021 when internal and stock work backfilled a thin order book, against ~89% in a normal year.
Are the percentages calculated against FTEs?
No — every percentage is hours ÷ hours: direct hours divided by hours present on the timecard. Headcount does not enter any of them, so a change in crew size cannot move the direct rate.
Headcount appears only where it is named:
| Measure | Denominator |
|---|---|
DirectPct, IndirectPct, CustomerPctOfDirect, WeldPctOfPresent | hours (hours present, or direct hours) |
DirectHrsPerFilerPerWeek, PresentHrsPerFilerPerWeek | people who worked in the dept that week (≥8 hrs) |
DirectHrsPerFTEPerWeek, PresentHrsPerFTEPerWeek | roster headcount from employeecount.csv |
CapacityUtilPct, DirectUtilPct | roster × 40 hrs — the one ratio with an FTE-derived denominator |
Two data-regime caveats that bound any long trend
1. Indirect coding changed in 2014-2015
The granular indirect op codes first appear in 2014 and are only fully in use from 2015. The scheme is dept-suffixed — 61X BREAKTIME, 62X MISC, 68X TRAINING, 63X LEAD PERSON, 51X CLEAN & SWEEP, 66X MEETINGS, 80X MAINTENANCE, 60X DOWNTIME, 70X BEREAVEMENT, where the last digit maps to the department (614 = BREAKTIME for dept 104).
Measured dept-104 indirect: ~2.4k hrs/yr before 2014 vs ~7–11k after. That is a coding change, not a behaviour change. Pre-2015 billable % is biased high and is not comparable to 2015+. The report’s billable-% chart starts at 2015; the hours-per-head charts run the full 20 years because they don’t depend on the indirect split.
2. There are no paid-absence op codes — but the hours do exist, one level up
tslabor2 has no VACATION / HOLIDAY / PTO / SICK op codes (only trace BEREAVEMENT), so “hours present” here is attendance, not payroll. Approved time off is absent from the denominator rather than counted against anyone — the behaviour you want for a utilization metric, but it means this is not a payroll reconciliation.
That is a property of this export, not of AFTEC. The timesheet header files — TS.HEAD.1287 (live) and LABORHIST.HEAD (posted) — carry REG.HRS, OT.HRS, DT.HRS, SICK.HRS, VAC.HRS, HOL.HRS and MISC.HRS per employee per day, alongside SHIFT and GRADE. tslabor2.csv is flattened from the line file, so none of it comes through. Anything needing paid hours, overtime, or true attendance must read the header files via the UniData API (/api/data/dev/LABORHIST.HEAD/{CO}!{card}) — verified live 2026-08-10.
Two consequences: overtime is invisible in tslabor2 (there is no OT flag on the line, only OT.HRS on the header), and a genuine payroll-vs-utilization reconciliation is possible — it just needs a second source.
3. Roster is department-wide, and it is not the whole crew
employeecount.csv resolves to department, so roster FTE is “people whose home department is 104” — which includes saw, punch, bend and shear operators, and excludes people from other shops who charge time into 104. “Per welder” therefore means “per person working in the weld/fab department”.
Roster validation — does employeecount.csv match who actually filed a timesheet?
welder_roster_validation.csv reconciles the two populations per month, both directions. It validates, with one structural caveat.
| Check (last 12 complete months) | Result |
|---|---|
| Roster headcount (dept 104) | 11.1 |
| Distinct people who filed dept-104 time | 13.3 |
| On roster but filed no dept-104 timecard | 0.1 / month |
| Filed dept-104 time but not on the 104 roster | 2.3 / month |
| Filer / roster ratio | ~1.20× |
- The roster is real and current — essentially nobody on it fails to file a timesheet, so there are no ghost/terminated employees inflating it.
- But the weld dept constantly borrows labour. The 2.3 non-roster filers per month come from dept 101, dept 102 (machine shop) and dept 106 (mech assembly). Dividing dept-104 hours by the 104 roster alone therefore overstates output per head by roughly 20%. Per-head figures are emitted against both denominators; the report leads with the filer-based one.
The employee-number join bug (read this before joining these files)
tslabor2.csv zero-pads employee numbers to four characters (0576); employeecount.csv and employee.csv do not (576). A raw join matches almost nothing — and fails silently in a way that looks like a finding: it reports ~7.6 roster members per month who never file a timesheet and ~12.5 filers per month who aren’t on any roster. Normalize with str(int(x)) on both sides (norm_emp() in the script).
Naming them — the identity join chain
welder_people.py → welder_people.csv resolves every person who charged dept-104 time into a named, departmented, identity-verified row. This join chain is reusable for any labor-by-person question, not just welding:
| Step | Source | Gives |
|---|---|---|
| 1 | tslabor2.csv Employee Name | Full name verbatim from the timecard — works for terminated people and all history, no auth |
| 2 | GET /api/redbook/dev/lookups/employees (UniData API, open, no auth) | EmpID → name, home department, AD userId (3-letter), isActive — the AFTEC employee master, 1,993 records |
| 3 | GET /api/data/dev/EMPLOYEE.PUBLIC.1287/export | AFTEC job TITLE (sparse) |
| 4 | Microsoft Graph /users on employeeId / onPremisesSamAccountName | UPN, accountEnabled, Entra job title + department |
| 5 | employeecount.csv | Month-by-month home department, so an inter-shop transfer is visible |
Match rate on the 26 dept-104 people (24-month window): 26/26 named, 26/26 matched to an Entra account, 26/26 present in the AFTEC master. The identity data is clean.
Two gotchas:
norm_emp()is mandatory on every join (see the zero-padding bug above).- Entra stores department as
"104 - Welding", AFTEC stores"104". Compare the leading code, or every correctly-matched person reads as a mismatch.
The same chain is available as an app: PSI Portal → User Explorer (/users, alpha-gated) is the AFTEC↔Entra identity view, and pcs_person_directory (UPN ↔ SAM ↔ department ↔ job title ↔ employee_id, refreshed by User Explorer’s load) is the same data in Azure SQL. aftec-entra-recon (C:\git\aftec-entra-recon, python reconcile.py) is the batch reconciliation with delta reporting.
Role vs trade — the department code does not tell you the job
Two independent classifications are emitted, because they disagree:
- Role — by home department:
core(dept 104),borrowed(another shop),visitor(<40 hrs in window). - Trade — by actual op mix:
welder(≥25% of dept-104 hours on OP 70/71),fab (saw/punch/bend),indirect only.
In the 24 months to 2026-08, 16 people were core to dept 104 but only 14 of the 26 actually weld:
| Trade (by op mix) | People | Dept-104 hrs | Weld hrs | Indirect % |
|---|---|---|---|---|
| welder | 14 | 31,817 | 19,741 | 38% |
| fab (saw/punch/bend) | 3 | 6,186 | 11 | 29% |
| indirect only | 1 | 504 | 0 | 100% |
Concrete disagreements worth knowing: two people titled “Saw Operator” sit on dept 104 and book 0% weld ops (one of them spends most of his time in dept 150 Supply Chain); one titled “Welder” books 0.5% of his dept-104 hours on OP 70/71 and 2,144 hrs in dept 102 Machining; and a Manufacturing Engineer whose home dept is 101 books 504 hrs into dept 104, 100% indirect. So “welder headcount” ≠ “dept-104 roster.”
Indirect is concentrated, not uniform
Department-wide indirect is ~35–37%, but per person it ranges from ~20% to 100%. The high end is explained rather than mysterious:
- a Weld Team Lead books op 634 LEAD PERSON (42% indirect)
- a Manufacturing Engineer books only overhead into the department (100%)
- a Saw Operator whose real job is Supply Chain books almost all his dept-104 time to indirect (99%)
- the newest hires run 45–50% indirect while training
Turnover explains much of the recent per-head dip: 4 welders left in the 24-month window (last charges 2025-07 through 2026-04), three of them carrying above-average indirect in their final months, and were replaced by 3 new hires with 8–11 weeks of tenure. Replacing experienced welders with new ones mechanically raises indirect and lowers direct hours per head. Chasing the department average is the wrong move — the per-person column is where the decisions are.
One record to fix
EmpID 1719 is department 102 (Machining) in AFTEC but 104 - Welding in Entra. He is titled “Weld”, welds 56% of his dept-104 hours, and splits time between the two shops. Entra looks correct and AFTEC looks stale — correcting AFTEC moves him from borrowed to core and raises the dept-104 roster by one.
Counting people: use substantive filers, not distinct filers
17.4% of employee-months carry under 20 hours in dept 104 — someone dipping in for an hour or two. A raw distinct-person count treats them as a whole welder and depresses every per-head figure. SubstantiveFilers counts only people with ≥8 hours (one day) in the week; EmpsCharging keeps the raw count for comparison.
Metric definitions
PresentHrs = all dept-104 timecard hours (Setup + Labor), direct + indirect
DirectHrs = hours booked to a work order / job of any kind
IndirectHrs = hours with a blank Job (WO 5010-104)
DirectPct = DirectHrs / PresentHrs # the headline
IndirectPct = IndirectHrs / PresentHrs
CustomerPctOfDirect = CustomerHrs / DirectHrs # mix within direct
WeldPctOfPresent = OP 70+71 hrs / PresentHrs
DirectHrsPerFiler = DirectHrs / SubstantiveFilers # per week — the preferred comparable
PresentHrsPerFiler = PresentHrs / SubstantiveFilers # attendance per head vs the 40-hr standard
DirectHrsPerFTE = DirectHrs / RosterHeadcount # roster-based variant (~20% flattering)
HoursFTE = PresentHrs / 40 # FTE the timecard actually accounts for
Efficiency = routed (standard) hrs / actual hrs # wiplabor.csv, by WO completion date
Weekly → monthly, two ways. The native grain is AFTEC’s WE Date (week-ending). Monthly figures are emitted both hours-weighted (DirectPctWtd — the true ratio for the month) and as the plain mean of that month’s weekly scores (DirectPctMeanOfWeeks). They diverge in December and July because an unweighted mean treats a 4-hour holiday week the same as a full one; weeks under 50% of roster capacity are flagged ShortWeek so they can be excluded.
Findings (as of 2026-08-03)
The direct rate is stable. About a third of clocked time is indirect, and that is the steady state.
| Year | Roster | Filers | Present hrs | Direct hrs | Indirect hrs | Direct % | Cust % of direct | Direct/filer/wk | Present/filer/wk |
|---|---|---|---|---|---|---|---|---|---|
| 2015 | 8.3 | 9.6 | 22,483 | 14,949 | 7,533 | 66.5% | 94% | 29.9 | 44.9 |
| 2017 | 9.2 | 11.2 | 25,432 | 16,975 | 8,457 | 66.8% | 94% | 29.7 | 44.1 |
| 2019 | 9.1 | 10.2 | 20,966 | 12,689 | 8,278 | 60.5% | 91% | 23.8 | 39.5 |
| 2021 | 7.3 | 9.3 | 15,510 | 8,328 | 7,182 | 53.7% | 64% | 17.7 | 32.5 |
| 2023 | 12.4 | 13.6 | 27,148 | 16,034 | 11,114 | 59.1% | 88% | 23.0 | 38.6 |
| 2024 | 10.0 | 10.7 | 18,340 | 11,093 | 7,248 | 60.5% | 84% | 20.1 | 33.0 |
| 2025 | 10.5 | 10.0 | 19,387 | 12,372 | 7,015 | 63.8% | 92% | 23.8 | 37.2 |
| 2026* | 11.1 | 10.0 | 12,156 | 8,040 | 4,116 | 66.1% | 86% | 26.8 | 39.9 |
partial year. Pre-2015 rows show 77–87% direct — that is the coding-regime artifact, not a better shop.
- The direct rate has not deteriorated. Every year since 2015 sits between 53.7% and 66.8% direct; TTM to 2026-08 is 65% (12,835 direct / 6,849 indirect of 19,684 present). Roughly a third of clocked time going to indirect is the department’s steady state. If that third is the target, it is a standing-cost question, not a trend to reverse.
- Per-head direct output moved less than headcount did. 26.4 direct hrs/welder/week TTM, against a 29.9 high (2015) and a 17.7 low (2021); 2024 was the other weak year. 2026 is running back at the top of the post-2015 range. Measuring against the roster instead of actual filers would report ~20% higher and exaggerate the decline.
- 2021 is the trough on every measure: direct fell to 53.7% and customer work to 64% of direct as internal and stock builds backfilled a thin order book.
- Estimate accuracy is not the problem. Routed-vs-actual on completed weld operations runs 94–102% every year from 2017 — welders are not taking longer than quoted on the parts they weld. The indirect share, not time-per-weldment, is where the hours go.
- Only ~53% of clocked time is actual welding. The rest of the direct time is saw, punch, bend, shear and inspection. Dept 104 is a fab department, not a weld-only cell — judge it on total direct, not weld hours alone.
Known limitations
wiplabor.csvcoverage is partial — it holds open + recent WIP, so the efficiency series only starts in 2017 and covers ~32% of lifetime dept-104 actual hours. It is directional, not a census.- No shift or overtime-premium split. Hours are hours; a Saturday hour and a Tuesday hour are identical here.
-Stkclassification is by job-number suffix, which is reliable but coarse:BLDG4-Stk(PSI’s own building) andM01304-Stk(a stock part) land in the same bucket despite being different kinds of work. Both are direct either way, so this only affects the within-direct mix.- The 8-hour substantive-filer threshold is a judgment call. It is set at one day in the department. Raising it would drop genuinely part-time contributors; lowering it lets one-hour visitors count as whole welders.
EmpsCharging(raw distinct count) is emitted alongside so the sensitivity is visible. - Not in the nightly pipeline. Like quarterly business metrics, this is manually triggered, so the artifacts go stale silently — re-run before presenting.
Related
- Quarterly Business Metrics — the same repo’s revenue/backlog view; shares
lib/psi_data.py - Lead Time Analysis — February 2026 — identifies engineering / purchasing / electrical assembly as the systemic bottlenecks
- PSI Data Brain — full data-source map
- Project1287 Data Patterns — read before writing anything against
PROJECT.1287
Created: 2026-08-03
Source: psi-analytics/eto/scripts/welder_utilization.py, build_welder_report.py