Assessment

You have just been employed as Data analyst in one of the fast growing product manufacturing and distribution companies and your first welcoming task by MD is to create a report for an upcoming board meeting. You are to go through and analyze the sales data from 2015-2017 in order to generate the requested report. The report should capture the following;

1. Revenue by region
2. Revenue by sales Rep
3. Revenue by products
4. Sales trend
5. Yearly changes in revenue Highlight the following on the report:
6. Top 3 products
7. The most productive sales Rep in the respective years. From your analysis, give 3 recommendations you think would help the company increase revenue in the following year.

Data Source:
docs.google.com/spreadsheets/d/1SWCOO70Yv7P..

Github code: github.com/anochima/Sales-Data-Analysis

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# Setup our visuals to use seaborn by default
plt.style.use('seaborn')
plt.rcParams["figure.figsize"] = (11, 4)

# Import our data using pandas
df = pd.read_excel('MODULE 2-Assessment working data.xlsx')

# Convert the sales excel file into .csv (Comma Separated Value)
# This is only done because because I feel more comfortable working with Csv files to xlsx files
df = df.to_csv('sales-data.csv', index=False)

# Read the sales data
df = pd.read_csv('sales-data.csv')

df.head()

df.describe(include=['object','float','int'])

Some useful Insights

There was a total of 9,971 sales entries between 2015-2017 out of which the following descriptions were made:

Units:

• The minimum number of units sold between 2015-2017 was 1
• The maximum number of units sold between 2015-2017 was 25
• The average number of units sold between 2015-2017 was aproximately 3

Revenue

• The least revenue generated between 2015-2017 was 21
• The most revenue between 2015-2017 was approximately 1902

---Others---

• We had 6 unique Sales Representatives between 2015-2017
• We covered 3 Regions between 2015-2017
• 5 unique colors

# At what amount did we sell most?

df['Revenue'].value_counts().hist(bins=50);

Here most items were sold between 21 - 70 respectively

df['Units'].hist(bins=50);

# What's the total revenue generated between 2015-2017?

round(df['Revenue'].sum())

