How a Multi-channel online seller consolidated its diverse data into easily accessible reports

  Nov 06, 2020 13:36:00  |    Joseph C V   Retail analytics, Data analytics, Retail, Case Study

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.



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.



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
  • Summary report with sales, returns, expense, and profit
  • Month wise complete year’s data
  • Covers all the geographies
  • A clear division between Amazon and eBay expenses and revenues
  • Details to category and subcategory level of items
  • Day-wise trends
  • Sales trends by month, quarter, year, country, channel, and product
  • Profit and loss reports
  • Scope of process and finance optimization in marketing, inventory, and delivery
  • Expense summary on advertising, keywords, coupons, return, delivery, and disposal
  • Gross margin and sales value per category divided over regions and months
  • Analysis of hidden costs in platform promotion and warehouse stocks
  • Profit and loss reports
  • Cash flow details
  • Expenditure on packing, delivery, promotion, ad-sense, and other charges per platform
  • Potential areas to save the expenditure
  • Possible optimization in return and delivery charges
  • Stock disposal with current year over last year
  • Shipment reconciliation report
  • On hand, stock out, low stock data
  • Monthly stock trends per country
  • Sales, returns, and turn-around time by fulfillment centers
  • Warehouse performance analysis



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



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.