Episode 130: The Importance of Data Integrity, Accuracy, and Consistency

Hey everyone, I'm Tony Yankovich, Director of RTA's Fleet Consulting Group, back with you to host another episode of the Fleet Success Show. You know, over the last month or so, we spent a lot of time and a number of our recent episodes have focused on, on data, on benchmarking, on KPIs, key performance indicators. We've talked a lot about data analytics. We've talked about using data to tell your story and so much more. This episode, what we wanted to do is talk a little bit more about the importance of, of really having data integrity, data accuracy, and data consistency. So to that end, I'm excited to be joined today by Griffin Scott. Welcome to the show, Griffin.

Hey, Tony, thanks for having me today.

Well, sure. I'm glad to have you with us today. Thanks for agreeing to spend the next 40 minutes or so with us. So those of you that are not real familiar with Griffin, he's been with RTA for quite a while now. He's been a guest on several of these episodes already. He's also one of our lead fleet analysts here at RTA and a member of our fleet consulting group. Griffin has nearly 10 years in fleet and data analytics experience, and I've been fortunate enough to work with him many times over the last— oh, how long have we been working together, Griffin? About 7, 8 years or so. Yeah. Where we've been working together here at RTA and then at a previous stop before this as well. Griffin started in student transportation where he supported a number of projects that looked at school bus route optimization. He looked at helping school districts meet their transportation needs, and then he kind of transitioned over to more of the general fleet analytics where he was supporting all of our consulting projects, whether it be fleet replacement planning, whether it be chargeback rate development, cost of service analysis, a lot of fleet rightsizing. Tasks and projects. He's one of our go-to fleet analysts for EV transition planning and fleet sustainability modeling. And really anything that has anything to do with data or data analytics is what he does to support our consulting efforts. He holds several certifications for student transportation planning, data analysis, SQL reporting, Fleet Management Information System and Use and Application, and a host of other certifications and skills as well. So let's jump right in it. Griffin, as one of our fleet analysts, you're dealing with fleet data every day, so you know better than most the importance of good data because you're the one that has to either clean it up, normalize it, scrub it, and you're the one dealing with the results of bad data oftentimes. So for context, tell us a little bit about the kind of fleet data that you're typically using, typically analyzing and that you manipulate on a regular basis.

Yeah, no problem. Thanks for the intro too.

You bet.

Yeah, really, I think number one thing, of course, is the inventory or the fleet inventory. So your basics, you know, what vehicles are they, what model year, what model, what make, all that basic kind of information. Realistically, probably the number one thing is, you know, the utilization of that fleet for the assets. How many miles? How many hours? You know, whatever kind of way you really track utilization for vehicles. Another big one is maintenance and repair information. So, you know, what's the labor cost? What's your parts cost? All that kind of good stuff. Really fleet budgets as a whole, that too. So, you know, what— How much are you budgeting for? How much are you actually spending? You know, what kind of charges are you making in regards to your fleet for labor, you know, washing cars, any kind of upcharges, any kind of stuff like that? You know, performance data in general on an organization. So looking at KPIs. And really, you know, even more in-depth, so, dates, you know, when was a vehicle in service, when was it sold, you know, auctionary kind of information, really anything that really has to deal with your fleet overall.

Well, good. And, you know, I almost chuckled when you mentioned the first one, fleet inventory data, you know, as a consultant over the last— oh, it is that, you know, I've been a consultant for, oh my gosh, 20— this is my 24th year as a fleet management consultant. And I would say 98% of our studies start with, hey, we need a fleet inventory. And over the years, what we've done is actually developed a very comprehensive Excel-based spreadsheet template that basically tells the client, hey, these are the data elements that we need. I mean, we set up the column headings, we set up the column formatting, we set up all of that stuff. But in your experience, and I've done probably 400, 450 fleet studies throughout North America at this time. And you've done well over 100, I'm sure, by now. What percentage of fleet studies that you've worked with and fleet inventories have you worked with have actually completed that fleet inventory template 100%?

Oh man, it's got to be in the single digits as far as percentage, honestly.

Exactly.

