Hwang's Journey

Contact

Victoria Solution – the Consulting Group’s Intern

Week 1 project

Context and Scenario:

As a Data Analyst for Shop Ease, an online retail company, the objective is to enhance sales performance and customer satisfaction through comprehensive analysis of sales data.

1. Cleaning Dataset:

1.1. Initial Inspection and Data Cleanup:

  • The dataset was manually uploaded to Excel, and its schema, columns, and rows were meticulously inspected. An empty cell was identified at H2.
  • The entire dataset underwent data cleanup, involving the removal of duplicate entries and the trimming of white spaces.

1.2. Data Type Formatting:

  • The “total_amount” column, identified as containing manual input, was formatted to ensure all cells were numeric rather than string type.
  • The “Date” column was formatted to display as “Friday, May 18, 2024.”

1.3. Calculation:

  • The empty cell H2 was populated with the formula “=F1*G1” to calculate the total amount (quantity x unit price). This formula was subsequently applied to the remainder of the column.

1.4. Export: The cleaned dataset was exported as a CSV file.

1.5 Cleaned dataset sample:

Transaction_IDDateCustomer_IDProductCategoryQuantityPriceTotal_AmountPayment_MethodRegion
1001Friday, January 5, 2024C001LaptopElectronics1800800Credit CardNorth
1002Wednesday, January 10, 2024C002SmartphoneElectronics26001200CashSouth
1003Friday, January 12, 2024C003HeadphonesElectronics1100100PayPalWest
1004Monday, February 5, 2024C004TabletElectronics1500500Debit CardEast
1005Thursday, February 8, 2024C005BookBooks32060Credit CardNorth
1006Saturday, February 10, 2024C001LaptopElectronics1800800Credit CardNorth
1007Friday, March 15, 2024C006ShoesClothing250100CashSouth
1008Monday, March 18, 2024C007T-ShirtClothing12525PayPalWest
1009Wednesday, March 20, 2024C008SmartwatchElectronics1200200Debit CardEast
1010Monday, April 1, 2024C009BookBooks22040Credit CardNorth
1011Friday, April 5, 2024C002SmartphoneElectronics26001200CashSouth
1012Wednesday, April 10, 2024C010TabletElectronics1500500Debit CardEast
1013Wednesday, May 1, 2024C011ShoesClothing15050CashSouth
1014Sunday, May 5, 2024C012HeadphonesElectronics1100100PayPalWest
1015Wednesday, May 8, 2024C013LaptopElectronics1800800Credit CardNorth
1016Friday, May 10, 2024C014T-ShirtClothing32575CashSouth
1017Saturday, June 1, 2024C015SmartwatchElectronics1200200Debit CardEast
1018Wednesday, June 5, 2024C016BookBooks42080Credit CardNorth
1019Saturday, June 8, 2024C017SmartphoneElectronics1600600CashSouth
1020Monday, June 10, 2024C018TabletElectronics1500500Debit CardEast

2. Insights:

Summary:

  • Total Sales from all categories products combined of all Regions are 31 sales which brings back the profit of $7930.
  •  Mid-week and end-of-week exhibit peak order activity, with Wednesday being the most popular day for placing orders, accounting for 6 recorded orders.
  •  May recorded the highest number of orders, totaling 4
  • Electronics emerged as the best-selling category for Q1 and Q2, with 15 orders over a six-month period, contributing 95% of the total profit (valued at $7500).
  • The North Region demonstrates the highest volume of product exports, followed by the South Region.

2.1 Dominance of Electronics in Sales:

  • Electronics is by far the highest-grossing category, generating a total of $7,500 in sales. Within Electronics, ‘Smartphone’ and ‘Laptop’ are the top-performing products, contributing $3,000 and $2,400 respectively. 
  • This significantly overshadows other categories, with Books at $180 and Clothing at $250.

2.2 Regional Sales Distribution

  • The South region leads in total sales with $3,225, followed closely by the North region at $2,580.
  • The East region shows moderate sales of $1,900, while the West region has the lowest sales at $225.

2.3 Preferred Payment Methods:

  • ‘Cash’ is the most frequently used payment method, accounting for $3,225 in sales.
  • ‘Credit Card’ is the second most popular method, with sales totaling $2,580.
  • ‘Debit Card’ and ‘PayPal’ are used less frequently, contributing $1,900 and $225 respectively.

3. Visualizations:

3.1. Total Sales by Category: 

3.2. Total Sales by Region

3.3. Sales by Category and Region:

3.4. Sales by Day of the Week:

Recommendations

Given that February, March, and May represent the months with the lowest sales, a meeting with stakeholders is proposed. The workshop, approximately two hours in duration and conducted online, aims to achieve the following:

1. Product Elicitation: Elicit potential new products in the book and clothing categories for add on over the next 3 months.

2. Product Prioritization: Brainstorm, prioritize, and verify decisions regarding new product additions for each quarter.

3. Strategy Review and Future State Definition: Review the current internal and external business state -> define the future state for the remaining six months of 2024, and, if time permits, predict market volume for Q1 of 2025.

4. Marketing Strategy Review: Review the current marketing strategy and propose necessary changes or improvements.

Leave a Reply

error: Content is protected !!