Supply Chain Cost Intelligence

SQL-driven supplier analytics on US federal procurement data

Author

Alvin Alias

Published

June 11, 2026

1 Executive Summary

This report analyzes 100,000 federal procurement contracts from USAspending.gov (FY2023, manufacturing NAICS 33), representing $42.5B in government spend across 11,257 unique vendors and 214 NAICS categories.

Using SQL window functions and K-means clustering, I identified four vendor performance tiers and quantified $182M in actionable cost reduction — scope-comparable vendor substitution at a conservative 30% re-sourcing rate. The unfiltered price-gap screen totals $8.6B, but most of that sits in defense platform contracts where substitution is structurally impossible; separating the two is the analytical judgment this report demonstrates.

The business parallel: I’ve run a structurally identical analysis manually — a BOM cost analysis for a major HVAC manufacturer, tracing supplier re-sourcing opportunities and lead-time-driven safety stock across weeks of spreadsheet work. This project shows that analysis as a reproducible, scalable data system.

Key findings:

  • Top 1.2% of vendors account for 80% of total spend — far steeper than the classic 80/20 rule
  • 1,456 awards (1.5%) exhibit significant price anomalies (|z-score| > 2)
  • 64 vendors classified as Underperforming (high cost + slow delivery)
  • Conservative, scope-comparable savings opportunity: $182M (30% re-source rate, 507 vendor-category pairs)

2 Data

Source: USAspending.gov Award Data Archive — FY2023 federal contracts, filtered to manufacturing (NAICS 33), awards ≥ $1,000, capped at 100K rows (scripts/fetch_data.py reproduces the slice). The row cap lands on the FY-end window (action dates Aug 21 – Sep 30, 2023) — the period when federal obligation volume peaks.

Why this dataset: Federal procurement data is public, real, at scale (millions of records), and structurally identical to private-sector supplier intelligence — same cost / vendor / category schema as a corporate BOM. Choosing real data over synthetic is itself a demonstration of analytical competence.

Code 
from src.data_loader import get_awards_summary
get_awards_summary(conn)
Table 1: Dataset summary
total_awards unique_vendors unique_naics total_spend_billions earliest_date latest_date avg_lead_time_days
0 100000 11257 214 42.539936 2023-08-21 2023-09-30 170.917537

3 Spend Distribution and Vendor Concentration

3.1 Pareto Analysis

The spend distribution is highly right-skewed — consistent with federal procurement patterns where a small number of large contractors dominate total spend. The Pareto analysis below shows concentration far beyond the classic 80/20 rule: the top 1.2% of vendors account for 80% of total spend, driven by defense primes in aircraft, shipbuilding, and missiles.

Code 
from IPython.display import Image
Image('../figures/01_pareto_concentration.png')
Figure 1: Vendor spend concentration (Pareto)

Implication: Optimization effort should concentrate on the top-spending vendors — but with a caveat this analysis carries through to the savings estimate: the very top of the distribution is dominated by sole-source platform contracts that cannot be re-sourced on price. The actionable opportunity lives in the next tier down, where multiple qualified vendors compete within the same category.

4 SQL Analysis

4.1 Vendor Performance Ranking

The following query ranks vendors within their NAICS category by cost efficiency × lead time, using CTEs and SQL window functions — the same patterns procurement analysts use for benchmark-and-rank work in any BI stack.

