Supermarket Sales: RFM Analysis & EDA

Introduction

The growth of supermarkets in most populated cities are increasing and market competitions are also high. The dataset is one of the historical sales of supermarket company which has recorded in 3 different branches for 3 months data , So we aim to help sales manager to know more about products` sales and customer attributes to increase sales and make higher profit

dataset source : https://www.kaggle.com/datasets/aungpyaeap/supermarket-sales

Invoice id: Computer generated sales slip invoice identification number

Branch: Branch of supercenter (3 branches are available identified by A, B and C).

City: Location of supercenters

Customer type: Type of customers, recorded by Members for customers using member card and Normal for without member card.

Gender: Gender type of customer

Product line: General item categorization groups - Electronic accessories, Fashion accessories, Food and beverages, Health and beauty, Home and

lifestyle, Sports and travel

Unit price: Price of each product in dolar ($)

Quantity: Number of products purchased by customer

Tax: 5% tax fee for customer buying

Total: Total price including tax

Date: Date of purchase (Record available from January 2019 to March 2019)

Time: Purchase time (10am to 9pm)

Payment: Payment used by customer for purchase (3 methods are available – Cash, Credit card and Ewallet)

COGS: Cost of goods sold

Gross margin percentage: Gross margin percentage

Gross income: Gross income

Rating: Customer stratification rating on their overall shopping experience (On a scale of 1 to 10)

Data Wrangling

There is no invalid data Max gross is 49.65, Avg gross is 15.3 , Min gross is 0.5 Max Total is 1042.65 , Avg Total is 322.96 , Min Total is 10.678 Math Note : tax/total = gross mergin percentage / gross income thats why 5/100 = 4.761905 / 95

Data types

lets change Time and Date columns to datatime type

#Changing Data Types to 'datatime64 ns df['Date']=pd.to_datetime(df['Date']) df['Time']=pd.to_datetime(df['Time'])

Missing values

There is no Missing Values in the dataset

Duplicate rows

=> the data is clean

Structuring the data

We won't need the column Invoice ID column So we will drop it

#Dropping Invoice ID Column df=df.drop('Invoice ID', axis=1)

#Making a column for Month df['month']=df['Date'].dt.month_name()

#Making a column for Month Number(it will be useful in sorting) df['Month_Number'] = df['Date'].dt.month

df['Hour']=df['Time'].dt.hour

df['Hour'].describe()

So the supermarkets opens from 10 am to 8 pm

time=[] for i in df['Hour']: if i >9 and i <12: time.append('Morning') elif i>12 and i<18: time.append('Afternoon') else: time.append('Evening') df['Part_of_day']=time

Now We don`t need Time and Hour columns , we will drop them

# Dropping some useless Columns df=df.drop(['Time','Hour'],axis=1)

Let's Check if there 's any changing in groos margin percentage

There is NO changing in gross margin percentage , so it is useless for us we can drop this column too

df=df.drop('gross margin percentage',axis=1)

Myanmar capital Naypyidaw ,but it seems that there is an error in writing , so we will replace it

EDA and Analysis

Data Visualisation

Which product line has the highest average rating?

There is no big difference between ratings for each product line Food and Beverages has the best rating , while Home and lifestyle has the worst rating

Which product line has the highest average sales?

Home and lifestyle , Sports and travel have average sales more than the general average

Food and beverages, Health and beauty have average sales close to the general average

Fashion accessories and Electronic accessories have average sales less than the general average

How many quantities did we sell in the last 3 months?

In January , We have sold the highest quantity , then March , then February

In which month have we achieved the highest sales?

=>We have made 322k dollars sales in last 3 months . =>The highest sales were in January with 116k $

What is the distribution of customer type ?

=> The counts of Members is almost equal to the number of normal Customers

Which part of the day has the highest gross income average ?

=> The best average gross income is in afternoon ,then evening and Morning

Which City has the highest gross income average ?

=> The average gross income increases in Naypyidaw city => Yangon city has the lowest gross income average

Which part of the day has the highest gross income average city-wise?

The highest gross income average is in Naypyidaw city in the afternoon The lowest gross income average is in Yangon city in the evening Our customers in Yangon city prefer purchase in the Afternoon Our customers in Mandalay city prefer purchase in the Morning Our customers in Naypyidaw city prefer purchase in the evening

What is the distribution of Payment ?

Male and Female Customers have an equal distribution E-wallet and Cash the most common payment ways, while Credit Card are less common

What is the most common payment way per Gender?

From the last bar chart , We can see that Males often pay with E wallet Women prefer pay with Cash

What is the most common Product lines per Customer Type?

Member Customers prefer buy Food and beverages , Sports and Travel Normal (non-member) Customers prefer buy electronic and fashion accessories

What are the interests of each Gender ?

From the last stacked bar chart , we can see that Females are interested in Fashion accessories , they aren`t interested in Health and Beauty Products Males are interested in all categories , but they are interested in Health and Beauty the most

What are the Product lines sales Gender-wise?

Which Gender has had the highest total sales ?

Female paid more than men in last three months

Which Gender has had the highest total sales City-wise?

Females paid more than Males in Naypyidaw city Females total purchases is close to Males total purchases in the other two cities

Which Product line has had the highest average sales City-wise?

In city Yangon, customers spend on Health and Beauty the least and Home and Lifestyle the most. In city Mandalay, customers spend on Food and Beverages the least and Sports and travel the most. In city Naypyidaw, customers spend on Home and lifestyle the least and Food and Beverages the most

Which branch has had the highest average sales?

Branch C has the highest average sales