Technical Documentation

Market Model Data Architecture

Complete documentation of 17 CSV data sources with sample records, data lineage, and how they power the $6.7B market intelligence model.

Data Lineage & Flow

Visual representation of how data flows from source systems through transformations to final outputs.

End-to-End Data Lineage

External Sources
IIR Database
ConstructConnect
Internal Sources
Job Cost System
Branch Master
↓
iir_project_list
cc_adjusted
Market_model_job_cost
dim_branch
main_offices
↓
Geographic Mapping
mm_msa
Branch Assignment
ind_plant_list_yard_current
iir_branch_assignment
cc_branch_assignment
↓
Industrial Aggregations
ind_capital
ind_maintenance
ind_turnarounds
ind_topline
Commercial Aggregations
comm_new_build
comm_alteration
comm_topline
↓
Industrial TAM ($4.2B)
Commercial TAM ($2.5B)
Territory Views
Persona Dashboards

TAM Calculation Methodology

Industrial TAM Formula
ind_capital + ind_maintenance + ind_turnarounds = $4.2B
Commercial TAM Formula
comm_new_build + comm_alteration = $2.5B
$6.7B
Total Addressable Market (2026)
1.9%
Projected Market Growth
63%
Industrial Segment Share
37%
Commercial Segment Share

Complete Dataset Reference

Detailed documentation for all 17 data files with sample records and lineage information.

Source

IIR Master Project List

iir_project_list_2025-12-22-1215.csv

Comprehensive project database from Industrial Info Resources (IIR). Contains detailed project scope, timing, contractors, status, and Total Installed Value (TIV) for industrial facilities across North America.

Data Lineage
Source: IIR External API
Feeds Into: ind_capital, ind_turnarounds, iir_branch_assignment
Refresh: Daily
Key Fields
PROJECT_ID PLANT_ID PROJ_TIV IND_DESC OWNER_NAME P_STATUS COMPLETION SCOPE NewCategory
Sample Records (3 rows)
PROJECT_ID IND_DESC PROJ_TIV OWNER_NAME P_STATUS NewCategory
300902851 Chemical Processing (CPI) $1,000,000 Valspar Coatings Operational Chemical Processing (CPI)
301104741 Power $5,300,000 Omaha Public Power District Operational Power (Coal)
300930832 Metals & Minerals $831,000,000 Vale Canada Limited Active Metals & Minerals
Market Model Usage
Primary source for Industrial capital projects and turnarounds. Project values are split by service type using industry benchmarks. PLANT_ID links to plant-to-yard mapping for territory assignment.
Source

ConstructConnect Projects

cc_adjusted_2025-12-22-1208.csv

Commercial construction projects from CMD/ConstructConnect database. Includes bid information, general contractors, project stages, and valuations for commercial buildings.

Data Lineage
Source: ConstructConnect SFTP
Feeds Into: comm_new_build, comm_alteration, cc_branch_assignment
Refresh: Daily
Key Fields
ProjectID Title Valuation_Value Stage PrimaryCategory WorkType GC_NAME StateProvince
Sample Records (3 rows)
ProjectID Title Valuation Stage Category GC_NAME
1007119761 School Renovations - Valley Park $16,500,000 Construction Underway High Schools Unknown
1007119461 Restroom Remodels - Harry Reid Airport $3,283,436 GC Award Airport Martin-Harris Construction
1007120590 Fannin County Justice Center $16,000,000 Construction Documents Courthouses Unknown
Market Model Usage
Primary source for Commercial TAM. Projects are classified as NewBuild or Alteration based on WorkType. Values are split by service type using category-specific benchmarks. Links to cc_branch_assignment for territory allocation.
Industrial

Industrial Topline Forecast

ind_topline_2025-12-22-1217.csv

Aggregated industrial market values by state, category, service type, and project funding. Contains monthly forecasts from 2021-2028 for trend analysis and TAM calculation.

Data Lineage
Source: ind_capital + ind_maintenance + ind_turnarounds
Feeds Into: Industrial TAM ($4.2B), Persona Dashboards
Grain: State + Category + Service Type + Funding + YardID
Key Fields
State NewCategory Service Type Project Funding Region YardID COUNTY 202501-202812 (Monthly Values)
Sample Records (3 rows)
State NewCategory Service Type Funding YardID 202501 202601
IL Food & Beverage Forming/Shoring Capital P:021301 $28.89 $28.89
IL Food & Beverage Insulation Capital P:021301 $230,769 $173,821
IL Food & Beverage Insulation Capital P:021301 $15,597 $11,748
Market Model Usage
Primary aggregation table for Industrial TAM. Sum of monthly columns provides segment-level market size. Used for Executive trend analysis, Sales Leadership resource allocation, and Branch Manager territory sizing.
Industrial

