Working on a dataset for a US Superstore (Using excel)
Introduction to the dataset
A superstore wants insights on their data. They wants to know the total revenue generated by the store. They also want to know what category of product that contribute the most to sales as well as the region generating the most profit.
End of the year party is coming at the store and it’s that time they promote staffs. They wants to know the region manager that has made the most sales and also the region manager that has made the least sales. They contacted me as a data scientist. Let’s analyze this dataset together.
Data Gathering
Let’s dig Into Superstore dataset and make deep insights from their data. This dataset was found on the sight Hackernoon.
Dataset: https://hackernoon.com/15-excel-datasets-for-data-analytics-beginners
I downloaded it into my excel from the data ribbon, using get data to import dataset from web.
Data Assessment
There are a total of 2000 records and 27 columns. After going through this dataset, we know that there are some columns that can be removed as they are not important for the analysis. It is also important to clean our data. Now let’s go to the cleaning process together.
There are a total of 2000 records and 27 columns. After going through this dataset, we know that there are some columns that can be removed as they are not important for the analysis. It is also important to clean our data. Now let’s go to the cleaning process together.
Data Cleaning
Before starting the cleaning process, I made a copy of the as a backup so if we make a mistake during cleaning, we can always refer back to the backup.
The next thing was to remove the Row ID. This is not important for analysis as we are using the customer’s name to determine the specific customer that has made the most sales.
The next cleaning to do is to remove null values.
Then we removed the duplicates.
After our basic cleaning process. The next thing is to through the data to check our case is consistent so there wouldn’t be some letters in upper case and some in lower case.
Now we also want to make sure our columns are formatted correctly, the data and ship date should be formatted in date.
We should also go through sales and profit column to make sure they are formatted in currency type.
We should also go through the discount column and make sure it is formatted in percentage.
Next thing we are doing is putting our data into a table. Putting our data into a table prior to analysis helps make the analysis easier and more interesting. The filter option that comes with putting our data into a table helps filter through our dataset. We are also going to rename our table, this helps analyzing get easier. We have three tables in our dataset.
we can move to pivot tables and chart. A Pivot Table is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. Charts are visual representation of data to make it more understandable.
Creating Pivot tables
A Pivot Table is used to summarize, sort, reorganize, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column), and using advanced calculations on them.
Using the pivot table, we can see that the total revenue by product category is given below
We can also see that furniture contributes the most to the sales by the table below.
We have been working with one table, now we want to get determine what region manager makes the most sales. Region manager is in another table. To create a relationship between this tables, we use a power pivot. Power Pivot allows us to access any of the fields in any of the tables in our data model, and then analyze them based on any relationships that we’ve defined.
Create a power pivot
To create a power pivot, we create a blank workbook. From the get data, under the data ribbon. We select the excel file we want to import. We needed multiple table, so we selected the three tables we need and click on transform.
This will take us to the power query editor where we can transform the data. This essentially means cleaning the data. This is a good step if we have not cleaned our data prior. Since we have cleaned our data we can go to the next step.
Our table has been added together let’s close and load into a pivot table.
This is the difference between a pivot table where we are using one source of data and a power pivot table where we are utilizing lots and lots of different sources of data.
To get the region manager that makes the most sales and the region manager that make the least sales. Region manager and sales are in two different table. We need to establish a relationship between tables.
To create a relationship we use the manage data ribbon. This will take us to an entirely different window.
We go to the diagram view to link tables
Linking order region in table_user to region in table_orders and linking order ID in table_return to order ID in table_orders creates a connection between the whole tables.
After linking the tables, we can see Erin has the highest sales and Sam has the least sales.
Data Visualization
Data visualization refers to the graphical representation of data using various charts, graphs, and other visual elements. We are using the data we created from our power pivot tables to build different charts and get insightful discoveries from our dataset.
- We would be creating 3 charts to see the category got products that contributed the most sales.
- We would also build a chart to determine the region manager that makes the highest and lowest sales.
- We would build a chat to see the sales trend over the past few years.
The link to the dashboard dataset is: US Superstore dataset
Conclusion:
From the dashboard above, we can get key insights into our dataset.
1. Sales by Category: From the dashboard, we can see that furniture is the category that has made the most sales.
2. Sales by Manager: From the dashboard, we can see that Erin is the region manager with the most sales thus he should be promoted. We can also see that Sam is the manager with the least sales.
3. Sales trend: From the sales trend line above, we can see that sales peaked in April and it was the lowest in March.
This information in the dashboard provides insights into the performance of the superstore for the year 2015. By making good use of this insights, the business owner can monitor their performance, formulate effective growth strategies to facilitate business growth.
