- The owner of an HVAC company in California spent money on advertising in several places, while contractors reported on leads with no link to revenue.
- We built end-to-end analytics on BigQuery: the path from spend to a paid job.
- The main feature: revenue is counted by the date of the completed job, not the date of the lead. Returning customers no longer disappear from the report.
Case 18 · HVAC installation and repair · California, 🇺🇸 USA · 8 weeks
Next HVAC Repair: end-to-end analytics where revenue is counted by the date of the work
For a California HVAC contractor we built end-to-end analytics on BigQuery in two months: revenue by completed job date, new versus returning customers and offline conversions in Google Ads.
Stack
- Housecall Pro
- BigQuery
- GA4
- Looker Studio
- Google Ads
- Slack
Before → After
Before
Lead reports with no revenue
Contractors reported on clicks and leads from ad accounts. The CRM had only a manual lead source, and a ready-made end-to-end tool forced managers to work in two systems.
After
Revenue by the work, not by the lead date
Revenue is counted by the date of the completed job, new and returning customers are shown separately and offline conversions with a dollar value flow into Google Ads. The first working dashboard launched about two months after the start.
The numbers in detail
What moved — Next HVAC Repair
2 months
To the first working dashboard
Started in December 2025, first version in February 2026.
3 views
Revenue: new, returning, total
A new customer is one created within the last 60 days.
~8 h
Saved per week (team estimate)
For the marketing director and the traffic team.
Section 01
The short version
Section 02
Where did the project start?
The company runs on the Housecall Pro CRM. It has no fields for detailed source analytics, only a manual field that a manager fills in. A ready-made end-to-end tool required a second CRM, which would have put managers in two systems at once. In December 2025 we agreed to build a custom solution on BigQuery.
Section 03
What did we build?
Customers, jobs, invoices and estimates are synced into BigQuery without duplicates. Website leads are linked to visits through GA4, calls are enriched with call tracking and phone data, and spend comes from the ad accounts. The results live on a Looker Studio dashboard, and Slack notifications report new website leads. Nothing is written back to the CRM.
Section 04
What is custom about it?
- Revenue by the date of the work. You pick a period by completed jobs, so the real financial picture is visible even with a long deal cycle.
- New and returning customers, separately. Three views: created within the last 60 days, created earlier and total.
- Job grouping by the client's rules. The most profitable service, installation, can be viewed on its own.
- A "From LLM" source. Leads from ChatGPT and other assistants have their own line.
- Offline conversions in Google Ads. Qualified leads, confirmed estimates and sales with the value of the job go to the ad account.
Section 05
What changed?
- Spend, leads, customers, jobs and payments are visible in one chain.
- The client shifted the focus to campaigns that bring in customers with the highest ticket and quickly decided which test channels not to keep.
- By the team's estimate, the marketing director and the traffic team save about 8 hours a week.
The client uses the reports and asks for improvements on his own: cohorts for repeat requests, a new ad channel and Slack alerts.
Read the full write-up in the blog article.
Strategy session · 30 minutes
Want analytics like this? A free 30-minute strategy session.
We review your funnel, data sources and reports and show how to build the path from ad spend to a deal around your logic.