E-Commerce Business Analysis - Data Analysis Project

Introduction:

As a Data Geek and an aspiring Data Analyst, I have analyzed the dataset related to an e-commerce business and presented my findings.

Questions to Analyze:

To understand the E-Commerce Business, I asked the following:

1. Year on Year Growth of the business?
2. Sales & Profit?
3. State Wise Sales?
4. Category Wise Profit & Sales?
5. Top Sub - Categories by Sales?

Excel Skills Used

The following Excel skills were utilized for analysis:

• 📊 Pivot Tables
• 📈 Pivot Charts
• 💪 Power Pivot

E-Commerce Business Dataset

The dataset used for this project contains data extracted from Kaggle.com. The dataset is available on Kaggle.com, providing a foundation for analyzing data using Excel. It includes detailed information on:

• 👨‍💼 Customers
• 💰 Products
• 📍 Orders
• 🛠️ Sales

1️⃣ Year-on-Year Growth of E-Commerce Business?

🔍 Skill: Power Query (ETL)

📥 Extract

• I first used Power Query to extract the original data (Ecommerce Sales Analysis Dashboard.xlsx).

🔄 Transform

• Then, I transformed the query by changing column types, sorting the rows, reordering the columns, and trimming excess whitespace.

• 📊 E-Commerce Dataset

  

Load

• Finally, I loaded the transformed query into the workbook, setting the foundation for my subsequent analysis.

  • 📊 E-Commerce Dataset

    

📊 Analysis

💡 Insights

• 📈 There is a positive Year on Year Growth of the E-Commerce Business with Sales & Profit being increased by 20.62% & 14.41% yearly respectively.

• Although the profit margin has reduced yearly, it has not hampered the overall growth of the business.

  

🤔 So What

• This trend shows positive long-term growth for the E-Commerce Business, focus should be on increasing the profit margin along with more sales.

---

2️⃣ Overall Sales & Profit of the business?

🧮 Skills: PivotTables
📈 Pivot Table

• 🔢 I created a PivotTable using the Data Model I created with Power Pivot.
• 📊 I moved the Months (Order date) to the rows area and the Sum of Sales & Sum of Profit into the values area.

📊 Analysis

💡 Insights

• 💼 Sales & Profit Analysis depicts that the month of December has been the highest sales & profit driver, followed by November & September.

• 💰 While the months of January, February & April being the slowest.

  

🤔 So What

• These Sales & Profit insights are important for planning and helping professionals and the business to align their product distribution according to the monthly sales variations.

---

3️⃣ State Wise Sales?

🧮 Skills: PivotTables
📈 Pivot Table

• 🔢 I created a PivotTable using the Data Model I created with Power Pivot.
• 📊 I moved the (States) column to the rows area and the (Sum of Sales) column into the values area.

📊 Analysis

💡 Insights

• 💼 Analysis depicts California, New York & Texas are the Top 3 States by Sales.

• 💰 The Sales are lowest in West Virginia, Maine & South Dakota, which are only contributing only 3.8K out of total sales of 2297.20K.

  

🤔 So What

• The Company should focus more on increasing the supply of products and do more heavy marketing in the states of California, New York & Texas in order to drive more sales and profit.
• Understanding the market better in the states with the lowest sales will help the company be more competitive in the market and optimize the sales.

---

4️⃣ Category Wise Profit & Sales?

🧮 Skills: Advanced Charts (Pivot Chart)
📈 PivotChart

• I created a Waterfall PivotChart to plot Category & Grand Total from my PivotTable.
• To customize the chart, I added a title axis title, removed the gridlines, added data labels, and added colors to effectively showcase the distribution of different categories.

📊 Analysis

💡 Insights

• 💰 Technology Category is the Largest Category which has driven the Sales for the Company with its subcategory being Phones.

• The Furniture category has been least fruitful for the company as only $18.45K has been the revenue from this category.

  

🤔 So What

• This Bar & Waterfall Chart highlights that the Technology Sector has been the best-selling category and the company should enhance the focus on this category by improving the standards and marketing, so the competitive edge would be in favor of the business.

---

Conclusion

As a data enthusiast and an aspiring data analyst, I embarked on this Excel-based project to uncover valuable insights about an E-Commerce Business. Using a dataset I’ve undertaken this project from Kaggle and you can find the dataset for this project on Kaggle's website. , I analyzed the Sales, Profit, Key Performance Indicators & Year on Year Growth of the Business. By leveraging Excel features like Power Query, PivotTables, and Pivot Charts, I discovered key correlations between Sales and Profits, particularly in States and Categories in which the business sells the products.

I hope this project serves as a practical guide for data professionals and provides an overview of the Excel skills used in analyzing this dataset.