Redesigning a Complex Metadata Workflow

Project Snapshot

The Problem: Too Much Knowledge Required Up Front

For publishers using CSV imports, preparing metadata required much more than filling in a spreadsheet.

Publishers first had to choose the correct template and determine which fields applied to their product. From there, they needed to understand accepted values and conditional requirements while also knowing how to format more complex metadata for import.

Some fields required precise compound syntax. A price, for example, might need to combine price type, amount, currency, dates, and promotional information into a single formatted value.

Reference information existed, but using it required repeatedly moving between the CSV and supporting materials while manually reproducing the expected formatting.

The files themselves created additional risk. Publishers sometimes edited previously saved CSVs directly in Excel, which could alter ISBNs, dates, and other metadata without making the problem immediately obvious.

Training compensated for much of this complexity. CSV onboarding required at least an hour of dedicated training and could require several sessions, followed by rounds of test-file review and correction. Even after onboarding, confusing import and approval errors could generate additional support and retraining.

The recurring problem wasn't simply that publishers were making mistakes.

| The workflow required publishers to know too many rules before the system could tell them whether they were right.

Understanding the Existing Workflow

Before designing a replacement, I broke the existing process into the decisions and tasks a publisher had to complete successfully.

A publisher first needed to select the correct CSV template for the product type. From there, each step introduced another set of decisions about required fields, accepted values, formatting, and conditional requirements. Much of the knowledge needed to make those decisions lived outside the spreadsheet itself.

That meant the CSV functioned primarily as a container for data. It did relatively little to help the publisher create valid data.

When something was wrong, useful feedback often arrived later in the process, during import or approval. By then, the publisher had already completed the file and needed to interpret the error, return to the metadata, determine the correction, and try again.

Looking at the workflow this way changed the design question from:

How can we make the CSV instructions clearer?

to:

How much of this knowledge can we put into the workflow itself?

Reducing Complexity Without Hiding It

Metadata itself is complex, and simplifying the interface couldn't mean pretending those requirements didn't exist.

The goal was to distinguish between complexity the publisher genuinely needed to understand and complexity the tool could safely manage for them.

Where users needed to make a meaningful metadata decision, the workbook exposes that decision clearly. Where the requirement was primarily structural or syntactic, I looked for ways to automate or constrain it.

This was especially important for compound metadata values, where several understandable pieces of information had to be converted into a strict machine-readable format.

Rather than teaching publishers to memorize and manually reproduce that syntax, the workbook lets them work with the individual concepts and generates the required structure from those inputs.

| Design principle: Simplify the user's decision, not the integrity of the data.

Designing Within Real-World Constraints

The most technically elegant solution isn't always the most usable or sustainable one.

Macros could have made the workbook more dynamic. For example, they could hide unused columns based on Setup selections and provide a button that automatically generates the final CSV.

But publishers work across both Windows and macOS, and some organizations restrict macro-enabled files for security reasons. A macro-based approach could therefore create different versions to maintain while making the tool unusable for some of the people it was intended to help.

I chose to keep the workbook macro-free.

That decision introduced compromises. Setup selections can control validation and workflow logic, but they can't dynamically hide every unused column. The Export view can prepare the appropriate structure, but users still need to copy the finished data into a new workbook and save it as a CSV.

Those aren't ideal interactions. They are tradeoffs made to preserve compatibility and reduce the risk of maintaining multiple versions of the same tool.

| Design principle: A feature isn't an improvement if it makes the system harder to adopt or maintain.

Using AI to Accelerate Prototyping

The scale of the workbook made AI-assisted development useful for moving from requirements to a functioning prototype more quickly.

I used AI as a development accelerator rather than as the source of the requirements. The underlying rules and relationships came from my analysis of the existing process, the problems users encountered, and the system's metadata requirements.

The resulting workbook demonstrates how quickly AI-assisted development can turn a detailed system concept into a functioning tool. It also exposed an important implementation concern.

A workbook containing tens of thousands of formulas and hundreds of validation and formatting rules is difficult to maintain if future changes depend on recreating the original AI-assisted development process. That raises questions about technical ownership, documentation, knowledge transfer, and long-term support.

The prototype therefore became useful for evaluating not only what could be built, but also what would need to be true for the solution to remain sustainable after the prototype stage.

| Design principle: Faster development doesn't eliminate the need for maintainable systems.

Testing the System With Users

A functioning workbook wasn't enough. The next question was whether someone unfamiliar with its design could actually use it successfully.

Internal user testing began with coworkers asked to approach the workbook as though they were first-time publishers trying to organize and format metadata for import.

That distinction matters. Someone who already understands what the workbook is supposed to do can unconsciously work around confusing instructions, unclear navigation, or assumptions embedded in the design.

Feedback is being collected in a shared document so I can look for patterns across the experience. I'm watching where users hesitate or misunderstand instructions, as well as places where the validation messaging or workflow itself needs improvement.

The tool is also being considered for a potential pilot involving a large publisher that does not want to work directly in the publishing operating system. In that workflow, the publisher would not see the validation errors normally provided inside the platform, making pre-import validation particularly relevant.

| Design principle: A tool isn't intuitive because its creator knows how to use it.

Reflection

This project started with a request to simplify a CSV process, but the deeper problem wasn't the file format. It was the amount of system knowledge users had to carry in order to create valid metadata.

That changed the problem I was trying to solve.

Instead of asking how to explain every rule more clearly, I began asking which rules users actually needed to understand, which decisions they needed to make, and which requirements the tool itself could enforce or construct for them.

The project also reinforced the difference between building a successful prototype and implementing a sustainable operational system. AI-assisted development made it possible to explore a complex solution quickly, but deployment raises a different set of concerns. Someone has to own the tool, maintain it as requirements change, and make sure publishers receive current versions. The team also needs a way to determine whether the tool is actually improving the workflow.

Those questions aren't separate from the design. They are part of it.

The most useful outcome of the work isn't simply a more sophisticated spreadsheet. It's a different model for the interaction between users and complex requirements:

Instead of teaching people to avoid every possible error, design the workflow to help prevent those errors in the first place.