4. Sales Data Analysis using MS Excel and Power BI
1. Introduction:
In these recent times of increased competition in the business world, one of the main factors that can help businesses perform well in their field is analysing the performance of their sales. Sales data provides valuable insights into the performance of a business and also an insight into the steps and measures to be taken to improve the performance.
This project aims to extract valuable insights by analysing and visualizing the sales data to identify the trends, top-selling products and revenue metrics like total sales and profit margins for business.
2. Data Collection and Sources:
The data is taken from the source provided as part of the internship at MeriSKILL. The sales data consists of 185951 rows and 11 columns. The rows in the data set are namely — order ID, Product Name, Quantity ordered, Price Each, Order date, Purchase address, Month, Sales, City and Hour.
3. Data Pre-processing and Cleaning Using MS Excel:
The steps for data pre-processing and cleaning that were carried out using MS Excel are:
Checking for blanks
i. FILTER under the DATA tab is used to check for the blank values
ii. No blank values were found in this data set
Date and Time format
i. The date and time were in the same column under the name ORDER DATE
ii. TEXT TO COLUMNS under the DATA tab is used to split the Date and Time into separate columns, space is the delimiter for the splitting of the same.
iii. Converted the month column in numbers to the month name using
=TEXT (DATE (2019, I2, 1), “mmmm”)
Data type conversion
i. The data type of the PRICE EACH and SALES columns were converted into currency
ii. The data type of ORDER ID, QUANTITY ORDER, MONTH and HOUR columns were converted into numbers.
Data Sorting
i. The data is sorted from newest to the oldest according to the ORDER DATE
Data Export
i. After cleaning and pre-processing, the data is stored in both CSV and Worksheet files.
4. Exploratory Data Analysis (EDA) using POWER BI:
The steps for Exploratory Data Analysis that was carried out using Microsoft POWER BI are:
a. Importing Data into Power BI
i. Select import data from Excel and select the Excel data set
ii. Click on the TRANSFORM DATA option if you have any more data cleaning to be done
iii. Check for empty values and if all the transformation is completed click on the CLOSE AND APPLY option
b. Data Exploration
i. From the FIELDS pane on the right drag and drop the columns to explore the data
ii. Various charts were used to find the ones essential for the analysis
c. Basic Summary Statistics
i. Using CARDS from the VISUALIZATIONS tab, the total quantity ordered, Sales, Average Sales, no of unique products and no of unique cities were measured and displayed
d. Univariate Analysis
i. A column chart is created for the bottom five products according to the sales amount that are not performing well when compared to the others
ii. A doughnut chart is created for the top five products that are performing well according to the sales amount
iii. A bar chart is created to find the quantity of products ordered from different cities.
iv. Another doughnut chart is created to find out the total sales that occurred in each city.
v. An area chart is also created for the quantity of products ordered.
e. Time Series Analysis
i. A line chart is created to analyze the trend in the no of sales throughout the months
f. Slicers
i. Two slicers were created for filtering data, one for the city data and the other for the monthly data.
5. Identifying Sales Trends and Patterns:
· From the analysis, it was found that around 209K quantity of products were sold in total generating a total revenue of 34.49M and an average revenue of 185.49M.
· A total of 19 products were sold in around 9 major cities from January to December 2019.
· From the time series plot on the NUMBER OF PRODUCTS SOLD MONTHLY, it is observed that it showed an upward trend from January till April and had some seasonal variations after that till September. September to October shows a steep upward trend with a small downward trend towards November. Recently the number of products sold shows an upward trend from November to December.
· Overall December gave the highest number of sales at 25K and January gave the lowest number of sales at 9.7K
· The Doughnut chart on Sales Revenue generated per city illustrates the percentage distribution of revenue that has been generated from each city after buying the products.
· There chart shows 9 cities from which the revenue is generated, namely — San Francisco, Los Angeles, New York City, Boston, Atlanta, Dallas, Seattle, Portland and Austin.
· It is observed that the highest sales revenue of 23.95% is generated from San Francisco and the lowest sales revenue of 5.28% is generated from Austin.
· The top three cities which generate the highest revenue are San Francisco, Los Angeles, and New York City
· The cities with the lowest sales revenue generation are Seattle, Portland and Austin.
· The Bar chart on the NO OF PRODUCTS SOLD PER CITY shows the quantity of each product that was sold in each city.
· This follows a similar trend to the sales revenue generated from each city where San Francisco is the top and Austin is the last city according to the sales revenue generated as well as the number of products sold
6. Product Performance Analysis:
· The Area chart on the REVENUE GENERATED BY EACH PRODUCT provides an overview of how much revenue is generated by each product.
· It is observed that the MacBook Pro Laptop is the top-selling product with an 8M revenue generation and AAA Batteries is the lowest income generating product.
· This graph just shows the overall trend of revenue generation and the upcoming charts will dive more into the top-selling and least-selling products.
· The Doughnut chart on THE TOP SELLING PRODUCTS shows the top 5 products that are performing well among the others.
· The top five are MacBook Pro Laptop, iPhone, ThinkPad Laptop, Google Phone and 27in 4K Gaming Monitor
· MacBook Pro Laptop is the top-selling product that has been sold 8.04M times.
· The bar chart for LEAST ORDERED PRODUCTS shows the bottom five products with the lowest number of sales.
· These products are Lightning Charging cable. USB-C Charging cable, Wired Headphones, AA Batteries and AAA Batteries.
· The AAA batteries are the least-selling product with some sales of 93k.
7. Recommendations and Business Insights:
The purpose of this analysis was to extract valuable insights by analysing and visualizing the sales data to identify the trends, top-selling products and revenue metrics like total sales and profit margins for the business.
The following sections provide recommendations and business insights based on the findings from the analysis that will help in improving sales performance or in making valuable business decisions.
KEY FINDINGS
o Total quantity of items sold — 209K
o Total Revenue generated — 34.49M
o Average Revenue generated — 185.49
o No of unique products — 19
o No of cities — 9
o The month with the highest number of sales — DECEMBER
o The month with the lowest number of sales — JANUARY
o City with highest Sales revenue — SAN FRANCISCO
o City with lowest Sales revenue — AUSTIN
o Highest selling product — MACBOOK PRO LAPTOP
o Least selling product — AAA BATTERIES
RECOMMENDATIONS
Seasonal Marketing Campaign
In the months when the product selling is low, seasonal marketing campaigns like holiday-themed selling, flash sales, summer campaigns, year-ending sales, clearance sales etc.
Marketing should be done through social media based on the seasons and on the theme of the upcoming sales.
Referral and Affiliate Programs
Providing referrals and affiliate programs can drive in more customers as well as increase the engagement of already existing customers
This is one of the cost-effective measures which can also enhance the brand image along with customer loyalty
Local SEO and Geotargeting
To increase sales in the cities with low sales local SEO optimization can be done which helps in targeting customers based on the location
Geotargeting will help in customizing the trending and popular products according to the customer’s location while ordering online.
The advertising can be done on locations based on the potential customers rather than focusing on a broader audience
Inventory Management
It will be cost-effective to manage and store inventories according to the selling of products
The products with high selling rates should be stocked up in a greater amount than the product with low selling rates, this can reduce the overstocking of products
The products with high demand should always be in stock and ready to shipped out to maintain customer satisfaction
Feedback and Reviews
Always get feedback and reviews from customers both online and offline to keep track of the business selling activities
Customer satisfaction is the important element that decides the success of a business
BUSINESS INSIGHTS
Seasonal Marketing Campaign
Spring sales campaign can be hosted from April to June to increase the no of sales
Back to School and College is very effective in selling products that are essential for students in the months from May to June
Health and Fitness Tech campaigns can help in selling the tech products that can track and help in fitness challenges
Referral and Affiliate Programs
Customers who buy the products regularly or very often can be given eligibility for the referral and affiliated programs.
Customers from the cities with the lowest selling rates like Austin, Portland, Seattle, Dallas and Atlanta can be given eligibility for these programs which will increase the selling rate of that particular city.
Partnering with influencers from cities like Austin and Portland can increase the demand for the products
Local SEO and Geotargeting
The Geotargeting should focus on the cities with the lowest selling rates like Austin and Portland rather than the top-selling cities
Asking customers to provide feedback in Google Maps or Google reviews can help in increasing the optimization
Inventory Management
The top selling products like MacBook Pro laptop, iPhone, and ThinkPad Laptop should be always in stock to meet the customer demand
The top-selling products should be restocked regularly after checking the sales traffic
The least-selling products should only be stocked to satisfy the immediate needs of the customers and to avoid overstocking
Feedback and Reviews
Feedback and reviews of the least selling products like AAA and AA battery packs should be reviewed to understand how to improve those products to increase sales
The feedback from least-selling cities like Austin should be compared with the top-selling city San Francisco to understand the customer requirement difference and to help it improve the sales
8. Dashboard:
9. Conclusion:
In conclusion, the data analysis conducted on the sales data of the tech products provided valuable insights on how well each product is performing as well as insights on the cities from which the orders are being placed. The main objective of the analysis was to extract valuable insights by analysing and visualizing the sales data to identify the trends, top-selling products and revenue metrics like total sales and profit margins for business. The patterns and trends were identified from the data visualization created and valuable recommendations and insights were provided on the same.
LIMITATIONS
One of the limitations was that due to the unavailability of customer information, the analysis couldn’t include any customer-centric approaches to find the product preferred by each age group or specific needs.
FUTURE SCOPE
A customer-centric analysis can be conducted if data is also collected based on the customer like the age, career or preference, which will also help in targeting and including more products based on customer preference.
10. Tools and Technologies:
MICROSOFT EXCEL
The data pre-processing and data cleaning was done on Microsoft Excel and the files were saved in CSV as well as XLSX files.
MICROSOFT POWER BI
The EDA, data visualization and dashboard creations were done on Microsoft Power BI Desktop and were exported as PDF files
11. Acknowledgements:
I would like to express my sincere gratitude to MeriSKill for giving me this opportunity to work on this project as a part of my internship. I learned and applied much knowledge to this project. After completing this project I learned valuable things regarding how to conduct a data analysis and to provide recommendations and business insights for the same.
12. Portfolio Links:
https://www.novypro.com/blog_published/sales-data-analysis-using-ms-excel-and-power-bi
