Strategic Analysis of Grocery Retail Performance

Introduction

The grocery retail industry is facing complex challenges in harmonizing customer data across various touchpoints, including physical stores, e-commerce platforms, and loyalty programs. These challenges hinder the optimization of operations and the delivery of a personalized shopping experience. Fragmented customer data, seasonal demand fluctuations, and intense competition often result in missed sales opportunities, inefficient marketing efforts, inventory imbalances, and higher customer churn. This case study focuses on addressing these issues by developing a unified data pipeline and analytics solution for a mid-sized grocery chain. By consolidating data into a centralized model, the goal is to enhance operational efficiency, improve customer engagement, optimize marketing strategies, and drive revenue growth through informed decision-making.

Objectives

1) Improve customer lifetime value (CLV) by targeting high-value customers and reducing churn through personalized engagements.

2) Streamline inventory management by refining demand forecasting and tracking fulfillment rates, while optimizing stock levels and turnover ratios.

3) Assess the ROI of marketing campaigns and identify the most profitable channels and promotions to maximize marketing spend.

4) Understand sales trends across seasons and adjust marketing spend and inventory levels accordingly to drive higher sales.

5) Utilize actionable insights to support growth strategies, including effective cross-selling and upselling opportunities, along with tailored promotions.

Key Analysis Questions

1) Which customer segments contribute the most to revenue, and how can they be targeted to improve customer retention?

2) How effective is the current inventory management system across stores, and where can improvements be made in stock management and demand forecasting?

3) Which marketing campaigns and channels provide the highest return on investment, and how can marketing strategies be optimized for better engagement?

4) What are the key drivers of seasonal sales trends, and how can marketing and inventory strategies be adjusted to capitalize on these patterns?

5) Which product categories generate the highest revenue, and how can inventory levels be optimized to meet customer demand more effectively?

6) How can high-value customers be identified and engaged to maximize their lifetime value and increase overall business profitability?

Data Transformation

To enable better analysis and to provide more actionable insights, various data transformation and update steps were performed. These steps focused on cleaning, enriching, and structuring the data for efficient querying and analysis. Below are the transformations performed on key tables like customer feedback, inventory tracking, historical forecasts, and stores.

SQL Queries for Data Transformation:

1) Cleaning Customer Feedback Ratings

1  UPDATE customer_feedback
2  SET RATING = REGEXP_REPLACE (RATING, '[^0-9]', '');

Purpose: Cleanses the customer feedback ratings by removing non-numeric characters, ensuring that ratings are stored in a consistent numeric format for analysis.

2) Updating Inventory Tracking Information

1  UPDATE INVENTORY_TRACKING
2  SET 
3      REORDER_STATUS = CASE 
4          WHEN STOCK_LEVEL < REORDER_LEVEL THEN 'Reorder Needed'
5          ELSE 'Stock Sufficient'
6      END,
7      STOCK_DIFF = STOCK_LEVEL - REORDER_LEVEL,
8      REORDER_QUANTITY = CASE 
9          WHEN STOCK_LEVEL < REORDER_LEVEL THEN REORDER_LEVEL - STOCK_LEVEL
10          ELSE 0
11      END;

Purpose: Adds new columns for reorder status, stock difference, and reorder quantity, helping track inventory levels and stock management. The data is transformed to reflect the current stock status, assisting in inventory decision-making.

3) Calculating Percentage Change and Forecast Accuracy

1  UPDATE HISTORICAL_FORECASTS
2  SET 
3      PERCENTAGE_CHANGE = ROUND(
4          ((ACTUAL_SALES_QUANTITY - FORECASTED_SALES_QUANTITY)::NUMERIC / FORECASTED_SALES_QUANTITY) * 100, 2
5      ),
6      FORECAST_ACCURACY = ROUND(
7          100 - ABS(
8              ((ACTUAL_SALES_QUANTITY - FORECASTED_SALES_QUANTITY)::NUMERIC / FORECASTED_SALES_QUANTITY) * 100
9          ), 2
10      );

Purpose: Calculates the percentage change between actual and forecasted sales quantities and determines forecast accuracy. This analysis is crucial for evaluating forecast performance and improving future projections.

4) Updating State Information for Stores

1  UPDATE STORES
2  SET STATE = CASE 
3      WHEN CITY = 'Thane' THEN 'Maharashtra'
4      WHEN CITY = 'Noida' THEN 'Uttar Pradesh'
5      WHEN CITY = 'Mumbai' THEN 'Maharashtra'
6      WHEN CITY = 'Lucknow' THEN 'Uttar Pradesh'
7      WHEN CITY = 'Kanpur' THEN 'Uttar Pradesh'
8      WHEN CITY = 'Gwalior' THEN 'Madhya Pradesh'
9      WHEN CITY = 'Bengaluru' THEN 'Karnataka'
10      WHEN CITY = 'Ajmer' THEN 'Rajasthan'
11      ELSE 'Unknown'
12  END;

Purpose: Adds state information for each store based on its city. This enables location-based insights and helps in analyzing regional trends across stores.

Merging Data Tables

