Scaling HVAC Operations With Data: A Complete Transition to Automated Power BI Reporting

Goal

The core objective was to replace fragmented sources and manual reporting with a modern analytical ecosystem capable of supporting daily decision-making at scale. Our client needed:

• A single data warehouse combining ServiceTitan CRM and manual sales plans from Excel

• Automated refreshes multiple times per day, moving close to real-time visibility

• 100+ business-critical HVAC metrics formalized, standardized, and documented

• A system where every KPI has one calculation logic, one interpretation, one home

• Reporting tools that work for field technicians, CSR teams, managers, and executives

• Independent analytics ownership — no reliance on Datacube black-box formulas

The end-goal was not to visualize data — but to finally gain clarity, consistency, speed, and operational truth, enabling the business to scale without increasing analytical overhead.

This meant building everything from architecture → pipeline → metric logic → visualization → documentation.

Result

We delivered a full analytical system replacing manual workflows with near real-time excellence.

Data Infrastructure

We designed a cost-efficient pipeline using a GCP VM + Apache Airflow instead of Fivetran — reducing projected spend 3–5x.
Data now flows automatically from ServiceTitan API and Google Sheets into BigQuery, where it is transformed and standardized through a 3-layer warehouse: Raw → Staging → Analytics.

Metrics & Governance

Together with the client we defined and implemented 100+ custom HVAC, technician, CSR, and marketing KPIs, documented formulas, business logic, and data usage guidelines. A full metric glossary now ensures that every team member speaks the same language.

Dashboards

We built a dedicated suite of dashboards to evaluate performance across technicians, sales roles, and the CSR team. Each dashboard follows a unified design system for intuitive navigation and fast comparison between roles.

HVAC / Plumbing / Electrical Techs

Three dashboards with identical structure, ranking the top 8 technicians by Total Sales for the month and showing individual performance metrics by specialization. Filters automatically adjust to HVAC, Plumbing, or Electrical units.

Sales – Techs

A cross-unit view highlighting the top 10 technicians by Total Sales YTD. Used to track sales efficiency, revenue leaders, and performance trends across the entire workforce.

CSR

Ranks the top 8 representatives by inbound call volume, covering all call center roles. Helps monitor service load, productivity, and call handling quality.

Marketing

Displays key marketing KPIs and campaign outcomes with comparative analysis across channels. Includes filters by Business Unit and Job Type for deeper insight into what drives ROI.

MTD Performance

A month-to-date summary of all core business metrics across the company with drill-down by business unit. Helps instantly see who is leading and who is falling behind.

Live Stats

A daily real-time performance view focused on operational activity, call volume, and workloads. Optimized for fast reaction and day-to-day management decisions.

Performance Overview

Yearly revenue and operational trends with monthly breakdowns and YTD progress to target. Built for spotting seasonality, velocity, and long-term performance dynamics.

The 2 views:

1. TV Dashboard (Real-Time) — Designed for wall screens and daily standups — shows MTD metrics by default with no interaction required. Used for live performance tracking throughout the day.
2. Analytical Dashboard — Full exploration mode with date controls, segmentation filters, and trend insights. Built for strategic analysis, forecasting, and understanding long-term patterns.

Documentation

We prepared full technical and user documentation:
architecture diagrams, ETL models, metric glossary, dashboards guide, and a Goals update manual.

Final Outcome

The company moved from disconnected Excel files and Datacube black-box reports to a unified analytical system capable of informing decisions daily — not monthly. The company now has clarity, speed, transparency, and measurable control across every operational layer: from technicians to revenue strategy.