Data Overview
The dataset contains sales data for various customers, including order details, sales amounts, product lines, and deal sizes. The dataset has 25 columns and 2823 entries. Key columns include ORDERNUMBER
, QUANTITYORDERED
, PRICEEACH
, SALES
, ORDERDATE
, STATUS
, PRODUCTLINE
, CUSTOMERNAME
, COUNTRY
, DEALSIZE
, and others.
Data Preparation
Data Loading:
The dataset was loaded from a CSV file using
pandas
.Data was encoded with 'latin1' to handle special characters.
Date Conversion:
- The
ORDERDATE
column was converted to datetime format for time-based analysis.
- The
Sales Trends Analysis
Sales by Year:
2003: $3,516,979.54
2004: $4,724,162.60
2005: $1,791,486.71
Sales by Quarter:
Sales peak in Q4 each year.
Highest quarterly sales were in Q4 of 2004 ($2,014,774.92).
Sales by Month:
Sales peak in November each year.
Highest monthly sales were in November 2003 ($1,029,837.66) and November 2004 ($1,089,048.01).
Top-Selling Products and Categories
Top 10 Products:
S18_3232: $288,245.42
S10_1949: $191,073.03
Top 10 Categories:
Classic Cars: $3,919,615.66
Vintage Cars: $1,903,150.84
Customer Segmentation
Customer Sales Data:
| CUSTOMERNAME | TOTAL_SALES | TOTAL_ORDERS | MOST_COMMON_DEALSIZE | SALES_SEGMENT | ORDER_FREQUENCY_SEGMENT | | --- | --- | --- | --- | --- | --- | | AV Stores, Co. | 157807.81 | 3 | Medium | Very High | Medium | | Alpha Cognac | 70488.44 | 3 | Medium | Medium | Medium | | Amica Models & Co. | 94117.26 | 2 | Small | High | Low | | Anna's Decorations, Ltd | 153996.13 | 4 | Small | Very High | High | | Atelier graphique | 24179.96 | 3 | Medium | Low | Medium |
Customer Segmentation Summary:
| SALES_SEGMENT | ORDER_FREQUENCY_SEGMENT | MOST_COMMON_DEALSIZE | CUSTOMER_COUNT | AVG_TOTAL_SALES | AVG_TOTAL_ORDERS | | --- | --- | --- | --- | --- | --- | | Low | Low | Medium | 7 | 50956.38 | 2.00 | | Low | Low | Small | 9 | 47269.63 | 1.89 | | Low | Medium | Medium | 5 | 43937.33 | 3.00 | | Low | Medium | Small | 1 | 64591.46 | 3.00 | | Low | High | Medium | 1 | 33440.10 | 4.00 |
Key Insights
Sales Performance:
Sales increased significantly from 2003 to 2004 but dropped in 2005.
The highest sales were recorded in the fourth quarter and in November of each year.
Top Products and Categories:
Classic Cars and Vintage Cars are the top-selling categories.
Product S18_3232 is the top-selling product.
Customer Segmentation:
Customers are segmented into
Low
,Medium
,High
, andVery High
based on their total sales and order frequency.Medium and Very High segments are dominant in terms of sales volume.
Most common deal sizes are
Medium
andSmall
.
Recommendations
Focus on Top-Selling Products and Categories:
- Increase marketing and stock for top-selling products like S18_3232 and categories like Classic Cars.
Enhance Customer Engagement:
Develop targeted marketing strategies for
Very High
sales segment customers.Offer loyalty programs and discounts to
Medium
andHigh
segment customers to increase order frequency.
Analyze Sales Drop in 2005:
- Investigate reasons for the drop in sales in 2005 and implement corrective measures.
Optimize Inventory Management:
- Maintain optimal stock levels for top-selling products to prevent stockouts.
Improve Shipping and Delivery:
- Ensure timely shipping to maintain customer satisfaction and encourage repeat purchases.
By leveraging these insights and recommendations, the company can enhance its sales strategies, improve customer satisfaction, and optimize inventory management.
Complete code
Comprehensive Guide to Sales Analysis and Reporting