Automated reporting on KPIs in a business environment

Having worked across many industries I can confirm that reporting is widely used to support decision making in pretty much any business environment. However, the methodology varies significantly: It ranges from zero automation, heavy data munging in Excel every week - up to professional business analytics software with 100% automation. In this article I would like to present some approaches on how to improve business reporting using simple tools.

Whilst bigger cooperations typically use of the shelf business analytics tools, smaller companies often struggle to develop a decent framework for reporting. I have seen many cases where employes just do the same analysis work every week or month. And all they do is calculating some numbers and updading existing templates such as powerpoint slides with current data.

This is obviously a potential area for improvement. I am sure you can use the time of your employees more wisely and replace such recurring activities by software.

Data sources for reporting

It might be a requirmenet that data from various sources is processed in order to generate the desired report. Typically the employee would just somehow open files, extract relevant data and copy it together. Possible sources of data are:

  • Databases (e.g. SQL, Oracle)
  • Logfiles (e.g. machine logfiles)
  • Other files (e.g. Excel, CSV)

As long as the raw data has at least some kind of structure, it can be processed automatically. Software such as python for instance comes with a wide range of modules that allow processing data from all type of sources which includes reading files or connecting to databases.

Data transformation for reporting

Typically some degree of data transformation is required. Possibly data from different sources needs to be linked or joined. Data is then transformed and it is also very common to use aggregation functions such as sum or mean to calculate some statistics. In the end only key information such as key performance indicators are presented in the report.

Output format of the report

The output format of the report depends on the usage. Given that a meeting is scheduled to run through the report, a presentation format such as a powerpoint slideshow might be ideal. However, when a report is solely distributed via email, an output as pdf-file might be more suitable.

Typical elements of such a report are text (mainly to explain), tables and charts. The information content of charts should not be underestimated as trends and changes can be easily detected.

Software for automation

You might be concerned that expensive software suites are required to fully automate reporting. However, this is not the case. In many cases simple and bespoken software scripts do the job and the development of such automation tools is often cheaper than expected.

In many cases we would recommend to look at python for such scripts. There are many python packages to read different file formats (xlrd for Excel, pandas for csv etc). Python also allows to connect to databases (e.g. via sqlalchemy). Data transformation and aggregation can be simply done in the data science package called pandas. Finally, packages like python-pptx an reportlab can be used to automatically create reports as powerpoint presentation and pdf-files, respectively.

Further improvements in terms of reporting: Live dashboard

As a next step a dashboard displaying live data might be the way forward. Such a dashboard could be implemented in the intranet of your company to distribute relevant information accross the company.

Please contact us if you wish more information about automated reporting in a business environment.