There's always some piece of it missing, right? And, you know, that's okay. It's, it's not the end of the world, quite honestly. And it's the reality we live in. So, well, it is.

And, you know, and it's a shame because so many people now are using fleet management information systems. You know, this data that we're asking for doesn't require necessarily any analytics. It doesn't require an analyst. It doesn't require an IT guru to come in and, you know, write specialty reports or SQL, you know, summary questions or whatever that kind of stuff is. It really just means extracting the data that you have and exporting it to an Excel spreadsheet. And yet oftentimes we just don't get the information. And part of the reason is that it's not there. So, you know, we talk a lot about the accuracy of data collection and input into the system. So what are some of the common data issues that you see with some of the clients that we work with?

Well, like you just mentioned, probably the number one thing is just missing data in general. Either it's not being collected or at some point it was being collected and it stopped. So there's gaps within it. You know, it's really how are you getting that data? Number one, right? A lot of people are still stuck manually entering information. So the data might be there, but, you know, it might not be very good. Ultimately, someone might have fat-fingered here or, you know, all sorts of issues that can come with manual data entry. They forgot to do it, you know, all sorts of stuff like that.

Well, one of the things that I've noticed— sorry to interrupt you there, Griffin. One of the things I've noticed is that a lot of times when we're dealing with data in a fleet organization, Whenever there's a transition within the organization, that's where we see those gaps in data. And, you know, obviously when someone goes from one fleet management information system to another, there's that whole effort of what do we take with us? What kind of data do we need to make sure that we transfer to the new system? How much of that information do we need? What can we get rid of or what can we store in a legacy system somewhere else? But also, and that's understandable, The other thing that I find where we often find gaps in the data capture is when there's a transition to, you know, somebody new in the shop, whether it be a new fleet manager, a new fleet administrator, or maybe just the supervisors or technicians that are inputting data into work orders. It seems like there's the lack of consistency from, you know, employee to employee, from supervisor to supervisor, or shop to shop. Have you seen that as well?

Oh, definitely. Yeah. I mean, just, just from hearing, you know, almost sometimes mid-project, you know, those positions will switch. And it's, it's almost kind of a whole, you know, coming up of this new person, right. And a lot, a lot of ours is a lot of situations that have happened are, you know, bringing that new person up to speed, right. So definitely, you're absolutely right. There's, there's always going to be, you know, within those transitions, this period of time where that new person has to come up to speed. And likely during that period of time is when those issues are going to happen. So yeah, that's spot on, Tony.

And in so many studies, what we find out is that we get halfway through the study or three-quarters of the way through the study, and, you know, we're going on a base level of assumptions that, hey, the fleet is 575 assets. And so we start providing the client preliminary deliverables and they say, well, no, our fleet's 1,000 assets. And we're like, well, we gave you the spreadsheet template, we asked for the inventory of everything that you maintain and repair, anything that's in your management purview, and yet they give us an inventory of 575 instead of 1,000 units. So, you know, it's making sure that we get the entire view or perspective of the fleet is important. And that includes a lot of the data elements. So Griffin, you know, one of the things that we also talk about a lot of in addition to data capture and input is how do we find errors? How does someone within the fleet organization, how can they work to find errors within their data sets?

