Near-realtime sales dashboard for a manufacturing company

Context

Our client was a industrial supplies manufacturer based in the US, with annual revenue of around $150M. It was previously a division of a larger conglomerate that used an Excel-based scorecard system to measure sales performance against plan.

The existing scorecard didn't provide the level of granularity that the new management team wanted to have - it did not show performance against prior year (only against plan), and didn't allow for drilldown into individual customers. The scorecard was also refreshed only at month end, so on-going progress during the month was not visible to the team without ad hoc manual analysis.

Objectives

The client engaged us to design and develop an automated sales dashboard to track their booking and sales performance by product category, customer, industry segment, for every sales territory, against targets and historical trends. They wanted both an at-a-glance overview of the sales performance against targets, as well as the ability to drill into the aforementioned details. Furthermore, they requested the ability to see this at a weekly frequency.

Additionally, the client also requested a dashboard on customer churn, to flag large customers that are dropping off so that their sales team can target these customers in a timely manner.

We recommended PowerBI to build the dashboard, as it was the most economical option that satisfied all the requirements.

Project delivery

We leveraged the data warehouse that we had previously set up for the client, which is refreshed daily with the raw data fom the client's ERP (Infor SyteLine) and CRM (Microsoft Dynamics). In terms of the Medallion Architecture, the data warehouse contained the Silver Layer data tables, which was a clean copy of the underlying ERP and CRM data objects.

We first worked with the client teams to align on the definitions of the KPIs they wanted to see. Specifically, we needed to understand any exclusions to be applied to the raw bookings and invoiced sales data (e.g. inter-co, returns, samples, accessories) to match their existing definitions. For the churn dashboard, since the client did not historically look at this metric, we helped them define it to be any customer whose bookings in the trailing 12-month period is less than 10% of the preceding 12-month period.

Once these calculations were aligned, we coded them into the data warehouse. We created SQL stored procedures to perform the bulk of the transformations of the raw bookings and invoiced sales data (aggregations, lookups, exclusions); these stored procedures are run overnight as they take about 30 minutes to complete. An additional complication we resolved was the client's definition of fiscal month which differed from calendar months and is based on whole weeks (so that fiscal months always started on a Monday and ended on a Sunday). This was a finance-managed rule that was historically manually applied to reports. We coded this rule into the data warehoue to procedurally determine the fiscal month start and end dates.

The results of these stored procedures are a set of denormalized Gold Layer tables (tables that contained the most cleaned bookings and sales data, along with all the necessary dimensions such as segment, territory, customer, sales person, fiscal month, etc.) that can be used by PowerBI to quickly produce all the dashboards. 

For the customer churn dashboard, we further created a view on top of the denoarmalized bookings table, to add several additional helper fields that enables the churn calculation.

Concurrent to the coding the data transformations, we mocked up the the PowerBI dashboards and worked with client sales leaders to understand the format that was most helpful to them (in terms of which dimensions to keep as separate pages, and which dimensions to put into filters or separate visuals). Once this was finalized, we connected it to the data warehouse and completed the dashboards.

Outcome

We deployed the dashboard to the client's online PowerBI portal, and created different workspaces to allow different groups of employees to see different versions of the dashboard (i.e. some metrics are only available to the senior leaders). The reports are automatically refreshed nightly, ready to be viewed when employees log in every morning.

The dashboard is used by 22 different employees each week, and has become embedded into the sales management workflows. Furthermore, we quickly replicated the same dashboard to other subsidiaries that the client had acquired since we deployed it.