About the Company
The client is an Indian retail company that manufactures and sells a diverse range of quality home décor and furnishing products. They also tread into accessorizing your abode with beautiful, affordable, and durable kitchenware, dining artifacts, and ethnic clothing.
Having their business rooted in India, this client operates in 9 countries, including the US, Canada, Australia, and several European countries. They sell their products globally via Amazon and eBay apart from local eCommerce platforms in India.
Challenges
Their business data was flowing in from multiple web pages with one site dedicated to each country on Amazon and eBay. Also, the 2 separate eCommerce platforms had their own data. This threw various challenges in terms of consolidating the data across different sources.
Diverse Data Channels
Amazon and eBay provide crude monthly data to the registered sellers. They also offer customized reports at an additional cost. Even if the client’s team wanted to utilize these reports, they had challenges consolidating disparate data across various platforms and geographies. Also, different internal systems pumped in more data.
Manual Intervention
The team used to sort and massage the data manually using PHP scripts to dump the results into a database. This process was time-taking, manual, and error-prone.
Varied Data Granularity
Inputs from the various systems were not at the same grain. That made it challenging to consolidate the numbers to visualize comprehensive business health. Moreover, effective reporting was complicated on incompatible datasets even after applying a useful tool like Tableau.
Missing Revenue’s Single View
Getting a comprehensive view of profit and loss (P&L) statements was not possible for management.
Amazon levies 30+ varieties of charges on the sellers. Additionally, other expenses on inventory management and bank charges piled up the costs. But understanding the scope to save and optimize overheads was difficult.
Solution
Logesys team did a thorough analysis of all the data sources. We defined the granularity of the datasets, that made data consolidation possible across the channels. This offered a holistic business picture.
After pulling data from all the channels across the geographies, we merged Amazon, eBay, and other systems' data. The data was cleansed to get rid of junk and duplicate values using the SQL scripts. Once massaged, data was consolidated into MS SQL Server, which served as a single input for the reporting layer.
We used Power BI to create dashboards and visualizations that helped the client’s team better understand their business. The dashboards provided a way to monitor the crucial KPIs related to finance, operations, stock keeping, and delivery.
Technology Stack and Time Lines
Microsoft Power BI
Microsoft SQL Server
The project took a quarter and a few weeks right from understanding the requirements to designing, developing, testing, and going live. Since in production, the system is proving to be highly beneficial.
The solution offered the following statistics through the reports customizable at run time by a layman:
| Executive Board | Finance Team | Inventory Team |
|
|
|
Benefits
We created various dashboards that benefitted the executive board, finance team, and inventory management team. These are some of the crucial benefits:
Common benefits of the solution
- Automated reports
- Highly visual with maps, charts, and color-coding
- Available online
- Accessible via Mobile
- Tight data security
- Daily auto-refresh
- Round the clock support by Logesys team
Unveiling the Hidden Data
The solution merged data from all the countries across the channels and for multiple years. While the inventory reports showed the hidden cost spent in the transit and return along with damaged goods, the facts also unveiled the scope to optimize inventory rotation between Amazon’s fulfillment center and the company’s warehouses.
Reports on product performance uncover the reasons for product cancelation or return like late delivery, damage, wrong shipment, etc.
Finance reports paved a route to go to the depth of every expense in advertising and promoting a product on the eCommerce platforms. Keyword performance monitoring and related expense helps to find what was not working despite massive expenditure.
With detailed P&L statements, the company's management could infer the surprise gains and unexpected losses. This, in turn, helped to take the right actions to promote the growth and curb the loss. The company could analyze various hidden fees and expenditures like import duty and bank charges
Results
The project proved to be highly effective. Logesys team completed the project in a short span of 3.5 months with rigorous testing.
This resulted in the accessibility of the reports right at the fingertips of the various teams. Mobile accessibility of these insights made it possible for the teams to check the data on the go.
