PMG

The firm's project history is its most valuable asset, and it did not exist in a form anyone could query. It lived across old proposal files, shared drives, and a marketing spreadsheet.

The spreadsheet was built to publish web pages. It held the text and images that populated the website, arranged in the order a page needed them. Marketing used it as the system of record because it was the closest thing available.

That carried a direct cost on every proposal. Writing a proposal for a university client means showing the firm's university work: which projects, what size, what role, what outcome. Finding that meant reading through old proposals and asking colleagues what they remembered.

Two other problems sat alongside it. The marketing team could not publish content without a developer, and HR had no way to manage job postings.

I sat with the marketing team to find where their process broke. The complaint was that they could not find data on past projects.

I did not take the complaint at face value. Instead of building better search over the existing spreadsheet, I profiled the source data to find out why the data could not be found.

The profiling answered it. Values were stored as free text, so nothing could be sorted or totaled. One client appeared under several spellings and counted as several different clients. Project status was buried inside other fields rather than held in a field of its own. Single rows held multiple distinct projects.

The analysis surfaced roughly a 40% undercount of the firm's largest market. The firm had been describing its own experience in that market at well under what it had actually delivered.

Profiling the data rather than interviewing more people was the right call for this problem. The marketing team could describe what they were unable to find. They could not describe why, because the reason sat in the structure of the file rather than anywhere in their process.

The spreadsheet was not badly maintained. It was built to generate web pages, and it was never going to serve as a system of record no matter how carefully anyone kept it up.

The stated problem was that the data could not be found. The real problem was that no structured source of truth existed for anyone to find.

Those two problems have different solutions. Better search over the spreadsheet would have returned the same unusable values faster, and the 40% undercount would have survived it.

I built a structured database with fixed-list inputs instead of free text. This was the decision that mattered most and the least popular one, because picking from a list is slower than typing into a box. Free text is what produced the original problem. Validation at the point of entry is the only thing that stops it from recurring, and the cost lands on the person entering data while the benefit lands on everyone retrieving it.

I built a custom CMS rather than buying one. The marketing team is not technical and needed to publish without waiting on a developer. An off-the-shelf CMS would have meant running website content in one system and the project record in another, which reintroduces the split that caused the problem in the first place.

I synced the careers page to a custom applicant tracking system, so HR manages postings in one place and they publish automatically. Before that, HR maintained postings in one system and requested website updates through another.

I synced the website to the project database, so project content stays current without anyone copying it between systems.

What I chose not to build matters as much as what I did. This is not a project management tool. It does not track schedules, budgets, or the document workflows that run during construction. Those systems already exist at the firm, and competing with them would have produced a worse version of software people already use.

The hub is the firm's record of completed work, built for retrieval and reuse. Holding that line is what kept four systems from becoming ten.

All four decisions point the same direction. Put the data in one structured place, then have each surface read from it rather than keep its own copy. The copies were the problem, and any solution that created another one was going to fail the same way.

Four connected systems. The project database, which is the structured record of completed work. A custom CMS that lets marketing publish without a developer. A careers page synced to the applicant tracking system. The company website, which draws its project content from the database.

Screenshots are redacted before publishing, since the systems hold client and project data.

The systems are in daily use across the 40-person firm. Marketing adopted the CMS, the project database, and the website. HR adopted the applicant tracking system. Leadership and the construction team adopted the project database.

There is no analytics layer, so adoption is observed rather than measured. I can name who uses which system and what they stopped doing by hand. I cannot show a usage curve.

Adoption happened without a mandate. Nobody was instructed to use these systems, which is the strongest evidence I have that they solved something real.

Instrument the systems so their value is measured rather than assumed. I built four systems that people use every day and I cannot put a number on what they are worth to the firm.

The 40% undercount is the one hard number in this project, and it describes the problem rather than the result.