Industrial Capital Projects

ind_capital_2025-12-22-1216.csv

Capital expenditure projects from IIR database. New construction, expansions, and major upgrades at industrial facilities with project-level detail.

Data Lineage
Source: iir_project_list (filtered: Capital projects)
Feeds Into: ind_topline
Transform: TIV split by service type benchmarks
Key Fields
Project ID PLANT_ID IND_DESC Service Type start end Value yyyymm
Sample Records (3 rows)
Project ID IND_DESC Service Type start end PLANT_ID Value
300579936 Power Specialty 202807 203011 1015370 $25,862
300990511 Food & Beverage Scaffolding 202902 202905 3600589 $825
300349781 Metals & Minerals Scaffolding 202802 202905 3244993 $5,906
Market Model Usage
Capital project opportunities for new facility construction and major expansions. Higher margin projects with longer engagement cycles. Values spread across project duration (start to end dates).
Industrial

Industrial Maintenance Spend

ind_maintenance_2025-12-22-1216.csv

Recurring maintenance spending by plant. Calculated from employee count proxies, production capacity, and industry benchmarks for steady-state maintenance work.

Data Lineage
Source: ind_plant_list + industry benchmarks
Feeds Into: ind_topline
Transform: ProxyValue x Industry Benchmark = Maintenance Spend
Key Fields
PLANT_ID Service Type IND_DESC Maintenance Spend ProxyToUse ProxyValue NO_EMP Value
Sample Records (3 rows)
PLANT_ID IND_DESC Service Type Maintenance Spend ProxyToUse NO_EMP Value
1062183 Metals & Minerals Scaffolding $7,572,680 NO_EMP 210 $3,155
1014375 Power Scaffolding $22,550,000 MW 200 $56,375
1000019 Petroleum Refining (HPI) Scaffolding $42,467,914 BBL/d 260 $374,708
Market Model Usage
Steady-state recurring revenue opportunity. Proxy calculations use employee count (NO_EMP), production capacity (MW, BBL/d), or other industry-specific metrics. Most predictable revenue stream for territory planning.
Industrial

Industrial Turnarounds

ind_trunarounds_2025-12-22-1217.csv

Scheduled plant shutdowns for major maintenance events. High-value, time-critical opportunities requiring advance planning and significant resource mobilization.

Data Lineage
Source: iir_project_list (filtered: Turnaround projects)
Feeds Into: ind_topline
Peak Season: Q2-Q3 (Spring/Fall)
Key Fields
Project ID PLANT_ID End Market Service Type start end Value YYYYMM
Sample Records (3 rows)
Project ID End Market Service Type start end PLANT_ID Value
300744310 Power Insulation 202403 202403 1015354 $396,000
300736492 Petroleum Refining (HPI) Scaffolding 202403 202403 1009046 $1,045,000
300872928 Petroleum Refining (HPI) Specialty 202403 202403 1016744 $1,500,000
Market Model Usage
Highest-value project type with concentrated timing. Used for turnaround calendar planning, resource mobilization, and peak season identification. Refinery turnarounds typically range $1-5M per event.
Commercial

Commercial Topline Forecast

comm_topline_2025-12-22-1214.csv

Aggregated commercial market values by state, category, service type, and project funding. Contains monthly forecasts for trend analysis and Commercial TAM calculation.

Data Lineage
Source: comm_new_build + comm_alteration
Feeds Into: Commercial TAM ($2.5B), Persona Dashboards
Grain: State + Category + Service Type + Funding + YardID
Key Fields
State NewCategory Service Type Project Funding Region YardID COUNTY 202501-202812 (Monthly Values)
Sample Records (3 rows)
State NewCategory Service Type Funding YardID 202501
WA Government Buildings Painting Alteration B:088 $1,106
WA Educational Painting Alteration B:850 $3,168
WA Bridges Painting Alteration B:088 $6,167
Market Model Usage
Primary aggregation table for Commercial TAM ($2.5B). Categories include Educational, Medical, Retail, Government, and Infrastructure. Used for open shop territory planning and GC relationship analysis.
Commercial

