Introduction
One of the most crucial steps in improving business performance is to identify opportunities and evaluate sales performance in order to establish an effective strategy. This can be accomplished through descriptive analysis of sales data.
In this project, I will do a simple sales analysis of a retail store based on a historical dataset.
The dataset used in this analysis can be found on Kaggle.
The main objective of this analysis is to better understand business performance by tracking historical transactions. The tools used in this analysis in Colab Notebooks for data cleaning and EDA analysis, and Power BI for a dashboard.
The dataset contains records of transactions/ orders of a retail company specializing in transport by selling cars, trucks, planes, ships and trains
Data Cleaning and Transformation
Importing data
from google.colab import drive
drive.mount('/content/drive')
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
path = "/content/drive/MyDrive/Colab Notebooks/Retail_Sales/"
data = pd.read_csv(path + "sales_data_sample.csv", encoding='latin1', parse_dates= ['ORDERDATE'])
#Make a copy of dataset
retail_data = data.copy()
data.info()
From this initial assessment, there is one datetime column, nine numerical columns and fifteen categorical columns.
Check for duplicates and null values:
#Check for duplicates
retail_data.duplicated().sum()
There were no duplicated rows in the dataset.
Check for null values
# Check for null values
retail_data.isnull().sum()
The territory column has 1074 null values, which correspond to transactions in the USA and Canada. I imputed these null values with AMER to represent the Americas territory.
null_territory = retail_data['TERRITORY'].isnull().sum()
print(f'Number of null values in territory column: {null_territory}')
# view countries in Each territory
countries_by_territory = retail_data.groupby('TERRITORY')['COUNTRY'].unique()
print(f'Countries by territory:\n{countries_by_territory}')
# Impute null values in territorry column with AMER - Americas consisting the USA and Canada
retail_data['TERRITORY'] = retail_data['TERRITORY'].fillna('AMER')
I then converted the object dtypes to categoricy dtypes.
#copy of our dataset
retail_df = retail_data.copy()
#Convert object dtypes to categorical columns
categorical_columns = retail_df.select_dtypes(include=['object']).columns
#retail_df[categorical_columns] = retail_df[categorical_columns].astype('category')
categorical_columns
Notably, the dataset had a sales column, quantity ordered and price of each product line, per order. I created a REVENUE column to find out if it was equivalent to the SALES column.
#Create Revenue Column
retail_df['REVENUE'] = retail_df['QUANTITYORDERED'] * retail_df['PRICEEACH']
I then dropped irrelevant columns.Checking the final dtypes:
#Check dtypes
retail_df.info()
Exploratory Data Analysis
Correlation
**Correlation heatmap**
numerical_columns = retail_df.select_dtypes(include=['int64', 'float64']).columns
plt.figure(figsize = (10,6))
sns.heatmap(retail_df[numerical_columns].corr(), annot=True, cmap='coolwarm')
plt.title('Correlation Heatmap')
plt.show()
The correlation co-efficient indicates the linear relationship between two variables.
- Quantity Ordered and Priceeach (0.0056) - this indicates a weak positive correlation. The two features are hardly related.
- Quantity Ordered and Revenue (0.75) - Indicates a strong positive correlation. When the quantity ordered increases, revenue also increases.
- Priceeach and Revenue (0.64) - Indicates a moderate correlation. When the price increases, revenue increases.
Outlier Detection
I plotted boxplots of Price, Revenue and Sales columns to check for possible outliers
**Identifying Outliers**
plt.figure(figsize=(8,6))
outlier = pd.DataFrame(data=retail_data, columns = ['REVENUE', 'QUANTITYORDERED', 'SALES'])
sns.boxplot(data=outlier, color='cyan')
plt.title('Outliers in the Revenue, Quantity and Sales columns')
plt.show()
The boxplots above identified possible outliers in the Sales column. However, the revenue column appears to have none. This indicates that there are discrepancies in the sales column. Recall the Revenue column was a direct calculation, of Quantity ordered and Price of each item, therefore, should have been equivalent to the Sales column values. The number of dicrepancies is 1304, about 46% of the dataset Since this is not the case, its important to find out why these discrepancies exist and their source.
After completion of the EDA analysis, I exported the dataset to Power BI for further analysis.
Analysis in Power BI
At this stage I used the dataset to create meaningful insights. The data collected contains data from 6th January 2003 to 31st May 2005.
Here is a preview of the final dashboard and insights from this dataset.
Insights and Recommendations.
There are 307 distinct orders, the total quantity of products sold over the 27 months was ninety nine thousand, revenue of 8 million USD and a shipping rate of 93%.
Revenue Trend
The revenue trend across all years remained consistent with its highest peak in November.This is consistent with sales trends during holiday seasons where sales are expected to rise.
Revenue also surged in the 4th quarter respectivelly and the retail company sold more units from Tuesday to Friday, compared to other days of the week.
Product Analysis
Classic Cars is the most popular product line, accounting for $3 million in revenue, about 37.5% of the total revenue during the entire period. Therefore, the company should extend extensive marketing of this product to their clientelle to improve revenue from this core product.
On the other hand, trains and ships were the least popular products, accounting for only 12.5% of the total revenue. The average prices of a ship, train and a classic car are $87.34, $83.86 and $75.65 respectively. Trains and ships are mostly purchased for commercial purposes, compared to classic cars that are used by individuals and this could explain the variance in revenue performance by these products. Due to the poor performance of the ships, marketing efforts can be redirected to cruise companies in Europe.
However, the retailer could consider smaller boats or yatchs, for private buyers as a potential investment.
Regional Analysis
The United States market recorded the highest revenue of $3 million while, Madrid city in Spain recorded the highest revenue during the period at $902,094, i.e. 10.88% of total revenue.
Additionally, the EMEA region, that is countries in Europe, accounted for 49.79% of the total revenue, followed closely by the Americas territory, AMER, at 38.35%. Marketing should also focus on these territories.
.
Link to Dashboard and Notebook
The final dashboard and notebook can be found below.
Top comments (0)