Excel_Sales_Project

Overview

Used Python (Pandas) to clean a years worth of sales data from a mock technology retail company containing over 180,000 rows.
Visualized and gained insights from data by building a dashboard through Excel which included sales by city, product, and time.

Original Data Formatting

Without cleaning and formatting the data, no insights could have been gained. The data came in individual month .csv files with many blanks and duplicated values.

Original formating

Data Preparation

Tasks completed during preparations step:

Combine the 12 individual .csv files.
Clean rows with blanks and duplicates.
Split columns with multiple pieces of information such as date/time or purchaser’s address.
Create a new copy with only the columns necessary for analysis.
Categorize product types to aid in future analysis and visualization.

View code Here

Import Cleaning

Create and separate columns

Export

Analysis

Insights:

October and December had the highest sales of the year.
12:00 and 19:00 were the times with the highest sales.
Cell phones, laptops, and headphones accounted for over 70% of sales.
San Francisco, Los Angeles, and New York accounted for 53% of total sales.
Apple products accounted for 55% of total sales.

Actionable steps for the business:

Look at the current strategy for attaching accessories to a purchase. Better store positioning, add-on deals, or website recommendations could boost attach rate. This should also include adding more accessories based on market research that pair well with the site’s most sold products: iPhone, Google phone, MacBook Pro, and ThinkPad.
Focusing marketing efforts on the typical customer during a given period such as a students/parents during back to school season should increase sales year-over-year.

Points for further analysis:

What deals were active during October and December? What part did back to school season and holiday have on the best months?
What is the typical customer profile? Student or business?
Gaining access to data for the profit/loss of products would help decide on which products to remove and which to keep stocked.