An Auto Repair Franchise

About the Business

This franchise offers high-quality auto body repair services by combining advanced training, OEM-certifications, and the use of state-of-the-art equipment. Its teams focus on delivering a high level of customer care and ensure that your vehicle is fixed right. It proudly employs its neighbors and friends across multiple states in the Central United States. The franchise’s shops are locally known and trusted community members and in 2019 provided quality repair service for more than 20,000 vehicles.

The Business Challenges

The client wanted to generate analytics for its operations across a variety of dimensions including time, employee, service, and franchise location. It was struggling 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 (so NOT 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. We 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

 o 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 showing similar information as 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 HR 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 2nd Watch Solution

2nd Watch designed an analytics solution that curated raw employee and operations data into an actionable, user-friendly Tableau dataset. First, the development team landed ingested data from external APIs using automated Python scripts to land the data in Snowflake using dbt, creating a straightforward data warehouse.

Finally, the report developers worked with the engineers to develop a semantic layer 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 allowing executives, regional managers, and shop managers to see data deemed appropriate for their level.

The Business Benefits

The executive team now has a dashboard they can pull up in seconds with accessible 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 know daily to track the health of the business.

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. 2nd Watch provided a process to ingest sales and operations data for both scenarios. Manual processes to gather this information would have become more difficult and timeconsuming, 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.