Client name changed to protect confidentiality.
The request
What they asked for
Pinewick runs marketing for home-services businesses: the plumbers, HVAC crews, roofers and electricians who live or die on whether the phone rings and whether the ring turns into a booked job. Its clients wanted one answer to a plain question. Of the money we spend on marketing, how much comes back as jobs?
The brief was a reporting system, not a dashboard theme. It had to work for clients on different software, show leads, estimates and jobs in one place, and hold up when the client or their accountant checked the numbers against what they could see in their own CRM.
The problem
What was broken
A home-services business does not keep its story in one system. The lead lands in one tool. The estimate or quote is built in another. The job and the invoice live in a third. Some clients ran GoHighLevel for leads and ServiceTitan for jobs. Some ran Jobber end to end. Some had a CRM somebody built years ago and a spreadsheet somebody exports on Fridays.
Ad platforms report the first step: clicks, calls, form fills. Nothing in the ad platform knows whether the lead became an estimate, whether the estimate became a job, or what the invoice was. So the marketing report said one thing and the owner’s bank balance said another, and the two were never reconciled. That gap was the whole problem: marketing activity nobody could tie to revenue.
- Leads, estimates and jobs were recorded in different CRMs with different definitions of the same thing.
- Lead sources were typed by hand and spelled five ways, so “Google LSA” and “google local” counted as two channels.
- History was missing. A new report could only start from the day somebody connected it.
- Nobody could say which salesperson closed what, or whether a bad month was bad leads or bad follow-up.
- Spend lived in Stripe and QuickBooks, far from the leads it had paid for.
What we built
The system, piece by piece
Architecture
A connector layer, then one model on top
Every source system gets its own connector. A connector knows how to authenticate, how to read that platform’s records and how to translate them into the shared model. Nothing downstream knows or cares whether a job came from ServiceTitan or from a spreadsheet.
The shared model keeps three record types apart on purpose: leads, estimates (quotes) and jobs with their invoices. Many clients really do run separate systems for each, and the reporting system had to respect that rather than force everyone into one CRM. A lead is matched forward to an estimate, and an estimate to a job, so the funnel can be walked in either direction.
| Source | Role in the system | How it connects |
|---|---|---|
| GoHighLevel | Lead and pipeline records, depending on how the client uses it | Connector |
| Housecall Pro | Estimate and job records, depending on how the client uses it | Connector |
| ServiceTitan | Job and invoice records, depending on how the client uses it | Connector |
| Jobber | Quote, job and invoice records, depending on how the client uses it | Connector |
| Custom CRMs | Whatever the client’s own system holds | Purpose-built connector per client |
| CSV and XLSX | Exports from tools with no usable connection | Import with column mapping |
| Stripe and QuickBooks | Budget and spend figures | Connector, mapped to channels |
Data flow
Backfill first, sync daily, reconcile monthly
A dashboard with three weeks of history is a toy. When a client is connected, the system pulls the full history it can reach, so the first report already contains the years that came before it. That backfill is what lets a client compare this March with last March on day one.
After the backfill, every connector syncs daily. Daily is a deliberate choice. These are monthly and yearly views for owners and managers, not a live call board, and a daily cadence keeps load on the client’s systems low and failures easy to spot.
Once a month the system reconciles. The totals it holds for the month are checked against the source and against the finance side, and any difference is surfaced for a person to look at instead of being quietly averaged away. Records that change after the fact, like an invoice edited a week later, are the reason this step exists.
The three loops
- Backfill: one-time pull of full history when a client connects.
- Daily sync: new and changed leads, estimates, jobs and invoices.
- Monthly reconciliation: totals checked against source and finance, differences flagged.
Client dashboards
What the owner sees
Each client gets a password-protected dashboard with a monthly and a yearly view. It leads with the funnel: lead, estimate and job counts, side by side, so the drop at each step is visible. Under that sit revenue, cost per click, return on ad spend, return on investment and closing rate.
Two views were built because owners actually use two. The monthly view answers “what did last month do?” The yearly view answers “are we better or worse than this time last year?”, which matters in a seasonal trade where a December that looks bad next to October is not necessarily bad.
Salespeople are ranked, with their individual closing rates, and lead sources are broken out so a client can see which channel produced jobs and not just calls. Because estimate and job data sits next to the lead, a source that produces cheap leads that never close shows up as exactly that.
Admin area
What the management team runs
The second product is an admin area for Pinewick’s own management team. It exists because the hard part of this system is not drawing charts. It is keeping data trustworthy across many clients without an engineer in the loop.
Teams and access
Management creates teams and decides who can see which client. Client dashboards stay behind passwords.
Two-factor authentication
Admin accounts use 2FA, because the admin area can see every client’s revenue.
Audit logs
Changes to mappings, access and data are recorded, so “who changed that?” has an answer.
Lead-source mappings
Messy source labels are mapped to a clean set of channels, once, instead of corrected in every report.
Custom mappings
Clients with unusual stages, service types or fields get their own mapping rather than a code change.
Data verification
Checks that a client’s numbers add up before the client sees them, using the reconciliation results.
Finance
Mapping budgets with Stripe and QuickBooks
Cost per click and return on ad spend are only as good as the spend figure underneath. Stripe and QuickBooks are connected so budgets can be mapped to the channels they paid for. That gives return on investment a denominator that comes from the books and not from an ad platform’s own dashboard.
Trade-offs
Decisions that shaped it
Most of the choices came down to what to be strict about and what to leave flexible.
Strict model, flexible mappings
The shared model is fixed so every dashboard means the same thing for every client. The differences between clients are absorbed by mappings, which management can edit without a release.
Daily over live
Daily sync is slower than live, and cheaper and calmer. For monthly and yearly decisions the trade is a good one.
History before polish
The backfill was treated as a core feature, not a nice extra. A report without history cannot show a trend, and a trend is what an owner is paying for.
A person in the loop on disagreement
When the system and the source disagree, the difference is shown and a person decides. Automatic correction would hide the very errors the reconciliation is there to catch.
Files as a first-class source
CSV and XLSX import is a proper connector with column mapping, not a fallback. Some clients will never have an API worth connecting to, and they still deserve the same dashboard.
Handover
What management owns once it is live
The point of the admin area is that the management team can operate the system without calling the people who built it. They create teams, set who sees which client, edit lead-source and custom mappings, and read the verification results. Every one of those actions is recorded in the audit log, so the system can be trusted by people who did not set it up.
Onboarding a new CRM type is the one thing that needs engineering, because it needs a new connector. Onboarding a new client on a CRM that is already supported does not.
Operations
How it runs day to day
Nobody pushes a button to refresh a report. Connectors sync daily, and dashboards read from the shared model. When a new client signs, the checklist is short: connect the source or import the file, run the backfill, set the lead-source mappings, check the verification results, then issue the dashboard login.
When something looks wrong, the audit log and the verification results are the first places to look. A source label that appears for the first time lands in front of a person for mapping instead of vanishing into “other”. At month end the reconciliation results tell management which clients need a closer look before the numbers go out.
Measurement
What the system measures
These are the figures the dashboards track. They are definitions, not results.
- Lead, estimate and job counts, monthly and yearly
- Revenue
- Cost per click (CPC)
- Return on ad spend (ROAS)
- Return on investment (ROI)
- Closing rate, overall
- Salesperson ranking and individual closing rates
- Lead sources, mapped to clean channels
Scope boundaries
What we chose not to build
Scope was decided as carefully as features.
- A replacement CRM
- Clients keep the tools their crews already use. The system reads from them. Asking a plumbing company to change CRM to get a report is how reports do not get built.
- A real-time call board
- Daily sync fits monthly and yearly decisions. Real time would add load and failure modes for a view nobody asked for.
- Silent data cleaning
- When numbers disagree with the source, the system flags the difference. It does not smooth it over to make a chart look tidy.
- Shared logins
- Each client sees only its own data, and admin access sits behind teams and 2FA instead of one common password.
Outcome figures
Outcome figures are held back for confidentiality and will be added when the client approves publication.