Data-Driven Decision and Visualization Changes Company Course
In this case study
Companies use their data to make decisions all the time, but what happens when you have the data but can't see it? One of our clients was about to decide based on the data about one of their divisions within the company. They held an assumption, based on data collected by the division and compiled by an analyst, that there was a customer segment that had over $20M annually in the cost of goods sold (COGS). Further, this company thought removing this segment would reduce almost all COGS and increase the division's profitability.
The division in question had four major customer segments, based on the complexity of the customer and the number of services they required. By removing the easiest part, the thought was COGS could be easily reduced. Given the numbers they were looking at, it made sense that this could be an easy decision. WWT was engaged to confirm this assumption by accounting for all COGS that could be addressed through the targeted customer segments and then determine which decisions would remove the COGS.
WWT was provided over 25 different excel workbooks based on business customer transactions (the division is only B2B). WWT was to build up the numbers from the receipts to determine a holistic financial depiction. While the original focus was COGS, we made sure to also understand the revenue impact that would occur when decisions were made. We were provided with all previous work from both the company and the division. Finally, the division had a tableau dashboard that pulled different data from unique reporting locations to help provide a visual for compiling information.
The company's initial assumption was $20-28M in COGS was addressable within the targeted subscriber segment. WWT's mission was to determine the makeup of these addressable COGS or costs that could be removed because they were not paramount to company success. The breakdown of COGS should then form recommendations that would allow for a reduction without a major loss in revenue and/or customer amount. The division leadership assisted WWT in determining what each dollar in COGS represented and how the money was connected across the division.
Every meeting was a validation check after the first two weeks. They had an assumption we had to balance with Company (who believed all targeted segment costs were the primary culprit for high COGS) and Division (who knew this wasn't the case but were not showing that as well as they could have for a few reasons – for example, no business insights team, poor connection between data sources). The consulting services team with support from the account team spent significant time over two days discussing the assumptions that the company leadership had and how we could confirm or improve those assumptions.
WWT's approach was to sort the data, understand the implications and tell the financial landscape story back to the company and division leadership. Key to our approach was being aligned with Company on our work to ensure the data matched the recommendations. WWT took the following steps to show a connected story for the leadership teams:
- Walked through data sources with the client: Due to the decentralized nature of the client data, it was necessary to walk through each provided spreadsheet together with key client stakeholders to understand relevant metrics related to potential COGS reductions
- Selected a sample group: Due to the very high number of colocations, a sample group was chosen to be able to take a deeper dive into the data. We categorized colocations based on what was housed in them and each location's overall complexity. Two colocations from each category were chosen to give WWT a representative sample of the overall data. Once this sample group was selected and validated with the client, WWT consultants and data scientists took a deep dive into each location to understand the relevant data and how it mapped from provided workbooks.
- Created a "master workbook": A consolidated workbook was created by pulling the key metrics that were previously identified. In order to build a comprehensive workbook, data had to be pulled from all provided workbooks.
- Performed a data audit: Due to client inconsistencies in how the data is stored and pulled, several data gaps, issues, inconsistencies, and contradictions were identified in the "master workbook." To clear up these issues, WWT performed on-site workshops to walk through individual identified issues and clear them up to get a more complete set of data.
- Built Dashboards for the sample data: WWT data scientists created dashboards using the cleaned-up master workbook for the sample colocations with a very short turnaround time due to the accelerated engagement timeline. WWT management consultants and data scientists iterated on the dashboards to clearly highlight relevant metrics and financials for client executives to easily understand the data and be able to make comprehensive informed decisions based on it.
- Socialized initial dashboard with the client: Shared dashboards with key client stakeholders receiving very positive feedback.
- Scaled the Dashboard to include all the client's colocations rather than just the sample size: Once client executives reviewed the initial version of the dashboards, data scientists simply pulled the full range of data from the master workbook to include all of the client's colocations. This provided a full view of relevant metrics not only at the colocation level, but also at the customer segment level.
In the final project readout, the company and division leadership heard some information they did not believe to be correct and wanted to know more about specific examples to understand the information better. WWT presented the dashboard to show an example of the targeted subscriber segment if they were removed from a colocation. In that moment the response was, "This is awesome," followed by many questions based on the data shown. The discussion amongst the company continued; they were thinking about what they could do going forward and what should be the correct path. Instead of cost-savings, they flipped to how profitable can we be if we keep some colocations and not others.
Our dashboard brought forth two major misconceptions:
- The initial belief was colocation costs were the cause of high COGS for the targeted customer segment. The costs for colocations were not high and the costs were not going to rise in the future. This realization happened in real time as we showed the dashboards to the company leadership.
- When showing the targeted subscriber segment costs, we also showed the tied revenue that would be lost. For the targeted segment, there was a saving to be had, but they also would have lost $25M in revenue for the division.
In a follow-up conversation with the company, the leadership thought more about the dashboards and what would provide them the best opportunity to make the right decision about the profitability of their network. The company thought rather than focus on the subscriber type, there may be opportunities in targeted geographic locations. The company leadership wanted a map with all locations on it, but WWT knew we could provide more, including where to expand the network, where to find the cheapest locations to build, and what types of subscribers could be reached along the way. Once again, our data scientists got to work with one of our analysts who had previous geographic information system (GIS) experience.
WWT started with consolidating geographic data of client's current assets. WWT was provided geographic information on the tens of thousands of data entries from before, and the team aligned these data from different sources into a universal, analyzable form. WWT team then drafted a prototype interactive tool for one of the client's major markets. The prototype tool laid out the client's existing asset locations, along with the locations and details on the different customer types. The prototype also contained a draft algorithm using Google Maps API to calculate construction opportunities from the list of customers to the client's current assets to expand business opportunities. The results were plotted on the map, along with the client's current assets and customer locations, overlayed on an option of different street map choices (omitted in the screenshot), and shown to the client.
The client was excited about the interactive tool, and WWT aligned with the client team on how to best deliver the bonus ask, enabling the client to maximize their value with the most efficient investment. WWT then expanded this effort to map out all major markets and optimized the algorithm with buildout calculations to improve accuracy. The final deliverable to the client is a web-based interactive tool with checkboxes to show different layers to explore different opportunities. WWT team has added color and icon differentiated designs to differentiate customer types and added dollar-figure adjustment tools to allow for exploration of buildout scenarios with different dollar amounts. The improved algorithm also allows for "stacking" of routes when multiple customer locations are in the vicinity, to reduce repetitive builds in simulating real-world scenarios. Finally, the WWT team also added a choropleth map layer to highlight the best buildout areas based on potential revenue.
To finalize the message, the WWT team also delivered a data summary slide capturing the best investment situations in all the major markets, highlighting the financial benefit of each of the scenarios. This allows for the recommendation of the best constructions to make, and the interactive map allows for the exploration of customization based on the best recommendation.