This is a story of a small business adopting business intelligence tools, allowing them to visualize and understand their data, acquire information, get insights and make information-driven decisions.

According to the ‘2018 Small Business Profile’ report of the U.S. Small Business Administration -access it here-, 99.9% of the business in the U.S. are small business (less than 500 employees), employ more than 58.9 million workers and account for roughly 50% of the private GDP; and 97.6% of the companies that export goods are small business, 32.9% of total exports. No need to highlight that small business are vital for the economy and the employment. Nonetheless, when it comes down to adopting new technologies, namely business intelligence, small business lag behind the larger companies for multiple reasons but, mainly, lack of knowledge and experience using these tools, lack of time as they are devoted to producing and selling, cost of the tools themselves and/or of hiring an analyst and, in most cases, a mix of them all.

While pursuing a master’s degree in Data Analytics at the University of Houston-Downtown, I had the opportunity of volunteering at a Dental Office in Houston. During that semester, I managed to bring business intelligence to the owners and managers of this company and provide them with a useful tool for making, hopefully, better decisions based on data. In the following paragraphs, I describe the process, challenges, and outcomes of this successful initiative.

The software used by the company only produces of the standard report that the software in use generates. The software doesn’t produce any kind of report different from pdf what makes of the information virtually useless.

The management printed these kind of reports and made calculations and stats in the old way, literally: using a pencil and a two-decades old hp 10bii financial calculator.

Standard reports

Getting the data out of the pdfs is really annoying, to say the least. The conversion tools usually produce lots of columns -in the best of the cases these are empty- which should not be there, thus the cleaning and tidying process is always tedious and time consuming. At this moment, I’m writing some code in R Studio to automate this process.

The first approach: creating a dashboard in Excel using pivot tables and charts as a pilot test. The dashboard was simple yet functional and provided the basic information that the management needed to track their business in a daily basis. Below is a print screen of that initial Excel’s pilot test.

First dashboard using Excel’s pivot tables and charts

As is natural, the management saw the potential of accessing their information and take decisions based on data, not merely intuition and gut, and supported the proposal of taking it the next level, using a professional Business Intelligence tool. However, there were several challenges to bring the project to real-life:

Among the alternatives evaluated were Tableau, Power BI, Looks and Sisense. Finally, Power BI was the weapon of choice given its cost -free for the basic version-, its versatility, robustness and quick learning-curve: basically, if you’ve used Excel’s pivot tables, you’ll be able to use Power BI.

Current version of the dashboard — Production, collection and some important stats to keep an eye on

This print shows the revenue and collection information, as long as some other stats that are important for the management to measure the practice’s efficiency.

Current version of the dashboard — Patients’ attendance

Current version of the dashboard — sales tracker

This screen shows the patient’s attendance, which, in turn, measure the ability of bringing new patients to the appointments and retain existing ones.

Finally, this screen measures the efficiency in sales, i.e., the proportion of treatments sold to treatments presented, both in quantity and dollar amount.

A side-effect of cleaning and tidying data to create dashboards is that you’ll end up with Cleaned and Tidy data! Once you have that precious yet scarce commodity, well, seize it, eat it, digest it, create a lot of visuals, turn them up-side-down, show your work to the ones in the front line and ask them lots of questions… those will be your fifteen minutes of fame so, enjoy them!

Below is just a sample of some of the visuals created. In the initial data analysis, I was creating a picture of the patients of the dental office: where do they come from? What’s their gender and age? Do they have insurance?

Some of the visuals generated during the data analysis