909171

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9971 entries, 0 to 9970
Data columns (total 7 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   Date      9971 non-null   object 
 1   SalesRep  9971 non-null   object 
 2   Region    9971 non-null   object 
 3   Product   9971 non-null   object 
 4   Color     9971 non-null   object 
 5   Units     9971 non-null   int64  
 6   Revenue   9971 non-null   float64
dtypes: float64(1), int64(1), object(5)
memory usage: 545.4+ KB

# Check if we have any missing entry
df.isna().sum()

Date        0
SalesRep    0
Region      0
Product     0
Color       0
Units       0
Revenue     0
dtype: int64

No entry is missing

Revenue by region

region_revenue = pd.DataFrame(df.groupby(by=['Region'])['Revenue'].sum())
region_revenue.sort_values(ascending=False, by='Revenue')

Visualize region revenue impact

region_revenue.plot(kind='bar', ylabel='Revenue', title='Region revenue impact');

It's very clear that the West Region generated the most revenue

Revenue by sales Rep

sales_rep_revenue = df.groupby(by=['SalesRep'])['Revenue'].sum()
sales_rep_revenue = pd.DataFrame(sales_rep_revenue).sort_values(ascending=True, by='Revenue')
sales_rep_revenue

Visualize salesRep revenue impact

sales_rep_revenue.plot(kind='bar', ylabel='Revenue', title='SalesRep revenue impact');

print('Mike Slightly beat Julie in revenue generation by ' + str(round(((sales_rep_revenue.loc['Mike'] - sales_rep_revenue.loc['Julie']) / sales_rep_revenue.loc['Julie']) * 100, 2))+'%')

Mike Slightly beat Julie in revenue generation by Revenue    0.55
dtype: float64%

Revenue by products

product_revenue = df[['Units', 'Revenue','Product']].groupby('Product').sum().sort_values(ascending=False,by='Units')
product_revenue

Visualize of Products revenue impact

product_revenue.groupby(by=['Product'])['Revenue'].sum().sort_values(ascending=True).plot(
                                                                                          kind='bar',ylabel='Revenue',title='Product Revenue');

Sales trend

# Convert the date column to a datetime object
df['Date'] = pd.to_datetime(df['Date'])
df['Year'] = df['Date'].dt.year
df['Month'] = df['Date'].dt.month
df['Day']  = df['Date'].dt.day
# df = df.drop('Date',axis=1)

Plot Yearly Sales Chart/Trend

years = [unique for unique in df.Year.unique()]
years

[2015, 2016, 2017]

def plot_trend(years:list, df):
    for year in years:
        new_df = df[df['Year'] == year]
        new_df.groupby('Date')['Revenue'].sum().plot(linewidth=1.2, 
                                             ylabel='Revenue', 
                                             xlabel='Date', 
                                             title='Sales Trend');

import matplotlib.patches as patches

year1 = patches.Patch(color='blue', label='2015')
year2 = patches.Patch(color='green', label='2016')
year3 = patches.Patch(color='red', label='2017')
plot_trend(years, df)
plt.legend(handles=[year1,year2,year3], loc=2);

The trend plot looks symmetrical for the months of October in 2017 and 2018 respectively.
This shows that most sales are made within the month of October. what could have influenced this?

ax = df[['Month', 'Units', 'Revenue']].groupby('Month').sum().plot(
                                                                 title='Monthly Sales Trend', 
                                                                 ylabel='Revenue',
                                                                 );
ax.vlines(10,1,300000, linestyles='dashed')
ax.annotate('Oct',(10,0));

How many times was entry made in each month?

df['Month'].value_counts().sort_values().plot(kind='bar', xlabel='Month', ylabel='Number of Entries', title='Monthly Entries');

products = pd.DataFrame(df[['Units','Revenue','Product','Month', 'Region']].groupby('Month')['Product'].value_counts())
products

products['No_of_products'] = products['Product']
products.drop('Product', inplace=True, axis=1)
products = products.reset_index()

products

products = products.pivot_table(values=['No_of_products'], index=['Month'], columns=['Product'], aggfunc= np.sum)
products

products.plot(ylabel='No of Products sold', title='Monthly product sales');

It's clear now that we sell more Bellen during october and that and HEAVY sales kick off between September and late November

Region Monthly Revenue

region_sales = pd.DataFrame(df[['Units','Revenue','Product','Month', 'Region']]).groupby(['Month','Region'])['Revenue'].sum()
region_sales = pd.DataFrame(region_sales)
region_sales

region_sales = region_sales.reset_index()
region_sales = region_sales.pivot_table(values=['Revenue'], index=['Month'], columns=['Region'], aggfunc= np.sum)
region_sales

region_sales.plot(kind='bar', ylabel='Revenue', title='Region Monthly Revenue');

Yearly changes in revenue

changes = pd.DataFrame(df.groupby([df.Date.dt.year])['Revenue'].sum())
changes

changes.sort_values('Date').plot(kind='bar');

Top 3 products

product_revenue

Our top 3 products are Bellen, Quad and Sunbell

The most productive sales Rep in the respective years. From your analysis, give 3 recommendations you think would help the company increase revenue in the following year.

salesReps = df[['SalesRep','Year','Revenue','Units']]
salesReps = pd.DataFrame(salesReps.groupby(['Year','SalesRep'])['Revenue'].sum())
salesReps.sort_values(by=['Year','Revenue'], ascending=False)

Julie Stands out in sales revenue yearly except in 2016

Conclusion/Recommendation:

1. The best months for sales are September, October and November. The company should look into creating jingles during these periods to further maximize profit.
2. Focus the ad targeted audience on East and South Regions
3. Finally Bellen and Quad sell most during these periods consider getting more of them.