CASE FILE 09/13CLASS: SAMPLE BUILDDATA: REALISTIC, NOT CLIENTTYPE: Ops systems & SOPs
Sample build: realistic data, built to show how I'd handle this work. Not client work.
Sample buildOps systems & SOPs
Jobber to Google Workspace and QuickBooks Online migration: billing checked before every wave goes live
195/363 recurring jobs move as is after a Jobber and QBO billing check that traced 73,774.99 of over-billing
- 195/363jobs move as is
- 73,774.99over-billed, Aug to Oct
- 339calendar events built
- 32/32controls pass
01Summary
- In this scenario a Melbourne commercial cleaning business (158 clients, 252 sites, 60 cleaners) moves 339 active recurring jobs off Jobber: rosters to Google Calendar, site files to Google Drive, billing to QuickBooks Online.
- Every job is matched to its QBO recurring template and three months of invoices from both systems are checked: 195 jobs move as is, and 73,774.99 over-billed and 24,094.07 under-billed between August and October are traced to 157 items.
- The pilot passes all six go-live gates; the four regional waves stay BLOCKED until their items are cleared. 32 of 32 formula controls and 12 of 12 tests pass.
Every job is matched to its QBO recurring template and three months of invoices from both systems are checked: 195 jobs move as is, and 73,774.99 over-billed and 24,094.07 under-billed between August and October are traced to 157 items.
Key point, computed from the sample files
02The scenario
In this scenario a commercial cleaning business runs recurring jobs and rosters in Jobber, but bills some sites from Jobber and the rest from QuickBooks Online recurring templates. Nobody compares the two, rate changes go into one system only, and alarm codes sit in visit notes. Jobber has to be gone before its renewal.
Source: sample files
03Issues in the data
Source: sample files
04How I approached it
- An engine (engine.py) that matches customers, sites and templates, reconciles rates, checks 884 QBO and 231 Jobber invoices against what should have been billed, finds roster clashes and moves access codes out of notes.
- A workbook with 27 tabs and 16,894 formulas: exports, matching, rate reconciliation, billing check, roster clashes, cleaner load, calendar build, Drive plan, cutover waves and gates.
- 339 recurring Google Calendar events as one .ics file per cleaner, and a 1,670-folder Drive plan with group permissions.
- A 19-page report, a one-page executive summary and an SOP for moving a site and every change after, as Word and PDF.
Source: my design and the sample files
05How this applies to your business
- Week 1: exports and matchingJobber and QBO exports, customers, sites and templates matched, the billing check run on three months.
- Week 2: decisions and fixesThe bookkeeper and owner clear the billing items; credit notes and catch-up invoices are raised in QBO.
- Week 3: pilot2 cleaners (8 jobs) move to Google Calendar and Drive, with a daily check.
- Weeks 4 to 8: wavesOne region a week once its six gates PASS; Jobber invoicing is switched off site by site, then Jobber is closed.
| Activity | Business owner | Specialist (me) | Your team |
|---|---|---|---|
| Export Jobber and QBO data | I | C | R |
| Run the engine and rebuild the workbook | I | R | C |
| Decide billing items and credits | A | C | R |
| Build calendars and Drive | A | R | C |
| Release a wave | A | R | C |
| Switch off Jobber invoicing per site | A | C | R |
Source: sample plan
06Controls and checks
| Check | What it catches | Status |
|---|---|---|
| Rate Reconciliation job rows = Jobber jobs | Every job is reconciled once | PASS |
| Active templates on Rate Reconciliation = active QBO templates | Every live template is accounted for, including QBO-only ones | PASS |
| Formula status = engine status (rows that differ) | The workbook logic and the engine agree on every row | PASS |
| Rows with no next action | Every status has an action and an owner | PASS |
| Actions tab rows = Rate Reconciliation rows | Nothing falls outside the action list | PASS |
| QBO parents with no Jobber client | Every customer has a home | PASS |
| QBO parents in Customer Match = parents in QBO | No customer skipped | PASS |
| QBO sub-customers with no Jobber property | Every location has a home | PASS |
| Templates pointing at a job that does not exist | No broken template links | PASS |
| Jobber invoices total = Billing Check Jobber column | Every Jobber invoice is in the check | PASS |
| QBO invoices total = Billing Check QBO column | Every QBO invoice is in the check | PASS |
| Should-bill (weekly + monthly active jobs) = 3 x monthly basis | Billing policy applied the same way everywhere | PASS |
| Gap total = billed (both systems) - should bill | Gaps add up | PASS |
| Billing Check gap = engine gap (rows that differ) | Workbook and engine agree on every job-month | PASS |
| Double-billed jobs: over-billing = their QBO billing | The QBO invoices are the duplicate | PASS |
| Not-billed jobs: anything billed | They really were missed | PASS |
| Archived jobs: should bill | Ended work is never expected | PASS |
| Roster clashes where Jobber shows a different cleaner | Clashes are real | PASS |
| Calendar events marked as clashing = 2 x clashes | Every clash is visible on the roster | PASS |
| Calendar events = active jobs | Every live job is on a calendar, nothing ended is | PASS |
| Codes found = codes moved to 02 Site Access | Every code is kept, just not in the calendar | PASS |
| Calendar events with an access code in the text | Codes never reach a phone calendar | PASS |
| Drive folders = clients + 6 x locations | Folder plan is complete | PASS |
| Active jobs with no wave | Every job has a cutover date | PASS |
| Active jobs across waves = active jobs | Waves cover everything once | PASS |
| Gate open items: formula = engine (cells that differ) | Gate logic agrees with the engine | PASS |
| Pilot gates passed | The pilot can go live | PASS |
| Rostered hours = active jobs' weekly hours | Load covers every job | PASS |
| Rostered hours: workbook = engine (cleaners that differ) | Same load either way | PASS |
| Exceptions: billing rows = rows needing a fix | Every billing fix is on the register | PASS |
| Exceptions: roster rows = clashes | Every clash is on the register | PASS |
| Engine regression: job status disagreements | Engine matches the answer key on every job | PASS |
Source: sample files (Checks / Controls tab, test output or run log)
07What the files show
- 195/363jobs move as is
- 73,774.99over-billed, Aug to Oct
- 339calendar events built
- 32/32controls pass
Source: sample files, measured by script. Sample data, not a client result.
- What each job should have billed, less what Jobber and QBO actually billed, equals the gap on every line, for 771,931.10 across August to October.
- The engine agrees with the answer key on all 363 job statuses, 168 customers, 289 templates, 66 access codes and 11 roster clashes.
- No calendar event contains an access code: every title, location and description is searched.
- Each wave's six gates are counted by formula; the pilot passes 6 of 6 and 20 gates across the four waves stay BLOCKED.
08Risks and how the build handles them
| Risk | Mitigation built in |
|---|---|
| A site is billed by both systems during the move | Gate G1 blocks the wave while any of its sites still invoices from Jobber and has a live QBO template |
| Two QBO templates bill one site | Gate G2; templates are matched on property and service, then amount, so an old template is never mistaken for the live one |
| A wrong rate is carried into QBO | Every job compared on a monthly basis (52/12 for weekly work); gate G4 holds the wave until rates agree |
| Access codes end up in a calendar invite | All 66 codes are moved to a restricted Drive folder; gate G6 searches every event and finds none |
| A cleaner is double-booked after the move | Clashes are found from the visit times; 11 are listed and gate G5 holds the wave |
Source: sample design
09Appendix: files
A. Screenshots of the deliverable
Exhibit 8: Dashboard tab: scope, matching, rates, billing, roster, calendar, Drive, cutover and controls, every figure a formula
Source: sample files
Exhibit 9: Rate Reconciliation tab: each Jobber job against its QBO templates, with the status worked out by formula, next action and owner
Source: sample files
Exhibit 10: Billing Check tab: what each job should have been billed each month against Jobber and QBO invoices
Source: sample files
Exhibit 11: Calendar Build tab: one recurring Google Calendar event per active job, by cleaner and wave
Source: sample files
Exhibit 12: Cutover Waves tab: the pilot passes all six gates; each regional wave stays BLOCKED until its open items reach zero
Source: sample files
Exhibit 13: Controls tab: 32 formula controls, all PASS
Source: sample files
Exhibit 14: Report page: target process across the office, Google Calendar, the cleaner, Google Drive and QBO
Source: sample files
Exhibit 15: SOP-01: moving a site from Jobber and every change after
Source: sample files
Exhibit 16: Overview of the Jobber migration sample build: jobs reconciled, over- and under-billing for August to October, calendar events and controls
Source: sample files
B. Downloadable files
| File | What it is | Size |
|---|---|---|
| Jobber-to-Google-and-QBO-Migration.xlsx | Workbook: Jobber and QBO exports, customer, site and template matching, rate reconciliation, three-month billing check, roster clashes, cleaner load, access notes, calendar build, Drive plan, cutover waves and gates, controls and test log | 654.3 KB |
| Jobber-Migration-Report.pdf | Report (19 pages): data profile, findings, options, target design, matching, reconciliation, billing check, roster, calendar, Drive, waves and gates, controls, tests, cutover plan, risks, SOP | 751.5 KB |
| Executive-Summary.pdf | One-page executive summary | 134 KB |
| Migration-SOP.pdf | SOP-01: moving a site and every change after (PDF) | 56.6 KB |
| Migration-SOP.docx | SOP-01, editable Word file | 37.6 KB |
| engine.py | Engine: matching, rate reconciliation, billing check, roster clashes, access-code removal, calendar and Drive build, gates and the regression test | 19.6 KB |
| Jobber-Migration-data-and-code.zip | Inputs as exported (CSV), engine output, one .ics calendar per cleaner, and the scripts that rebuild the workbook, report and SOP | 208.6 KB |
Sample build: realistic data, built to show how I'd handle this work. Names are placeholders and no client data is used.
Have a similar problem in your business?
Hire me on Upwork (opens in a new tab)