Work snippet
Nightly operations board in Looker Studio or Google Sheets
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
Six branches, a monthly pack that arrives late and starts arguments. Each night n8n extracts the data into a model built on agreed KPI definitions, which feeds a Looker Studio or Google Sheets board. Unassigned items show on their own line, and each manager can open only their own branch.
The problemThe monthly pack is late, rebuilt by hand, and argued over.
What changesA nightly board with written definitions, so the numbers are the same for everyone.
- Area
- Operations reporting
- Tools
- n8n · Google Sheets · Looker Studio
- Typical scope
- US$2,200-3,800
- Timeline
- About 5 weeks · fixed milestones
The problem
Figures came out weeks after month end, revenue was sometimes taken from job totals rather than invoices, branches defined KPIs differently, and the file showed every branch to everyone.
- The meeting spends its time on whether the file is right
- Unbilled completed jobs have been counted as revenue
- Unallocated work is dropped from the charts
- A branch manager can see another branch's pipeline
What discovery would examine
A review of the monthly pack, the job system, the ledger, and how branch managers currently get numbers, for a six-branch service group.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | The monthly pack arrives after the month has been closed, and each branch is a paste from a different export | A 12-day lag last quarter, and two branch names spelled differently | The meeting argues about the file, not the work |
| F2 | Revenue in the pack does not always agree to the ledger, because job totals are used instead of invoiced amounts | A $18,400 gap in one month, later traced to unbilled completed jobs | Branch margin is discussed on the wrong base |
| F3 | There is no written definition of a completed job, or of cash collected | Managers use different filters in the job system | Two branches cannot be compared |
| F4 | A person with the pack file can see every branch | The file is emailed as a workbook | A manager sees another branch's pipeline |
| F5 | Unallocated or uncoded work is dropped from the charts | Charts that add to less than the ledger total | The gap is invisible in the meeting |
Approach
- Write the definitions first
Revenue, margin, completed jobs and cash, signed before a chart is built.
- Extract nightly, and show the date
A failed pull is visible, not disguised as a fresh morning.
- Keep Unallocated on the page
A gap that is hidden will be argued about later anyway.
- Gate go-live on a ledger tie-out and an access test
Three closed months, to the cent, and a login test for each role.
Current state and future state
Current state
Future state
Future-state modules
How it is built
- Module ADefinitions book
Each KPI is written once: revenue is invoiced amounts in the ledger, not job estimates; a completed job is a job in a named status in the period.
- Module BNightly extract
n8n pulls the agreed fields, stores a dated file, and logs a failed pull rather than serving yesterday as if it were today.
- Module CModel
A warehouse or a structured Google Sheets model, with Unallocated always kept as a line.
- Module DViews
Pages for revenue and margin, jobs and quotes, cash and debtors, and data quality.
- Module EAccess
A manager sees their own branch; a director sees all, including Unallocated.
Field mapping
| KPI | Definition | Source fields | Unallocated |
|---|---|---|---|
| Revenue | Invoiced, ex tax, in the period, from the ledger | Ledger invoice date, amount, tax, tracking category | Shown as its own line, never dropped |
| Gross margin | Revenue less materials, subcontractor and direct labour, as defined in the book | Ledger accounts named in the book, plus job labour if agreed | A cost with no branch stays in Unallocated |
| Jobs completed | Jobs that reached the named completed status in the period | Job system status and completed timestamp | Jobs with no branch tag are listed, not omitted |
| Quotes won | Accepted quotes in the period, by lead source if present | Quote status, accepted date, source | Source blank is a category, not a hidden row |
| Closing cash | Ledger cash and bank accounts, as named | Ledger balances on the report date | N/A |
| Debtors | Open invoices, by age band | Ledger invoice due date and open amount | Unallocated is an age band, not a deletion |
Worked exhibits
Exceptions and runbook
| Case | Rule | Who acts | First action |
|---|---|---|---|
| Nightly extract fails | The board shows the last good date, not a silent yesterday, and an alert is sent | Operations | Rerun, then agree whether to keep the stale date visible |
| Revenue does not agree to the ledger report | The board is wrong until the difference is named; it is not "fixed" by dropping Unallocated | Finance and me, if support is in place | Work the data-quality page first |
| A job or invoice has no branch | It appears in Unallocated, and on the data-quality list | Branch admin | Code the record, then it moves on the next extract |
| A manager should not see another branch | Access is by login, tested for each role | Owner | A failed access test blocks go-live for that view |
| A KPI definition changes | The book is updated, dated, and the board shows the version date | Owner | History is not quietly rewritten without a note on the page |
How to read the exhibits
- The month on the board is a walkthrough layout, including Unallocated, not a result claimed for a named group.
- Revenue is invoiced amounts, not job estimates, as the definitions book states.
- Access is part of the test plan, not a later tidy-up.
Test plan
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Revenue tie-out | Compare three closed months to the ledger report | Equal to the cent, including Unallocated | A gap, or Unallocated missing |
| T2 | Stale extract | Fail the nightly pull on purpose | The board shows the last good date and an alert fires | Yesterday is shown as today |
| T3 | Branch access | Log in as a North manager | North only, no other branch, Unallocated hidden or shown as agreed | Another branch is visible |
| T4 | Director access | Log in as a director | All branches and Unallocated | Unallocated is missing |
| T5 | Definition change | Change a cost account in the book in a copy | The version date updates, and the test month still ties or the difference is named | A silent change with no date |
Scope
| Area | Included |
|---|---|
| Discovery | Current pack, sources and access, with written findings |
| Definitions book | Each KPI, signed |
| Nightly extract | n8n pulls, dated files and failure alerts |
| Model and views | Revenue and margin, jobs and quotes, cash and debtors, data quality |
| Access | Branch by login, director all, including Unallocated |
| Tie-out and test plan | Three closed months, plus the access and stale-extract checks |
| Handover and support | SOPs, walkthrough, 14 days of fixes, optional monthly hour block |
| Not included | A full finance system replacement |
Rollout, handover and monthly support
| Milestone | Deliverable | Timing | Share of fee |
|---|---|---|---|
| M1 Discovery and definitions | Findings and the signed KPI book | Week 1 | 25% |
| M2 Extract and model | Nightly pull and the first views in a copy | Weeks 2 to 3 | 35% |
| M3 Access, tie-out and tests | Three months agree, login tests pass | Week 4 | 25% |
| M4 Handover | SOPs, 14 days of fixes | Week 5 | 15% |
Handover
- Written operating procedures for the happy path and each exception in the runbook
- A recorded walkthrough of the architecture, the queue and how to replay an item
- A credentials and ownership list, with every connection in your name
- Fourteen days of fixes after go-live, for defects in the agreed scope
Monthly support
A set block of hours each month, at US$35 per hour, agreed in writing before it starts. Typical use of the block: a failed extract, a new KPI line, and branch access changes
- Review of the exception queue and the weekly digest
- Small mapping or rule changes, agreed before they are made
- A short written note on what was done and what is still open
Typical scope for this kind of project: US$2,200-3,800
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, to watch the flows, handle changes and work the exception queue.
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.