Private equity-backed home services group

A private equity-backed HVAC, plumbing and electrical services group was experiencing rapid growth through M&A and needed 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 Ollion based on our experience building data and analytics solutions to drive a single source of truth for private equity investors and their branches.

Overview

THE CHALLENGE

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.

THE SOLUTION

The Ollion marketing analytics team created Looker sales and marketing dashboards consisting of several different visualizations to illustrate the client’s marketing metrics and their relation to the sales data.

THE OUTCOME

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.

The challenge

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.

The solution

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

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 3: 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 Ollion 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.

The outcome

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, Ollion had integrated data 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.

Key metrics

FINANCIAL PERFORMANCE

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