Yeah, so that's definitely probably, you know, one of the most time-consuming things on a lot of the projects that I work on is, you know, making sure that data is good because, you know, everyone says You know, garbage in, garbage out, right? So really, it's the beginning of most of my projects are making sure the data is actually good enough for us to work with, to actually provide people with the results that they're, you know, they're looking to see. So really, you know, what are the big ways to find those errors? I guess for an organization, for themselves, it's really exception reporting, right? You know, setting up basically kind of these, these markers where, hey, you know, this doesn't make sense anymore. You know, I can't put on 100,000 miles in a single month, right? You know, maybe you can if you're in a certain business, but the majority of people certainly can't. So really, it's finding those markers that you're, you're kind of looking out for. You know, on an annual or quarterly basis that will kind of make you notice, hey, something's up here, right? You know, maybe our manual entry, someone fat-fingered it, right? Maybe the system it's pulling from, you know, things aren't lining up the way it's supposed to be. So that's probably the best way organizations can really help themselves find errors is one, you know, if you're able to setting up those kind of exceptions. And then also, like I mentioned, you know, taking a look at it. You know, for some small organizations, you know, it's hard to do that, right? You don't have the people to spend the time scouring over the data. You're only really pulling reports kind of every now and then. But for some of the bigger organizations, you really, you know, it helps out a lot to have a person who's kind of looking at the data all the time, right? Like an actual fleet analyst. It's super helpful because they can catch those kinds of things and fix, you know, whatever potential problem is going on way early on, so you're not, you know, realizing you've got years of terrible data and everything you've been reporting on isn't real, right? So that's, you know, that's my suggestion to organizations as far as trying to catch errors. You know, the way that I find errors is really, you know, I'm looking at— I'm analyzing data, I'm trying to find outliers. I'm trying to find those, those big mess-ups. Those are the easy ones, right? And then realistically, like, making key performance indicators and comparing them to, you know, other organizations where it makes sense, where the context kind of fits. You know, some of the things I look at is, you know, looking at the labor hours per vehicle for, you know, a year and the labor cost. You know, does it make sense? Is the cost— is your, you know, labor hour cost $100,000. You know, that doesn't make any sense. No one's charging that much for a labor hour. Making sure that kind of fits, right? You know, on the fuel side, you know, how many gallons did a vehicle use and how much fuel did you charge out for that vehicle? Does that make sense? Is it, you know, $100 per gallon? That doesn't make a lot of sense, right? You know, so really it's kind of looking at the numbers and then fitting in the context of reality and seeing if things really make sense.

Well, and I think you're right, Griffin. And I think I'm going to go a little technical on you and give you a technical term that I like to use. And does the data really meet the, what I call, smell test? Does it smell right? You know, sometimes you can just look at the numbers and say, there's no way. Just last week, I was working on a fleet technician productivity analysis for a client, and one of their technicians had reported over 4,000 hours of direct labor in a single year. Now, we all know that, you know, the standard work year is what, 2,080 hours? And, you know, we would like to see a technician get between, you know, somewhere 1,500, 1,700 direct labor hours per year depending on the industry. And when the number came up, 4,000 direct labor hours, you know, Okay, there may have been some overtime, but I don't think there was that much overtime. So, you know, that's when you identify, hey, this is an outlier, we need to go back and look. And of course, a lot of times what happens on something like that is— and in this case, what they found out was the technician had clocked on to a job, and they were doing real-time work order tracking. So he clocked on to, to a task, and then at the end of the day or at the end of his shift, he forgot to clock off. And typically, not a huge deal. But he had clocked off and had taken PTO for 2 weeks. And so, that clock was running the whole time until someone finally found it. So, there's a lot of reasons that that happens. But I think, you know, like you said, looking for those outliers. You know, another one that's very common and I do it. It's very simple. You know, I'm not a data analyst like you are, Griffin. But I know my way around Excel quite a quite well. And so what I do is if I look at the model years for fleet inventory, and even if the fleet inventory has 5,000 assets in it or more, you know, with a simple sort or a filter, you can go in and look at, or even a pivot table, you can look at the model years and find those 1909 model years. And obviously we know, unless it's some type of antique or parade vehicle or something like that, that it's probably been a fat finger. It probably should have been a 1999. And then you dig a little further, you look at maybe the delivery date or the in-service date. And if nothing else fails, then you just simply run the VIN decoder and find out, you know, when that vehicle was manufactured. So there's a lot of very simple ways that you can do that as well. And I think using the tools that you have at your disposal, like Excel, like sorting, like pivot tables, like filtering, is an easy way to find some of that stuff.

Absolutely.

So Griffin, you know, we talk a lot about data consistency and integrity and inputting data. What are things that an organization can do to avoid these data errors to begin with?

