Conflicting Procore, CRM & ERP inputs stalled backlog — Snowflake model enabled approvals

Overview

A national contractor’s backlog prioritization was guesswork because project data in Procore, opportunity details in the CRM, and actuals in the ERP didn’t tie out. Project managers, Preconstruction, and Finance brought competing spreadsheets to meetings, and priorities shifted as teams reconciled basics. Intelligex stitched Procore, CRM, and ERP into a conformed model in Snowflake, computed project?level profitability and risk scores in dbt, and stood up a weekly approval cadence run by the Transformation Office. A decision dashboard and audit-backed workflow replaced ad hoc debates, so the team prioritized with fewer conflicts and reduced rework.

Client Profile

• Industry: Commercial construction (general contractor)
• Company size (range): Multi-regional operations with diversified project portfolio
• Stage: Established firm modernizing portfolio governance and analytics
• Department owner: Strategy, Analytics & Executive Leadership (Transformation Office / PMO)
• Other stakeholders: Preconstruction, Operations/Project Management, Finance & Accounting, Risk & Safety, Legal/Contracts, IT/Data Engineering, Regional Leadership

The Challenge

Backlog decisions required a clear view of project profitability, delivery risk, and client health. In practice, Procore tracked budgets, RFIs, submittals, and change orders; the CRM captured pipeline, account context, and win probabilities; and the ERP held actuals, commitments, and WIP entries. Identifiers and calendars didn’t align, and definitions for cost to complete, margin, and risk factors differed by team. Meetings started with data tie?outs instead of selecting which projects to advance, bid, or pause.

When data sources didn’t match, decisions stalled. Subcontractor performance flags in Procore weren’t consistently linked to ERP commitments, and CRM revenue stages didn’t roll forward into the backlog view. Operations argued schedule feasibility while Finance challenged margin assumptions, and there was no shared rubric to weigh risk against profitability. Leaders wanted to keep Procore and the existing CRM and ERP, but needed a unified model, transparent scoring, and a repeatable approval cadence.

Why It Was Happening

Identity, calendars, and taxonomies were fragmented. Job numbers didn’t map cleanly across systems, change orders posted on different cut?offs, and regions applied their own codes for contract type, delivery method, and phase. As a result, tying Procore budget lines to ERP WIP and CRM opportunities required manual joins that drifted over time.

Governance arrived late. Profitability views were assembled in slides, risk signals lived in project notes, and exceptions were handled by email. There was no single scoring framework or approval path, so every backlog session restarted debates about inputs, assumptions, and ownership. Without an audit trail, it was hard to explain why priorities had shifted from one week to the next.

The Solution

We implemented a governed portfolio layer that harmonized Procore, CRM, and ERP data, calculated project?level profitability and risk scores, and embedded a weekly approval cadence. Feeds landed in Snowflake, dbt encoded identity mapping and definitions, and a dashboard presented ranked projects with drill?through to evidence. A lightweight workflow captured Transformation Office approvals and reason codes for changes. Existing systems remained systems of record; the orchestration aligned data, scoring, and governance around them.

• Project, budget, RFI, and change order feeds from Procore with cost code and schedule context
• CRM opportunity and account data with stages, likelihood, and customer health (for example, Salesforce REST API)
• ERP actuals, commitments, and WIP extracts mapped to projects and cost codes
• Conformed warehouse model and snapshots in Snowflake with mastered project and account IDs
• Profitability and risk scoring encoded in dbt with definitions and validations for identity joins, cut?offs, and accepted values
• Risk factors included schedule variance, change order volatility, subcontractor performance flags, outstanding RFIs/submittals, contract type, and client payment behavior
• Executive and practitioner dashboards with ranked backlog, score breakdowns, margin ladders, and drill?through to Procore and ERP references (Power BI)
• Weekly approval cadence captured in a routed workflow with comments, reason codes, and audit logs (for example, ServiceNow)
• Role?based access by function and region with views for Transformation, Operations, Finance, and Preconstruction
• Human?in?the?loop review queue for mapping exceptions and justified score overrides

Implementation

