HVAC is the one Funnel-tab product that is not sourced from HubSpot. It comes entirely from ServiceTitan (the HVAC field-service system), so it has a different data model, a different set of KPIs, and its own calculator and drill-down classes. This page documents every HVAC KPI, the ServiceTitan tables and joins behind them, the raw SQL (quoted verbatim), date bucketing/timezone, and the known gotchas.
The HVAC tab is built to reconcile to the F2P – 2026 HVAC presentation sheet. ServiceTitan has no "Runs" stage the way HubSpot funnels do; instead Sets split into changeout-opportunity vs service-opportunity job types, and Closes/Bookings come from sold estimates + direct invoices with a $6,000 "changeout" threshold.
For shared conventions — the 30-minute funnel cache, the America/Chicago reporting timezone, month-bucketing, and the current-month EOM projection — see the Funnel Conventions page. This page only documents what is HVAC-specific.
<aside> ❄️
Source files: app/services/funnel/hvac_calculator.rb (metrics) and app/services/funnel/hvac_drilldown_query.rb (drill-down). Routing lives in app/controllers/api/v1/funnel_controller.rb.
</aside>
product=hvac reaches the calculatorThe funnel controller branches on the validated product param. HVAC gets its own calculator, its own drill-down class, and is explicitly excluded from cross-tab matrix and "investigate" endpoints (ServiceTitan has no CAC-source or disposition dimensions).
compute_metrics routes when "hvac" to Funnel::HvacCalculator.new(scheduler:, start_date:, end_date:).call (funnel_controller.rb:191-192). Note scheduler is accepted but ignored — HVAC has no scheduler dimension.drilldown_result routes if product == "hvac" to Funnel::HvacDrilldownQuery with stage / month / region / source / start_date / end_date (funnel_controller.rb:76-86).not_supported for HVAC: "HVAC is ServiceTitan-sourced and has no disposition fields" (funnel_controller.rb:47-48).funnel_controller.rb:138-140).Rails.cache.fetch(cache_key, expires_in: CACHE_EXPIRY) with CACHE_EXPIRY = 30.minutes and CACHE_VERSION = "v11" wraps all products including HVAC (funnel_controller.rb:11-15, :172-175).flowchart TD
A["service_titan_jobs"] --> S["Sets (deduped opportunity jobs)"]
MAP["f2p_hvac_job_map (HvacJobMap)<br>source + job_type"] --> S
BU["service_titan_business_units<br>name → region"] --> S
APPT["service_titan_appointments<br>MIN(start) = sched"] --> S
EST["service_titan_estimates<br>status=Sold"] --> C["Closes / Changeouts / Gross Bookings"]
MAP --> C
BU --> C
INV["service_titan_invoices"] --> R["Revenue (region only)"]
BU --> R
INV --> P["Pending Rev Rec (cumulative)"]
EST --> P
AD["marketing_ad_spend (product=hvac)"] --> L["New Leads + Lead Spend (Advertising)"]
QS["quinstreet_leads (Modernize)"] --> TPL["3PL Vendors spend/leads"]
EL["elocal_calls (eLocal)"] --> TPL
| Table | Role in HVAC funnel | Key columns used |
|---|---|---|
| service_titan_jobs | The job/opportunity spine. Sets are deduped opportunity jobs; every estimate/invoice/pending row joins back to a job for region + source. | id, customer_id, business_unit_id, project_id, created_on, completed_on |
| service_titan_business_units | Region derivation via business-unit name; HVAC filter (name ILIKE '%HVAC%'). | id, name |
| service_titan_appointments | Provides the earliest scheduled time per job (sched), used for Sets dedup partitioning. | job_id, start |
| service_titan_estimates | Sold estimates drive Closes / Changeouts / Gross Bookings and the sold-estimate leg of Pending. | id, job_id, customer_id, job_number, subtotal, status, active, sold_on |
| service_titan_invoices | All invoices drive Revenue; open invoices on incomplete jobs drive Pending. | id, job_id, customer_id, reference_number, total, invoice_date, active |
| f2p_hvac_job_map (HvacJobMap) | fsp-bi-hub table supplying source + job_type per job id (the dash role cannot read service_titan_job_types / service_titan_campaigns). | id (= job id), source, job_type |
| service_titan_customers | Drill-down only, best-effort: customer name label. Joined only if the grant exists. | id, name |
ServiceTitan foreign keys arrive as dotted composite strings (e.g. "12345.0" or "tenant.jobid"), while primary keys like service_titan_jobs.id and service_titan_business_units.id are bare. Every FK join therefore normalizes the referencing side with SPLIT_PART(<fk>, '.', 1) (take the first dotted segment) before comparing to the bare PK. This pattern appears on every join in the file:
JOIN service_titan_business_units bu ON SPLIT_PART(j.business_unit_id, '.', 1) = bu.id
LEFT JOIN service_titan_jobs j ON SPLIT_PART(e.job_id, '.', 1) = j.id
JOIN service_titan_jobs j ON SPLIT_PART(inv.job_id, '.', 1) = j.id
... SPLIT_PART(a.job_id, '.', 1) = j.id -- appointments → job
... SPLIT_PART(j.customer_id, '.', 1) -- customer dedup key
... SPLIT_PART(j.project_id, '.', 1) -- project rollup for Pending