Challenge:
Retan Convenience Latvia manages the Narvesen brand, a network of 275 retail stores across Latvia offering coffee, fresh food, snacks, and magazines. With a high store count and diverse product range, the data generated by this network was rapidly increasing, posing challenges for effective analysis. The primary questions were, “What are we selling?”, “How much are we selling?”, “What is our profit?”, and “Are we improving day by day?”
To drive better sales performance, Retan wanted to set and monitor sales targets for each store. This required a system that would allow individual store managers to access real-time reports relevant to their own stores, necessitating a sophisticated data access management solution.
Journey
To address the data challenges, we developed several SQL import applications to aggregate data at different levels, ensuring only essential data was imported for analysis. This resulted in a large OLAP data cube containing over 110 million rows of transactional data.
To optimize report execution on this massive dataset, we implemented an additional database powered by ClickHouse—a fast, open-source, column-oriented database management system. This setup enabled real-time analytics by handling large-scale SQL queries efficiently, drastically improving report load times.
For management, we created detailed sales dynamics reports showing sales metrics by store and product, along with receipt averages. Additionally, target monitoring reports were developed for both top management and store-level managers. To simplify data access and ensure security, a custom solution was created to manage data access based on each user’s email address, automating role assignment and eliminating the need for manual configuration.
Result
The result is a custom analytics solution that balances data aggregation, optimized storage, and dynamic access control.The system provides store and product-level sales analytics, comparative performance analysis over time, and target monitoring at both the corporate and store levels. This solution allows top management to gain a comprehensive view of the network’s performance, while enabling each of the 275 store managers to track their goals and monitor progress effectively.