Commercial New Construction

comm_new_build_2025-12-22-1214.csv

New commercial construction projects from ConstructConnect. Ground-up developments across all commercial categories including healthcare, education, retail, and data centers.

Data Lineage
Source: cc_adjusted (filtered: NewBuild)
Feeds Into: comm_topline
Transform: Valuation split by service type benchmarks
Key Fields
Project ID Category Service Type start end Value YYYYMM
Sample Records (3 rows)
Project ID Category Service Type start end Value
1007437140 Commercial Scaffolding 202507 202601 $10,714
1007533662 Commercial Forming/Shoring 202511 202605 $2,143
1007388920 Community Scaffolding 202509 202604 $10,000
Market Model Usage
Ground-up construction opportunities. Typically higher values than alterations. Requires strong GC relationships for bid access. Data Centers and Healthcare are fastest-growing categories.
Commercial

Commercial Alterations

comm_alteration_2025-12-22-1214.csv

Renovation and retrofit projects for existing commercial buildings. Includes tenant improvements, facility upgrades, and building renovations.

Data Lineage
Source: cc_adjusted (filtered: Alteration)
Feeds Into: comm_topline
Transform: Valuation split by service type benchmarks
Key Fields
Project ID Category Service Type StateProvince ZipPostalCode Value YYYYMM
Sample Records (3 rows)
Project ID Category Service Type State Zip Value
1007573923 Commercial Specialty TX 78640 $1,294
1007028591 Educational Scaffolding TX 78640 $2,450
1007767357 Civil Scaffolding TX 78640 $7,801
Market Model Usage
Renovation opportunities across existing buildings. More predictable timing than new construction. Strong correlation with economic cycles and tenant turnover rates.
Mapping

Plant-to-Yard Mapping

ind_plant_list_yard_current_2025-12-22-1216.csv

Maps industrial plants to BSW service yards/branches. Enables territory-level market sizing, sales coverage analysis, and opportunity assignment.

Data Lineage
Source: IIR Plant Database + Geographic Assignment
Feeds Into: ind_topline (YardID), Territory Views
Join Key: PLANT_ID
Key Fields
PLANT_ID YardID PLANT_NAME OWNER_NAME NewCategory COUNTYNAME MARKET_REG LATITUDE/LONGITUDE
Sample Records (3 rows)
PLANT_ID PLANT_NAME OWNER_NAME NewCategory YardID COUNTYNAME
3665144 Mustang Natural Gas Gathering Crestone Peak Resources Production (Oil & Gas) B:781 Adams
3521991 Chester Greenhouse Plenty Unlimited Inc Food & Beverage B:786 Chesterfield
3666074 Archbald Data Center Archbald I LLC Data Centers P:031349 Lackawanna
Market Model Usage
Critical mapping table linking plants to BSW service territories. YardID enables branch-level TAM calculations. Used for white space analysis and territory optimization.
Mapping

CC Project-to-Branch Assignment

cc_branch_assignment_2025-12-22-1213.csv

Maps commercial ConstructConnect projects to BSW branches. Simple lookup table linking ProjectID to YardID for territory assignment.

Data Lineage
Source: cc_adjusted + Geographic Assignment
Feeds Into: comm_topline (YardID)
Join Key: ProjectID
Key Fields
ProjectID YardID
Sample Records (5 rows)
ProjectID YardID
1007566716P:057101
1005249869B:762
1004605616B:865
1005463318B:769
1006453365B:056
Market Model Usage
Links ConstructConnect commercial projects to serving BSW branches. Enables branch-level commercial TAM calculation and territory performance tracking.
Mapping

IIR Project-to-Branch Assignment

iir_branch_assignment_2025-12-22-1215.csv

Maps IIR industrial projects to BSW branches. Links PROJECT_ID to YardID for territory-level opportunity assignment.

Data Lineage
Source: iir_project_list + ind_plant_list_yard_current
Feeds Into: ind_capital, ind_turnarounds (YardID)
Join Key: PROJECT_ID
Key Fields
PROJECT_ID YardID
Sample Records (5 rows)
PROJECT_ID YardID
300961675B:076
300781514B:321
301104782B:752
301104305P:031801
301004052P:056801
Market Model Usage
Links IIR industrial projects to BSW branches via plant location. Enables branch-level industrial project tracking and sales rep opportunity assignment.
Reference

