Work snippet 20 / 34Management reporting
Looker Studio branch KPI dashboard from Xero and the job system
How I'd approach a live branch dashboard for a six-branch service group, with written KPI definitions, nightly loads from Xero and the job system, each manager seeing only their own branch, and every figure traced to its source.
Work snippet with example figures.
- Area
- Management reporting
- Tools
- Looker Studio · Xero · ServiceM8 or Jobber
- Typical scope
- US$2,500–4,500
- Timeline
- About 6 weeks · 4 fixed milestones
01Context
A multi-branch service group with six branches, a finance lead and a general manager for each branch. Jobs are run in ServiceM8 or Jobber and the books are in Xero. Each month the management pack is rebuilt by hand from exports.
The systems in play
In the example the group wants one live dashboard in Looker Studio that shows revenue, margin, jobs, quotes and cash by branch, with each branch manager seeing only their own branch and every figure traceable to its source.
| System | What it does for the business |
|---|---|
| ServiceM8 or Jobber | Jobs, quotes, completion dates and branches. Read only. |
| Xero | Invoices, bills, credit notes, tracking categories and the ageing of customer balances. Read only. |
| Scheduled loads | Copy the data each night into a reporting store. Make in the example. |
| Reporting store | A BigQuery dataset in the example. A Google Sheet is used for a smaller group. |
| Looker Studio | The dashboard that managers open. No data is typed in. |
| General managers and finance lead | Read the dashboard and agree what each figure means. |
02Problem
What the owner sees
- A monthly pack that is late and different every time
- Branch margins that the finance lead and the managers argue about
- Completed jobs that were never invoiced, found weeks later
- No way to give each manager only their own numbers
A dashboard that nobody trusts is worse than a spreadsheet. The work is mostly agreeing what each number means, joining the job system to the ledger properly and proving that the totals tie.
| Everyday situation | What goes wrong |
|---|---|
| Two definitions of revenue | Jobs completed and invoices issued give different totals, and both are called revenue |
| Missing tracking | Invoices with no Branch land in no branch and disappear from the branch view |
| Credit notes | Revenue is overstated because credits are left out |
| Late supplier bills | Margin looks better than it is until the bill arrives |
| Data limits | A spreadsheet source slows down or fails as the history grows |
| Shared access | One report with filters that a manager can remove |
03Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookA read-only review of the monthly pack, the job system exports and the Xero reports. The numbers managers use today are listed with how each is made.
- Agree the definitionsEvery figure gets a written definition, a source, an owner and a refresh time.
- Build and connectThe loads, the reporting store, the model and the dashboard are built together, one page at a time.
- CheckDashboard totals are compared with Xero and the job system for three closed months, and with each manager’s own view.
| KPI | Definition | Source |
|---|---|---|
| Revenue | Authorized invoice lines excluding sales tax, less credit notes, by invoice date and Branch | Xero |
| Direct cost | Bill lines coded to cost of sales accounts, by Branch, by bill date | Xero |
| Gross margin % | (Revenue − direct cost) ÷ revenue | Xero |
| Jobs completed | Jobs with status Completed, by completion date and branch | Job system |
| Average job value | Revenue from invoices linked to jobs ÷ number of those jobs | Job system and Xero |
| Quote conversion | Quotes accepted ÷ quotes sent, by the month sent | Job system |
| Completed, not invoiced | Completed jobs with no Xero invoice after 3 days, at their quoted value | Job system and Xero |
| Debtor days | Customer balance ÷ revenue over the last 90 days × 90 | Xero |
04What the setup includes
What the setup includes, and why each piece is there. The detail of how it is configured is agreed in the scoping call.
Show the setup, layer by layer
| Layer | What is set up and why |
|---|---|
| Nightly loads | Each source is read with a modified-since filter, so a night’s load is small and stays within the API limits. A full refresh of the last 90 days runs weekly to catch edits and voids. |
| Reporting store | Fact tables for invoice lines, bill lines, jobs and quotes, with dimension tables for branch, service line and date. Raw loads are kept untouched. |
| Join keys | Job number on the invoice reference links Xero to the job system. Invoices with no job number are listed, not guessed. |
| Data quality page | Missing tracking, missing job numbers, completed jobs without invoices and failed loads, each with a count and a link to the records. |
| Dashboard pages | Executive view, branch view, jobs and quotes, cash and debtors, and the data quality page. One filter bar and the same definitions on every page. |
| Access by branch | Viewers see the dashboard with their own credentials. A permissions table maps each email to its branch, and the data views filter by it. |
| Refresh log | Every load writes its time, row counts and status, which the dashboard shows. |
| Definitions page | The written KPI definitions inside the dashboard, so the meaning is one click away. |
How the data moves
- Nightly load from Xero
- Nightly load from the job system
- Reporting store
- Joined on job number
- Dashboard in Looker Studio
- Branch view by login
- Data quality page
05Edge cases
These are agreed with the finance lead and tested on three closed months.
| Situation | How it is handled |
|---|---|
| Job completed in one month and invoiced in the next | Both views are shown and labeled: completed and invoiced. Revenue is always by invoice date. |
| Invoice with no Branch | Shown as Unallocated in its own bar, with a link to the list |
| Credit note | Reduces revenue in the month it is dated, with the same Branch as the invoice |
| Voided or deleted invoice | Removed on the weekly refresh. The refresh log shows how many were removed. |
| Supplier bill dated after month-end for that month | Appears in the month it is dated. A late-cost note appears on the margin view for the prior month. |
| Job done by one branch for another | Revenue stays with the invoice’s Branch. The helping branch’s cost stays with the bill’s Branch. |
| Branch renamed or merged | Handled in the branch dimension with valid-from dates, so history is not rewritten |
| API limit reached | The load pauses, retries later and logs it. The dashboard shows the last good refresh time. |
| Manager changes branch | One row in the permissions table changes. Nothing else needs editing. |
| Data source larger than a spreadsheet allows | The store is a BigQuery dataset, so history can grow without slowing the dashboard |
06Worked example
The tables below use one month with example figures. They show the shape of the checks, not a client result.
| Branch | Revenue | Direct cost | Gross margin % | Jobs completed | Average job value |
|---|---|---|---|---|---|
| North | 148,200.00 | 67,800.00 | 54.3% | 412 | 359.71 |
| South | 131,500.00 | 62,300.00 | 52.6% | 371 | 354.45 |
| East | 96,400.00 | 41,200.00 | 57.3% | 288 | 334.72 |
| West | 88,700.00 | 44,100.00 | 50.3% | 254 | 349.21 |
| Central | 112,900.00 | 50,200.00 | 55.5% | 333 | 339.04 |
| Coast | 74,300.00 | 36,500.00 | 50.9% | 206 | 360.68 |
| Group | 652,000.00 | 302,100.00 | 53.7% | 1,864 | 349.79 |
| Branch | Quotes sent | Accepted | Conversion | Unbilled jobs | Unbilled value |
|---|---|---|---|---|---|
| North | 92 | 61 | 66% | 3 | 4,120.00 |
| South | 88 | 54 | 61% | 2 | 2,860.00 |
| East | 70 | 38 | 54% | 1 | 1,340.00 |
| West | 66 | 33 | 50% | 4 | 6,210.00 |
| Central | 79 | 47 | 59% | 2 | 2,480.00 |
| Coast | 58 | 29 | 50% | 0 | 0.00 |
| Group | 453 | 262 | 58% | 12 | 17,010.00 |
| Branch | Dashboard revenue | Xero report | Difference |
|---|---|---|---|
| North | 148,200.00 | 148,200.00 | 0.00 |
| South | 131,500.00 | 131,500.00 | 0.00 |
| East | 96,400.00 | 96,400.00 | 0.00 |
| West | 88,700.00 | 88,700.00 | 0.00 |
| Central | 112,900.00 | 112,900.00 | 0.00 |
| Coast | 74,300.00 | 74,300.00 | 0.00 |
| Unallocated | 0.00 | 0.00 | 0.00 |
| Total | 652,000.00 | 652,000.00 | 0.00 |
| Source | Method | Schedule | Rows loaded last night | Status |
|---|---|---|---|---|
| Xero invoices and credit notes | Modified since the last run | Nightly, 04:00 | 1,184 | Complete |
| Xero bills | Modified since the last run | Nightly, 04:10 | 652 | Complete |
| Xero customer balances | Full snapshot | Nightly, 04:20 | 1,962 | Complete |
| Jobs and quotes | Modified since the last run | Nightly, 04:30 | 1,017 | Complete |
| Last 90 days full refresh | Replace | Weekly, Sunday 02:00 | 48,310 | Complete |
07How the result is checked
Each figure on the dashboard is traced to a source before the dashboard is handed over, and the check is repeated every month by comparing the dashboard to a Xero report.
| Check | How | Pass condition |
|---|---|---|
| Revenue by branch | Dashboard compared to the Xero profit and loss by Branch for three closed months | Equal to the cent, with Unallocated shown |
| Direct cost | Same comparison on cost of sales accounts | Equal to the cent |
| Job counts | Dashboard compared to the job system report for the same months | Equal |
| Quote conversion | Counts of quotes sent and accepted compared to the job system | Equal |
| Customer balances | Debtor total compared to the Xero ageing report | Equal at month-end |
| Access | Each manager’s login tested, plus a login with no row in the permissions table | Managers see only their branch; an unknown login sees nothing |
| Freshness | Refresh log reviewed for a week | No failed load, and the dashboard shows the last good time |
| Definitions | Each figure read back to the finance lead against the written definition | Signed off |
08Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Audit | Read-only review of the current pack, the job system exports and the Xero reports, a 45-minute call, written findings and a fixed quote |
| Definitions | KPI definitions, sources and owners in writing, signed by the finance lead and the managers |
| Loads and store | Nightly loads from Xero and the job system into a BigQuery dataset, with a refresh log and alerts |
| Data model | Fact and dimension tables, join keys and a data quality page |
| Dashboard | Five pages in Looker Studio, a definitions page and one design for desktop and mobile |
| Access | A permissions table, branch views by login and a test for each role |
| Reconciliation | Three closed months compared to Xero and the job system, with a tie-out tab |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Forecasting and budgeting models. Changes to how jobs are recorded in the job system. Moving to a different dashboard tool. Software subscriptions and cloud usage (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and definitions | Findings, a fixed quote and the KPI definitions in writing | Week 1 | 20% |
| M2 Loads and store | Nightly loads running with a refresh log | Weeks 2 to 3 | 30% |
| M3 Dashboard and access | Pages live, branch views by login tested | Week 4 | 30% |
| M4 Reconciliation and handover | Three months tie out, SOPs, handover video, 14 days of fixes | Weeks 5 to 6 | 20% |
Typical scope for this kind of project: US$2,500–4,500
About 6 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I'll say where I'd look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.