Data-Driven Sales Analytics: Transformation Journey of an E-Commerce Business with Planning in a Box and Google Cloud Platform

Customer Overview

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.

Business Challenges

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:

• Inefficient Data Analysis: The use of spreadsheets for performing complex sales analytics severely hindered the efficiency and depth of the analysis, making it cumbersome to derive actionable insights.
• Limited Insight into Buy Box Impact: Without a dedicated data platform, understanding the intricate relationship between Buy Box and various parameters such as weekly sales, average price, repeat customers, and total sessions remained obscured. This limitation curtailed their ability to make informed strategic decisions.
• Difficulty in Understanding Customer Behavior: The absence of an advanced analytics dashboard made it challenging to analyze and interpret the complex customer behavior patterns effectively, resulting in missed opportunities for sales enhancement.
• Constrained Visualization Capacities: The inability to visualize data comprehensively hampered the clear and concise interpretation of sales data, making it impossible to ascertain the Buy Box impact.

The Project’s Goal: Unveiling the Correlation Between Buy Box & Key Sales Metrics for Enhanced Business Insights

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.

• Weekly Sales: Analyze the trends and patterns of weekly sales in relation to the Buy Box presence, to assist in more effective sales strategies.
• Average Price: Analyze how the Buy Box presence relates to the average price of items sold, to help optimize pricing for increased sales
• Repeat Customers: Examine the rate of repeat customers when products are listed in the Buy Box versus when they are not, to understand how it aids in enhancing customer loyalty and satisfaction
• Total Session of Customers: Assess the number of customer sessions in relation to the Buy Box status, to get insights into customer engagement and interest levels

Addressing Data Challenges: Preparing for Data Centralization

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:

Spends Data

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.

Promotions Data

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’s Solution Approach: Robust Data Foundation for Advanced Sales Analytics and Visualization 

Pluto7 devised a comprehensive solution, paving the way for streamlined and enhanced sales analytics. This is the roadmap we followed:

Data Foundation

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.

Data Analysis

We then delved into preprocessing and analyzing the data. This step was crucial in unveiling patterns and insights that would further drive our strategies.

Data Warehouse using BigQuery

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.

Data Visualization Dashboards

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.

Key Results

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.