Branch Dimension Table

dim_branch_2025-12-22-1218.csv

Master data for BSW branches/yards. Contains organizational hierarchy, region assignments, division structure, and branch attributes.

Data Lineage
Source: JE (JD Edwards) + Oracle ERP
Feeds Into: Branch lookups, Region rollups
Join Key: BRANCH_CODE
Key Fields
BRANCH_CODE BRANCH_NAME DIVISION_NAME REGION_NAME COUNTRY_NAME AREA_NAME MARKET_NAME
Sample Records (3 rows)
BRANCH_CODE BRANCH_NAME DIVISION_NAME REGION_NAME COUNTRY
6796 6796-BRANDSAFWAY FORT MYERS North America Southeast & Specialty United States
0577 0577-CONCRETE REPAIR FL North America Southeast & Specialty United States
6729 6729 - BIRMINGHAM North America Southeast & Specialty United States
Market Model Usage
Dimension table for branch hierarchies. Enables rollup from branch to area to region to division. Links YardID references across all datasets to branch details.
Reference

MSA Geographic Mapping

mm_msa_2025-12-22-1218.csv

Metropolitan Statistical Area classifications by county. Enables urban vs. rural market segmentation and market density analysis.

Data Lineage
Source: US Census Bureau MSA Definitions
Feeds Into: Geographic enrichment, Market density analysis
Join Key: STATE + COUNTY
Key Fields
STATE COUNTY COUNTRY POSTAL_CODE MSA
Sample Records (5 rows)
STATE COUNTY COUNTRY MSA
KSSewardUNITED STATESLiberal, KS Metropolitan Statistical Area
SDMarshallU.S.A.Rural
NEJohnsonU.S.A.Lincoln, NE Metropolitan Statistical Area
OKNowataU.S.A.Tulsa, OK Metropolitan Statistical Area
INOhioUNITED STATESCincinnati, OH-KY-IN Metropolitan Statistical Area
Market Model Usage
Geographic enrichment for market density analysis. MSA classification affects competitive dynamics and service delivery costs. Rural markets typically have lower competition but higher mobilization costs.
Reference

Main Offices / Yard Locations

main_offices_2025-12-22-1217.csv

Physical locations of BSW yards/offices with geographic coordinates. Used for distance calculations and territory visualization.

Data Lineage
Source: Internal Operations System
Feeds Into: Territory mapping, Distance calculations
Join Key: YardID
Key Fields
YardID Division Region Area Branch YardLat YardLon YardCity YardAddress1
Sample Records (3 rows)
YardID Branch Region YardCity YardLat YardLon
B:782 720782C El Paso West EL PASO 31.729488 -106.347265
B:752 Atlanta Southeast & Specialty Kennesaw 34.008037 -84.610874
B:761 Austin Commercial Gulf AUSTIN 30.379662 -97.714642
Market Model Usage
Yard physical locations enable distance-based territory assignment and drive-time analysis. Lat/Lon coordinates used for map visualizations and proximity calculations.
Reference

Company Project History

company_project_hist_2025-12-22-1215.csv

Historical record of companies involved in ConstructConnect projects. Tracks GCs, architects, engineers, and their project participation.

Data Lineage
Source: ConstructConnect SFTP
Feeds Into: GC relationship analysis, Key customer identification
Join Key: ProjectID + CompanyID
Key Fields
ProjectID CompanyID Name Role Stage BiddingRole Key Customer
Sample Records (3 rows)
ProjectID Name Role Stage Key Customer
1006766985 Noland Builders, Inc General Contractor Low Bids Announced
1006766861 J. Bradley Moore & Associates Architect Schematic Design J. Bradley Moore & Associates
1006766774 Centras Engineering Civil Engineer GC Award Centras Engineering
Market Model Usage
Identifies key GC relationships and project stakeholders. Enables analysis of which contractors BSW should target based on project volume and historical win rates.
Internal

Job Cost Consolidated

Market_model_job_cost_consolidated.csv

Consolidated job cost data from BSW internal systems. Contains revenue, costs, customer details, and service type breakdown for historical jobs.

