Enhancing Sales Performance and Operational Efficiency in a Superstore Using AWS Athena and QuickSight

Project Overview

This project aims to analyze and improve a superstore's sales performance and operational efficiency using AWS Athena for data querying and AWS QuickSight for visualization. The comprehensive analysis covers various business aspects, including customer segmentation, shipping logistics, discount impacts, and overall sales performance.

Objectives

• Analyzing customer segmentation and behavior
• Optimizing shipping and logistics efficiency
• Assessing the impact of discounts on revenue and profit
• Evaluating sales performance trends and profitability

Tools and Technologies

• AWS Athena
• AWS QuickSight

Key Findings

1. Identified top-performing customer segments and states: The Consumer segment contributes the largest share of sales, followed by the Corporate and Home Office segments. The Home office is smaller but has the highest average.
2. Top-Performing States: California and New York are identified as Top-performing states in terms of sales.
3. Purchasing Frequency: The Consumer and Corporate segments show higher purchase frequencies than the Home Office segment, indicating frequent buying behavior.
4. Shipping Mode Preferences: Standard class dominates sales, but there are opportunities to promote second—and first-class shipping options to improve profitability.
5. Discount Impact: Both revenue after discount and profit decline sharply as discount rates increase beyond 0.2, with profit margins turning negative beyond a 0.3 discount rate.
6. Seasonal and Yearly Trends: Sales show significant monthly fluctuations due to several trends and promotional efforts, with a consistent upward growth trend observed from 2014 to 2017.
7. Sub-Category Sales and Profitability: Binders and Paper are the top-selling sub-categories, with Copiers and Paper being the most profitable. However, products like the 'Lexmark MX611dhe Monochrome Laser Printer' and the 'Cubify Cubex 3D Printer Double Head Print' incurred significant losses.

Recommendations

1. Individualized Marketing Strategies:

   • Direct advertising campaigns are geared towards the Consumer segment, as it is the most significant contributor to sales.
   • Developing focused campaigns for the Corporate and Home Office segments by examining their purchasing behavior.
   • Implementing loyalty programs for the Consumer and Corporate segments can be a great way for motivating customers to make repeat purchases.

2. Exciting Promotions:

   • To optimize revenue from these valuable customers, provide specific offers to the Home Office segment, known for its significant average order value.

3. Shipping Optimization:

   • Encouraging to take advantage of Second and First-Class shipping options to improve profitability while maintaining customer satisfaction.
   • Reviewing and improving the cost structure of Standard Class shipping can greatly improve overall shipping efficiency.

4. Discount Management:

   • Discount rates exceeding 0.2 should be avoided to avoid significant decreases in revenue and profit.
   • Setting a maximum discount rate of 0.3 is important to preserve positive profit margins and secure the company's financial well-being.

5. Strategic Promotions:

   • Carefully planning promotions and marketing campaigns to take advantage of seasonal trends and maximize sales during peak periods.
   • Highlighting the market expansion strategies contributing to steady business growth between 2014 and 2017.

6. Review of Product Portfolio:

   • Considering reassessing the viability of unprofitable products such as the "Lexmark MX611dhe Monochrome Laser Printer" and "Cubify Cubex 3D Printer Double Head Print" to decide whether they should be discontinued or their prices adjusted.
   • To boost overall profitability, focusing on investing in and promoting the most profitable sub-categories, such as "Copiers" and "Paper."

Conclusion

The project delivered practical recommendations for focused marketing, strategic pricing, and improved operational choices. By using AWS Athena for data querying and AWS QuickSight for visualization, the superstore can gain valuable insights into customer behavior, enhance shipping logistics, efficiently manage discount strategies, and ultimately achieve improved business outcomes.

Author

Lasya Priya Konduru