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
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
formulize 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 Power BI reports. After removing the redundant metrics, we formulized 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.