Code 
vendor_sql = open('../sql/01_vendor_performance.sql').read().split('-- ── Save')[0]
vendor_df = conn.execute(vendor_sql).df()
vendor_df.head(20)
Table 2: Top 20 vendors by composite efficiency score
recipient_name naics_code naics_description award_count lifetime_spend avg_award median_award avg_lead_time_days std_lead_time_days last_award_date ... naics_spend_share naics_total_spend cost_rank_asc speed_rank_asc volume_rank naics_vendor_count composite_efficiency_score is_low_cost is_high_cost is_fast_delivery
0 STAINLESS SHAPES INC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 7 27230.67 3890.10 3267.05 77.7 15.6 2023-09-18 ... 0.0017 15599237.17 1 5 4 40 0.0750 1 0 0
1 HURLEN CORPORATION 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 9 37455.87 4161.76 2762.50 71.9 23.3 2023-09-27 ... 0.0024 15599237.17 2 4 3 40 0.0750 1 0 1
2 PIERCE ALUMINUM COMPANY, INC. 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 3 18270.34 6090.11 5418.98 29.3 27.6 2023-09-27 ... 0.0012 15599237.17 3 1 8 40 0.0500 1 0 1
3 BAYFRONT METAL PRODUCTS LLC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 5 31615.00 6323.00 6345.00 90.2 0.4 2023-09-21 ... 0.0020 15599237.17 4 7 6 40 0.1375 1 0 0
4 MGB ASSOCIATED SERVICES INC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 3 26059.31 8686.44 9308.60 31.0 13.5 2023-09-28 ... 0.0017 15599237.17 5 2 8 40 0.0875 0 0 1
5 RUDY III, ERNEST 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 11 101327.90 9211.63 6138.00 123.7 63.6 2023-09-26 ... 0.0065 15599237.17 6 8 2 40 0.1750 0 0 0
6 ORION-METCO LLC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 5 60297.30 12059.46 12770.00 240.6 126.9 2023-09-21 ... 0.0039 15599237.17 7 9 6 40 0.2000 0 0 0
7 SUPPLYCORE LLC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 43 1262405.43 29358.27 13861.06 44.0 41.7 2023-09-26 ... 0.0809 15599237.17 8 3 1 40 0.1375 0 0 1
8 SUPER ROCO STEEL & TUBE, LTD. II 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 3 267993.60 89331.20 90049.32 81.7 24.1 2023-09-07 ... 0.0172 15599237.17 9 6 8 40 0.1875 0 1 0
9 BPD ENGINEERING, LLC 331110 IRON AND STEEL MILLS AND FERROALLOY MANUFACTURING 6 1415611.07 235935.18 219156.52 923.7 5.1 2023-09-28 ... 0.0907 15599237.17 10 10 5 40 0.2500 0 1 0
10 AIRCRAFT TUBULAR COMPONENTS INC 331210 IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... 4 22659.59 5664.90 4579.83 150.5 61.0 2023-09-28 ... 0.0043 5292172.79 1 3 4 61 0.0328 0 0 0
11 AL-TECH ASSOCIATES, INC. 331210 IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... 5 38800.74 7760.15 3186.54 45.8 0.4 2023-09-28 ... 0.0073 5292172.79 2 1 3 61 0.0246 0 0 1
12 BB&G ENTERPRISES INC 331210 IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... 24 207644.84 8651.87 5036.46 174.2 116.9 2023-09-27 ... 0.0392 5292172.79 3 4 1 61 0.0574 0 0 0
13 RUDY III, ERNEST 331210 IRON AND STEEL PIPE AND TUBE MANUFACTURING FRO... 10 90598.86 9059.89 3846.16 142.7 108.3 2023-09-20 ... 0.0171 5292172.79 4 2 2 61 0.0492 0 0 0
14 BAYFRONT METAL PRODUCTS LLC 331221 ROLLED STEEL SHAPE MANUFACTURING 3 9950.00 3316.67 1700.00 90.0 0.0 2023-09-13 ... 0.0046 2185068.48 1 7 7 36 0.1111 1 0 0
15 STAINLESS SHAPES INC 331221 ROLLED STEEL SHAPE MANUFACTURING 3 22686.00 7562.00 3582.00 75.3 15.5 2023-09-26 ... 0.0104 2185068.48 2 5 7 36 0.0972 0 0 1
16 BB&G ENTERPRISES INC 331221 ROLLED STEEL SHAPE MANUFACTURING 12 110535.10 9211.26 7434.55 106.7 50.6 2023-09-25 ... 0.0506 2185068.48 3 9 2 36 0.1667 0 0 0
17 MASTERSOURCE CO INC 331221 ROLLED STEEL SHAPE MANUFACTURING 5 53759.00 10751.80 9550.00 34.0 7.4 2023-09-12 ... 0.0246 2185068.48 4 2 5 36 0.0833 0 0 1
18 HURLEN CORPORATION 331221 ROLLED STEEL SHAPE MANUFACTURING 10 141118.79 14111.88 7666.00 69.7 19.9 2023-09-29 ... 0.0646 2185068.48 5 4 3 36 0.1250 0 0 1
19 BRIGHT HORIZON METALS, INC. 331221 ROLLED STEEL SHAPE MANUFACTURING 3 42650.00 14216.67 13625.00 11.7 4.0 2023-09-11 ... 0.0195 2185068.48 6 1 7 36 0.0972 0 0 1

20 rows × 21 columns

4.2 Price Anomaly Detection

Z-score analysis identifies awards priced abnormally relative to their NAICS category distribution.

