Manaz Afzal Book a consultation

Work snippet

Nightly operations board in Looker Studio or Google Sheets

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

Discovery findings

A review of the monthly pack, the job system, the ledger, and how branch managers currently get numbers, for a six-branch service group.

Findings from the review
IDFindingEvidenceRisk if left
F1The monthly pack arrives after the month has been closed, and each branch is a paste from a different exportA 12-day lag last quarter, and two branch names spelled differentlyThe meeting argues about the file, not the work
F2Revenue in the pack does not always agree to the ledger, because job totals are used instead of invoiced amountsA $18,400 gap in one month, later traced to unbilled completed jobsBranch margin is discussed on the wrong base
F3There is no written definition of a completed job, or of cash collectedManagers use different filters in the job systemTwo branches cannot be compared
F4A person with the pack file can see every branchThe file is emailed as a workbookA manager sees another branch's pipeline
F5Unallocated or uncoded work is dropped from the chartsCharts that add to less than the ledger totalThe gap is invisible in the meeting

Approach

  1. Write the definitions first

    Revenue, margin, completed jobs and cash, signed before a chart is built.

  2. Extract nightly, and show the date

    A failed pull is visible, not disguised as a fresh morning.

  3. Keep Unallocated on the page

    A gap that is hidden will be argued about later anyway.

  4. 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

Reporting path, current and future

Current state

Job system + ledgerSeparate exports
Spreadsheet pasteOnce a month
Email the fileAll branches visible
MeetingArgues about the numbers

Future state

Nightly extractn8n, typed fields
Warehouse or Sheets modelWritten definitions
Looker Studio or SheetsOne page per topic
Access by loginOwn branch only

Future-state modules

SourcesJob system, ledgern8n extractNightly, loggedModelKPI definitionsLooker / SheetsBranch viewsAccessLogin = branch

How it is built

Module-level build
  1. 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.

  2. 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.

  3. Module CModel

    A warehouse or a structured Google Sheets model, with Unallocated always kept as a line.

  4. Module DViews

    Pages for revenue and margin, jobs and quotes, cash and debtors, and data quality.

  5. Module EAccess

    A manager sees their own branch; a director sees all, including Unallocated.

Field mapping

KPI definitions and source fields
KPIDefinitionSource fieldsUnallocated
RevenueInvoiced, ex tax, in the period, from the ledgerLedger invoice date, amount, tax, tracking categoryShown as its own line, never dropped
Gross marginRevenue less materials, subcontractor and direct labour, as defined in the bookLedger accounts named in the book, plus job labour if agreedA cost with no branch stays in Unallocated
Jobs completedJobs that reached the named completed status in the periodJob system status and completed timestampJobs with no branch tag are listed, not omitted
Quotes wonAccepted quotes in the period, by lead source if presentQuote status, accepted date, sourceSource blank is a category, not a hidden row
Closing cashLedger cash and bank accounts, as namedLedger balances on the report dateN/A
DebtorsOpen invoices, by age bandLedger invoice due date and open amountUnallocated is an age band, not a deletion

Worked exhibits

One month on the board, walkthrough figures (AUD, ex tax)
Revenue, all branches$412,600
Gross margin38.4%
Jobs completed486
Unallocated revenue$6,140visible on the page
Pack lagNightlynot 12 days
North$98,000Central$121,000East$86,000South$64,460West$37,000Unallocated$6,140
A branch view and a data-quality row
Looker Studio · North, one month
Revenue$98,000
Margin36.1%
Jobs completed112
Quotes won40
AccessNorth only
Data quality page
Jobs with no branch7
Invoices with no tracking14, totalling $6,140
Extract last finished05:12, same day
Failed extractNone this week

Exceptions and runbook

Exception runbook
CaseRuleWho actsFirst action
Nightly extract failsThe board shows the last good date, not a silent yesterday, and an alert is sentOperationsRerun, then agree whether to keep the stale date visible
Revenue does not agree to the ledger reportThe board is wrong until the difference is named; it is not "fixed" by dropping UnallocatedFinance and me, if support is in placeWork the data-quality page first
A job or invoice has no branchIt appears in Unallocated, and on the data-quality listBranch adminCode the record, then it moves on the next extract
A manager should not see another branchAccess is by login, tested for each roleOwnerA failed access test blocks go-live for that view
A KPI definition changesThe book is updated, dated, and the board shows the version dateOwnerHistory is not quietly rewritten without a note on the page

How to read the exhibits

  1. The month on the board is a walkthrough layout, including Unallocated, not a result claimed for a named group.
  2. Revenue is invoiced amounts, not job estimates, as the definitions book states.
  3. Access is part of the test plan, not a later tidy-up.

Test plan

PASS / FAIL checks
IDCheckMethodPASS whenFAIL when
T1Revenue tie-outCompare three closed months to the ledger reportEqual to the cent, including UnallocatedA gap, or Unallocated missing
T2Stale extractFail the nightly pull on purposeThe board shows the last good date and an alert firesYesterday is shown as today
T3Branch accessLog in as a North managerNorth only, no other branch, Unallocated hidden or shown as agreedAnother branch is visible
T4Director accessLog in as a directorAll branches and UnallocatedUnallocated is missing
T5Definition changeChange a cost account in the book in a copyThe version date updates, and the test month still ties or the difference is namedA silent change with no date

Scope

AreaIncluded
DiscoveryCurrent pack, sources and access, with written findings
Definitions bookEach KPI, signed
Nightly extractn8n pulls, dated files and failure alerts
Model and viewsRevenue and margin, jobs and quotes, cash and debtors, data quality
AccessBranch by login, director all, including Unallocated
Tie-out and test planThree closed months, plus the access and stale-extract checks
Handover and supportSOPs, walkthrough, 14 days of fixes, optional monthly hour block
Not includedA full finance system replacement

Rollout, handover and monthly support

MilestoneDeliverableTimingShare of fee
M1 Discovery and definitionsFindings and the signed KPI bookWeek 125%
M2 Extract and modelNightly pull and the first views in a copyWeeks 2 to 335%
M3 Access, tie-out and testsThree months agree, login tests passWeek 425%
M4 HandoverSOPs, 14 days of fixesWeek 515%
Handover and monthly support

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.

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