BI is not only used by companies to help generate more revenue. It can also help save money by making businesses more efficient.

In this article I’m going to talk about a project I worked on where a simple scorecard dashboard helped save this one company over $2million a year*. Let me tell you how.

So this is going to be a case study and I'll talk about the project requirements and objectives, the data set up, what the challenges were, how we overcame those challenges, and what benefits were gained by the client on the successful completion of the project.

Now, client privacy is something I take very seriously so I won’t be revealing anything that would identify who the client was, which country the project was done in etc.

I’ll try and keep things as anonymous as possible while making sure that everything makes sense and you’re learning about a real BI project.

I’m just going to say that it was for a multinational beverage company. Ok, let’s jump in.

Objectives and requirements

So, the client’s objectives centered around making life easier for the around 600 members of their B2B salesforce in the field, who were working with retail outlets like grocery stores, supermarkets and the like.

They wanted to produce a dashboard - well, more of a scorecard really - that could be easily accessed by each member of the salesforce in the field and show them the metrics and stats for each of the retail outlets they were in charge of.

This would make it much easier for the salesperson to see things like which of the client’s different drinks brands and their variations were currently stocked, and, more importantly, which ones weren’t being stocked so they could try and get the outlet to stock them.

Also, they could see what kinds of coolers and fridges they had in store, how much shelf space was available and how much of that shelf space was taken up with their brands.

They could see their goals for certain metrics and what percentage of that goal was being achieved which, in turn, would allow them to focus their store visit on things that would help them more easily reach their objectives. Their priorities, if you like.

All of this would be available by simply clicking on a link on the retail outlet’s page in the client’s CRM which would open up a dashboard on the salesperson’s tablet.

The overall idea was, not only to make the salesforce's job easier, but also save them a lot of time that they would normally have spent searching for different pieces of information and data in different places before setting out to visit each outlet.

Essentially making the whole salesforce much more efficient, allowing them to visit more stores in the same time that it would have taken them previously.

So the dashboard required figures that showed the current figures, current and yearly objectives and the percent of objectives achieved for 7 different KPIs.

There would also be a list of “missing” brands and their variations. In terms of interactivity there would just be a couple of simple data filters that would allow the user to look at other retail outlets in their portfolio directly from the dashboard rather than having to go back to the CRM and search for it there.

So nothing at all complicated. In fact, as I explained in my previous article, the building of the scorecard dashboard only took me about an hour or two to put together.

The layout was simple, they wanted something that looked nice and professional but weren’t demanding when it came to the design, and there were no complicated calculations to be done in the BI tool because they had already been done when building the data sources. Which brings me nicely on to...

Data setup

The data setup is where things got complicated. The data for each of the 7 different sets of KPIs lived in separate databases that belonged to different departments.

The data could be accessed only when express authorisation was given to the user and only on the company’s intranet or VPN so essentially, nothing really open to the outside that could be connected to via a cloud-based BI tool like the one they had chosen for the project.

At the time, in order to create certain calculations, aggregated data was being exported from separate sources and combined in Excel where the calculations were done manually using formulas.

The client had to weigh up the risk of any potential data leaks or hacks versus the savings, both in terms of time and money.

Our job was to figure out the best way to have the least amount of data exposed and out in the open as possible.

Even though the BI tool already had bank-level data security, there are simply no ironclad guarantees that leaks won’t happen either technically or down to human error (which, as we know, is by far the most common way that leaks occur).

So how did we overcome these issues of data security? Well, firstly, we knew we needed help from a third party tool that would fit seamlessly with the cloud BI tool that was hosting the dashboards.

This tool would need to act as an ETL that would receive data exports from the different databases before loading them into a data warehouse that would then be connected to by the BI tool.

Only the data necessary for calculating the KPIs was to be exported automatically from the different databases.

Each weekly export would delete and replace the previous one. All the different datasets were actually sent separately as attachments via automated email to an address that was set up specifically for the task.

These attachments were then picked up by the ETL and all the necessary transformations, joins and calculations were applied.

The fact these calculations were done beforehand rather than in the BI tool was also a decision made for security reasons as it meant that the full exported data wasn’t accessible via the BI tool.

So if someone got their hands on one of the salesforce’s tablets, they wouldn’t be able to access the full data, just the aggregated data needed for the KPIs.

So there was a lot of time spent in the preparation spec’ing phase of the project that I talked about in my previous article.

Because we needed to list where the data came from for each KPI and what the Excel formulas that were being used did to the data in terms of calculations so that we could then go about translating that into SQL.

It’s difficult to determine accurately exactly how much time all this took to achieve. There were around 8-10 people on the team working on the project from the client, the ETL partner and ourselves.

Scheduling in-person meetings for this number of people was not that easy but, if memory serves, I believe we needed around 2 and a half days to spec everything out.

Once this had been done, the creation of the dashboard was fairly quick. But there was one more crucial requirement that needed to be addressed.

And that was to secure the data for each user, each member of the salesforce, so that they would only be able to access the KPIs for the stores in their portfolio.

But the regional sales managers needed to be able to see more than one outlet at a time.

So, aggregated data for multiple stores to see the overall efficacy of their salesforce.

To achieve this we used the API of the BI tool to automatically set up access for each user based on an excel sheet that was provided by the client and updated weekly.

This simple sheet contained the user name and the store ids that they should be granted access to.

Finally, we needed to create a custom URL for each outlet that could be inserted as a custom field into the client’s CRM.

This was, in fact, very simple to do because there was essentially only one dashboard providing hundreds of filtered views.

So there was only one URL that just had the retail outlet id appended to it. That id lived in the CRM already so the custom field was easily created.

And that’s it. A fantastic example of how a BI project isn’t just all about visualising data and building dashboards.

It shows you the different moving parts, if you will, that need to all come together to make a project a success. But just how successful was the project after all?

End result

So, this was a while back and I don’t think we ever received feedback about exactly how successful the project was, only that it was being used and it was working great.

But let’s do some quick, back of a napkin (or beermat) calculations to give you an idea of just how beneficial this project was to the client.

Let’s say that the dashboard we produced saved each salesperson on average 2 hours of work every week (and that’s probably a conservative estimate).

With a salesforce of 600 that makes 1,200 hours saved every week. Times that by 50 and you’re looking at 60,000 hours of time saved per year.

That’s the equivalent of 7,500 8 hour work days per year.

Now, I don’t know how much the average salesperson cost the client for an 8 hour day but let’s say, for argument’s sake, that it cost the company $300.

300 times 7,500 makes a total of $2.25M per year in savings. Personally I don’t think you can really quantity it in those terms but it does show you the power of BI. Just one small dashboard having a huge impact.

Well, I guess that’s why the big boys stay the big boys.

I hope you enjoyed this case study. If you’ve got any questions or comments, please do let me know below.

*Ok, so the title of this article is quite clickbaity and, full disclaimer, the 2 million dollar figure is a back of the napkin calculation that, although I’d imagine is fairly accurate, I don’t have an actual figure.