Manaz Afzal Book a consultation

Work snippet

Job margin dashboard from ServiceM8 and Xero, in Google Sheets and Looker Studio

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.

What a read-only review checks firstWeek 1
IDFindingEvidenceRisk if left
F1Bills coded to an account, not a jobLarge share of material spend with no job referenceMargin overstated on busy jobs
F2Timesheets without job numbersHours booked to general timeLabour cost unassigned
F3No cut-off for late billsBills dated last month entered this monthLast month's margin changes after reporting
F4Report built from manual exportsDifferent filters each monthFigures that cannot be reproduced
F5No definition of marginGross, contribution and labour-loaded mixedDecisions on different numbers
F6Variations invoiced separatelyExtra work not linked to the original jobJob 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

Job margin reporting architectureServiceM8 jobs and invoices, Xero bills and spend, and timesheets are extracted by n8n into Google Sheets staging tabs, joined on the job key into a model with an allocation queue, and shown in Looker Studio with freshness checks.020040060080jobsbillsstaging tabsmodel onlyno job keycodedA · REVENUEServiceM8Jobs, invoices,variationsB · COSTXeroBills, spend,trackingC · EXTRACTn8n nightlyWatermarks,row totalsD · MODELGoogle SheetsJob key join,allocation queueE · VIEWLooker StudioMargin by job,service, crewF · QUEUEAllocationUncoded costswith an ownertimesheets join on the same keymargins are provisional until the month-end gateREPORTING · JOB COSTINGManaz Afzal
One job key, three sources, one modelSolid: live data path · Dashed: exception path

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.

Looker Studio · Job margin · overviewFresh 06:10
RevenueUS$412,600month
Direct costUS$268,190materials + labour
Gross margin35.0%provisional
UnallocatedUS$9,84014 bills

Margin by service

Drains41.2%
Hot water33.8%
Leak detection46.5%
Maintenance28.4%
Renovation19.6%

Margin by month

MayJunJulAugSepOct

The banner turns amber while any cost for the month is unallocated, and the month shows as provisional.

Google Sheets · Allocation queueLive

14 items · US$9,840

SourceSupplier or personAmountSuggested jobOwnerAge
Xero billSupplier 0312,410.004471 (PO match)Office2 d
Xero billSupplier 014860.00NoneOps lead5 d
TimesheetTech 067.5 h4466 (same day)Office1 d
Spend moneyCard, fuel96.20OverheadOffice1 d
Xero billSupplier 0225,120.004458 (variation)Ops lead3 d

Suggestions come from the PO number, the date and the crew; a person confirms each one.

Looker Studio · Job 4458 · drill-throughLive

Job

CustomerCustomer 2208
ServiceRenovation
InvoicedUS$18,400.00
MaterialsUS$9,920.00
LabourUS$4,212.00 · 64.8 h
Margin23.2% provisional

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-level build6 modules
  1. Module AKPI dictionary

    Gross margin, labour-loaded margin and contribution defined with their sources and exclusions.

  2. Module BExtraction

    n8n pulls ServiceM8 jobs and invoices and Xero bills, spend and tracking nightly, with watermarks and row totals.

  3. Module CJob key

    Job number on bills and timesheets, with variations linked to the parent job.

  4. Module DModel

    Separate fact tabs for revenue, materials and labour, joined on the job key; no formulas in the staging tabs.

  5. Module EAllocation queue

    Costs without a job key, with a suggested job, an owner and an age.

  6. 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 mapping
SourceFieldModelRule
ServiceM8Job numberjob_keyPrimary key; variations carry the parent
ServiceM8Invoice total ex taxrevenueBy invoice date
XeroBill line, job referencematerialsReference or tracking to job_key
XeroBill line, no referenceallocation queueNever spread by formula
TimesheetsHours by joblabour_hoursTimes loaded hourly cost
XeroAccount totalstie_outModel totals must equal the ledger
Margin definitions
MeasureFormulaUsed for
Gross margin(revenue - materials - subcontract) / revenuePricing by service
Labour-loaded margingross less hours at loaded costCrew and job review
Contributionlabour-loaded less direct job costsOwner view
ProvisionalAny month with queue items or no tie-outBanner and month flag

Worked figures

Walkthrough arithmetic for one month of jobs.

A month, revenue to marginWalkthrough figures
Jobs invoiced284in the month
RevenueUS$412,600ex tax
Direct costUS$268,190materials and labour
Gross margin35.0%provisional
UnallocatedUS$9,84014 items
Ledger tie-outUS$0.00difference

Revenue to margin, US$

Revenue412.6k
Materials171.3k
Labour96.9k
Margin144.4k

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

AreaIncluded
DiscoveryThree months of jobs, invoices, bills and timesheets reviewed, with findings
DefinitionsKPI dictionary signed before the build
Extractionn8n nightly extracts with watermarks and failure alerts
ModelGoogle Sheets model, job key, allocation queue and ledger tie-out
DashboardLooker Studio overview, service and crew views, job drill-through
Close routineProvisional and final month gate, late-bill handling
Not includedTimesheet policy changes, bookkeeping corrections in the source systems, or data warehouse storage

Milestones, week by week

  1. Week 1

    Definitions

    • KPI dictionary signed
    • Job key and source checks
    • Data-load assessment
    20% of the fee
  2. Week 2

    Extract and stage

    • n8n reads
    • Watermarks and totals
    • Failure path
    25% of the fee
  3. Week 3

    Model

    • Fact tabs
    • Allocation queue
    • Ledger tie-out
    25% of the fee
  4. Week 4

    Dashboard

    • Overview and drill-through
    • Permissions and freshness
    • User review
    20% of the fee
  5. Week 5

    Handover

    • Close SOP
    • Data dictionary
    • 14 days of fixes
    10% of the fee

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.

Related work snippets

Next step

Bring me the work that repeats.

I scope it in writing, test it against your current process on your own data and hand over everything I build.

Book a consultation