Walmert Sales Data Analysis using SQL

Walmart is one of the largest retail chains globally which offers a wide range of products at competitive prices. It is known for its large network of stores and online platforms. It serves millions of customers worldwide by providing everything from groceries to electronics.

Walmart can refine its strategies to drive growth and customer loyalty by analyzing key metrics like total sales, average purchase amounts, and customer demographics. In this article, We will analyze Walmart's sales data and derive insight that helps the stakeholders make better decisions.

About The Data

The below Walmart dataset consists of the following columns:

• User_ID: The unique identifier for each user.
• Product_ID: The unique identifier for each product.
• Gender: Gender of the user (Male/Female).
• Age: Age of the user which is grouped into bins (e.g., 18-25, 26-35, etc.).
• Occupation: Occupation of the user (masked).
• City_Category: The category of the user's city (A, B, C).
• StayInCurrentCityYears: The number of years the user has been staying in the current city.
• Marital_Status: Marital status of the user (Single/Married).
• ProductCategory: The product category (masked).
• Purchase: The total purchase amount made by the user.

Exploratory Data Analysis Using SQL

Before we proceed with the analysis, we need to clean the data. SQL is a powerful tool for this, and here are the key steps:

• Remove Null Values: Ensure there are no missing or null values in critical columns like `Purchase`, `Gender`, and `Age`.
• Standardize Data: Standardize the data format for columns like `Gender`, `City_Category`, and `Marital_Status`.
• Handling Duplicate Entries: Remove any duplicate records to ensure data accuracy.

1. Total Sales Analysis

SELECT SUM(Purchase) AS Total_Sales
FROM walmart_sales;

Output:

Explanation: This query calculates total revenue by summing up the Purchase column. The total sales metric reflects overall business performance.

2. Average Sales Per Transaction

SQL Query

SELECT AVG(Purchase) AS Average_Sales
FROM walmart_sales;

Output:

Explanation: This calculates the average transaction amount, giving insights into typical customer spending behavior.

3. Total Products Sold

SQL Query

SELECT COUNT(Product_ID) AS Total_Products_Sold
FROM walmart_sales;

Output:

Explanation: This counts all product purchases, helping assess demand and inventory management.

4. Average Purchase by Stay in City

SQL Query

SELECT StayInCurrentCityYears, AVG(Purchase) AS Avg_Purchase
FROM walmart_sales
GROUP BY StayInCurrentCityYears
ORDER BY StayInCurrentCityYears;

Output:

Explanation: This groups customers by their length of stay in the city and calculates the average purchase amount. It helps analyze consumer spending patterns over time.

Advance Data Analysis With Dashboard Creation

Once the data has been cleaned and analyzed using SQL, now it's time to create a dashboard that offers a detailed visualization of key performance indicators (KPIs) and sales trends.

An interactive dashboard helps decision-makers monitor and analyze various aspects of sales performance by providing insights that can guide strategic decisions. Tools like Power BI, Tableau, or Excel are ideal for building these dashboards as they allow easy integration of data, dynamic updates, and customization of visualizations.

1. KPIs

Total Sales

The total sales metric provides a snapshot of the overall revenue generated during a specific period. This figure reflects the combined total of all purchase amounts in the dataset, making it a critical KPI for assessing the overall success of the business.

By understanding total sales, Walmart can assess whether its current marketing efforts, promotions, and product offerings are driving sufficient revenue. This number is often used as a benchmark for evaluating the performance of sales strategies, and comparing against previous periods helps in understanding growth or decline in sales.

A large total sales figure indicates strong market demand and customer engagement, while a low total sales figure suggests that adjustments may be needed in the sales strategy.

2. Average Sales

The average sales metric helps identify how much, on average, customers are spending per transaction. By dividing total sales by the total number of transactions, you gain a sense of the typical purchase value.

