A Dashboard That Can Show Its Math
Every season, a youth soccer club asks the same question in a dozen different ways: are we ahead of last year or behind? Registration doesn’t arrive evenly. It comes in waves around deadlines, tryouts, and the first warm weekend of spring. So “how many players do we have?” isn’t a useful number on its own. What you want is how many players you have compared with the same date last year, broken down by program, region, gender, and age group.
So I built a pacing dashboard. The first version came together quickly. It looked good, the charts moved, and people liked it.
Then someone asked a simple question about one of the numbers, and I couldn’t answer it.
What was wrong with the first version
Nothing was dramatically broken. It had three quieter problems, and together they were worse:
- It couldn’t be edited. Changing how a program was categorized meant starting over.
- It didn’t reconcile. When I added things up from the source export by hand, the totals didn’t quite match the dashboard.
- It couldn’t show its math. There was no way to follow a number back to the rows that produced it.
A dashboard that can’t explain itself asks people to trust it. The first time it’s wrong and nobody can say why, they stop trusting it, and they go back to their own spreadsheets.
Defining the words before counting anything
When I dug into why the totals didn’t always match, sometimes the reason for the gap wasn’t arithmetic. The dashboard and I were counting slightly different things. “How many players do we have?” sounds simple until you try to write the rule down. Does a player count when they’re invited to a team, or when they accept? If one player is on two teams, are they one player or two? Which date does this season get compared against last season?
Each of those questions has a reasonable answer. The trouble starts when nobody writes the answer down. Then the spreadsheet decides for you, usually by counting whatever happens to be in the row, and two people looking at the same chart end up reading two different numbers without knowing it.
Churn is the clearest example. Say a player spends a year in the club’s development program and then moves up to a competitive team. For the family, that’s a success: they stayed, and their kid moved up. But if churn means “a player who was in a program last season and isn’t in it this season,” the data counts that player as one loss in one program and one new player in another. Repeat that across a whole club and retention looks worse than it is, and the programs doing the best job of moving players up look like the ones losing families.
A smarter chart doesn’t fix that. A definition does, written down and built into the logic. A player who leaves one program and joins another is retained by the club and counted as a move between programs, not as a loss plus a new sign-up. With that rule in place, the same data answers the two questions leadership cares about: are we keeping families, and where are players going?
So the rebuild started with a short list of definitions: what counts as a rostered player, how a player on more than one team is counted, which date each season is measured on, and how each program is categorized. Each one is a decision someone made on purpose, not a side effect of how the export happened to be shaped.
What the rebuild does differently
The new version is a self-contained page. You upload the latest roster export and it does the rest. Alongside it sits a plain spreadsheet model that does the same calculation step by step, so anyone can check the work. Each step follows the definitions above, so when two people disagree about a number, the conversation is about the rule, not about whose spreadsheet is right.
It also archives each season when it closes. Before, refreshing the data quietly overwrote the history the comparison depended on. Now last year is still there next year.
What I took from it
The first version wasn’t a mistake. It showed what people actually wanted to look at, which is a useful thing to learn early. But “looks finished” and “is finished” are different, and in reporting the difference is whether you can show your math.
Now I ask four questions before calling a report done: Have we written down what each number means? Can someone else change it? Does it tie back to the source? Can it explain any number it shows?