Wednesday, July 17, 2019

Sales Platform Improves Sales Manager Productivity

Key Challenges

   Automate product categorization in profit and loss hierarchy.
   Integrate sales and budget platforms.
   Reduce data processing time.

High Sales Volume Drives Need for Automation

Our client, a large software company, runs a chain of successful online stores and retail outlets. The stores generate millions of dollars in revenue each day. Because of the enormous number of sales, sales data is difficult to visualize and track. Without an easy way to interpret sales data, the client cannot resolve problem areas and improve customer relationships.

In 2014, our client approached us with a straightforward request. The client needed a way to more easily track sales data. Sales managers needed a platform to project sales and forecast data. Sales managers spent significant time on four tasks. First, sales managers manually categorized products into a profit and loss hierarchy. Manually creating the profit and loss hierarchy meant the chances of data discrepancies were high. Second, sales managers manually tabulated cost calculations (such as cost of goods sold). The client’s upstream data source did not provide the current cost for most products. Third, sales managers reported sales on one platform and used another for budgeting. As a result, the field budget for sales managers’ stores often differed from the number provided on the budgetary platform. Finally, sales managers could not track daily progress toward targets.

Automating Sales Manager Tasks

We devised multiple approaches to solve the sales managers’ dilemmas. We completed several proofs of concept and tracked results. We then approached each of the client’s four business problems individually.

To alleviate the need to manually create profit and loss hierarchies, we identified a better method to manage and store the data. We used Excel as a configuration model, so users could independently manage configuration changes. We then identified and selected the best scalable design. With our client’s continually expanding operations, scalability was critical. Lastly, we created scripts to automatically create the profit and loss hierarchies. The scripts automatically registered and categorized new products as needed, eliminating the risk of data discrepancies.

To ensure sales managers no longer needed to calculate costs, we created automatic cost rules. We created different cost rules for each level of the product hierarchy. To prevent duplication, we associated each product with only one cost categorization. To store the data, we selected a scalable design.

Next, we addressed the sales manager’s budgetary problems. We implemented a breakout view for budgeting and forecasting customized for each retail store. We first identified several metrics shared between the sales platform and the budgetary platform. Then, we used our refined understanding of the two platforms to determine how to split the margin between the two platforms. To formulate the  split, we created multiple algorithms. We then shared the algorithms with our client to review. Following the review, we implemented our client’s choice of algorithm, eliminating the budgetary discrepancy.

Finally, to improve daily planning, we implemented a daily breakout view for budgeting and forecasting. We first identified several metrics to measure daily split data. We used machine learning algorithms to ensure we accurately tracked the data. We then shared the algorithms with our client for review. After our client’s review, we applied the best algorithm. The completed daily breakout view enabled sales managers to better track daily progress.

Building a Data Pipeline

After we completed the data forecasting and sales projection platform, our client asked us to create a data pipeline for store analysis. We created the data pipeline to address three key problems. First, we needed to reduce overall data processing time. Slow refresh times meant sales managers didn’t receive timely reports. Without timely reports, the managers couldn’t quickly address critical customer issues. We needed to transition our client’s tabular models to a scalable solution with improved processing. Second, we needed to introduce a custom item category mapping. With our client’s existing solution, individual products were selected for every metric. As a result, the likelihood of data discrepancies was high. Third, we needed to consolidate various data points (such as revenue, inventory, and labor).

To reduce the overall data processing time, we analyzed our client’s system. We identified the scope of optimization in data staging, data warehouse processing, and cube processing. We then removed redundant metrics from Microsoft Power BI reports. After removing the redundant metrics, we formulated approaches to solve other parallel issues. We then monitored the effectiveness of the approaches. Our systematic testing reduced overall processing time significantly.

To introduce custom item category mapping, we used an Excel configuration. We expanded the configured categories at the product level. By expanding the configured categories, we began to automatically track data related to new products, eliminating the need for manual input.

To consolidate the disparate data points, we reduced the number of data sources. Staging all the data at one source ensured our client’s data pipeline was scalable even as new streams were added.

Integrated Platform Improves Store Performance

By the conclusion of our project, we had integrated a data forecasting and sales projection platform with a store analysis platform. The integrated platform automated critical sales manager activities. Sales managers now spend less time gathering insights and more time serving customers. The integrated platform greatly improved store performance. The new platform reduced refresh times, increased report reliability, automated manual tasks, and improved scalability. Sales managers now possess access to reliable data, enabling them to immediately address sales and budgetary concerns.