Code 
anomaly_sql = open('../sql/02_price_anomalies.sql').read().split('-- ── Output 2')[0]
anomalies = conn.execute(anomaly_sql).df()
award_count = conn.execute("SELECT COUNT(*) FROM federal_awards WHERE action_amount > 0").fetchone()[0]
print(f"Anomalous awards: {len(anomalies):,} ({len(anomalies)/award_count*100:.1f}% of awards)")
anomalies.head(15)
Anomalous awards: 1,456 (1.5% of awards)
Table 3: Price anomalies: |z| > 2
recipient_name naics_code naics_description action_date action_amount category_mean category_median z_score iqr_ratio anomaly_type agency_name
0 NOBLE SUPPLY & LOGISTICS, LLC 332722 BOLT, NUT, SCREW, RIVET, AND WASHER MANUFACTURING 2023-08-30 1.100286e+07 12072.17 3031.82 67.684 2283.728 overpriced Department of Defense
1 IGOV TECHNOLOGIES, INC. 339999 ALL OTHER MISCELLANEOUS MANUFACTURING 2023-09-15 1.252539e+08 217912.68 34512.76 61.366 1034.554 overpriced Department of Defense
2 LOCKHEED MARTIN CORPORATION 336413 OTHER AIRCRAFT PARTS AND AUXILIARY EQUIPMENT M... 2023-09-12 2.977303e+08 618655.28 14196.19 50.054 5105.186 overpriced Department of Defense
3 NEW YORK EMBROIDERY STUDIO INC. 339113 SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING 2023-09-21 1.488960e+08 142134.67 17130.08 49.960 5882.680 overpriced Department of Health and Human Services
4 GENERAL ELECTRIC COMPANY 332510 HARDWARE MANUFACTURING 2023-09-30 2.829695e+07 48283.31 3917.98 46.290 2452.252 overpriced Department of Defense
5 IRON BOW TECHNOLOGIES, LLC 334111 ELECTRONIC COMPUTER MANUFACTURING 2023-08-30 7.084946e+07 295455.10 42900.00 41.935 564.715 overpriced Department of Veterans Affairs
6 EXECUTIVE PROTECTION SYSTEMS LLC 336111 AUTOMOBILE MANUFACTURING 2023-09-08 9.206174e+06 68330.80 42061.00 38.803 535.381 overpriced Department of State
7 DETROIT DEFENSE, INC. 339999 ALL OTHER MISCELLANEOUS MANUFACTURING 2023-08-29 7.775438e+07 217912.68 34512.76 38.054 642.117 overpriced Department of Defense
8 NEW YORK EMBROIDERY STUDIO INC. 339113 SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING 2023-09-25 1.077240e+08 142134.67 17130.08 36.132 4255.843 overpriced Department of Health and Human Services
9 ELECTRIC BOAT CORPORATION 336611 SHIP BUILDING AND REPAIRING 2023-09-15 5.172488e+08 1396142.90 80263.00 33.792 1481.022 overpriced Department of Defense
10 THE BOEING COMPANY 336411 AIRCRAFT MANUFACTURING 2023-09-28 1.713444e+09 4041338.19 8007.68 32.510 14695.341 overpriced Department of Defense
11 LOCKHEED MARTIN CORPORATION 334519 OTHER MEASURING AND CONTROLLING DEVICE MANUFAC... 2023-09-26 9.526186e+07 277510.29 19616.48 31.687 1636.975 overpriced Department of Defense
12 RTX CORPORATION 332510 HARDWARE MANUFACTURING 2023-09-27 1.834581e+07 48283.31 3917.98 29.983 1589.753 overpriced Department of Defense
13 NORTHROP GRUMMAN SYSTEMS CORPORATION 334511 SEARCH, DETECTION, NAVIGATION, GUIDANCE, AERON... 2023-09-28 3.459215e+08 2100466.66 92627.50 29.430 664.332 overpriced Department of Defense
14 XEROX CORPORATION 333316 PHOTOGRAPHIC AND PHOTOCOPYING EQUIPMENT MANUFA... 2023-09-15 2.433729e+06 11940.52 2243.30 28.659 834.152 overpriced Department of Defense

5 Supplier Segmentation

5.1 Clustering Methodology

K-means clustering groups vendors into performance tiers based on: cost percentile · lead time percentile · award frequency · spend concentration.

Code 
from IPython.display import Image
Image('../figures/02_elbow_silhouette.png')
Figure 2: Elbow and silhouette analysis for optimal k