This metric provides valuable insight into customer spending habits. If the average sales value is high, it might indicate that customers are buying more expensive products, possibly in response to premium offerings or upselling tactics. On the other hand, a low average might suggest that customers are making smaller purchases, which could be due to pricing strategies or product offerings.

A high average sales value could indicate successful marketing strategies targeting high-ticket items, while a low average might highlight opportunities for product bundling or upselling.

3. Total Products Sold

The total products sold metric measures the total number of products purchased across all users in the dataset. It helps businesses understand how many items were bought, regardless of price.

This KPI shows the **product variety and demand** across different categories. A large number of products sold means that customers are purchasing a variety of items, which could be a sign of effective product assortment and pricing strategies. Conversely, a low number of products sold could indicate that inventory needs to be optimized or that certain products are underperforming.

If the total products sold is low, it could suggest that the product range is not attractive to customers or that there are supply chain issues. A high value can suggest that customers are buying from a wide range of products that shows effective targeting and a successful product offering.

Average Purchase by Stay in City

This analysis examines how the length of time a user has stayed in their current city correlates with their average purchase amount. A stacked column chart would be ideal for comparing purchase behavior across users who have lived in their city for different durations (e.g., 1-2 years, 3-5 years, etc.).

This metric helps to identify trends in consumer behavior based on how long they've been living in a city. For example, it may reveal that users who have lived in their city longer tend to spend more on average, possibly due to increased economic stability or stronger brand loyalty.

Understanding how the length of residence impacts purchases can help Walmart customize promotions and marketing efforts. For instance, new residents might be targeted with discounts or incentives to encourage higher spending while long-term residents could receive loyalty rewards or premium offers to retain them.

Sales by Age

The sales by age analysis tracks how sales are distributed across different age groups, allowing Walmart to understand which age demographics are spending the most.

This analysis is crucial for businesses that want to target specific age groups with their product offerings or marketing campaigns. For example, if younger customers (e.g., 18-25 years old) are contributing significantly to sales, Walmart may want to offer trendy or budget-friendly products aimed at this age group. Similarly, older age groups (e.g., 35-50 years old) might respond better to premium products or loyalty programs.

A stacked bar chart would visually display the proportion of sales contributed by each age group. This information can help Walmart focus on the most profitable customer segments, tailor its marketing strategies, and adjust product offerings accordingly.

Top 10 Products by Purchase

This analysis identifies the top 10 products by total purchase amount. It helps Walmart determine which products are the most popular and generate the most revenue.

By understanding which products drive the most sales, Walmart can focus on promoting high-demand products, optimize inventory levels, and potentially create bundle deals to maximize revenue. Additionally, these insights can guide decisions on product placements and marketing focus.

A stacked column chart helps to visually compare the sales figures for the top products. This metric also highlights areas where product sales might be underperforming, potentially revealing opportunities for product improvement or targeted promotions.

Sales by Gender

The sales by gender analysis shows the distribution of total sales between male and female customers. By using a donut chart, this data is visualized in a way that clearly communicates the proportion of sales contributed by each gender.

Understanding sales distribution by gender helps Walmart tailor marketing strategies and product offerings. If a particular gender is contributing more to total sales, Walmart might consider focusing their advertising efforts or promotions on the other gender to balance sales. Additionally, certain product categories might perform better with a specific gender, informing product recommendations and inventory management.

A donut chart would reveal whether male or female customers are responsible for most of the sales. For instance, if women contribute more to total sales, Walmart could consider introducing more female-targeted products, while still offering a balanced product range for men.

Conclusion

Overall, By visualizing the KPIs and trends in Walmart's sales data, the dashboard provides critical insights that can drive strategic decision-making. The insights gained from the total sales, average sales, product variety and other metrics enable Walmart to refine its marketing strategies, you can adjust its product offerings and enhance customer targeting. By leveraging these insights, Walmart can optimize its operations, improve customer satisfaction, and ultimately drive increased sales and profitability.This analysis provides valuable insights that can help businesses to improve their operations, product distribution and customer engagement efforts.