Yeah, so I mean, a lot of this stuff I've learned from you, Tony, just through all the projects we worked on. You know, I think the number one thing from kind of the top level is policies and procedures, right? Like SOPs. Making sure that you've, you've set it in stone. This is the process you need to go through. Look, you, you need to record this information every time you're doing a PM. You know, all sorts of, all sorts of procedures and policies that really kind of, you know, set it in stone. So if someone's not doing it, you can point back and look like, hey, you know, I'm not just asking you as a favor. You've got to do this. It's part of the job requirements, right? Another one is really kind of, you know, utilize the systems that you have. I think nowadays almost everyone has an FMIS system. And, you know, there are a lot of them are very in-depth, very big. They hit every single function. And it can be overwhelming for a lot of people to really utilize it. You know, we've, we've run into a lot of, um, clients who don't like their FMIS system. And, you know, most of the time what I found is, you know, realistically it's not the system's fault. They're just not using it, you know, to its full capability. They haven't been fully trained on it. And, you know, maybe it's the system's fault for not being as intuitive as people like these days with all the the operating systems that people have with phones and all that kind of stuff that make it super easy. A lot of them are dated. But, you know, realistically, if you use that system to its fullest potential, you know, it's, it's kind of built-in ways to kind of stopgap, you know, really creating those errors in the first place. You know, and, and a lot of it is setting up those systems to work for you, right? So there's a lot of time investment that you have to make. And like, like you're saying, Tony, you know, any kind of transition period, you know, you're going to kind of lose out on those steps if it's, if it's at the higher level that actually set that up, right? So I think it's taking the time to invest in your systems and really, you know, help them help you. So I think in the long run, ultimately, you know, once, once you have things going and it's smooth, you really don't have to pay that much attention to it other than making sure and checking that nothing crazy has happened out of the air, right?

Exactly. And I think one of the things that people don't realize as much as they should is, you know, that transition of the workforce is happening quite remarkably now. You know, the average length of time that people are staying in a job for a millennial is 2 years. You know, the baby boomers stayed in their positions 15 years on average. So, with all this turnover and transition, SOPs, policies and procedures, are so important. And I would even take it one step further. You know, you always start with a policy, then you start the procedure, but I like to add a work instruction. The work instruction is very specific. You know, when you're doing this activity, this is step 1, this is step 2, this is step 3, this is step 4, because you really need that desk reference manual almost to instruct people what to do and how to do it. And again, just, just going back to some of the consulting gigs that we've done over the years, years, you know, we'll ask a client, hey, run me your average repair turnaround time for your shop. And they run the report. You know, almost every fleet management information system out there can, can generate that report. It's typically a canned report, you know, how, how many vehicles or work orders are closed on average, you know, within 24 hours, for instance. And they'll run you the report, but then there's always a disclaimer. It's like, well, here's the report, but I don't think it's right. You know, the data is not right. We haven't input the information correctly. You know, Tony over puts it in this way, Griffin puts it in this way. So, I don't think this is very good. Well, then why are we capturing this data? You know, I mean, we're capturing it to monitor our performance, to benchmark ourselves, to identify are we getting better or are we getting worse? And so, I think, you know, having specific data protocols in place is very important. I think the other thing, you know, and again, from we all purchase everything online anymore and look at your credit card. When you input that credit card, there are fixed number of spaces of data of input fields and cells that, you know, it's not going to let you put in, um, 13 for your month. You know, it— there are strict data limits on the input. And I think setting up cell limits, whether it be in Excel or in a database or any type of FMIS, making sure that you reduce the possibility of, of the fat finger approach or, or input errors And it increases consistency because if they have to put in a 2-digit month code, then if it's the 3rd month of the year, you're gonna have to put in 03, you know, and I think that's gonna help your data as well. You know, I, I like to, Griffin, you're not old enough to remember this, but a lot of times on the fuel management systems out there, you know, they've always had, well, you know, slide your card to get access to the fuel. Then it would say, enter your, you know, PIN number or enter your vehicle number. And then, you know, people realize that they they could grab the mileage or odometer readings or our readings off of a piece of equipment or vehicle. And so they said, enter your mileage. So you know what most people did at that time? They would punch in 99999 and hit the 5 digits, and then you would have access to fuel. Then they realized, oh, we need to be smarter about this, we need to put mileage limits. So most, or well, I think all fleet manager or fuel management systems now have that functionality where you can actually put limits on it. So you can't put in a mileage greater than, you know, 750 miles from the last time you fueled up. So I think there's, there's some limits that you can put on data input as well, whether it be your fleet management information system, your fuel management system, or even just, you know, analytics that you're doing outside of those systems, you know. And think about just the You know, even though we have all this technology, even though we have, you know, artificial intelligence and all these other resources at our fingertips now, think about work order systems, you know, whether it be a fleet management work order system, a facility management work order system, you know, public works work order system, whatever it is. Generally, what you do is you have that technician that is doing the work complete the work order. And then there's always that supervisor that comes in reviews what was input into that work order to make sure that, hey, you know, they put 40 hours on this brake job when it should have been 4 hours. So, someone just mistakenly added, you know, a zero to the 4 hours. So someone's looking at that secondarily to make sure that that data has been input correctly. And so I think, you know, having that second set of eyes, we do something here at RTA called internal peer reviews. And so even though Griffin, I know you never make a mistake, I still think, you know, when you said me something, I still look through it just to make sure that, hey, does this meet the smell test? Yeah. And, you know, it's always good to have that second set of eyes. So Griffin, what else? What other things that you think of that— how, how can we handle missing or bad data?