Optimal k = 4, silhouette score 0.271 — four tiers matching the natural business segmentation: Premium Reliable (566 vendors), Cost-Efficient (1,475), Risky / Volatile (1,243), and Underperforming (64).

5.2 Segment Profiles

Code 
vendor_segmented = pd.read_parquet('../data/processed/vendor_segmented.parquet')
vendor_segmented.groupby('segment_name').agg(
    vendor_count=('recipient_name', 'nunique'),
    total_spend=('lifetime_spend', 'sum'),
    avg_award=('avg_award', 'mean'),
    avg_lead_time=('avg_lead_time_days', 'mean'),
).round(0)
Table 4: Vendor segment profiles
vendor_count total_spend avg_award avg_lead_time
segment_name
Cost-Efficient 1475 4.776472e+08 42710.0 120.0
Premium Reliable 566 1.867790e+10 475752.0 179.0
Risky / Volatile 1243 8.458073e+09 764523.0 280.0
Underperforming 64 7.877465e+09 5882265.0 370.0

5.3 Cluster Visualization

Code 
from IPython.display import HTML
HTML(open('../figures/02_cluster_scatter.html').read())
Figure 3

6 Cost Reduction Opportunities

Code 
opp_sql = open('../sql/03_cost_opportunities.sql').read().split('-- ── Opportunity 2')[0]
opps = conn.execute(opp_sql).df()
screened = opps['conservative_savings_30pct'].sum()

# Scope-comparability filter: the p25 benchmark vendor is only a credible substitute
# if its price is within 10x of the incumbent's. Without this filter the "savings"
# are dominated by defense platforms where no substitute exists.
actionable = opps[opps['target_avg_award'] >= 0.1 * opps['avg_award_current']]
print(f"Raw price-gap screen:            ${screened/1e9:,.2f}B  ({len(opps)} vendor-category pairs)")
print(f"Scope-comparable (actionable):   ${actionable['conservative_savings_30pct'].sum()/1e6:,.1f}M  ({len(actionable)} pairs)")
actionable.head(20)
Raw price-gap screen:            $8.63B  (1284 vendor-category pairs)
Scope-comparable (actionable):   $182.5M  (507 pairs)
Table 5: Top scope-comparable vendor substitution opportunities (conservative: 30% re-sourcing)
naics_code naics_description high_cost_vendor current_spend avg_award_current target_avg_award price_premium_per_award awards_to_re_source gross_savings_full_switch conservative_savings_30pct cost_tier naics_alternatives_available
43 336414 GUIDED MISSILE AND SPACE VEHICLE MANUFACTURING THE BOEING COMPANY 1.446868e+08 10334774.84 2172577.87 8162196.97 14 1.142708e+08 34281227.28 High-Cost 11
97 334111 ELECTRONIC COMPUTER MANUFACTURING CDW GOVERNMENT LLC 4.379124e+07 521324.33 52137.85 469186.48 84 3.941166e+07 11823499.35 High-Cost 124
134 339113 SURGICAL APPLIANCE AND SUPPLIES MANUFACTURING ATLANTIC DIVING SUPPLY, INC. 2.555800e+07 131742.28 15365.24 116377.04 194 2.257715e+07 6773143.84 High-Cost 233
145 332722 BOLT, NUT, SCREW, RIVET, AND WASHER MANUFACTURING NOBLE SUPPLY & LOGISTICS, LLC 2.741172e+07 11769.74 3124.84 8644.90 2329 2.013397e+07 6040191.34 High-Cost 178
165 333120 CONSTRUCTION MACHINERY MANUFACTURING CATERPILLAR INC 2.223350e+07 404245.38 103228.00 301017.38 55 1.655596e+07 4966786.70 High-Cost 29
198 337215 SHOWCASE, PARTITION, SHELVING, AND LOCKER MANU... JPL & ASSOCIATES, LLC 1.353041e+07 193291.56 21374.62 171916.94 70 1.203419e+07 3610255.69 High-Cost 22
207 334111 ELECTRONIC COMPUTER MANUFACTURING EMERGENT, LLC 1.245912e+07 498364.96 52137.85 446227.10 25 1.115568e+07 3346703.28 High-Cost 124
209 337214 OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING STEELCASE INC. 1.330300e+07 154686.10 27039.73 127646.37 86 1.097759e+07 3293276.22 High-Cost 116
220 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING ROCHE DIAGNOSTICS CORPORATION 1.156327e+07 312520.73 42044.78 270475.95 37 1.000761e+07 3002283.03 High-Cost 159
227 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING AGILENT TECHNOLOGIES INC 1.333096e+07 156834.85 42044.78 114790.07 85 9.757156e+06 2927146.74 High-Cost 159
235 337214 OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING PRICE MODERN LLC 1.096872e+07 192433.60 27039.73 165393.87 57 9.427451e+06 2828235.18 High-Cost 116
252 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING THERMO ELECTRON NORTH AMERICA LLC 1.035538e+07 184917.58 42044.78 142872.81 56 8.000877e+06 2400263.17 High-Cost 159
254 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING ILLUMINA, INC. 1.066415e+07 161578.03 42044.78 119533.26 66 7.889195e+06 2366758.49 High-Cost 159
258 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING LIFE TECHNOLOGIES CORPORATION 9.442584e+06 214604.19 42044.78 172559.42 44 7.592614e+06 2277784.29 High-Cost 159
290 337214 OFFICE FURNITURE (EXCEPT WOOD) MANUFACTURING ALLSTEEL LLC 7.708304e+06 167571.83 27039.73 140532.10 46 6.464477e+06 1939342.97 High-Cost 116
312 332992 SMALL ARMS AMMUNITION MANUFACTURING THE KINETIC GROUP SALES LLC 1.098159e+07 156879.88 74354.40 82525.49 70 5.776784e+06 1733035.25 High-Cost 9
330 333120 CONSTRUCTION MACHINERY MANUFACTURING CERTIFIED STAINLESS SERVICE INC. 6.093860e+06 553987.25 103228.00 450759.25 11 4.958352e+06 1487505.53 High-Cost 29
331 334516 ANALYTICAL LABORATORY INSTRUMENT MANUFACTURING ABBOTT LABORATORIES INC. 6.057199e+06 208868.91 42044.78 166824.14 29 4.837900e+06 1451370.00 High-Cost 159
337 333120 CONSTRUCTION MACHINERY MANUFACTURING GROVE U.S. LLC 5.519380e+06 689922.55 103228.00 586694.55 8 4.693556e+06 1408066.92 High-Cost 29
346 334510 ELECTROMEDICAL AND ELECTROTHERAPEUTIC APPARATU... ALLIANT ENTERPRISES, LLC 5.111718e+06 189322.88 25604.38 163718.50 27 4.420400e+06 1326119.87 High-Cost 41

