Data Governance: Feeling Left Out

One challenge in Data Governance is ensuring that business rules are accurately reflected in your processes, reports, queries and spreadsheet formulae. A critical element is anticipation of the strange things that might happen that would cause false positives and false negatives. Here’s a situation where process owners were taking the heat for poor data management that was invisible because it was baked into the process.

Here we go …

BUSINESS RULE

Employees are supposed to receive a bonus upon completion of 3 courses.

• Courses had to be completed after 1 year of hiring.
•  Courses had to be completed before the 3rd year of employment.
• Courses could be taken in any order.

There would be a monthly process to determine bonus recipients who completed their requirements the previous month.

PROBLEM

Over a period of YEARS a lot of people were not receiving their bonuses. Imagine the frustration, and heated phone calls. Imagine what work wasn’t getting done while someone spent time digging up proof that the employee did the work and was indeed due their bonus.

This wasn’t a small issue. 40-70 people  per month were eligible and roughly one-fourth of eligible recipients weren’t getting their bonuses on time.

They were simply left out

Also, a process that was supposed to take 1 week each month transmogrified into a daily diet of do-overs, damage control, research, apologies, and unnecessary frustration.

Eventually, there was general resignation about the process being imperfect. There was doubt about the various process owners being willing or capable of “getting it right.”

Research showed, however, that everyone was doing what they were supposed to do. However …

CAUSE OF THE PROBLEM

The reports were perfectly reflecting the business rules but only for the employees who were obviously within the parameters.  The reports failed with these types of scenarios:

• The person who finished their courses within 8 months (4 months short of the required minimum). There was wasn’t a trigger to award the bonus on the 12th month. The report completely ignored such persons.
• People who started their program; left the company; returned 5 years later; took and completed all 3 courses in 18 months. The monthly report saw a span of more than 3 years and determined such persons ineligible.
• Courses 1 and 2 are tied to Richard Goodgrades, Course 3 is tied to Rich Goodgrades. Same person, 2 incomplete profiles.
• Miscellaneous other situations.

SOLUTION

• Ignore the monthly report. Instead, download a completed data dump of everyone who’d ever taken a course over the previous 4 years–maybe 60,000 rows in Excel.
• Create clever unique identifiers to detect and merge any split profiles (Rich vs. Richard Goodgrades).
• Use conditional formatting and formulae to determine who’s completed all 3 courses.
• Bounce that list against the shipping report to determine whose certificate had already been mailed.

I developed a process that considered not only the business rules but the exceptions, hiccups, previous omissions … any kind of a detour an employee may have taken on the path to earning their bonus.

RESULTS

• Error rate was reduced from 25% to less than 1%.
• The entire process was reduced from daily, down to 3 days per month.
• More time to do actual work instead of apologizing and fielding hostile phone calls.
• A robust process that could even handle favors. Example: “Angela’s ceremony is 2 weeks before you process the bonuses next month, can you send her’s early?” Prior to overhauling the process, such a favor would have resulted in a duplicate being sent out, more frustrated phone calls, more damage control, more apologies.

CONCLUSIONS

Poor Data Hygiene was at a level deeper than your typical typos, shortcuts or incompletions. In this case, the misery was baked into the reports that had been written long ago.

 Keep that data clean, y’all!