1. Overview

This business case outlines the analysis of Amazon sales data to identify key performance indicators and trends that can inform strategic decisions for improving sales and marketing efforts. The data used for this analysis was sourced from the Kaggle website (https://www.kaggle.com/datasets/knightbearr/sales-product-data). However, it is important to note that this dataset is no longer available at Kaggle but is available in a Google Drive folder (https://drive.google.com/drive/folders/1Fm3AfA4goE5hccNuCFJaIH-vmrmDB3Yb?usp=sharing), and its authenticity cannot be confirmed. Given Amazon's massive sales volume, the 185,000 rows of data analyzed represent only a small fraction of the company's overall sales. They may not fully capture the scope and dynamics of Amazon's operations.

2. Overall Recommended Strategy after Data Analysis

1. Maximize Year-End Sales: Focus on marketing during the 4th quarter of 2019 and have enough inventory to take advantage of peak sales periods, such as Black Friday, Cyber Monday, Christmas, and New Year.
2. Target High-Performing Regions: Increase marketing efforts in California, especially in San Francisco and Los Angeles, leveraging a strong sales history.
3. Optimize Advertising Timing: Schedule ads during peak engagement times (18:00-20:00 and 11:00-13:00) and to maximize ROI.
4. Leverage Cross-Selling Opportunities: Enhance product bundling strategies to capitalize on frequently purchased items.
5. Analyze and Replicate Success: Investigate the success of top-selling products and apply similar tactics to other products for portfolio-wide improvement.

3. Key Objectives

The primary objective of this project is to develop an interactive Excel dashboard designed to identify the best year, month, and city for sales, optimal advertising times, top-selling products, and frequently paired items. The dashboard will include dynamic filters, enabling users to seamlessly explore detailed insights across states, products, and periods (Months).

1. Determine the Best Year for Sales

   • Identify the year with the highest sales volume to understand trends in annual performance.

2. Identify the Best Month for Sales

   • Analyze monthly sales data to pinpoint months consistently driving the highest revenue, providing insights into seasonality and promotional timing.

3. Identify the City with the Highest Sales

   • Determine which city generates the most sales, offering opportunities to focus marketing and logistical efforts in high-performing regions.

4. Find the Best Time for Advertisement

   • Analyze sales data by time of day to determine the optimal periods for running advertisements, maximizing customer engagement and conversion rates.

5. Identify Products Frequently Sold Together:

   • Analyze sales patterns to uncover product combinations often purchased together, which can inform cross-selling strategies and product bundling opportunities.

6. Determine the Top-Selling Product:

   • Identify the highest-selling product to focus inventory, marketing, and promotional efforts on items that drive the most revenue.

4. Results, Analysis and Recommendations

1. Best Year for Sales

- Analysis: The data shows that 2019 was the dominant year for sales, generating approximately $34,483,365, making 2020's $8,670 sales revenue negligible.

- Recommendation: Focus strategic planning on replicating the success of 2019. Investigate what drove the high sales and apply those strategies to future years.

2. Best Month for Sales

- Analysis: December emerged as the top month for sales, generating around $4,613,443 from 24,004 orders, representing 13.4% of annual sales. It was followed by October and November respectively, making the 4th quarter outperformed the other 3 quarters.

- Recommendation: Capitalize on year-end events such as Christmas, Black Friday, and Cyber Monday by launching targeted marketing campaigns. Increase inventory and prepare for heightened demand during this period.

3. City with the Highest Sales

- Analysis: San Francisco led with $8,259,719 in sales from 44,720 orders, making up 24% of total revenue. Los Angeles follows closely.

- Recommendation: Double down on marketing and sales efforts in San Francisco and Los Angeles. Investigate the factors contributing to their success and apply those insights to other promising markets in California.

4. Best Time for Advertisement

- Analysis: The optimal times for advertising are between 18:00-20:00 and 11:00-13:00, especially on weekends. During weekdays, sales peak on Monday & Wednesday from 11h00 to 12h00; and on Tuesday & Thursday from 18h00 to 20h00. These trends might be due to those times being dining times, so during weekdays most workers and students surf on their devices through various social media platforms, read their emails, etc.; thus, they are much more likely to see the ads.

- Recommendation: Schedule key advertising campaigns during these peak hours to maximize visibility and engagement. Adapt content to appeal to the audience's context during these times, such as offering quick deals or highlighting convenience.

5. Products Frequently Sold Together

- Analysis: The pairs Lightning Charging Cable & iPhone, and USB-C Charging Cable & Google Phone are frequently bought together, with 890 and 868 orders respectively.

- Recommendation: Implement targeted cross-selling strategies to promote these pairs. Consider bundling these products in promotions to drive additional sales and enhance customer satisfaction.

6. Top-Selling Product

- Analysis: USB-C Charging Cable and Lightning Charging Cable were the most sold units, but they are not among the top revenue generators.

- Recommendation: If cost data becomes available, analyze the contribution margin for these products to determine their profitability. Consider bundling these cables with higher-margin products they are frequently sold with to boost overall revenue. Additionally, apply the successful sales strategies of these top-selling items to other products in the portfolio.

5. Data Preparation and Cleaning

Tools Used: Excel, Power Query, Power Pivot, PivotTables, DAX (Data Analysis Expressions) and VBA (Visual Basic for Applications) .

The source data comprised monthly sales records stored in separate CSV workbooks. To streamline the data preparation process, Power Query was employed to efficiently import, combine, clean, and normalize the data. Normalization is a crucial step in data preparation as it involves organizing the data into structured tables, reducing redundancy, and improving data integrity.

5.1 Normalization Process

5.1.1. Data Segmentation

The combined dataset was divided into three distinct tables:

• FactSales: Contains all order information, with details of each transaction.

• DimProduct: Contains only product-related information.

• DimRegion: Contains only region-specific information.

5.1.2. Creation of a Calendar Table

An additional Calendar table was created to serve as a time dimension, providing a structured timeline for analysis. This table is essential for time-based calculations, such as month-over-month comparisons, and helps align data with specific periods.

5.1.3 Data Model Integration

The normalized tables were then added to the data model, allowing the use of Power Pivot to establish relationships between the tables. Creating a data model is crucial as it enables the integration and analysis of data from multiple tables seamlessly, enhancing the accuracy and efficiency of the analysis.

5.1.4 Measure Creation with DAX

Using DAX (Data Analysis Expressions) within Power Pivot, advanced calculations and measures were created, such as month-on-month sales growth, without the need for additional columns in the tables. To maintain organization, a MyMeasures table was created to store all measures in one place. This table is not connected to other tables, as its primary purpose is to organize and centralize the measures for easy reference. DAX provides a more powerful and flexible approach to data analysis compared to traditional Excel formulas. DAX can handle more complex calculations, enable more dynamic analysis, and improve performance by operating directly within the data model.