• Discovery: Cataloged Procore modules in use, CRM objects and stages, ERP structures for WIP and commitments, and regional project coding; gathered recent backlog decks to surface repeated reconciliation issues and exception patterns.
• Design: Defined mastered project and account IDs, shared calendars and cut?offs, and a consistent cost taxonomy. Authored the profitability and risk scoring framework with clear factor definitions. Designed validations, exception categories, dashboard drill paths, and the weekly approval cadence with reason codes.
• Build: Landed Procore, CRM, and ERP feeds into Snowflake; implemented dbt models for identity stitching, metric normalization, profitability, and risk scoring; published certified datasets; developed Power BI views with score breakdowns and evidence links; and configured a ServiceNow workflow for approvals and change logging.
• Testing and QA: Replayed recent quarters of projects to reconcile profitability with Finance baselines; validated risk factors against known field conditions; tested identity joins and cut?offs; verified dashboard drill?through to Procore and ERP references; and dry?ran the approval cadence with Transformation, Operations, and Finance.
• Rollout: Launched dashboards in read?only mode alongside legacy spreadsheets; after validation, activated the weekly approval cadence for a pilot region; expanded to additional regions and divisions with a manual exception lane and post?review documentation.
• Training and hand-off: Delivered quick guides for Operations on score drivers and drill?through, for Finance on margin components and WIP alignment, for Preconstruction on opportunity mapping, and for the Transformation Office on running approvals and managing exceptions. Established stewardship for mappings, definitions, and the scoring rubric.

Results

Backlog sessions opened with a ranked list grounded in shared definitions. Profitability and risk scores included transparent breakdowns and links to Procore, CRM, and ERP evidence. The weekly cadence captured approvals and rationale, so priorities were clear and traceable. Leadership spent time on sequencing, resourcing, and client strategy rather than reconciling inputs.

Rework declined as identity mapping and calendars stabilized and score drivers were encoded in the model. Operations and Finance aligned earlier on margin assumptions and risk posture, and Preconstruction focused on opportunities that matched capacity and return targets. The Transformation Office used the audit log to explain shifts and to close the loop on exceptions.

What Changed for the Team

• Before: Project data didn’t tie out across Procore, CRM, and ERP. After: A conformed model in Snowflake harmonized IDs, calendars, and cost codes.
• Before: Profitability and risk were debated in slides. After: dbt encoded a shared scoring framework with drill?through to evidence.
• Before: Approvals happened informally via email. After: A weekly, routed cadence captured comments, reason codes, and sign?offs.
• Before: Exceptions were undocumented and repeated. After: A review queue and audit log tracked mapping fixes and justified score overrides.
• Before: Meetings restarted data tie?outs. After: A ranked backlog and certified views kept discussions on sequencing and trade?offs.

Key Takeaways

• Unify construction, CRM, and ERP data under a mastered project identity and shared calendar to ground backlog choices.
• Encode profitability and risk scoring in transformations; definitions in code reduce drift and debate.
• Run a regular approval cadence with reason codes so priorities are transparent, traceable, and defensible.
• Keep Procore, CRM, and ERP; orchestrate integration, scoring, dashboards, and workflow rather than replatforming.
• Use human?in?the?loop reviews to handle mapping exceptions and thoughtful overrides without breaking comparability.

FAQ

What tools did this integrate with?
We ingested project and change information from Procore, opportunity and account data from the CRM (for example, Salesforce), and financials and WIP from the ERP. Data landed in Snowflake, transformations and scoring ran in dbt, dashboards were delivered in Power BI, and approvals and change logs were captured in ServiceNow.

How did you handle quality control and governance?
A mastered project and account ID aligned sources, with dbt enforcing identity joins, calendars, and accepted values. The scoring rubric defined profitability and risk factors with owners and documentation. Validations flagged unmapped jobs, stale feeds, and out?of?range metrics. A weekly approval cadence required reason codes for changes, and an audit log bound each decision to dataset versions, scores, and evidence.

How did you roll this out without disruption?
We ran certified dashboards alongside existing spreadsheets while teams validated counts, joins, and scores. After trust was established, the weekly approval cadence went live for a pilot region, then expanded progressively. Core systems stayed in place; the new layer standardized integration, scoring, and review around them.

How were risk scores derived and explained?
Risk combined schedule variance, change order volatility, subcontractor performance flags, outstanding RFIs/submittals, contract type, and client payment behavior. Each factor carried a definition and weight in dbt. Dashboards showed a component breakdown and linked to Procore and ERP references so stakeholders could understand and, when justified, request a documented override.

How did you align WIP and backlog across systems?
WIP extracts from the ERP mapped to Procore cost codes and job numbers via the mastered ID. Shared calendars and cut?offs ensured change orders and actuals aligned to the same periods. CRM opportunities rolled forward to backlog only when criteria matched stage definitions. Exceptions were routed to a human reviewer, and fixes were versioned to protect historical comparability.