Walmart SQL Sales analysis

Analysis List

1. Product Analysis

Conduct analysis on the data to understand the different product lines, the products lines performing best and the product lines that need to be improved.

2. Sales Analysis

This analysis aims to answer the question of the sales trends of products. The result of this can help us measure the effectiveness of each sales strategy the business applies and what modifications are needed to gain more sales.

3. Customer Analysis

This analysis aims to uncover the different customer segments, purchase trends, and the profitability of each customer segment.

Approach Used

1. Data Wrangling: This is the first step where inspection of data is done to make sure NULL values and missing values are detected and data replacement methods are used to replace missing or NULL values.

Build a database create a table and insert the data. Select columns with null values in them. There are no null values in our database as in creating the tables, we set NOT NULL for each field, hence null values are filtered out.

2. Feature Engineering: This will help use generate some new columns from existing ones.

Add a new column named time_of_day to give insight of sales in the Morning, Afternoon and Evening. This will help answer the question on which part of the day most sales are made.

Add a new column named day_name that contains the extracted days of the week on which the given transaction took place (Mon, Tue, Wed, Thur, Fri). This will help answer the question on which week of the day each branch is busiest.

Add a new column named month_name that contains the extracted months of the year on which the given transaction took place (Jan, Feb, Mar). Help determine which month of the year has the most sales and profit.

2. Exploratory Data Analysis (EDA): Exploratory data analysis is done to answer the listed questions and aims of this project.
3. Conclusion:

Business Questions To Answer

Generic Question

1. How many unique cities does the data have? The Walmart stores are located in 3 cities which include; Yangon, Naypytaw and Mandalay.

2. In which city is each branch? Each city is allocated a branch. Yangon is branch A, Naypyitaw is branch B and Mandalay is branch C

Product

1. How many unique product lines does the data have? Walmart has a total of 6 product lines.

2. What is the most common payment method? Walmart stores accept 3 payment methods which include; Ewallet, Credit card and Cash. Ewallet is the most common type of payment method with a total of 345 transactions.

3. What is the most selling product line? Fashion and accessories have been bought a total of 178 times and Health and beauty have a total of 152 transactions.

4. What is the total revenue by month? January had the highest Revenue of the 3 months and on the other hand, February had the lowest revenue and didn't reach 10,000 in Revenue.

5. What month had the largest COGS?

Cost of goods sold (COGS) includes all of the costs and expenses directly related to the production of goods. COGS excludes indirect costs such as overhead and sales and marketing.

January also had the highest COGS and February with the least.

6. What product line had the largest revenue? Food and Beverages had the highest revenue while on the other Health and Beauty had the lowest revenue which didn't reach a total of 50,000 in revenue.

7. What is the city with the largest revenue? Naypyitaw City had the highest Revenue and was the only one with a revenue above 110,000.

8. What product line had the largest VAT?

9. Which branch sold more products than the average product sold?

10. What is the most common product line by gender?

11. What is the average rating of each product line?

12. What month had the highest Profit?

13. Which product line had the highest Profit?

Sales

1. Number of sales made at each time of the day per weekday

2. Which of the customer types brings the most revenue?

3. Which city has the largest tax percentage/ VAT (Value Added Tax)?

4. Which customer type pays the most in VAT?

Customer

1. How many unique customer types does the data have?

2. How many unique payment methods does the data have?

3. What is the most common customer type?

4. Which customer type buys the most?

5. What is the gender of most of the customers?

6. What is the gender distribution per branch?

7. Which time of the day do customers give the most ratings?

8. Which time of the day do customers give the most ratings per branch?

9. Which day of the week has the best avg ratings?

10. Which day of the week has the best average ratings per branch?