Note: Some details have been changed and specifics omitted to respect a non-disclosure agreement.

Summary

As is disturbingly common in many growing businesses, the backbone of the product management department was an Excel spreadsheet. Until a certain point, the flexibility offered by this was a worthwhile compromise, but it had several drawbacks.

First, versioning and version control. Hosted locally or on a network drive, as this was, version and access control presented an issue. Second, interfacing with other tools. This spreadsheet was used to maintain a record of the key product information such as MSRP and COGS, and used for business evaluations such as gross margin. While these can, and often are done by hand, interfacing with sales tools again relied on this data being manually imported.

I was recruited to modernize this into a flexible tool, which could fill several other holes in the product management process within the company.

Process and Role

The design process followed a jobs-to-be-done format, where I would first investigate the utility provided by the excel spreadsheet, then determine how to translate and extend those functionalities in a custom application.

There were several key stakeholders in this development. Primary was the product department for which the tool was developed. They defined the key business processes that would need to be replicated. Within this department, requirements emerged from technical staff on the hosting and data management, and from the product department on exact definitions of tools. Unconstrained by the limits of a spreadsheet, additional requirements emerged from the product department. Secondary was an outside developer, who created and maintained a custom sales tool.

I was the project manager and primary developer for this project. I performed the use-case analysis, as well as data structure design. Additionally, I was responsible for the testing, deployment and management of the tool. I interviewed users of the Excel spreadsheet, and determined what they were “hiring” it to do.

Research and Design

The requirements for this tool naturally led to an authenticated web application. This would allow multiple users to edit a shared database, while maintaining a coherent, traceable and auditable base.

Compare Existing Solutions

There were two primary factors for using an in-house developed system. First was company culture, which preferred such solutions. Second, was the challenge in finding something that delivered all of the required features.

Base Features

Pages were required for searching products, and editing them. These pages should load quickly, perform the expected task, and allow for intuitive control. It was requested to add features beyond the capabilities of the spreadsheet, including items such as a “depends on” relationship between items.

Core Feature: PDF Datasheet

Previously, all datasheets were manually generated by the marketing department. This would require regeneration when (any) feature or property of a product changed. This would lead to product datasheets being out of date, or using the wrong format, etc. Because the data was structured, it was then easy to format this into a PDF export functionality, allowing always-up-to-date datasheets.

Core Feature: Excel Export

Excel is, in many ways, inescapable. For some business processes, be those official or “how someone does something”, there are hard requirements that clash with the existing or proposed solution. Interaction designers have two solutions at this point: Ignore the requirement and hope it goes away, or incorporate it into your design.

To take an illustrative example, at first it seems at odds with the initial goal of getting away from spreadsheets to offer an export-to-excel function. However, the real goal was to create a unified, controlled and auditable system for product management. Having an export tool simply allows a copy of that data to be exported for convenience. At no point does the exported copy become the primary. In this case, the solution was not a code-solution, per-say, but a business process one.

Core Feature: Audit Tracking

Every change made to product information was to be logged and associated with an account. This was a feature that set the tool apart from simply placing an excel spreadsheet in a cloud-provider platform. Accountability and traceability were important for a company seeking to maintain ISO certification.

Platform and Tools

This system was developed as a Python web-application, running on a Linux server, connected to a Microsoft SQL database.

This project utilized the TurboGears web framework, which was chosen at the time for being quickest-to-build and was a competing product to Django. SQLAlchemy was used as the backing ORM, as it gave a good level of abstraction between the SQL backend database and the higher-level Python code.

Platform Structure

Lessons Learned

The number one takeaway from this project was the directness with which jobs-to-be-done theory could be applied to business processes. Users had very specific routines that they were “hiring” the spreadsheet for - and many besides that they wanted to.

The second lesson has been the longevity of the tool. In research, things are often built as one-offs, or outdated by new developments. This tool has been in successful operation for nearly a decade.

Finally, seeing the impact on business processes over a long time-span has been a key driver in valuing the importance of creating systems for the humans that use them, and not just for the business process.