- A private equity-backed company was rapidly growing through M&A and needed the ability to quickly integrate and analyze data to spot trends, track performance, and make informed decisions.
- Aptitive created a scalable data infrastructure for private equity analytics that quickly integrated, transformed, and centralized the data.
- The new infrastructure is now used to power fast, accurate, and reliable reports that leaders can use to track KPIs and make data-driven decisions.
Just the Headlines
Short on time? Here are the key facts.
A private equity-backed HVAC, plumbing, and electrical services group was experiencing rapid growth through M&A and needed the ability to quickly integrate and analyze data as they acquired new branches. Their data-driven approach to sales, marketing, operations, and other critical business functions meant they required a scalable modern data and analytics solution to deliver fast and reliable insights with clear KPIs in easy-to-use dashboards.
They partnered with Aptitive based on our experience building data and analytics solutions to drive a single source of truth for private equity investors and their branches.
HVAC, Plumbing, & Electric
Siloed reports, unsustainable analytics, and an unscalable data architecture
The first step of the engagement with the client was to understand their current challenges that were preventing them from getting the insights they needed. We learned that the client’s approach was creating small-scale reports within an online data visualization tool connected directly to source data systems. Data visualizations and reports were built directly off of full data loads from various source systems. As our client created more branches through acquisition, it quickly became apparent that their current architecture was not sustainable. Their data visualization tool was also not scalable, meaning it could not consistently refresh data multiple times within the same day. The lack of scalability would lead to lost opportunities for measuring performance and decision-making.
We uncovered that our client’s existing reporting system limited the types of data transformation and visualization they wanted to see. They needed to build out a more extensive system that would appropriately catalog and normalize all of their data, allowing for deeper analysis of critical data stored in locally-owned Excel spreadsheets.
To create the data infrastructure and reports our client needed, the project was broken into three phases:
Phase 1: Ingestion and centralization of data from source systems
Phase 2: Transformation of data into an analytics-ready format
Phase 3: Reporting and dashboard creation
Phase 1: Ingestion
We centralized data from multiple data sources (ADP, Intacct, and ServiceTitan) into a Snowflake environment. Various methods and processes were employed depending on the data and the source to ensure data from all sources was ingested accurately and efficiently. This was the first step to consolidate all store branches into a raw layer so we could eventually create our dimensional model for reporting and analytics.
Methods for Ingesting Data into Snowflake
The Aptitive team used this as the initial stage, ingesting multiple data sources (ADP, Intacct, and ServiceTitan) into a Snowflake environment. Based on the source system, we had a few methods to ingest data:
- Files were dropped into an SFTP branch connected through an Azure VM and landed into Blob storage.
- Emails were sent to an Outlook account containing an attached Excel document. A logic app was triggered and downloaded the Excel document into Blob storage.
- An API was called in JSON format, landed into a stage table, and then was flattened into a separate stage table.
Files were extracted from a cloud storage system through an Azure logic app, landing in Blob storage.
Phase 2: Transformation
Once the data was ingested into the data warehouse, the data needed to be transformed to ensure clean, consistent, accurate reports. The data transformation approach ensures the data is:
- Cleaned – Duplicates, erroneous values, and other data quality issues are removed or corrected.
- Standardized – The data is normalized in a way that aligns with a standard definition or format defined by the stakeholders.
- Organized – The data is “analytics-ready,” meaning data is organized in a way that aligns with how it will be used in analytics and reports.
The approach and technology used for transformation were selected to be easily repeated and scaled as the client acquired more branches and needed to ingest additional data sources.
Data Transformation Technology
Dbt was used to help with the transformations to build out the business layer. The testing scripts and polished UI utilized by dbt allow for a simplified process previously handled by manual configuration files. This approach will allow for a repeatable and scalable data cleaning process that automatically runs for each ingested piece of data.
Both the raw and transformed data are housed in the Snowflake data warehouse.
Phase 2: Reporting
Once the data was transformed and analytics-ready within Snowflake, Power BI was connected for reporting and analytics. Working with stakeholders from multiple business functions, including operations, HR, finance, and marketing, the Aptitive team designed custom drill-down dashboards based on the metrics, KPIs, and critical insights each function needed to see. Each functional dashboard was designed with a user-friendly interface and row-level security to ensure data was only accessible to the intended user, department, and/or region.
With the new solution in place, our private equity client can now add newly acquired branches into their reporting within a very short timeframe. They are also able to pull data from more source systems for more reliable metrics and comprehensive reports.
As of the project wrap-up date, Aptitive had integrated from hundreds of systems in just fewer than 50 separate branches so the parent company and private equity backers have a centralized view across their portfolio with the ability to drill down to individual branches. They are also able to add new acquisitions quickly and easily without any outside resources or support.
- What are the operating costs of individual branches?
- What are the profits by branch, region, and portfolio as a whole?
- What are the profits by service line?
- How are their actuals tracking against their budget?
- How are they trending by day, month, and year versus the previous year?
Service Technician Utilization and Performance
- What is the utilization rate of service technicians?
- What is the performance level of technicians?
- How do technician utilization/performance rank and compare by branch, by region, and nationally?
- Which technicians and branches are underperforming, and which are performing at an exceptional level?
Sales and Marketing Performance and Effectiveness
- Which branches have the highest sales? Which have the lowest?
- Which sales and marketing channels are most effective?
- How do sales and marketing performance vary by branch?
- What is the revenue of marketing campaigns and sales initiatives?
- Which branches have the highest turnover? Which have the lowest?
- What factors have the most significant impact on employee turnover?