So really, I think it's, it's kind of, you know, keeping up with those measures, I think. Are really kind of the big way to do that. So, you know, if you're not tracking KPIs, start tracking KPIs. I think that's probably the easiest way to kind of tell when something's amiss because realistically, you know, you're taking your measurements, comparing them with each other to take a look at, you know, important things. And what do I mean by when I say that? So, you know, what's your miles per gallon, right? What's your average miles per gallon? That's probably one of the numbers that I look at the most to kind of tell with utilization for gallons and also miles, you know, something funky here, right? Are you getting 100 miles per gallon? You know, maybe with some of these new vehicles. I don't think so though.

Definitely not.

Definitely not with the diesel truck. You know, we all wish. So really, you know, if you're currently doing it, keeping track with it, right? Not setting it up and then forgetting about it until you need to pull bullet for your annual report, right? You know, really keeping close eyes on those things because, you know, issues can happen in a moment, right? You're messing with some setting and then it changes everything. And if you're not paying attention, you're going to miss it until you finally look at it, right?

Absolutely.

So, you know, also, if you have the time, if you have the person whose job it is, you know, take a close look at the granular level. Of things. You know, take a look at, hey, you know, is the police department reporting their utilization correctly? You know, are they, are they getting, you know, 10 miles a day? That doesn't make a lot of sense for a police department, right? You know, kind of look at the data, but also add the context, right? You know, a lot of people say, hey, you know, is the data reporting? It's working. Okay, good. It's, it's doing its job. I don't have to worry about this anymore. But a lot of time, you know, things can be working and it's not making any sense, right? It's not pulling from the right place. You know, you're making those manual errors. Just because data is appearing doesn't mean it's correct, right? A lot of people just, hey, oh, it stopped, now I have to worry about it, right? So, you know, looking at the granular level of does this make sense, I think is probably one of the biggest things that people can do. Because, you know, I said missing data is a big issue earlier, but quite honestly, you know, I'm getting data most of the time, at least in some sections, and it's just not good. It doesn't, doesn't pass that sniff test, right? And that's just because people are seeing it come in and then they think it's fine. They don't have to worry about it anymore, even though, you know, realistically it's not coming in correctly.

