Macro data analysis

Which clients cost more than they bring in?

Berkhout is a Dutch accounting practice that runs the entire administration for thirty-five clients on a fixed annual fee. Six people, three years of logged hours. The answer to that question sits in the time registration — nobody could get at it.

Berkhout is a simulated engagement: a practice we set up ourselves to build this pipeline against and measure it on.

logged time entries: 21,093
spellings for 35 clients: 140
clients losing money three years running: 3

doorspitten.py

calls AIruns in code

The data was already there. It just could not be reached.

This is not a special case. Almost every time registration older than a year has the same three defects: the client name was never agreed, the type of work is not recorded, and the registration is full of rows that cannot be true.

The consequence is that you can see that a client costs a lot of hours, but not what those hours went into. And that is exactly the difference between “we need to raise the fee” and “we need an agreement about what you hand us”.

So the job was not to put a model on the data. The job was to get one question out of it that holds up, and to be able to show how good the answer is.

Six things a pivot table runs aground on.

None of these six is exotic. They sit in almost every time registration, which is why they all sit in the test data.

Four names for one client

Vlietstra Wholesale appears as “Groothandel Vlietstra”, “Vlietstra groothandel”, “Vlietstra” and “gh Vlietstra”. A pivot table on client name therefore returns a hundred and forty clients instead of thirty-five.

“Vos” resembles nothing

Timmerwerken Vos is also simply written as “Vos”. Textually those two barely resemble each other, so matching on similarity loses that client entirely. They do share the one word that matters.

“Meeting”, and nothing else

That same word is either a conversation with the client or an internal team meeting. The difference is not in the description but in whether a client is attached to the row at all.

One row in seventeen says nothing

“Various”, “work”, a dash, or nothing at all. Those rows exist and the hours on them are real. So they have to count without an invented type of work appearing underneath them.

Rework looks like ordinary work

“Balance discrepancy investigated” is not annual accounts work, it is rework. Miss that distinction and you miss exactly the category the answer turned out to be hiding in.

The registration itself is not clean

Rows of twenty-four hours, rows of zero hours, and the same row twice because someone hit submit twice. Across these three years: 196 and 84 of them.

Two losses that look identical.

Three clients cost more than they bring in, three years running. That was the question. The usable answer sits next to it: across the whole practice, nine percent of costs is rework — correcting, investigating, doing it again. That is the baseline you measure the rest against.

Talsma: − € 19,706

21 percent of costs is rework, more than twice the baseline. Over three years that is € 22,812 of work with no agreement behind it — more than the loss itself. Without the rework this client is simply profitable.

Vlietstra: − € 14,880

Rework at 7 percent, below average. Nothing unusual is happening here except the amount: it is too much work for what was agreed. Different problem, different conversation.

Kolthoff: + € 48,563

Appears on no list of problem clients, and rightly so. It does contain € 22,704 of rework though — eighteen percent of everything done for that client. Without a type of work under the hours, that is invisible.

Two clients sitting next to each other in the same overview, with two conversations that have nothing to do with one another. That distinction is the entire point of this engagement. The percentages above are what the pipeline itself produces, and they run low rather than high — by how much is further down.

Every step says what it threw away.

A cleaning step that does not tell you what it discarded is a cleaning step you cannot check. So every step counts its own result and all of it lands in one file.

This is the output of the run every number on this page comes from. It is one script of seven steps, in Dutch because the practice is; there is no dashboard around it and there does not need to be.

python doorspitten.py

1. ingelezen          21093 regels, 35 klanten
2. opgeschoond          196 onmogelijke uren, 84 dubbele regels weg -> 20813 over
3. klanten gekoppeld  19055 regels, 140 schrijfwijzen -> 35 klanten
4. sleutels           20813 regels -> 6694 letterlijk uniek -> 3342 na normaliseren
5. gelabeld            3342 opgevraagd, 0 uit cache, 79.6s, $0.42
6. gecontroleerd    klantkoppeling 100.0% juist
   model          95.9% juist bij 93.3% dekking = 89.5% van alle regels
   woordenlijst   96.1% juist bij 89.4% dekking = 85.9% van alle regels
7. geanalyseerd     6 vragen op 20813 regels

What this turns on.

Of the seven steps, one calls AI. That is not thrift but a design choice: code is cheaper, gives the same answer twice on the same input, and can be checked.

Match client names without a model

For each official client name, keep the words that distinguish that client from every other, and match a written name to the client sharing the most of them. Only when that yields nothing does textual similarity decide. Measured result: 100% correct across all 20,813 rows.

Ask 3,342 questions, not 20,813

The number of different things people write down is far smaller than the number of times they write them. Strip digits and punctuation and “btw aangifte Q3” and “btw aangifte Q1” become the same question. Six times less work for the model, same outcome.

III

“Unknown” is a good answer

The instruction to the model says in so many words that hesitating is allowed. A confident-looking guess disappears silently into the totals and shifts the answer; an honest “unknown” stays visible and can be looked at separately.

The analysis itself is just SQL

Once there is a type of work under the hours, the rest is adding up and dividing. That lives in a separate file that can be read and run on its own, so the bookkeeper can check it by hand. No model belongs there.

Was the model actually needed?

A word list runs alongside the model: about thirty search terms, what a developer writes in an afternoon. It is not there to lose. If the word list is good enough, the model is redundant, and then that belongs here.

Across all 20,813 rows the model gets 89.5 percent right and the word list 85.9 percent. The model is not more precise — it is just willing to answer more often instead of saying “unknown”. That cost $ 0.42 and eighty seconds.

And on the point this engagement turned on, it makes almost no difference. Of the rework actually present, the word list finds 66 percent and the model 68 percent. Both miss a third, and for the same reason: “balance discrepancy investigated” looks like annual accounts work, to a model as well, because nothing in it says something had gone wrong. That is not a shortcoming of the model but of the question.

So the advice to Berkhout is not to put this model into production.

Put a “this is rework” checkbox in the time entry screen. Then you measure it up front instead of guessing at it afterwards, and next year it reads 100 percent rather than 68. The model has already done its job by then: it made three years of old hours usable, so you knew this was the question to ask.