This is my first Excel project. It marks the beginning of my journey into understanding and working with data. I am eager to learn and use Excel to uncover insights and make things better. Let's dive into it.
Problem Statement
In the retail industry, understanding sales, customer behavior, and product trends is essential for businesses striving to optimize their operations and enhance customer satisfaction. This dataset focusing on online retail transactions, presents an opportunity to uncover critical aspects influencing business performance. As a Data Analyst, the primary goal is to address the following questions, each designed to guide the analysis toward valuable and actionable conclusions
Objectives
Before we jump in, let's identify business questions related to our data:
Question 1: What was the best month for sales? How much was earned that month?
Question 2: Top 10 Country sold the most products?
Question 3: At what time do we have the highest sales?
Question 4: Are there certain days where the number of items sold peaks or dips significantly?
Question 5: What top 10 product sold the most?
Identify Data Sources
For this project, we leveraged a dataset from Kaggle, specifically the "Online Retail Transaction Data" by UCI. This dataset contains a rich trove of information regarding customer transactions, offering a valuable opportunity to extract meaningful insights.
Load Data
To do that, first download the Online Retail Transaction Data dataset from Kaggle's website, the file format is .csv. We can use Power Query to load data from a CSV file into Excel.
1. Go to the "Data" tab in the Excel ribbon at the top of the window.
2. In the "Data" tab, locate and click on the "From Text/CSV" or you can click on "Get Data" to use another source option.
3. Select the CSV file you want to import and click "Import."
4. After clicking "Import," the Power Query Editor will open and click "Transform Data"
Data Cleaning
Data Cleaning is the process of removing errors, inconsistencies, and inaccuracies in datasets that might distort your data or make it less useful. To ensure that the dataset was ready for analysis, we began a thorough cleaning process :
1. Remove columns that are not required (index and StockCode columns)
2. Remove Errors
3. Delete Blank Rows
4. Split InvoiceDate column by Delimiter and create new Hours Column
5. Transform InvoiceDate column to Date type
6. Check datatypes to verify the datatype of each column.
7. Choose "Close & Load" to load the data into a new worksheet in Excel.
Add New Columns
After Cleaning and Loading the data into worksheet, next we created two new columns leveraging the extracted and cleaned data :
- Add Total Revenue column: Using the formula : =SUM([@UnitPrice] * [@Quantity])
2. Add Days Colum: Using the formula : =WEEKDAY([@InvoiceDate];2)
The table's outcome after cleaning and manipulation has 10 columns and 397,924 rows.
Descriptive Statistics
To begin exploring and learning from the data, we first look at simple descriptive statistics. This will helps us understand the fundamental features of the data and identify trends driven by the data.
The statistical analysis of the UnitPrice column indicates that, on average, the products sold by the online retail company are priced at £272.26. While the standard deviation of £2,087 suggests some variation, the relatively narrow 95% confidence interval of £266.82 to £277.71 implies that there is consistency in pricing. However, the Z-score of 1.64 suggests that the mean UnitPrice is slightly higher than the overall mean, indicating a potential tendency towards higher-priced products within this sample.
Data Exploration
Question 1: What was the best month for sales? How much was earned that month?
This question is to identify the peak month for sales to understand the seasonality performance. To easily analyse & calculate between data fields, we will create pivot table to calculated Sum of Total Revenue.
To do that, click Insert > Pivot Table > OK. Drag the InvoiceDate field under Rows and the Total Revenue under Values.
Next, we create chart to visualization the data. Click on the PivotTable Analyze, and then go to PivotChart > Line > select the Line chart > OK
Insight:
1. September emerged as the peak month, generating £44.14 million in sales.
2. December recorded the lowest sales among the top months, with £20.73 million.
Question 2: Top 10 Country sold the most product?
This question is to determine the country with the highest sales to inform targeted marketing strategies. Create another Pivot Table by repeating the exact same steps as before, but now we will put Description in Rows and Total Revenue in Values. Next, we want to create a Clustered Column for this table.
Insight:
1. The United Kingdom leads with £302.56 million in sales, significantly surpassing other countries. This makes sense since it's an online retail company based in the UK.
2. Belgium ranks lowest among the top 10 countries, contributing £1.43 million in sales.
Question 3: At what time do we have the highest sales?
This question is to identify peak sales by hours and to optimize advertisement timing for increased awarness. We will put Hours in Rows and Total Revenue in Values. We again create Line chart for this table.
Insight:
1. Sales peak around 12 PM, with £56.95 million in revenue.
2. Indicates a significant increase in consumer activity during the lunch hour.
3. The lowest sales occur at 8 PM, generating £234.45 thousand pounds.
Question 4: Are there certain days where the number of items sold peaks or dips significantly?
This question is to identify patterns in daily sales to optimize inventory management and staffing. For this, the Rows should now be Day and the values should be Total Revenue. And then create a Clustered Column.
Insight:
1. Day 2 (Tuesday) and Day 1 (Monday) show significantly higher sales compared to other days
2. Day 3 (Wednesday) records the lowest sales among the analyzed days.
Question 5: What top 10 product sold the most?
This question is to identify the most popular product based on the number of units sold for each product. Next, put Description in Rows, Quantity field as a Sum, Unitprice as an Average in values.
Insight:
1. "Paper Craft, Little Birdie" leads the most popular product with 80,995 units in quantity and Average Unit Price: $208.
2. The top 10 products have a wide range of average unit prices, ranging from £168 to £1,250.
3. Consumers tend to buy lower priced items. The lower the price of products, the higher the number of products sold and vice versa.
Create Dashboards
Now is the time to bring it all together in Dashboard. A Dashboard is like a visual snapshot of important information, showing key data and insights in a simple and clear way. Dashboards are designed to simplify complex information, allowing people to make smart decisions without getting lost in the details. They provide a user-friendly experience, making it a breeze to explore different metrics and get valuable insights with minimal effort.
Recommendations
1. Leverage high September sales to plan exciting marketing campaigns. Think about special promotions or events that customers will love during this time
2. Since this is a retail company based in the UK, put more effort into marketing in the UK. Create campaigns that match what people in the UK like and how they shop.
3. Show more ads during a certain time, usually in the morning and early afternoon (9 AM — 2 PM). Try special promotions or flash sales during these hours to get more people interested and make conversions.
4. Plan your stock items and staff based on the most popular product and the days when the most people shop. Make sure you always have available products (especially products that sold the most), and your team is ready to help customers during these busy times.
5. Consider adding more similar products (Paper Craft, Little Birdie) or using similar strategies to sell more things overall.
That's all from me. Thank you for taking the time to explore my first Excel project journey with me. Feel free to provide feedback or ask questions about the project I'm working on. I look forward to sharing more insights and learning experiences in the future.