Improve data integrity, data quality, and business insights with standard operating procedures (SOPs)

Is your data worth tracking? This is a question about the quality and integrity of your data – its accuracy, completeness, and fit with your use cases. And it’s an important question to consider, because low quality data can mean that your business insights are ill-informed. I’ve covered some of these points in my posts on clean data and data classification. In this post, I’m going to focus on how standard operating procedures, or SOPs for short, can help you improve the quality and integrity of both your data and your business insights.

A SOP is a set of instructions for a routine task (standard operating procedure) or routine process (standard operating process). One of my favorite SOP tips is to write your first draft as you go. I’m going to follow my advice with this post scenario, and then massage the SOP until it meets our needs.

Note that there are actually multiple SOPs at play in this post -a birds-eye view of the entire process – which would be supported by other SOPs that detail how and when the individual data fields in the database in question would need to be filled out and used in order to support that process.

The Scenario: Work Orders

So imagine we’re at a college campus with a lot of buildings to maintain. We use a work order database application to report maintenance issues, log work time spent by the various trades people who did the work, and close out the work order as completed. Ideally, the work order database application fulfills three business needs:

• it supports people on the college campus in addressing their maintenance concerns,
• it supports the trades people in performing their maintenance work, and
• it supports administrators in gathering business insights into:
  • how many tradespeople are needed,
  • the overall health of their buildings, and
  • because unaddressed maintenance issues impact our impression of a place, calculations such as the general response time between requesting a work order and when the work order is actually completed.

A lot of data needs to be tracked to address all of these needs. For brevity, we’ll focus on just how the database’s fields and our SOP for using that database impact our ability to calculate accurate response times.

The First SOP Draft

A work order comes in – there’s a ceiling leak.

Part one of this SOP is creating a new work order in the system. We’ll open the work order system, start a new work order, fill in the description of the problem, and pick the department we think should handle this problem. Since this is a leak, we’ll guess plumbers. And we submit.

Presumably, because we picked them from the list, the plumbers are notified that they’ve got a new work order. They deploy and fix the leak.

But there’s a complication. The plumbers need to call in the carpenters to fix the ceiling, and the carpenters need to call in the janitors to clean up all the mess. Only once all of that work is complete can we consider the work “done.”

In our SOP we write this as tradespeople doing the work, logging their time, and marking the work order as closed.

Now, looking at our SOP, we did not include a request date as part of creating a work order, which means that it’s hit-or-miss as to whether we can do the response time calculation between when the work was initially requested and when it was marked as complete. We have a data issue.

The Second SOP Draft

We’ll revise the SOP to incorporate request date so that the response time can be calculated.

If the database application records the date the record was created in the system, we might be okay. But, for accuracy and data integrity, we might also need to capture those word-of-mouth work orders where someone mentioned it, and the work was done and entered into the system after the fact.

So we’ll modify our work order creation SOP to include entering the date the work order was requested. Now we can calculate response time.

Using this revised SOP, it would seem that we have everything we need.

Unfortunately, our revised SOP still doesn’t necessarily help us support “accurate” response times, because it doesn’t have a good way of letting the carpenters or janitors know that they’ve got work to do, or clarifying whether the plumbers, carpenters, or janitors should be responsible for filling out the final close time for the work order.

So we need to modify again.

The Third SOP Draft

As it turns out, our work order system will let us spawn off a child work order for every smaller part of our parent work order, letting us track all of the work back to that initial request. So, in this SOP revision, we’ll take better advantage of the work order database at our disposal.

If we were tracking just the child work order response times, we’d get a faulty view of our response times per work order request, because we’d be tracking only parts of the job instead of the job as a whole. So our SOP should reflect the work order spawning in a way that captures the entire response time.

First, plumbers address their part of the job and log their time.

But now, because there’s more work to be done, they also create a child work order, and then follow the steps for work order creation, selecting carpentry as the department, selecting the date requested, entering in the description about ceiling repair, and submit. Now the carpenters can be notified in a timely fashion, which supports their timely response, and it supports the overall business desire for tracking response time from start to finish under the parent work order request.

The carpenters will do the same with the janitors.

The janitors will complete their work and log their time.

But now the janitors have the extra onus of going back to the parent work order and marking the entire thing as complete with a date completed.

With this new SOP revision, as long as everyone does their part, the data will now be accurate enough to calculate an accurate response time. In instances where the janitors do not take that extra step to close out the parent work order, it’s possible that that date could be accurately backfilled in the future by copying the janitor’s child work order close date into the parent work order’s close date.

Wrapping Up

These SOP revisions improve data integrity by supporting complete and accurate data, and improve data quality, because now our data is a better fit with our business insight needs, and both of these give us better business insights.

If you would like to talk to me about how to write or revise your standard operating procedures for better data or better insights, let’s connect on Zoom.

—
This blog post is a reworking of the transcript of a video on the Blou Designs YouTube Channel. If you would like to “watch” this post, you can find it at: https://youtu.be/w6VJRNXtsKE

If you prefer video content, don’t forget to subscribe to the Blou Designs YouTube Channel so you can get a notification each time we release a new video there! Make sure that your notification settings are set to All instead of Personalized.