Python for BI: from three surveys a day to dozens

By the fourth year, the work had outgrown us. The BI practice I had been building at a consultancy for private schools in Brazil now had a solid database and reports rebuilt on top of it (that part of the story is in From Excel to SQL Server). But the volume and complexity of the work went past what the team could handle. On a good day, we could treat the data from three client surveys. That was the ceiling.

That year turned out to be decisive. This post is about how we broke the ceiling, and why the answer was a programming language rather than more people.

Where the hours were going

Survey data rarely arrives ready to analyze. Questions get reworded from one edition to the next, answers come in slightly different formats, and some responses are incomplete or duplicated. Before anything reaches a dashboard, someone has to clean it, standardize it and map it onto a structure the reports understand.

We were doing that treatment largely by hand. Each survey meant repeating many of the same steps, and repeating steps by hand has two problems: it is slow, and it fails in a different place every time. The more surveys came in, the more of the team's day went to preparing data instead of analyzing it.

Hiring would have helped for a while. But when the bottleneck is a repetitive procedure, adding people mostly adds more hands doing the same repetitive procedure.

Why Python

I moved the data treatment processes to Python, using pandas and numpy. The choice had less to do with fashion than with one property: a script does the same thing every time you run it.

pandas is the workhorse for tabular data. It reads files and database tables, cleans and reshapes them, joins one source to another and aggregates the result. numpy sits underneath and handles the numerical work, fast and over large arrays. Between the two, almost every step we used to do manually could be written down once and run again on the next survey.

Around them sits a wide ecosystem. matplotlib and seaborn produce static and statistical charts, plotly builds interactive ones for the web, and Jupyter notebooks are handy for exploring a new dataset and prototyping a treatment before it becomes a routine. You do not need all of it on day one. For us, the core job was getting the treatment itself out of people's hands and into code.

From three surveys a day to dozens

The effect was large. Automation took over most of the process, efficiency went up, and the time needed to treat large volumes of data dropped sharply. We went from treating at most three client surveys a day to dozens.

What changed for the team matters as much as the number. People stopped executing the treatment and started supervising it. Their work became correcting errors and adjusting questions and answers when a survey called for it, which is exactly the part that requires judgment. The rest ran on its own.

That shift transformed our operating capacity. It also opened the next step: with processes automated and the team grown to four people, we could start thinking about the client's experience beyond the reports. That led to the fifth year and a web portal built around Power BI Embedded.

Automation is only as good as its checks

Automating a messy process gives you messy results faster. A script that runs dozens of times a day needs guardrails, and data quality has to be designed in, not inspected at the end. These practices make the difference:

• Define what good data means. Accuracy, completeness, consistency and timeliness are the usual criteria. Write down what each one means for your data.
• Validate before the data is used. Checks for missing values, impossible answers and duplicates belong in the pipeline, before anything reaches a report.
• Profile every new batch. A quick look at the structure and content of incoming data catches surprises, like a renamed question or a new answer option, before they turn into wrong charts.
• Make failures loud. When a step breaks or a check does not pass, someone should know right away instead of finding out in a client meeting.
• Give quality an owner. Someone has to be responsible for the rules, working with whoever owns the source.

That is where the human role landed in our case. The scripts handled volume, and people handled the exceptions that the checks surfaced.

Learning to code is no longer the hard part

Not long ago, learning a new technical tool was a project in itself. That has changed. With AI assistants, the technical learning curve has dropped in a way I had never seen, and lack of time to learn is no longer a good reason to stay away from a technical tool.

Two things follow. AI has become the main tool for almost any technical work. And the core skill of anyone who works with data is now learning itself: being fluent at picking up something new, getting past technical obstacles and finding better solutions. The problem should choose the tool, not whatever the analyst already happens to know.

What I would do again

• Automate the step people repeat most, first. For us, that was survey treatment.
• Keep people where judgment is needed. Correcting errors and adjusting questions is not busywork. It is the part that needs someone who understands the survey.
• Write the quality checks with the pipeline, not after it. Retrofitting them means trusting a period of results you never verified.
• Don't hire your way out of a repetitive process. Automate it first, then hire for the work that grows on top of it.

The full timeline of those six years is in building a BI practice from scratch.

If a manual data routine is eating a good part of your team's week, tell us about it.