Methodology: Savings estimated as (current_avg_award - cost_efficient_benchmark) × award_count × 0.30. The 30% re-sourcing rate accounts for sole-source constraints, contractual obligations, and transition costs. The scope-comparability filter (benchmark price within 10× of current) removes structurally impossible substitutions — without it, the screen inflates to $8.6B by “re-sourcing” aircraft primes to small machine shops. Knowing which number to put in front of a VP is the judgment call that separates a screening query from a recommendation.

7 Limitations and Caveats

  1. Lead time proxy: Lead time derived from period_of_performance_end - action_date, not actual delivery date. This may overestimate lead time for contracts with options.

  2. No quality signal: Federal procurement data does not include defect rates or performance ratings. The clustering is cost × speed only.

  3. 30% re-source assumption: The savings estimate assumes 30% of Underperforming volume can be re-sourced — conservative but still an estimate. Actual recoverability depends on contract type (FFP vs. T&M), NAICS specialization, and competitive landscape.

  4. Scope: Analysis covers manufacturing (NAICS 33) federal contracts ≥ $1,000, capped at 100K rows which lands on the FY2023 year-end window (Aug 21 – Sep 30, 2023) — the highest-volume period of the federal fiscal year. Micro-purchases excluded. International procurement not covered.

  5. Award heterogeneity: Average award value is a coarse price proxy — awards within a NAICS category differ in scope and contract structure. The scope-comparability filter mitigates the worst distortions, but a production system would benchmark at the product/PSC level.

8 Conclusions

Supply chain cost intelligence is not a modeling problem — it is a data organization and SQL problem with clustering on top to make the output actionable.

The SQL-first approach here deliberately mirrors how procurement analysts actually work: aggregate by vendor and category, rank by KPIs, flag anomalies, then hand off to Python for the segmentation that SQL can’t do cleanly.

The output — named business segments with quantified dollar estimates, not cluster numbers — is the format that a procurement VP can act on next Tuesday morning.

Report generated with Quarto. Source: github.com/aalias01/supply-chain-cost-intelligence