Moving Fleet Data from Spreadsheets to Databases

Managing fleet metrics is a constant iterative process that requires a lot of willingness to experiment, and while it’s not for the faint of heart, anyone can take it on with the right mindset. We would know – Fleetio transitioned to a proper data platform from a sprawling spreadsheet filled with an intimidating conglomeration of internal metrics back in 2022.

Here’s how fleet managers can start thinking like data scientists as their roles in fleet evolve, using our own journey as a guiding framework.

Starting from ground zero

Maddy Flagg, a senior analytics engineer on Fleetio’s data team, emphasizes the importance of starting with what you have when it comes to fleet data reporting. "If there’s not much of an existing data framework, a Google spreadsheet is a good place to start for really simple stuff," she said.

For smaller operations, a spreadsheet can serve as an initial repository for essential data like vehicle lists, driver information, and vendor contacts.

"Obviously, you need a list of all of your vehicles, a list of all of your drivers, and contact information for anyone that you work with, like regular vendors or maintenance providers," Flagg said.

Making the switch

Fleetio’s experience truly highlights the challenges of scaling data management, to say the least. We were collecting a lot of data – cross-departmental touchpoints that left no metric stone unturned – while we scaled from startup to established company, and that data accumulated over the course of nearly 10 years.

"And for a long time, it worked,” Flagg said. “But as we grew, it became clear that we needed a more scalable solution.”

The turning point came when there was nowhere else to go – literally. The spreadsheet reached its physical limit.

"We hit the row limit for adding raw data, and it was clear we couldn’t use the spreadsheet anymore," she said.

The transition involved moving key metrics to a data warehouse, which in our case meant onboarding Snowflake.

"We had to get a data warehouse and set up processes to load and transform the data,” Flagg said. “Snowflake has become an industry standard, so that's what we went with.”

Building the infrastructure

Creating a robust data infrastructure involves a few key steps:

1. Data Warehouse Setup: Establish a data warehouse to store and manage large volumes of data.
2. Data Loading Processes: Develop processes to load data into the warehouse consistently.
3. Data Transformation: Implement methods to transform raw data into meaningful insights.
4. Regular Updates: Ensure data reflects real-time changes, such as vehicle information updates.

"You need processes to update the data. For example, if vehicle information changes, it should be reflected in the data warehouse as soon as possible," Flagg said.

Overcoming challenges

Fleetio’s transition wasn’t without its challenges. Lack of tranparency and consistency in how metrics were being recorded and reported was a significant issue. "When the company was small, that was fine, because if there's 10 people, they probably do know what [those metrics are] and you only need a couple of people to manage it, but we're a company of nearly 300 now, and that's just not sufficient anymore," Flagg said.

Another challenge was dealing with the limitations of tools like Google Sheets. "There’s just a limit to what Google Sheets can handle," she said. This limitation pushed Fleetio to reassess the metrics they tracked and the tools they used to track them.

"The effort was mainly to review all these metrics, decide what to keep, what to let go and then find the best way for people to look at them," she said.

Tossing the noise

Determining which metrics to keep is an important part of the transition. Flagg said this process requires a careful evaluation of each metric's relevance and impact.

"Start by identifying the metrics that directly align with your business goals and operational priorities," she said. Metrics that provide actionable insights or highlight key performance indicators should be prioritized over pure transparency.

"It was initially a lot of hyper specificity that maybe is nice to just have, but it can really muddy the waters and just make it hard to l do anything actionable at that point," Flagg said. “What we tossed was the noise.”

It’s also essential to involve stakeholders in this process. "There was no hard and fast rule of what was gotten rid of. It was just like all part of a discussion," she said.

Finally, be prepared to iterate. Your data needs will evolve, so regularly review and adjust your metrics, because what’s important today might change as your business grows.

Key takeaways for fleet managers

In a perfect world, it would be simple to just set up a database and watch the numbers flow in, but in truth, there’s no one right way to collect fleet data.

1. Start Simple: It’s totally acceptable to begin with accessible tools like spreadsheets to manage basic data. Transition to more robust systems as your data needs expand.
2. Prioritize Transparency: Ensure the processes for calculating and updating metrics are transparent and consistent, especially as your fleet grows.
3. Scale with Your Needs: Recognize when your current tools have reached their limits and be prepared to invest in scalable solutions like data warehouses and BI tools.

“I think a lot of people get hung up on being like, we have to start this perfectly right now, and future proof it for 10 years down the line,” Flagg said.

"It's not about doing everything perfectly from the get-go. Start where you can and scale as needed.”