Data Lineage
Source: JD Edwards / Oracle ERP
Feeds Into: Win rate analysis, White space identification, Market share calculation
Grain: Job + Period + GL Account
Key Fields
JOB_NUMBER CUSTOMER_NAME BRANCH_NAME NewCategory Project Funding ServiceTypeMapped Revenue Cost PERIOD
Sample Records (3 rows)
CUSTOMER_NAME BRANCH_NAME NewCategory Service Type Revenue PERIOD
LEASE CRUTCHER LEWIS Seattle Educational Forming/Shoring $0 2023-07
VALERO REFINING COMPANY Tulsa Petroleum Refining (HPI) Scaffolding $15,596 2023-06
DMC MINING SERVICES SUDBURY Metals & Minerals Scaffolding $186 2025-07
Market Model Usage
Historical revenue data enables market share calculation (BSW revenue / TAM). Identifies white space (plants with TAM but no BSW revenue) and validates TAM assumptions against actual performance.

6-Step Data Processing Logic

How raw data is transformed into actionable market intelligence through our ETL pipeline.

1

Data Ingestion

Raw data is loaded daily from external sources (IIR, ConstructConnect) via SFTP and internal systems (JD Edwards, Oracle) via database connections.

iir_project_list cc_adjusted Market_model_job_cost
2

Data Cleansing & Standardization

Normalize country codes (U.S.A. vs UNITED STATES), standardize industry categories, clean project statuses, and validate geographic coordinates.

U.S.A. / UNITED STATES / US → United States
3

Geographic Assignment

Plants and projects are assigned to BSW service territories using proximity algorithms. Each plant/project gets a YardID based on nearest yard location.

ind_plant_list_yard_current cc_branch_assignment iir_branch_assignment
4

Value Decomposition

Total project values (TIV) are split into service types using industry-specific benchmarks. A $10M refinery project becomes Scaffolding (40%), Insulation (25%), Painting (15%), Specialty (20%).

$10M Project TIV → $4M Scaffolding + $2.5M Insulation + $1.5M Painting + $2M Specialty
5

Temporal Distribution

Project values are spread across their duration. A $1.2M project from 202501 to 202506 becomes $200K/month across 6 months.

ind_capital (start/end → monthly) ind_turnarounds (start/end → monthly) comm_new_build (start/end → monthly)
6

Aggregation & Output

Data is aggregated at multiple grain levels (State, Category, Service Type, YardID) to produce topline forecasts and persona-specific views.

ind_topline comm_topline Territory Views Persona Dashboards

Persona-Specific Data Usage

How each persona uses the market model data for their specific decision-making needs.

Executive

Strategic Planning

Primary Data Sources

  • ind_topline - National TAM trends by segment
  • comm_topline - Commercial market forecasts
  • Market_model_job_cost - Market share calculations

Key Metrics

  • Total Addressable Market ($6.7B)
  • Industrial vs Commercial split (63%/37%)
  • Year-over-year growth trends
  • Regional market share

Sales Leadership

Resource Allocation

Primary Data Sources

  • ind_topline/comm_topline - Regional forecasts
  • dim_branch - Branch hierarchy
  • ind_turnarounds - Turnaround calendar

Key Metrics

  • Region-level TAM by quarter
  • Turnaround peak season timing
  • Service type mix by region
  • Resource mobilization needs

Branch Manager

Territory Operations

Primary Data Sources

  • ind_plant_list_yard_current - Plants in territory
  • cc_branch_assignment - Commercial projects
  • main_offices - Service coverage area

Key Metrics

  • Territory TAM by plant
  • White space opportunities
  • Customer concentration risk
  • Project pipeline by month

Sales Rep

Deal Execution

Primary Data Sources

  • iir_project_list - Active project details
  • cc_adjusted - Commercial bid opportunities
  • company_project_hist - GC relationships

Key Metrics

  • Project-level opportunity values
  • Project stage and timing
  • Owner/contractor contacts
  • Service type requirements

Service Type Allocation Benchmarks

How project values are split into service types based on industry-specific benchmarks.

Industry Category Scaffolding Insulation Painting Specialty Forming/Shoring Motorized
Petroleum Refining (HPI) 40% 25% 10% 20% 2% 3%
Chemical Processing (CPI) 35% 30% 15% 15% 2% 3%
Power 45% 20% 15% 10% 5% 5%
Food & Beverage 30% 25% 25% 10% 5% 5%
Commercial (New Build) 25% 5% 15% 10% 40% 5%
Commercial (Alteration) 40% 10% 30% 10% 5% 5%