The owner of an air conditioning installation and repair company in California spends money on advertising in several places at once, including Google Ads and Yelp. His question was simple. Which channel brings the main revenue and profit, from which campaigns and over what period? The contractors had no answer. They reported on leads from ad accounts, with no link to revenue.
We built end-to-end analytics for Next HVAC Repair on a database of our own. Below we explain how it works, how it differs from off-the-shelf tools and what the client does with it every day.
In this article
Section 01
Where did the project start?
The company runs its jobs and customers in the Housecall Pro CRM. It is convenient for managing work, but it has little to offer for analytics.
- There are no fields for detailed source analytics. There is only a manual Lead Source field that a manager fills in, so the source depended on a person and attribution was never exact.
- Contractors reported on clicks and leads from ad accounts, without revenue.
- The first attempt used a ready-made end-to-end analytics tool. It required connecting a second CRM, so managers had to work in two systems at once. A sync between them was built, but it turned out incomplete and clumsy.
In December 2025 we agreed with the client to build a fully custom solution on BigQuery.
Section 02
What did we build?
At the core is a single data warehouse on BigQuery. Customers, jobs, invoices and estimates from Housecall Pro are synced into it without duplicates.
On top of it the system assembles the path from spend to a paid job.
- Website leads are linked to a visit through GA4, so the campaign and channel are visible.
- Calls are enriched with call tracking and phone system data, and calls from Google Ads are pulled in with an automated report upload.
- Google Local Services leads are tracked separately.
- Spend comes from Google Ads, Bing Ads, OpenAI Ads and Yelp.
The results live on a Looker Studio dashboard, and Slack notifications report new website leads. The first working dashboard launched in February 2026, about two months after the start.
One decision was made on day one. Nothing is written back to Housecall Pro. All enrichment and syncing happen on our side, in BigQuery tables, so the CRM stays a clean working tool.
Section 03
What is custom about it?
Revenue by the date of the work, not the date of the lead. This is the main feature. In the dashboard you pick a period by completed jobs. If a job is completed this month, its revenue shows up here even when the customer was created long ago. Off-the-shelf tools tie revenue to the lead date and lose returning customers. The client explained the logic this way: as an owner, he wants to see the real financial picture, because a deal cycle can be long.
New and returning customers, separately. Later the client asked to split revenue by the customer's creation date. There are now three views: customer created within the last 60 days, more than 60 days ago and all revenue. This shows whether the money came from new advertising or from the returning base.
Job grouping by the client's rules. Housecall Pro has no such grouping, so we built our own groups, for example installation and service. This lets the client look at the most profitable service, installation, on its own.
A separate "From LLM" source. Leads from ChatGPT and other assistants appear as their own line. Off-the-shelf tools do not break out this source.
The client set the definition of paid. A job counts as paid at the stage "completed." Repeat requests count from the customer's creation date, so new and returning customers are separated honestly.
Offline conversions in Google Ads. We send qualified leads, confirmed estimates and sales with the dollar value of the job to the ad account. Estimates were added later at the client's request. An approved estimate is an early signal of lead quality and ticket size, before the work is done, so the account optimizes for real money and not for the number of leads.
Section 04
What changed for the client?
- The owner and the marketing director see spend, leads, customers, jobs and payments in one chain.
- The client assessed the efficiency of each source and shifted the focus to campaigns that bring customers with the highest ticket.
- New channels can be tested quickly. The client tried several and soon decided which ones not to keep.
- By the team's estimate, the marketing director and the traffic team save about 8 hours a week, which is a full working day.
This is one of our most successful implementations. The client uses the reports, trusts them and asks for improvements on his own: cohorts for repeat requests, a new ad channel, Slack alerts. When new requests appear without prompting, that is the best sign the system is needed.
Section 05
What can you take from this?
- Tie revenue to the date of the completed job if your deal cycle is long. Otherwise returning customers disappear from the report.
- Do not make managers work in two systems. Keep the enrichment in a separate database and leave the CRM clean.
- Separate new and returning customers. This is the only way to see whether new advertising pays back.
- Give new sources their own line, including leads from AI assistants, so they do not dissolve into "other."
Section 06
Want the same analytics for your business?
We will show how to calculate the path from ad spend to a paid job in your business. Contact us and we will review your funnel.
Section 07
FAQ
How long does it take to build analytics like this?
We built the first working dashboard in about two months. After that the system is extended for new client requests without stopping the reports.
Do we need to change our CRM?
No. We work with your CRM and write nothing back to it. All data is enriched in a separate database.
Which channels can be connected?
Google Ads, Bing Ads, Yelp, call tracking, phone systems, GA4 and other sources. A new channel fits into the schema without rework.
How is this different from off-the-shelf systems?
The data model is built around the client's logic, not a template. For example, revenue is counted by the date of the completed job, and sources are grouped the way the business needs.
For your team
Stop hiring agencies and freelancers
Hire not agencies and freelancers — but Marketing AI Agents for the AI Search.
- Per-engine citation map across 6 AI engines
- Content + schema work that earns the citation
- Honest 30-min strategy call before you commit
Cited across
- ChatGPT
- Gemini
- Google AIO
- Google AI Mode
- Perplexity
- Copilot