Case study 11
Reports nobody has to assemble
Leadership used to wait for someone to build the week. Now a client health page arrives every Friday on fixed written rules, each person gets their own digest and priorities email, reminders stop only when the work is closed, and client hours land in a ledger that checks itself.
Every weekly view was assembled by hand. Client status came from reading boards one by one. Client hours were reconciled from monthly exports reshaped in spreadsheets. Nobody's week lived in one place, so people opened the tracker to find out what was theirs.
I replaced assembly with four scheduled jobs.
The client health page. A script pulls every client board, measures overdue tasks, open blockers and days since the last client touch, and applies fixed rating rules written down and approved by leadership. A small override sheet can force a rating, but only with a one-line driver. Each row shows client, rating as color plus word, the rule that fired, next step, owner and an ask for leadership. Every reference is linked: tasks, files, meeting notes, emails. Red sorts first. A preview mode sends to me only; the live page goes out on Friday afternoon.
A digest and priorities email per person. Sunday evening covers the week ahead, Friday afternoon the week in review. Sections are overdue, due this week, waiting on others and recently completed, with a link on each section to a tracker view the script refreshes every run. People with nothing due get nothing.
The reminder engine. Each morning, open tasks carrying a reminder tag and due today or earlier email their owner, one line per task with a link. Closing the task is the only off switch. There is no snooze.
The client hours ledger. A weekly pull groups hours by client into a block per client: purchased hours, flat-fee work that is tracked but never deducted, hours used, balance. New clients get a block automatically. A check row reconciles hours pulled against hours in the blocks and must read zero. Before a balance on a client's hours task changes, the old figure is logged as a comment. It ran beside the manual method for the first weeks until the two agreed.
Weekly Client Health page (demo): red and amber sort first
| Client | Rating | Driver |
|---|---|---|
| Dolomite | Red | Approver unresponsive 12 days; two deliverables blocked |
| Ironbark | Red | Client approver blocking 9 days; two deliverables past due |
| Basalt | Amber | Two invoices unpaid past 30 days |
| Garnet | Amber | Three out-of-scope requests this week |
| Kaolin | Amber | New point of contact |
| Alder | Green | On schedule; cadence holding |
Client Hours Ledger, OVERVIEW tab (hourly blocks)
| Client | Bought | Used | Left |
|---|---|---|---|
| Alder | 120 | 47.50 | 72.50 |
| Basalt | 80 | 19.75 | 60.25 |
| Dolomite | 100 | 47.00 | 53.00 |
| Fernworth | 40 | 16.25 | 23.75 |
| Juniper & Co | 50 | 30.75 | 19.25 |
| Total (8) | 615 | 223.50 | 391.50 |
| Check (= 0) | 0 |
Click any image to see it full size. Every file here is rebuilt with invented data.
The results, in bands. The weekly client health page became a scheduled send, and my part shrank to review. Monthly reshaping of hours became an automated weekly pull. Each person's week now arrives in their inbox. Time saved was not formally measured.
- A test or live switch kept flipping whenever code was pasted in. I moved it into a stored setting outside the code, so a paste cannot change who receives a send.
- My confirmation replies cluttered leadership's inboxes. I stopped sending them. Nothing urgent goes in a scheduled report; urgent asks go separately with a clear subject line.
- I did not measure time before and after. The claim stays at "assembly replaced by review" until I can show the numbers.
Live demo: the weekly client health pageLive demo: the per-person digestDownload the Client Hours Ledger (xlsx)Download the Task Health Dashboard (xlsx)The Client Health RaterThe Retainer Hours Balance toolReports that build themselves, and the line you should not crossLive demos of the working filesPart of the work in case study 07