Transform Your Supply Chain Planning and Marketing Strategies with Google Cloud and SAP Integration
The client is an oral rehydration solution company, delivering efficient, medical-grade hydration solutions accessible to everyone. Their easy-to-carry hydration powder packets, loaded with essential electrolytes, ensure fast absorption and substantial fluid retention, making dehydration treatment safe and simple. Perfect for every situation – from daily hydration to travel and fitness, our client stands as a beacon of convenient, effective hydration, keeping you healthy and hydrated.
In their quest for superior market insights and sales analytics, our client grappled with significant challenges. Their existing setup, heavily reliant on spreadsheets and anchored in Netsuite ERP, proved insufficient for the real-time analysis required to gauge Buy Box and its impact on weekly sales, average price, repeat customers, and total sessions of customers.
They faced following challenges:
The client wanted to use statistical analysis to find out if there is a relationship between winning the Buy Box and weekly sales, average price, repeat customers, and total sessions of customers.
The project focused on analyzing how the Buy Box affects these metrics to gain insights that can help in making informed business decisions.
Pluto7’s approach to analyze Buy Box impact on key sales metrics was to combine the sales, promotions, spending datasets through a common identifier/column. However, there were a few challenges with Spends and Promotions datasets:
Challenge: Spends data was all grouped by Campaign, and each Campaign had more than one item (SKU) in it.
Workaround: To deal with this, we split the total Spend by the number of items in each Campaign. This could add some mistakes in our Exploratory data analysis, but it’s the best that could be done with available data sources.
Challenge: The Promotions data was organized by Date and Promotion Name, with several items (SKUs) under one Promotion.
Workaround: We solved this by segregating the data at SKU level by mapping to Promotion at Number of Promotion per Week at SKU level. This helped us look at the data in more detail.
Pluto7 devised a comprehensive solution, paving the way for streamlined and enhanced sales analytics. This is the roadmap we followed:
We began by moving the customer’s data into the GCS bucket, ensuring a smooth and secure transition. This data then seamlessly flowed into BigQuery via Cloud Data Fusion, laying a solid foundation for further analysis and insight extraction.
We then delved into preprocessing and analyzing the data. This step was crucial in unveiling patterns and insights that would further drive our strategies.
The data was then loaded to BigQuery, where it underwent integrity checks and schema validation. This facilitated the creation of master tables, paving the way for upcoming machine learning initiatives. Data was organized and refined to unveil efficient querying, advanced insights and power sophisticated machine learning models.
With the data thoroughly analyzed and structured, it was time to make it visually articulate. We harnessed the power of Looker to craft intuitive and insightful dashboards for sales analytics. This visualization brought clarity and made the data easily navigable, allowing for efficient and informed decision-making.
Exploration Empowered with Centralized Data: Our custom-designed data models successfully centralized vital information, enhancing exploratory data analysis and visualization. This foundational achievement paved the way for a more profound and clearer insight into complex data, promoting an enriched understanding and informed decision-making.
Effortless Data Visualization Achieved with Looker Studio: With Looker Studio, we realized seamless, scalable, and automated data visualization. This advancement streamlined and expedited the analytical processes, offering efficient insights and reinforcing our data-driven approach.
Critical Insights Unveiled with In-Depth Analysis: Our thorough analysis successfully brought to light critical insights into each SKU’s Buy Box Performance against spends, promotions, and featured offers. This unveiled trends for Featured Offers/ Buy Box against Total Order, Avg Order, Spend & Repeat Customers, enhancing our strategic planning and bolstering confident and data-backed decisions.
With the robust data foundation and analytical prowess, we not only transformed client’s sales analytics but also unlocked valuable insights that are set to guide their future e-commerce decisions with precision and confidence.