Data Warehousing and Pricing Engine for Outdoor Equipment

Our client, a large manufacturer and distributor of outdoor equipment, wanted to establish a central platform for their data, as a basis for advanced analytics of customer data and business data. Niologic implemented a machine learning algorithm for pricing based on Google Cloud AutoML, dashboarding in Google Data Studio, and a central data warehouse using Google BigQuery.

Challenge

Our client had multiple data sources from various storage locations like eCommerce shop systems, SAP ERP, online APIs from marketing or internal systems like call centers. To improve their data analytics and open up the possibilities for machine learning solutions, our client wanted to integrate those data sources into a singular platform. Once integrated, Niologic could use this data for a combined view onto customer interaction and provide machine learning models.

Procedure

Niologic linked multiple heterogeneous data sources, including an SAP/S4 system, a VoIP business phone system, a shop system as well as all data from the client’s Google Analytics, Google Ads, and other marketing channels.

Within a following ETL process, all data was subsequently exported to Google Cloud Storage as a data lake and loaded to BigQuery using Google Cloud Composer. By using polyglot business keys, Niologic combined data records from multiple sources into a singular data lake. The data was refined into data marts both for analytics and machine learning.

Based on the derived data a pricing engine was developed within Google Clod AutoML with data from BiqQuery inputs which takes into account weather and other external data sources. Price limits are determined by internal needs and costs from the ERP system.

Results and Customer Value

After successfully linking and exporting all data sources, Niologic built a central data lake on Google Cloud Storage for long term archival, a data warehouse running on Google BigQuery, and multiple fully automated data marts and dashboards in Google Data Studio. Instead of relying on multiple platforms, our client is now able to view and analyze all data in a singular dashboard combining both sell and cost side from ERP and marketing. Data marts are exported to Google Sheets for external processes. At the same time, manual import and visualization are no longer necessary, as the dashboards update automatically.

With the data warehouse combining multiple locations, entities, and data sources, this opens up possibilities for continuous machine learning implementations like the implemented pricing engine. Moreover, to secure data from multiple subsystems containing private identifiable information (PII) for each remote third-party hosted, a secure VPN connection was set up.