Saving Time & Exceeding Goals: Transforming Manual Reporting in Construction & Engineering
Imagine this: Your team is spending hours each week — sometimes even days — on manual reporting, time that could be redirected toward strategic initiatives. In one of my most memorable and rewarding projects, I was brought in as the “data guy” to overhaul a cumbersome, error-prone process. The result? Each project manager saved 8 hours per week, freeing up the equivalent of 2.5 full-time employees’ worth of time. Does this sound familiar? Maybe it’s exactly what you’re dealing with right now… This is that story.
A Patchwork of Manual Reporting
The Challenge: A Patchwork of Manual Reporting
In the construction and engineering organization I worked with, project managers were pulling data from a variety of systems:
CASPR: for real-time project statuses
NORAD: for vendor quotes and cost approvals
ORACLE: for purchase order and payment details
SharePoint & Emails: for data from other groups and vendors like Site Acquisition, Integration vendors, and Close Out teams.
This extensive process meant endless copy/pasting, manual updates, and last-minute fixes during meetings. Not only was this approach inefficient, but it also delayed critical decisions. The team frequently missed their On Air POE counts each month and fell behind on annual closeouts due to time-consuming reconciliations. Without timely and accurate reporting, they struggled to pinpoint why targets were not being met and project delays were occurring.
The Impact: Operational Bottlenecks and Frayed Nerves
The daily grind wasn’t just a technical hiccup, it was affecting people. Project managers were stressed, often staying late and working into the weekends to reconcile their projects. The delays in reporting meant leadership was frequently making decisions based on outdated, and sometimes inaccurate information. In one meeting I heard a Director lay into a vendor for not getting their documentation completed and updated, only to have to eat his words. In short, the process was stifling productivity, degrading trust, and killing morale with tangible costs to the organization’s bottom line and ability to meet the needs of their customers. But, that was all about to change.
Digging Deep: Uncovering the Root Causes
After observing the chaos firsthand and speaking with project managers, the finance team, vendors, and site acquisition staff, it became clear that the issue wasn’t due to individual effort, it was the lack of an integrated system. The information, that was critical for the PMs, was dispersed across multiple systems and platforms, forcing each PM to develop their own workaround. This fragmented data environment led to compounded inefficiencies and errors, reducing visibility into end-to-end project issues and diminishing the PMs’ ability to identify and resolve problems. Ultimately, this disjointed approach set the stage for the dramatic turnaround we were about to achieve.
From Insight to Implementation
Our Strategic Approach: From Insight to Implementation
I approached the project with a simple guiding principle: listen first, then act. Here’s how I methodically transformed the process:
1. Understanding the Process
I spent days with the project managers, attending their meetings and asking, “Why do you do it this way?” One PM candidly remarked, “We’re drowning in data. It feels like we’re always behind.” This wasn’t just a technical issue, it was a human one. Understanding their struggles was the foundation for crafting a solution that addressed both the technology and the team’s needs while showing them that I was in it with them. This helped me to build relationships and acknowledge the herculean task they had been accomplishing.
2. Mapping and Validating Data Sources
Armed with insights from the team, It was time to help! I started by mapping every data source:
APIs and Direct Feeds: Where available, I connected directly.
Flat Files & SharePoint Lists: I identified key files (CSV, Excel) that needed automated extraction.
Legacy Systems: For platforms without direct access, I set up scheduled exports or automated email attachments.
Before moving forward, I rigorously compared the outputs from these new methods against the manual process. This iterative verification ensured that our new system would maintain the same level of accuracy and then some. This again helped to build trust with the other PMs and leaders. Checking with them for accuracy and completeness, while gathering feedback when things were not quite right, helped them know that I was being thorough and valued their experience.
3. Building the Data Engine
With validated sources in hand, I built an ETL (Extract, Transform, Load) process using SSIS. The process involved:
Connecting to Oracle and integrating data from SharePoint and CSV files.
Staging the raw data on a SQL Server.
Organizing the data into logical fact and dimension tables for clarity and consistency.
This robust data engine laid the groundwork for automated, real-time reporting. While I used SSIS for this project, these fundamentals are equally applicable using modern tools like Azure Data Factory or AWS Glue.
4. Transforming Reporting: From Excel to Dynamic Dashboards
The next step was to replace static, easily corruptible Excel trackers with dynamic dashboards. Initially, due to technology constraints, I began with an Excel solution that updated daily based on views. However, I eventually developed Power BI dashboards that updated in real time, providing:
Detailed views for project managers: Real-time insights into project statuses and bottlenecks.
High-level summaries for leadership: Up-to-date information on budgets, forecasts, and overall performance.
One AVP, Megan, was quick to note how these new dashboards revolutionized her meetings, enabling her to be more focused and data-driven. She frequently shared how having immediate access to critical information allowed her to respond to questions with confidence and speed.
5. Empowering the Team Through Training
Technology is only as effective as its adoption. To ensure a smooth transition:
I organized, and recorded, multiple Webex training sessions.
I created detailed documentation and recorded tutorial videos.
I created a SharePoint page of Q&A as well as a form for suggestions.
I recruited “champions” that I had been working with through the process, that helped me to build confidence and trust with their peers.
I personally reached out to those who were initially hesitant, revisiting their concerns and making sure they knew that they were heard.
By engaging early adopters as champions within the team, the new process gained momentum and trust across the organization. Sure, there were some holdouts and when we migrated to Power BI plenty of people were exporting the data to Excel, but that’s natural. After all, the business loves Excel for its familiarity and ease of use.
The Results: Tangible Benefits and Lasting Change
The transformation was profound. Project managers reclaimed over 8 hours per week each — freeing up the equivalent of 2.5 full-time employees’ worth of productivity. This saved time wasn’t just about efficiency; it reduced errors, improved decision-making with close to real-time data, and significantly eased the day-to-day stress on the team.
Key Takeaways for Business Leaders and Aspiring Data Engineers: The HEART Approach
It was then that I realized I had developed a winning approach — one that has guided me throughout my 25-year data engineering, leadership, and consulting career: H.E.A.R.T.
H — Hear:
Listen first. Understand the real issues your team is facing — this is the foundation for any successful solution.E — Evaluate:
Validate rigorously. Ensure that new methods meet existing standards to prevent costly errors down the line.A — Automate:
Build systems that not only streamline processes but also empower your team to work more effectively.R — Recruit:
Build relationships and acknowledge the hard work of your team. Recruiting internal champions and keeping communication open makes adoption smoother.T — Train:
Invest in comprehensive training. Proper education and support ensure that the new system is fully embraced and utilized.
A Blueprint for
Data-Driven Success
Conclusion: A Blueprint for Data-Driven Success
This experience taught me that the best data solutions blend robust technical execution with a genuine understanding of people and processes. If your organization is bogged down by manual reporting or overwhelmed by data chaos, there’s a clear path forward. By systematically addressing each challenge — from data mapping to team training — you can transform inefficiency into a competitive advantage.
Interested in turning your data headaches into a streamlined, efficient process? Let’s connect and explore how a tailored approach can help you save time and exceed your business goals.
—
Chris Gambill
Gambill Data
chris.gambill@gambilldataengineering.com