EXPLORATORY DATA ANALYSIS — SUPER STORE ANNUAL SALES REPORT — Power BI Use Case
INTRODUCTION
At the end of every calendar year, a business owner would like to compare sales and profit from one year to the next in order to determine which item is the least profitable or the most profitable with respect to sales. In addition, they would like to know who their regular customers are, which month had the highest and lowest sales, and how many products were sold out during that particular year.
OBJECTIVES
The project’s goal is to analyze the store’s sales figures for 2016 and 2017 and offer recommendations on how to improve them the following year.
ABOUT THE DATASET
The data was obtained as an excel file from a secondary source and saved to my Google drive @super store dataset. The data consists of the following columns: row, order id, order date, ship date, ship mode, customer id, customer name, segment, country, city, state, postal code, region, product id, category, sub-category, product name, sales, city, quantity, discount, and profit.
Tool used: Microsoft Power BI
After importing the dataset into the power query, the values were categorized into the dimension table and fact table.
- The fact table is a table that contains quantitative data, number data types, dates, continuous variables, foreign keys, and duplicates.
- The dimension table consists of qualitative data, text data type, categorical variables, primary keys, and no duplicates.
All dimension tables (foreign keys) were connected to the fact table (primary key), establishing relationships between all of the tables. This is done in order to link all pertinent data and create zero-error visualizations.
DATA CLEANING PROCESS
- I created a new measure called Calendar Date and extracted the date (date and time) from the Order date column using the Dax formula (Calendar function) listed below:
- Created a new column and extracted the year from the calendar date, using the function below;
- Created a new column for the month, which was also extracted from the calendar date column.
- I shortened the customer's name because it appeared too long by using only the first name and the first letter of the last name. I split the customers’ name column by delimiter and extracted the first letter of the last name, i.e; ‘Biliqees Abolomope’ was transformed into ‘Biliqees A’.
DATA VISUALIZATION & INSIGHTS
The store made a profit of $81,000 in 2016 and a profit of $93,000 the following year. In 2016, the store sold over 9000 items and 12,000 the following year. This rise in sales and profits was brought on by more potential customers.
Quantity and Sales by Month:
For the calendar year 2016, the store acquired the highest revenue in December and November, with over 1000 items sold out respectively, and had the lowest sales at the beginning of the year, which relatively picked up by May. Compared to 2016, sales increased relatively in 2017. The highest sales of items occurred in September and November 2017 as a result of an increase in the number of items purchased by customers. The increase in sales could also be a result of discounted prices of items offered in the 4th quarter of the year.
Sales and Profit by Category:
Over time, the most profitable items were those classified as technology and office supplies, which also had the highest sales. Although furniture-related items initially made significant sales, they eventually started to lose money. This could be the result of the cost price (the expenses to produce the furniture items) being higher than the selling price; as a result, as sales increase, more loss is incurred.
Profit by Region and Category:
In the US southern region, sales of certain products produced more profit in 2016; however, in 2017, sales in this region saw a sharp decline, possibly as a result of some unforeseeable circumstances. In 2017, the western region contributed more to overall profits, which were $46,000 higher than in 2016.
Sales and Profit by Sub-category:
Humans will always purchase phones because they want the newest, most luxurious models. The revenue generated from the sales of phones increased by $29,000, with an additional profit of $4,000 in 2017. The purchase of furniture (tables) increased year over year, which led to the store owner suffering more losses.
Top 6 Profitable Products:
Top 6 purchasing customers:
Jonathan, Paul, and Edward purchased the highest quantity of items from the store in 2016. While in 2017, John, Seth, and Janet made more purchases.
Top 6 purchasing Customers by product category:
REPORT VIEW
The report is a three-page document:
- The home page
- Sales report page for 2016
- Sales report page for 2017
RECOMMENDATION
- A business's primary goal is typically to maximize profits. For the products that are losing money, more research can be done to improve them better with lower costs while keeping their selling price.
- To attract more potential customers and give existing ones more justification for buying a specific product, a marketing campaign can be implemented.