To facilitate the analysis and ensure a cohesive understanding of sales and marketing performance, the data from various sources were consolidated into a unified table. This preparation step enables efficient querying and comparison across different datasets, including sales transactions, promotions, campaigns, marketing channels, payment methods, and campaign linkages.

SQL Query for Merging Data Tables:

1  DROP TABLE IF EXISTS _sales_marketing_perforance;
2  create table _sales_marketing_perforance as
3  select distinct
4      st.TRANSACTION_ID, st.customer_id, st.product_id, st.store_id,  st.TRANSACTION_DATE, st.QUANTITY as SALES_QUANTITY, 
5      st.UNIT_PRICE AS SALES_UNIT_PRICE, st.TOTAL_AMOUNT AS SALES_TOTAL_AMOUNT,
6      pp.start_date as Promotion_start_date, pp.end_date as Promotion_end_date, pp.regular_price, pp.promotional_price, 
7      pp.units_sold as promotion_units_sold, pp.revenue_generated as promotion_revenue_generated, pp.promotion_cost, pp.roi as promotion_roi,
8      c.campaign_name, c.campaign_id,  c.start_date as campaign_start_date, c.end_date as campaign_end_date, c.budget as campaign_budget, 
9      c.target_audience as campaign_target_audience, c.success_metrics as campaign_success_metrics,
10      mc.MARKETING_CHANNEL_ID, mc.CHANNEL_NAME as marketing_channel_name, mc.COST_PER_ENGAGEMENT,
11      pt.promotion_type_id, pt.promotion_type_name, pt.description as promotion_description, pt.minimum_purchase_required, 
12      pt.maximum_discount_value,
13      pm.payment_method_id, pm.payment_type,
14      ct.channel_type_id, ct.channel_type_name,
15      cpl.revenue_attributed as campaign_revenue_attributed,
16  from SALES_TRANSACTIONS st
17  join PROMOTION_PERFORMANCE pp
18  on  pp.product_id=st.product_id and pp.channel_type_id=st.channel_type_id
19  join CAMPAIGNS c
20  on c.campaign_id= pp.campaign_id
21  join MARKETING_CHANNELS mc
22  on mc.MARKETING_CHANNEL_ID=c.MARKETING_CHANNEL_ID
23  join PROMOTION_TYPES pt
24  on pt.promotion_type_id=c.promotion_type_id
25  join payment_methods pm
26  on pm.payment_method_id=st.payment_method_id
27  join channel_types ct
28  on ct.channel_type_id=st.channel_type_id and ct.channel_type_id=pp.channel_type_id
29  join CAMPAIGN_PRODUCT_LINK cpl
30  on cpl.campaign_id=c.campaign_id

Purpose: The table consolidates sales, promotions, campaigns, and marketing channel data to analyze performance, evaluate ROI, and track the impact of marketing efforts on sales.

Data Analyses and Insights

Key Performance Indicators (KPIs)

Total Sales Revenue

1  SELECT SUM(SALES_TOTAL_AMOUNT) AS total_sales_revenue 
2  FROM _SALES_MARKETING_PERFORANCE;

Insight: This KPI measures the total revenue generated from all sales transactions, providing an overall financial picture of the business's performance.

Average Feedback Rating

1  SELECT ROUND(AVG(FEEDBACK_RATINGS), 2) AS average_feedback_rating 
2  FROM _CUSTOMER_INSIGHTS;

Insight: This KPI calculates the average customer feedback rating, helping to assess overall customer satisfaction and identify areas for improvement.

Overall Inventory Stock Level

1  SELECT SUM(STOCK_LEVEL) AS total_stock_level 
2  FROM _INVENTORY_DEMAND_FORECAST;

Insight: This KPI measures the total stock level across all products, aiding in inventory management and ensuring optimal stock availability.

Average Time Spent per Customer

1  SELECT ROUND(AVG(TIME_SPENT), 2) AS average_time_spent_per_customer 
2  FROM _CUSTOMER_INSIGHTS;

Insight: This metric calculates the average time customers spend interacting with the business, providing insights into customer engagement and service effectiveness.

Detailed Analyses

Customer Lifetime Value (CLV) Calculation

1  SELECT ci.CUSTOMER_NAME, SUM(sm.SALES_TOTAL_AMOUNT) AS LIFETIME_VALUE
2  FROM CUSTOMERINSIGHTS ci
3  JOIN SALESMARKETING_PERFORANCE sm ON ci.CUSTOMER_ID = sm.CUSTOMER_ID
4  GROUP BY ci.CUSTOMER_NAME 
5  ORDER BY LIFETIME_VALUE DESC 
6  LIMIT 10;

Insight: This analysis identifies the top 10 customers contributing the most revenue, helping prioritize high-value customer segments for loyalty and retention strategies.

Customer Engagement by Channel

1  SELECT ci.ENGAGEMENT_CHANNEL, COUNT(DISTINCT ci.CUSTOMER_ID) AS ENGAGED_CUSTOMERS, AVG(ci.TIME_SPENT) AS AVG_TIME_SPENT 
2  FROM CUSTOMERINSIGHTS ci
3  GROUP BY ci.ENGAGEMENT_CHANNEL 
4  ORDER BY AVG_TIME_SPENT DESC;

