Optimising E-commerce with Data: BI Transformation for a Leading European E-commerce Aggregator

Details of the business intelligence solution for the European e-commerce aggregator

Client Overview

Crux Digits collaborated with one of the largest aggregators of e-commerce in Europe to deliver a high-scalable business intelligence solution for it. The end client was working on multiple platforms, such as Amazon, Bol.com, Netsuite, etc. They required an integrated system that would aggregate diversified data sources for informed decision-making through real-time actionable insights on areas like sales performance and efficiency, advertisement effectiveness, and inventory management.

Key Challenges

Data Silos: The client had information fragmentation across platforms like Amazon, Bol.com, and Netsuite that made it hard to come up with a holistic view of business performance.

Campaign Performance Tracking: There was no effective means to monitor or optimise advertising campaigns across different channels of e-commerce.

Manual Reporting: The reliance on outdated, manual reporting methods caused significant delays, especially in areas such as inventory management and campaign performance analysis, hindering the client's ability to respond swiftly and make informed business decisions.

Solution

Crux Digits developed a cloud-native BI solution built on top of Medallion Architecture framework with Azure services and Databricks. It helped in bringing together data from all sources and integrating the BOL Retailer and Advertising APIs to help optimise the client's advertising and SEO performance.

Solution Architecture and Data Modeling

We adopted a three-tier Medallion Architecture (Bronze, Silver, Gold) so that we could easily manage data transformation and analysis.

  • Bronze Layer: This layer is the raw data ingestion layer that encompasses data from sources such as Amazon, Bol.com, and Netsuite. It possessed sales data, order history, advertising expenditure, and product return. This data was further stored in Azure Blob Storage for purposes of processing.
  • Silver Layer: Here, we clean, transform, and standardise all our data in Azure Synapse Analytics. This, in fact, helps achieve data consistency across platforms for smooth and error-free reporting.
  • Gold Layer: We aggregated and enriched the clean data using Databricks to create a star schema that allowed easy and intuitive reporting of key business metrics in terms of sales performance, inventory status, and advertisement effectiveness.

Integration of BOL Retailer and Advertising APIs

  • Monitor Sales and Product Details: Real-time tracking of sales performance on Bol.com and specific product data helped in optimising the product listings and managing the stock.
  • Campaign Performance Tracking: We assisted in tracking campaigns across Bol.com by monitoring advertising metrics and analysing their performance to give a proper assessment of campaign effectiveness.
  • SEO and Keyword Analysis: Keyword performance analysis identified the best-performing keywords for the client and its competitor strategies, hence increasing product visibility through improved listings and strategic optimization of SEO actions toward achieving higher conversion rates.

Automated ETL Process

The entire data pipeline, including the ETL process, was automated using Azure Functions. Manual intervention was removed, and data can flow gracefully through all layers: Bronze, Silver, and Gold, for stakeholders to gain up-to-date real-time insights to make decisions faster.

Data Automation for SEO Optimisation

With the BOL Advertising API, the client could view the performance of advertising and keywords in real-time. This resulted in continuous optimization of their SEO strategies for better rankings of their products in search and also optimization of advertising spend for better ROI.

Visualisation with Power BI

We built a dynamic Power BI dashboard to present these insights in an accessible format, displaying real-time sales, advertising, and inventory data in accessible, interactive visualisations that would empower stakeholders to drill down into any particular categories, campaigns, or marketplace metrics, providing them with necessary information to make quick decisions.

Tools and Technologies Used

  • Azure Blob Storage: For raw data ingestion.
  • Azure Synapse Analytics: To clean, transform, and standardise data at scale.
  • Azure Functions: For automating ETL pipelines and ensuring real-time data processing.
  • Databricks: To handle scalable data transformations and manage the Gold Layer.
  • Power BI: For interactive, real-time dashboards and data visualisation.
  • BOL Retailer and Advertising API: To analyse SEO and advertising performance.
  • Amazon Rainforest API and Netsuite: For integrating and managing sales, inventory, and operations data.
  • SQL Server: Used during the transformation process for data management.

Results Achieved

  • Unified Data Access: We aggregated the data collected from different e-commerce platforms of our clients into a single integrated system. This provided the client with an all-inclusive view across sales, inventory, and advertising metrics.
  • Real-Time Analytics: Automating the flow of data into the system yielded near-time updated results. The speed at which decisions were made and the resultant response time of the business was significantly increased.
  • SEO Optimization: Through optimization of SEO parameters using real-time data, the client was able to optimise product listing, which increased the amount of traffic for higher sales.
  • Scalability for Future Growth: Built on Azure and Databricks, the solution is scalable and can easily incorporate additional data sources as the client’s business continues to expand.

Conclusion

Through the integration of Azure services, Databricks, and the BOL Retailer and Advertising API, Crux Digits successfully delivered a scalable, cloud-based BI solution. This solution not only provided real-time, actionable insights into the client’s e-commerce operations but also enabled significant improvements in SEO and advertising strategy. By automating key business processes, the client gained a competitive advantage, leading to better product visibility, increased sales, and sustained business growth in an ever-evolving e-commerce landscape.