Work snippet
Job margin dashboard from ServiceM8 and Xero, in Google Sheets and Looker Studio
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
Margin measured per job. n8n gathers ServiceM8 jobs and invoices, Xero bills and spend, and timesheet hours into a Google Sheets model keyed on job number, which drives a Looker Studio dashboard. Costs missing a job code sit in their own visible queue.
The problemCosts sit in Xero by account, so nobody can say which jobs, services or crews make money.
What changesMargin by job, service and crew, with uncoded costs in a visible queue until they are fixed.
- Area
- Reporting and job costing
- Tools
- ServiceM8 · Xero · n8n · Google Sheets · Looker Studio
- Typical scope
- US$2,200-4,000
- Timeline
- About 5 weeks · fixed milestones
The situation and what was breaking
Everyone has an opinion on which work is profitable. The ledger cannot answer it while most costs carry no job reference.
Revenue sits in ServiceM8 by job. Costs sit in Xero by account: supplier bills coded to materials, labour in wages, subcontractors in their own line. A profit and loss report shows the business made money last month, not which services, crews or customers made it.
Spreadsheets built to answer the question break when someone forgets a job number on a bill, or when a late bill arrives after the month has been reported. The answer looks precise and is quietly wrong.
- Supplier bills without a job number
- Labour hours not linked to jobs
- Margins reported before late bills arrive
- Each report rebuilt by hand from exports
- Nobody trusts the figure enough to change a price
What good looks likeMargin by job, service and crew that I can trust enough to quote from, and a list of what is still missing.
Discovery findings
What a read-only review checks first: three months of ServiceM8 jobs and invoices, Xero bills and spend, timesheets, and the current report.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | Bills coded to an account, not a job | Large share of material spend with no job reference | Margin overstated on busy jobs |
| F2 | Timesheets without job numbers | Hours booked to general time | Labour cost unassigned |
| F3 | No cut-off for late bills | Bills dated last month entered this month | Last month's margin changes after reporting |
| F4 | Report built from manual exports | Different filters each month | Figures that cannot be reproduced |
| F5 | No definition of margin | Gross, contribution and labour-loaded mixed | Decisions on different numbers |
| F6 | Variations invoiced separately | Extra work not linked to the original job | Job looks unprofitable when it was not |
Architecture: job costing dashboard
Sources stay where they are. n8n extracts on a schedule into a staging sheet, a model sheet joins revenue, materials and labour on the job key, and Looker Studio reads only the model.
Scroll sideways to see the full drawing
The screens your team will use
Owners read the Looker Studio view. The office works the allocation queue, which is the only way a cost reaches a job.
Margin by service
Margin by month
The banner turns amber while any cost for the month is unallocated, and the month shows as provisional.
14 items · US$9,840
| Source | Supplier or person | Amount | Suggested job | Owner | Age |
|---|---|---|---|---|---|
| Xero bill | Supplier 031 | 2,410.00 | 4471 (PO match) | Office | 2 d |
| Xero bill | Supplier 014 | 860.00 | None | Ops lead | 5 d |
| Timesheet | Tech 06 | 7.5 h | 4466 (same day) | Office | 1 d |
| Spend money | Card, fuel | 96.20 | Overhead | Office | 1 d |
| Xero bill | Supplier 022 | 5,120.00 | 4458 (variation) | Ops lead | 3 d |
Suggestions come from the PO number, the date and the crew; a person confirms each one.
Job
What is still open
- Quote US$16,200, accepted
- US$2,200 linked
- US$5,120 waiting in queue
- Gate not yet passed
Every figure links back to the source row in the model.
How it is built
Definitions before data. Margin is agreed in writing, then extraction, then the model, and the dashboard last.
- Module AKPI dictionary
Gross margin, labour-loaded margin and contribution defined with their sources and exclusions.
- Module BExtraction
n8n pulls ServiceM8 jobs and invoices and Xero bills, spend and tracking nightly, with watermarks and row totals.
- Module CJob key
Job number on bills and timesheets, with variations linked to the parent job.
- Module DModel
Separate fact tabs for revenue, materials and labour, joined on the job key; no formulas in the staging tabs.
- Module EAllocation queue
Costs without a job key, with a suggested job, an owner and an age.
- Module FDashboard and gate
Looker Studio overview and drill-through; months marked provisional until the queue is clear and the ledger ties out.
Field mapping and rules
The job key and the margin definitions carry the model. Both are written down before any formula is built.
| Source | Field | Model | Rule |
|---|---|---|---|
| ServiceM8 | Job number | job_key | Primary key; variations carry the parent |
| ServiceM8 | Invoice total ex tax | revenue | By invoice date |
| Xero | Bill line, job reference | materials | Reference or tracking to job_key |
| Xero | Bill line, no reference | allocation queue | Never spread by formula |
| Timesheets | Hours by job | labour_hours | Times loaded hourly cost |
| Xero | Account totals | tie_out | Model totals must equal the ledger |
| Measure | Formula | Used for |
|---|---|---|
| Gross margin | (revenue - materials - subcontract) / revenue | Pricing by service |
| Labour-loaded margin | gross less hours at loaded cost | Crew and job review |
| Contribution | labour-loaded less direct job costs | Owner view |
| Provisional | Any month with queue items or no tie-out | Banner and month flag |
Worked figures
Walkthrough arithmetic for one month of jobs.
Revenue to margin, US$
QA and testing checklist
Every check runs in a copy or on test records first, and each one is ticked with evidence before go-live.
- T1TotalsModel revenue equals ServiceM8 invoices for the month.
- T2Tie-outModel costs equal the Xero accounts in scope, to the cent.
- T3No referenceA bill without a job key appears in the queue, never on a job.
- T4VariationA variation invoice adds to its parent job.
- T5Late billA bill dated last month after the gate reopens the month as provisional.
- T6FreshnessA failed extract turns the banner red and alerts.
- T7RerunRunning the extract twice adds no rows.
- T8Drill-throughEvery figure links to its source rows.
- T9PermissionsCrew leads see their own jobs only.
Scope
| Area | Included |
|---|---|
| Discovery | Three months of jobs, invoices, bills and timesheets reviewed, with findings |
| Definitions | KPI dictionary signed before the build |
| Extraction | n8n nightly extracts with watermarks and failure alerts |
| Model | Google Sheets model, job key, allocation queue and ledger tie-out |
| Dashboard | Looker Studio overview, service and crew views, job drill-through |
| Close routine | Provisional and final month gate, late-bill handling |
| Not included | Timesheet policy changes, bookkeeping corrections in the source systems, or data warehouse storage |
Milestones, week by week
- Week 1
Definitions
- KPI dictionary signed
- Job key and source checks
- Data-load assessment
- Week 2
Extract and stage
- n8n reads
- Watermarks and totals
- Failure path
- Week 3
Model
- Fact tabs
- Allocation queue
- Ledger tie-out
- Week 4
Dashboard
- Overview and drill-through
- Permissions and freshness
- User review
- Week 5
Handover
- Close SOP
- Data dictionary
- 14 days of fixes
Handover and keeping it running
Handover pack
- KPI dictionary formula, source and owner for every measure
- Model map tabs, keys, transformations and checks
- Allocation SOP how to resolve missing and unclear job codes
- Recorded walkthrough dashboard to job to source row
- Access list connections, protected sheets and report permissions
- 14 days of fixes for defects in the agreed scope
Keep it running
The dashboard should never hide a problem to look tidy. Unallocated cost, provisional months and stale extracts stay visible until the cause is fixed.
- Daily: freshness banner green for both sources
- Weekly: allocation queue cleared and late bills reviewed
- Monthly: tie-out signed before margins are used for quoting
- Optional support hours at US$35 per hour, agreed in writing
Typical scope for this kind of project: US$2,200-4,000
About 5 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.