Insight: This analysis evaluates customer engagement across channels, highlighting the most effective channels for targeting and optimizing marketing strategies.

Campaign ROI Analysis

1  SELECT CAMPAIGN_NAME, SUM(PROMOTION_REVENUE_GENERATED) AS REVENUE, 
2      SUM(PROMOTION_COST) AS COST, SUM(PROMOTION_REVENUE_GENERATED) - SUM(PROMOTION_COST) AS NET_ROI 
3  FROM SALESMARKETING_PERFORANCE 
4  GROUP BY CAMPAIGN_NAME 
5  ORDER BY NET_ROI DESC;

Insight: This analysis identifies the most profitable campaigns by calculating the ROI, helping to optimize future marketing investments.

Loyalty Program Effectiveness

1  SELECT PROGRAM_NAME, COUNT(DISTINCT CUSTOMER_ID) AS TOTAL_MEMBERS, 
2      SUM(LOYALTY_POINTS_EARNED) AS TOTAL_POINTS_EARNED, SUM(LOYALTY_POINTS_REDEEMED) AS TOTAL_POINTS_REDEEMED 
3  FROM CUSTOMERINSIGHTS 
4  GROUP BY PROGRAM_NAME 
5  ORDER BY TOTAL_POINTS_EARNED DESC;

Insight: This metric evaluates the effectiveness of loyalty programs by tracking participation, points earned, and redeemed, indicating the program's impact on customer loyalty and engagement.

Most Popular Products

1  SELECT P.PRODUCT_NAME, SUM(S.SALES_QUANTITY) AS TOTAL_SALES 
2  FROM SALESMARKETING_PERFORANCE S
3  JOIN INVENTORYDEMAND_FORECAST P ON S.PRODUCT_ID = P.PRODUCT_ID 
4  GROUP BY P.PRODUCT_NAME 
5  ORDER BY TOTAL_SALES DESC 
6  LIMIT 10;

Insight: This analysis identifies the top 10 products with the highest sales, offering insights into popular products that drive business performance and inform inventory decisions.

Store-Wise Sales and Inventory Analysis

1  SELECT I.STORE_NAME, SUM(I.STOCK_LEVEL) AS SUM_STOCK_LEVEL 
2  FROM SALESMARKETING_PERFORANCE S
3  JOIN INVENTORYDEMAND_FORECAST I ON S.STORE_ID = I.STORE_ID 
4  GROUP BY I.STORE_NAME;

Insight: This analysis helps assess inventory management and sales performance across store locations, guiding operational efficiency and stock optimization.

Insights

• Overall Business Performance:
• Total sales revenue: $335.57MAverage feedback rating: 3.44 (indicating room for improvement)Total stock level: 53,403 (ensuring supply stability)Average customer time spent: 71.36 minutes (strong in-store engagement)
• Top Products, Markets & Engagement:
• Best-selling product: 'Libero' (75,240 units)Top market: Pune ($49.62M in sales)Leading feedback topics: 'Service' (324) and 'Product Quality' (267)Highest engagement channel: Text Messages (80.6 min avg. engagement time)
• Marketing & Customer Value:
• Most profitable campaign: 'End of Season' ($179.47M ROI)Most engaging campaign: 'Search Engine Marketing' (71 engaged customers)Highest lifetime value customer: Ali Armstrong ($71.94M LTV)Highest loyalty balance: Floyd Lowery (949.91 points)
• Category Revenue, Demographics & Inventory:
• Highest revenue-generating category: Mitzie Keller ($32.91M revenue)Dominant customer demographic: 18-35 age group (799 customers)Preferred payment method: Digital Wallets (2,779 transactions)Low-stock item: IKEA Wardrobes Comfort (15 units, -35 deficit)Best inventory management: Khalilah Neal (61,152 units in stock)
• Loyalty, Promotions & Fulfillment:
• Customer with highest total time spent: Jonelle Jensen (1,320 min)Highest loyalty balance: Floyd Lowery (949.91 points)Best-performing promotion: Discount Promotion (481,358 units sold, $337.41M revenue)Top fulfillment status: 'Out for Delivery' (281 orders)Loyalty memberships: Evenly distributed across genders

Conclusions

This analysis of the grocery retail industry provides a comprehensive framework for addressing the key challenges faced by businesses in optimizing customer experience, operational efficiency, and marketing effectiveness. By integrating data from various sources, this project has successfully identified critical insights that will allow the grocery chain to streamline inventory management, enhance customer engagement, and improve the ROI of marketing campaigns.

The findings highlight the importance of understanding customer behavior, optimizing product offerings, and using targeted strategies to boost loyalty and retention. Additionally, by leveraging seasonal trends and regional sales data, the business can better align its inventory and promotional strategies to maximize sales and minimize stock imbalances.

Overall, the implementation of a unified data pipeline and advanced analytics solutions will empower the business to make data-driven decisions that drive growth, improve profitability, and ensure a personalized shopping experience for customers. This approach will ultimately contribute to long-term success and a competitive advantage in the rapidly evolving grocery retail market.