Executive Summary
SSF Imported Auto Parts LLC is the leading North American warehouse distributor of OEM and aftermarket automotive parts for European vehicles.
SSF’s Business Intelligence team recently embarked on the journey of developing and deploying Qlik Sense as an end-to-end enterprise solution to replace scattered data sources, and were tasked with creating short and long-term goals to navigate the business impact of COVID-19.
Due to the global pandemic and resulting lockdowns, industries across the globe came grinding to a halt in March 2020. Short-term, SSF needed to maximize sales and protect profit margins while long-term, the organization needed a tool-box of analytics and data views, allowing the company to quickly make fact-based, data-driven decisions.
These actionable insights would result in changes to SSF’s sales, purchasing, pricing and operational strategies which reversed much of the declines brought on as a result of the global pandemic.
The following case study documents the methodologies used by the SSF BI team in conjunction with Qlik Sense to navigate one of the most challenging business environments in modern history.
SSF Imported Auto Parts LLC – 45 years of serving the nation’s auto repair industry
Check engine light illuminated on your dash? Squealing brakes? Know your car is due for service soon? It is probably time to head to your local automotive repair workshop, the core of SSF’s warehouse distribution network.
If the service advisor mentioned they were ordering parts to complete the repair, in many cases, the genuine original equipment or aftermarket parts were coming from one of nine SSF warehouse locations, which offer multiple same-day deliveries, as well as overnight shipping – directly to the workshop.
Founded in 1976, SSF Imported Auto Parts LLC (SSF) is based in South San Francisco, California and serves the $880 billion automotive industry. SSF specializes in European carlines and offers a full program of genuine, OEM and aftermarket auto parts to customers nationwide.
COVID-19 lockdowns force the entire automotive industry to pump the brakes
In early 2020, COVID-19 began making news across the world and changing the way we live, work and travel. From manufacturing constraints due to social distancing requirements, logistic delays caused by container shortages and port congestion, to an immediate drop in consumer driving habits, the automotive industry was flipped upside down. By the middle of March 2020, passenger miles driven, a rough estimate of demand for the auto repair industry, were down 50% and the economy had entered a recession.
As with many companies across the world, business at SSF Imported Auto Parts experienced a steep decline, however, the precise underlying causes were unclear. SSF could see that demand was down, but to formulate an effective response they needed to be able to identify why it was down and which segments of the business were most affected.
With lockdowns causing difficulty across the economy, SSF Imported Auto Parts CEO Thomas Beer tasked Chris Kahler and Ryan Lessig, the companies Business Intelligence and Strategic Projects team, with generating critical insights to answer those questions.
Access to integrated, company-wide data is the foundation of successful analysis
Chris and Ryan used the Qlik Sense Data Analytics and Integration Platform on their fact finding mission. The BI team was already in the process of developing a large suite of Qlik Sense applications to replace legacy analytic tools and scattered data sources. The SSF Qlik Sense environment consists of internal and external data sources – IBM AS400, Oracle NetSuite, IHS Markit, and Elite Extra, to name a few.
Ryan considers the ability to integrate different sources into a comprehensive set of data key to the value proposition Qlik offers:
“Prior to integrating Qlik as a company-wide BI platform, everyone was looking at their own individual reports. With Qlik, we’ve be able to tie dozens of data sets, spanning across all departments, and begun breaking down silos within the organization. This gives us an incredible level of transparency that we can utilize to understand all aspects of our business – and before many data sets were not accessible to all users or the data was outdated by the time you received it.” – Ryan Lessig
Planning for success: What gets measured gets done
In the world of analytics and business intelligence, a measure is a quantitative calculation used to represent data in a visualization -- and finding the most relevant measure to track is the crucial first step of any analysis. While there are standard measures such as sales or profit margin, advanced analytics requires a broader knowledge of the business and the many different factors that can affect a seemingly simple measure like sales.
For example, following Chris’ intuition that a spike in the number of orders the company was processing was increasing while sales revenue was declining, they created a measure to analyze invoices with only a single item purchased. The trend was clear: a major spike in single-line invoices occurred right as the COVID-19 lockdowns took effect, indicating that only critical car repairs were being performed by the workshop. Another insight from this measure implied workshops were potentially price shopping for the absolute best price and / or unable to upsell customers on routine maintenance items.
Leveraging Qlik, Chris and Ryan continued their analysis creating multiple order-focused measures which allowed them to dissect the business and bring to the surface data-driven conclusions that they could present to Mr. Beer, CEO. Combing these new measures with time-series visualizations such as line charts, bar charts and bullet charts, the user can then see how these trends changed over time.
Digging Deeper into The Data with The Right Dimensions
To drill down into the measures to create meaningful insights, dimensions – qualitative information – from tens of millions of rows of raw data was combined from multiple data sets. Categorizing this information in dimensions allowed the team to make sense of what they would be looking at, without being overwhelmed by the massive quantities of data.
Leveraging a Qlik Sense feature called transformations, they combined millions of product and pricing records together to create groupings of products based on their average sell price. Comparing sales at different price levels before and after the lockdowns took effect, Chris and Ryan were able to achieve a second key insight regarding product price in comparisons to sales growth or decline.
“Ryan and I [Chris] have hit our stride. My diverse understanding of the automotive aftermarket, from the days in my family-owned workshop to tenure at parts manufacturers and distributors – I have a broad perspective of the automotive industry as a whole. In combination with Ryan’s product management and data science background, we have developed a unique ability to bring raw data to life and analyze current business trends – allowing for the creation of actionable insights backed by fact-based solutions.” - Chris Kahler
Using Qlik to enhance raw data with new measures, dimensions and transformations – combined with the power of data visualizations, Chris and Ryan were able to combine all the pieces needed to create a data supported and informed business case.
Accurate, Data-Driven Conclusions Lead to Swift, Effective Action
Meeting with Mr. Beer, two short weeks after originally being tasked with tracking down the impact of the pandemic on SSF’s sales, the BI team came armed with the ‘golden nuggets’ found in a deep well of data. Those ‘golden nuggets’, combined with a keen knowledge of organizational, market, and global events allowed them to weave the most pertinent data points together into a cohesive story:
“The fact that the pandemic is affecting the entire automobile industry is undeniable. Vehicle miles traveled are down more than 50% across the country, and 78% of customers in the auto repair industry are reporting declined sales since the lockdowns had begun.
When times are good, drivers often purchase add-on items from repair shops in addition to the spare parts required to get them back on the road, and by analyzing single-line invoices we can see that our customers are having a more difficult time upselling their customers. By the same token, in good times shops will place all their parts orders with a single distributor for the sake of convenience, even though some individual parts may be available from another supplier at a lower cost.
With shops everywhere having to tighten their belts, they were not only placing more single-line and small orders, but fewer large orders as they shop different suppliers to find the best deals. Shops were acting in the classic manner of price-constrained consumers by increasing their search effort.”
Aided by Qlik storytelling mode, Chris and Ryan were able to curate a compelling case associated with the business challenges faced due to the global pandemic and subsequent lockdowns. Using data pulled across multiple departments within the organization, SSF leveraged Qlik and was able to respond with effective, fact-based decisions.
“Traditionally the automotive aftermarket took an old school approach to most decision-making processes. Looking back, even 5 years ago, organizations would have static spreadsheets living within silos of departments and individuals. By leveraging Qlik to implement a company-wide BI tool and break down said silos, we have forged a culture that will not settle for less than fact-based decisions, forcing the conversation to be data driven.” – Chris Kahler
While COVID-19 certainly accelerated SSF’s development of Qlik Sense, their BI Team already had sights on the future of data analytics and business insights.
A holistic approach to Business Intelligence
Prior to Chris and Ryan joining SSF, data was segmented and one-dimensional. With the power of Qlik at their disposal, coupled with their diverse backgrounds, the SSF BI team has developed an enterprise level tool to replace spreadsheets and legacy business analytic tools.
SSF has utilized Qlik Sense to be the ‘one-stop shop’ for data needs throughout the organization. Spanning the Sales, Product Management, Purchasing, Pricing and Operations departments, specific applications have been developed and made available to key stakeholders to support their day-to-day functions. With analytic ready data, these applications have transformed the way SSF’s employees and suppliers review the business:
“Working with the SSF vendor scorecard using Qlik Sense data has been invaluable in measuring our performance as a supplier and also highlighting areas where we can improve. Having an insight of the metrics allows us to build a more collaborative problem solving relationship where we “let the data speak” and direct our common efforts. A first class tool that I highly recommend.” Peter Henry, General Manager, Nissens North America, Inc.
Improving data accuracy and availability to business process owners has enabled SSF to drive conversations with data to support all angles. Continuous improvement and development of SSF’s Qlik Sense environment will only further solidify the holistic approach to BI that they strive for.
Analytic Stack and Data Architecture
SSF has deployed Qlik Sense Enterprise Client-Managed on AWS to fulfill their BI needs – from data integration to analytics to data warehousing. They rely on AWS to host Qlik in the cloud, granting users across the country high-speed access to even the most complex and demanding Qlik Sense applications with enterprise level reliability.
Source tables from IBM AS400, Oracle NetSuite and other systems are converted to flat files and stored in AWS S3 buckets for seamless access and a single point of read / write. Updates from source systems happen throughout the day in addition to an automated ETL process – making data available to Qlik Sense users in a fraction of time compared to a couple of years ago.
Qlik Sense Enterprise, running on an AWS EC2 instance for reliable computing power, and seamless access, ingests flat files from AWS S3, performs the ETL process and creates QVD files. Qlik Sense sends compressed QVD files back to AWS S3 for long term storage. Qlik then builds data models with the clean QVD files to power SSF’s applications for dozens of users.
Qlik nPrinting runs on a second EC2 instance for SSF. Powered by Qlik Sense and AWS, users can access, visualize, manipulate, and analyze any piece of data SSF possess for true data-driven decision making.
About IPC Global
IPC Global is leader in data integration, data analytics and cloud technology and provides Qlik and AWS consulting and support services to SSF Imported Auto Parts LLC. To learn how IPC Global can help your organization make better fact-based decisions, get in touch with us.
Comments