Analytics for auto repair operations
This franchise offers high-quality auto body repair services by combining advanced training, OEM-certifications and the use of state-of-the-art equipment. However, they lacked high-quality analytics to help them build upon their high level of customer care.
Overview
THE CHALLENGE
An auto repair franchise wanted to generate analytics for their operations across a variety of dimensions.
THE SOLUTION
Ollion designed an analytics solution that curated raw employee and operations data into an actionable, user-friendly Tableau dataset.
THE OUTCOME
The client's executive team now has an accessible, comprehensive dashboard they can pull up in seconds.
The challenge
The client wanted to generate analytics for their operations across a variety of dimensions, including time, employee, service type and franchise location. They struggled to extract the required data out of their various source systems and deliver a report that was:
- Sliceable: easily apply filters to view different data frames
- Drillable: able to start at a high level of context/KPIs and expand into more detail
- Scalable: add new franchise locations to the underlying model with minimal effort (rather than a one-off report)
In other words, the requirements were to build an automated data ingestion process from source systems and Excel reports provided by locations not yet onboarded. The Ollion team then needed to model the data into a data warehouse in Snowflake that would allow for the following dashboards and reports:
- Nine-box executive dashboard to show KPIs and trends over time of open, closed and in-progress repairs/jobs across the company and the dollars associated with each; incoming and answered phone calls; repair cycle times; and head counts
- Ability to drill into these KPIs and more detailed reports for each location, with a date parameter and Snowflake Data Cloud snapshot table allowing the user to see the dashboard and graphs at any point in the past
- Operations dashboard similar to the executive dashboard but designed for quick use by each location’s shop manager with additional trending graphs and shop ranks among KPIs like cycle time
- HR dashboard showing benefit enrollment information and a dashboard showing trends in hires, fires and tenure with by-location drillable reports
- Dashboard displaying KPIs involving incoming and answered/missed calls with drillable reports to show these details for each location
The challenge for the client was enabling non-technical sales and marketing employees to discover where they can make operational improvements based on the data.
The solution
Ollion designed an analytics solution that curated raw employee and operations data into an actionable, user-friendly Tableau dataset. First, the development team used automated Python scripts and dbt to land ingested data from external APIs in Snowflake, creating a straightforward data warehouse.
Then, the report developers worked with the engineers to develop a semantic layer in Snowflake used to meet the KPI, trending graph, dashboard time travel feature and detailed report requirements. The semantic layer and dashboards/reports were developed at the same time and then published to Tableau Online with extracts from Snowflake as each dashboard’s data source. A security process was developed for the client so executives, regional managers and shop managers could see data deemed appropriate for their level.
The outcome
The executive team now has a dashboard they can pull up in seconds, giving them access to information that used to be in several out-of-the-box reports or manually created reports. The new executive dashboard contains the KPIs and changes in KPIs that they need to track the health of the business on a daily basis.
Additionally, detailed reports allow them to dive into trends to begin to take action. For example, a single shop with an abnormally high number of unanswered calls means potential missed business and possibly indicates a need for more front desk employees. The executives can use the operations and HR dashboards in similar ways to dive into greater detail about their findings in the executive dashboard.
The operations dashboards available to every shop manager allow them to quickly see the health of their shop. Previously, they would receive a report from the finance office once per month showing the KPIs that they now can view daily. The new dashboard provides actionable information such as the number of cars on their lot and the estimated dollar amount of the repairs, allowing them to prioritize the order of repairs. The dashboard also provides cycle time information, the shop’s rank among other shops, etc.
The finance team no longer has to manually combine each shop’s information from the source systems and Excel docs. This was previously done manually a couple of times per month but now is available to view daily, which has greatly increased insight into the health of the business and raised awareness of areas that can be addressed mid-month instead of the following month to more quickly improve sales. The ingestion process and data warehouse also allow their team to ingest new sources and conduct further research.
The greatest impact is the ability to quickly imprint new franchises into a standardized data warehouse of employee and operations data and accelerate analytics within a growing enterprise. Our client is growing fast, so they are always adding new shops both on and not on their current source systems. Ollion provided a process to ingest sales and operations data for both scenarios. Manual processes to gather this information would have become more difficult and time-consuming, while our solution makes viewing company-wide sales and operations information something they can do daily (or as often as they upload shop data from shops not on their source system) to take action in problem areas.