Well, exactly. And, you know, I think anytime that you can avoid the manual input, you're going to be better off. I mean, look at parts inventories. You know, there's barcoding and barcode scanners for a reason. What it does is it eliminates that opportunity for failure. You know, it eliminates the opportunity to make mistakes when you're transposing letters and numbers into, you know, a system. You know, look at the VINs. You know, VINs are what, 17 digits for vehicles? And there's a reason that They don't use the letter I, O, or Q in a VIN because they can be transposed too easily. And so, you know, there's reasons for the data protocol. You know, the other thing that it's just, it's one of my pet peeves is the inconsistency in the way people input data into systems. And again, the most simple one is the make of a vehicle. You know, how many times have we done, looked at, you know, fleet standardization, for instance, and we go through and, and, you know, they list the make as Chevrolet. Okay, great. But then they make the list, then they, you know, the next one is Chevrolet, but it's misspelled. Then they use Chevy, then they use Chev, then they use GM. So, you know, there might be 5 or 6 uses of the word or some form of the word Chevrolet in the system. And if you simply run a report on Chevrolet spelled the correct way, you might miss a whole group of datasets. And so whether, you know, and even like the models, I mean, I mean, you know, Ford, the F-Series trucks, you know, is it F-150? Is it F-150 with no spaces? Is it F space 150? You know, so the way that people put the data in the system is so important. And same way with department names. You know, if I'm working with a city organization right now and Department of Public Works, is it Public Works? Is it Department of Public Works? Is it Public Works Department? Is it DPW? Is it PW? So, you know, you really need a set of protocols and you really need to identify, what it's gonna be. And it, whenever possible, get that information either in a dropdown list or, reference a, a department code number and then, then bring in through a VLOOKUP or something, the, the actual, description of that. So there, I think there's a, there's a lot of ways to do that and a lot of ways to do it properly that you can really clean up your data.

Absolutely.

Griffin, what are— what other things do you have? Would you have any advice for people, you know, down at your level, the fleet analyst or the analyst level that are inputting data in the organizations?

Yeah. So I think, you know, one way you can kind of check on yourself is really when possible, use use kind of multiple data sources to check your accuracy on things. You know, like you mentioned with the fuel systems, you know, you, you're getting mileage in there, right? You're looking at the mileage on there. You know, on a lot of work orders, you know, people are recording, hey, what was the odometer reading at this point? So you can, you can kind of use multiple work orders to kind of, you know, figure out where in time, how much utilization was used. You know, cross-reference your fuel stuff, find out kind of what the utilization was on that side too. So really, you know, I've had to do that a lot where, you know, I'm trying to make sense of things over a period of year. Hey, you know, how does it come out on the fuel side? How does it come out on the maintenance side? You know, are these things somewhat accurate? Are they perfectly accurate where, you know, boom, I can automatically trust all of this data? A lot of time I can't. So, you know, we'll have to go back to the client and and talk to them and say, hey, you know, which one do you think is accurate? Right. Which one do you think, you know, you're actually tracking to a fine point?

Well, one of the things that you said earlier, and you've said it a few times throughout the podcast today, is a context. You know, how do we look at data in context? And that's one of the problems with hiring just an analyst or a data analyst. They're great with numbers. They may be you know, the best database, you know, analysts in the world or whatever. But if they can't understand what the numbers mean in context, they're not going to know, you know, where those outliers really are. Yeah, you can do some statistical analysis and find outliers, but, you know, you're not going to be able to give the sniff test. And that's why one of the reasons that we really like having someone like you, Griffin, on our team. And you're a fleet guy, you're a fleet person, you understand fleet. You've been around fleet for the last 10 years. So, you know, when we look at miles per gallon, when we look at utilization trends, when we look at cost or cost per AEU, you know, if something's outlandish, you're going to recognize that. And that's what's important, I think. So I just wanted to come back and circle around and, and stress the, the fact that, you know, that sniff test, that smell test, that understanding data in context is so very important. But with that, Griffin, I really want to thank you for joining us today. We're definitely going to have you on more in the future. You have great insight into this data world somewhere that you like to live, and I don't necessarily like to live that deep in the numbers, but great having you on today. And for those out there, thank you very much for joining us again. If you have any comments, suggestions, questions, please reach out at podcast@rtafleet.com. We'd be more than happy to listen for suggestions for new topics for the show, to answer any questions that you may have, or reach out individually or directly to us through our website. With that, I think that's— we'll sign off for today, and everyone have a great day.

Have a good one, guys. Thanks for having me, Tony.

Thanks, Griffin.