In the retail and wholesale domain, sales data is always used to help business in decision making. In addition to the sell-in and sell-out data, pricing data is also valuable to help business set a suitable product price in order to justify their cost and maximise their profit.
Our client, from the retail and wholesale domain, had difficulty handling a huge amount of product price data manually. Doing Excel calculation and works from time to time was heavy loads to business users. So, Explora provided a solution together with Microsoft BI tools to help them solve the problem.
What/How ETL and Microsoft BI tools help?
To make our client's life easier, the first thing that we needed to do was structuring the pricing data and ensuring the quality of source data. To integrate different source data into a MSSQL database, we used SSIS (SQL Server Integration Services) to extract data from Excels format (manually maintained by business users) and CSV format (generated by other Information System) and then did an appropriate data transformation so that we can keep the data in an accessible and reusable format. After that we loaded the data to a SSAS (SQL Server Analysis Services) Cube for user to access their data easily.
To help our customer get valuable insights from the pricing data, we chose Microsoft Power BI as the data visualisation tool. Instead of a sea of texts or numbers, graphs/visuals can make data more understandable and presentable. Power BI Dashboard not only helps us visualise the data but also makes users more interactive with data. Users can even predict the outcome such as projected revenue based on some pricing scenarios they simulate with the tool and data models available.
Automation in data ETL not only reduces labor cost and time cost, but it also reduces the chance of manual error in data preparation. Also, error alerting can be enabled if there are any tailor-made rules for your data.
Actually, in-house pricing data is not enough for getting an accurate insight on pricing. So, Explora also helped our customer to develop a mobile application for collecting the pricing data from the current markets and different competitors. This IOS mobile app allows business users to travel to different store locations and then collect the competitor’s records. Once users enter records through the app, the pricing data will then be sent to the application database. Data will then be transmitted to the pricing database. Eventually, we have collected in-house pricing data and also the markets/competitor’s pricing data. Therefore, our customer can do the comparative analysis so that they can set a suitable price to increase their competitiveness.
Nowadays, data becomes more and more valuable, and how to collect and transform data becomes a bigger challenge to different business. If you are interested to explore more about our service on this topic, please contact Explora and we will be glad to discuss with you about solutions that can bring value to both your IT department and your business users.
